http://www.carlosag.net/tools/excelxmlwriter/
Imports CarlosAg.ExcelXmlWriter
Public Sub returnDatasetSQL(queryString As String, cadenaCONN As String, ByRef ds As DataSet)
Try
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim da As SqlDataAdapter
myConnection = New SqlConnection(cadenaCONN)
myConnection.Open()
myCommand = New SqlCommand(queryString, myConnection)
da = New SqlDataAdapter(myCommand)
ds = New DataSet()
da.Fill(ds)
myConnection.Close()
Catch ex As Exception
writeInLogFile(ex.Message, "error")
End Try
End Sub
Private Sub loadEXCEL(sessionID As String, CONN As String)
Dim query As String = "Select distinct(headerMISDATOS),nMISDATOS from MISDATOS where SessionID='" & sessionID & "' order by nMISDATOS "
Dim DS As New DataSet
returnDatasetSQL(query, CONN, DS)
For i = 0 To DS.Tables(0).Rows.Count - 1
ReDim Preserve headerDATOS(i)
headerDATOS(i) = DS.Tables(0).Rows(i).Item("headerMISDATOS")
Next
query = "Select * from MISDATOS where SessionID='" & sessionID & "' order by nMISDATOS"
returnDatasetSQL(query, CONN, DS)
Try
If DS.Tables.Count > 0 Then
If DS.Tables(0).Rows.Count > 0 Then
Dim book As New Workbook()
book.Properties.Author = "Yo Mismo"
book.Properties.Title = "Mi Excel"
book.Properties.Created = DateTime.Now
Dim style As WorksheetStyle = book.Styles.Add("HeaderStyle")
style.Font.Bold = True
Dim style2 As WorksheetStyle = book.Styles.Add("DefaultStyle")
style2.Font.Bold = False
Dim style3 As WorksheetStyle = book.Styles.Add("FooterStyle")
style3.Font.Italic = True
Dim styleGREEN As WorksheetStyle = book.Styles.Add("GreenStyle")
styleGREEN.Font.Color = "#000000"
styleGREEN.Interior.Color = "#31B404"
styleGREEN.Interior.Pattern = StyleInteriorPattern.Solid
Dim styleYELLOW As WorksheetStyle = book.Styles.Add("YellowStyle")
styleYELLOW.Font.Color = "#000000"
styleYELLOW.Interior.Color = "#FFBF00"
styleYELLOW.Interior.Pattern = StyleInteriorPattern.Solid
Dim styleRED As WorksheetStyle = book.Styles.Add("RedStyle")
styleRED.Font.Color = "#FFFFFF"
styleRED.Interior.Color = "#FE2E2E"
styleRED.Interior.Pattern = StyleInteriorPattern.Solid
createWorksheet(book, DS, "MIS DATOS EN LIBRO 1")
createWorksheet(book, DS, "MIS DATOS EN LIBRO 2")
book.Save(System.AppDomain.CurrentDomain.BaseDirectory & "DATA_SAVED/" & sessionID & ".xls")
End If
End If
Catch ex As Exception
writeInLogFile(ex.Message, "error")
End Try
End Sub
Sub createWorksheet(book As Workbook, ds As DataSet, NameLIBRO As String)
Dim sheet As Worksheet = book.Worksheets.Add(NameLIBRO)
Dim rowHeader As WorksheetRow = sheet.Table.Rows.Add()
rowHeader.Cells.Add(New WorksheetCell("A continuación la tabla de MIS DATOS", "HeaderStyle"))
rowHeader = sheet.Table.Rows.Add()
Dim _i As Integer
For _i = 0 To headerDATOS.Length - 1
rowHeader.Cells.Add(New WorksheetCell(headerDATOS(_i), "HeaderStyle"))
Next
rowHeader = Nothing
Dim row As New WorksheetRow
For i = 0 To ds.Tables(0).Rows.Count - 1
If CDbl(ds.Tables(0).Rows(i).Item("valor")) < 70 Then
backcolor = "RedStyle"
Else
If CDbl(ds.Tables(0).Rows(i).Item("valor")) >= 70 Then
If CDbl(ds.Tables(0).Rows(i).Item("valor")) >= 100 Then
backcolor = "GreenStyle"
Else
backcolor = "YellowStyle"
End If
End If
End If
row.Cells.Add(ds.Tables(0).Rows(i).Item("valor"), DataType.Number, backcolor)
Next
End Sub
TRABAJANDO CON BLOBS DE AZURE
'No olvides referenciar Microsoft.WindowsAzure.StorageClient
Imports Microsoft.WindowsAzure
Imports Microsoft.WindowsAzure.StorageClient
Private Function enviarBLOB() As Boolean
Dim Path As String = "C:\Temp\prova.pdf"
Dim FS As FileStream = File.OpenRead(Path)
Dim StorageAccount As CloudStorageAccount
Dim BlobClient As CloudBlobClient
Dim BlobContainer As CloudBlobContainer
Dim sendOK As Boolean = False
Try
StorageAccount = CloudStorageAccount.Parse("DefaultEndpointsProtocol=https;AccountName=" & AccountName & ";AccountKey=" & AccountKey)
BlobClient = StorageAccount.CreateCloudBlobClient
BlobClient.Timeout = New System.TimeSpan(1, 0, 0)
BlobClient.ParallelOperationThreadCount = 2
BlobContainer = BlobClient.GetContainerReference(ContainerName)
Dim myGUID As System.Guid = System.Guid.NewGuid()
Dim sGUID As String = myGUID.ToString()
Dim Blob As CloudBlob = BlobContainer.GetBlobReference(sGUID)
Blob.UploadFromStream(FS)
Blob.Metadata("no") = "12134"
Blob.Metadata("Nombre") = "Pepe"
Blob.Metadata("timestamp") = Now
Blob.SetMetadata()
Blob.Properties.ContentType = "application/pdf"
Blob.Properties.ContentEncoding = "base64"
Blob.SetProperties()
sendOK = True
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Return sendOK
End Function
Private Function urlBLOB_paraDescarga(ContainerName As String, Filename As String, AccountName As String, AccountKey As String, accessMinutesExpiry As Integer) As String
Dim StorageAccount As CloudStorageAccount
Dim BlobClient As CloudBlobClient
Dim BlobContainer As CloudBlobContainer
Dim Options As New BlobRequestOptions
StorageAccount = CloudStorageAccount.Parse("DefaultEndpointsProtocol=https;AccountName=" & AccountName & ";AccountKey=" & AccountKey)
BlobClient = StorageAccount.CreateCloudBlobClient
BlobClient.Timeout = New System.TimeSpan(1, 0, 0)
BlobClient.ParallelOperationThreadCount = 2
BlobContainer = BlobClient.GetContainerReference(ContainerName)
Dim Blob As CloudBlob = BlobContainer.GetBlobReference(Filename)
Dim SHaccesPolicy As New SharedAccessPolicy()
SHaccesPolicy.SharedAccessExpiryTime = DateTime.UtcNow.AddMinutes(accessMinutesExpiry)
SHaccesPolicy.Permissions = SharedAccessPermissions.Read
Dim signature As String = Blob.GetSharedAccessSignature(SHaccesPolicy)
Return Blob.Uri.AbsoluteUri + signature
End Function
Suscribirse a:
Entradas (Atom)