EXPORTAR DATASET A EXCEL CON CarlosAg.dll

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