Export Dataset to Excel with Spreadsheetgear

Question

I’m trying to export 7 datatables to a spreadsheet, each datatable into a sheet using Spreadsheetgear. I am having enough difficulty in returning the data and exporting them to excel.

This is my Dataset where I upload all the datatables, but every time I run the program and enter the dates when I arrive on the line RetornaPlanilha.Tables.Add(ListarRegistros(sb.ToString, oConn, "ATENDIMENTO")) Returns the error The Table Argument cannot be null.

    Function RetornaPlanilha(ByVal dataInicial As Date, ByVal dataFinal As Date) As DataSet

            oConn.ConnectionString = "Data Source = caoaportal; User ID = portalweb; Password = p#t2talw;"

            Try


                oConn.Open() 'Abre conexão
                RetornaPlanilha = New DataSet

                'ATENDIMENTO
                sb = New System.Text.StringBuilder
                sb.Append(" SELECT A.ID_ATENDIMENTO, ")
                sb.Append(" B.DS_ATENDIMENTO_STATUS, ")
                sb.Append(" C.DS_CANAL_COMUNICACAO, ")
                sb.Append(" FN_RETORNA_DESC_NIVEL_ATEND(A.ID_ATENDIMENTO) AS DS_CLASSIFICACAO, ")
                sb.Append(" DECODE (A.ID_CLIENTE_SIMPLIFICADO, 0, E.NM_CLIENTE, ES.NM_CLIENTE ) AS NM_CLIENTE, ")
                sb.Append(" E.NU_DOCUMENTO, ")
                sb.Append(" G.DS_TIPO_ANIMO_CLIENTE, ")
                sb.Append(" PE.NM_PESSOA AS NM_AGENTE, ")
                sb.Append(" PE.NU_CPF AS CPF_AGENTE, ")
                sb.Append(" DECODE(A.IN_UNIDADE_PARADA, 0, 'Não', 'Sim') AS UNIDADE_PARADA, ")
                sb.Append(" A.NU_VERSAO, ")
                sb.Append(" A.DT_ABERTURA, ")
                sb.Append(" A.DT_CONCLUSAO, ")
                sb.Append(" A.DT_PROXIMA_ACAO, ")
                sb.Append(" A.NU_KM_ATUAL, ")
                sb.Append(" A.DT_ULTIMA_INTERACAO, ")
                sb.Append(" A.NM_TITULAR as CONTATO, ")
                sb.Append(" A.NM_CONSULTOR, ")
                sb.Append(" A.NU_OS, ")
                sb.Append(" A.DT_OS, ")
                sb.Append(" A.NU_HOTLINE, ")
                sb.Append(" DECODE(A.IN_VEICULO_ALUGADO, 0, 'Não', 'Sim') as VEICULO_ALUGADO ")
                sb.Append(" FROM ATENDIMENTO    A, ")
                sb.Append(" ATENDIMENTOSTATUS   B, ")
                sb.Append(" CANALCOMUNICACAO    C, ")
                sb.Append(" CLIENTE             E, ")
                sb.Append(" CLIENTEATUALIZACAO  EA, ")
                sb.Append(" CLIENTESIMPLIFICADO ES, ")
                sb.Append(" AGENTE              F, ")
                sb.Append(" TIPOANIMOCLIENTE    G, ")
                sb.Append(" PESSOA PE ")
                sb.Append(" WHERE A.ID_ATENDIMENTO_STATUS = B.ID_ATENDIMENTO_STATUS ")
                sb.Append(" AND A.ID_CANAL_COMUNICACAO = C.ID_CANAL_COMUNICACAO ")
                sb.Append(" AND A.ID_CLIENTE = E.ID_CLIENTE ")
                sb.Append(" AND A.ID_CLIENTE = EA.ID_CLIENTE ")
                sb.Append(" AND A.ID_CLIENTE_SIMPLIFICADO = ES.ID_CLIENTE_SIMPLIFICADO ")
                sb.Append(" AND EA.IN_ATUAL = 1 ")
                sb.Append(" AND A.ID_AGENTE = F.ID_AGENTE ")
                sb.Append(" AND F.ID_PESSOA = PE.ID_PESSOA ")
                sb.Append(" AND A.ID_TIPO_ANIMO_CLIENTE = G.ID_TIPO_ANIMO_CLIENTE ")
                sb.Append(" AND A.DT_ULTIMA_INTERACAO BETWEEN TO_DATE('" & dataInicial & "', 'dd/MM/yyyy') and TO_DATE('" & dataFinal & "', 'dd/MM/yyyy') + 1 ")
                sb.Append(" ORDER BY A.ID_ATENDIMENTO ")
                RetornaPlanilha.Tables.Add(ListarRegistros(sb.ToString, oConn, "ATENDIMENTO"))

Return RetornaPlanilha


    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

    oConn.Close()
    oConn.Dispose()

End Function

Another problem I’m having is when I call the dataset to export it, on line. I’m doing something wrong in this part, which I didn’t quite understand what it is.

ds = workbook.GetDataSet(RetornaPlanilha(dataI, dataF).ToString, SpreadsheetGear.Data.GetDataFlags.None)

This is my main

Sub Main()

        oConn.ConnectionString = "Data Source = caoaportal; User ID = portalweb; Password = p#t2talw;"

        Dim dataI As String
        Dim dataF As String

        Console.WriteLine("Data Inicial")
        dataI = Console.ReadLine().ToString

        Console.WriteLine("Data Final")
        dataF = Console.ReadLine().ToString

        Console.WriteLine("Gerando Arquivo. Por favor aguarde!")
        'Define Workbook

        Dim workbookSet As SpreadsheetGear.IWorkbookSet = SpreadsheetGear.Factory.GetWorkbookSet()
        ' Create a new empty workbook in the workbook set.
        Dim workbook As SpreadsheetGear.IWorkbook = workbookSet.Workbooks.Add()

        'Define a sheet/aba 
        workbook.Worksheets("Sheet1").Name = "ATENDIMENTO"
        workbook.Worksheets.Add()
        workbook.Worksheets("Sheet2").Name = "CLASSIFICACAO"
        workbook.Worksheets.Add()
        workbook.Worksheets("Sheet3").Name = "EMPRESA"
        workbook.Worksheets.Add()
        workbook.Worksheets("Sheet4").Name = "INTERACAO"
        workbook.Worksheets.Add()
        workbook.Worksheets("Sheet5").Name = "PED_ITEM"
        workbook.Worksheets.Add()
        workbook.Worksheets("Sheet6").Name = "PRODUTO"
        workbook.Worksheets.Add()
        workbook.Worksheets("Sheet7").Name = "CLIENTE"


        'Define a primeira linha/coluna a ser vista de cada aba.
        Dim range As SpreadsheetGear.IRange
        range = workbook.ActiveWorksheet.Cells("A1")

        'Formata os campos númericos
        range.Cells.NumberFormat = "@"

        ds = workbook.GetDataSet(RetornaPlanilha(dataI, dataF).ToString, SpreadsheetGear.Data.GetDataFlags.None)

        workbook.ActiveWorksheet.UsedRange.Columns.AutoFit() 'Ajusta tamanho das colunas de acordo com a informação.
        workbook.SaveAs("C:\Users\Igor\Desktop\CargaSac.xls", SpreadsheetGear.FileFormat.Excel8) 'Salva workbook 
        Console.WriteLine("Arquivo Gerado!")

    End Sub

Listraregistro method

Function ListarRegistros(ByVal Instrucao As String, ByRef Conexao As OracleClient.OracleConnection, tableName as string) As DataTable

        Dim oDAD As OracleClient.OracleDataAdapter
        Dim oDSE As DataSet
        oDAD = New OracleClient.OracleDataAdapter(Instrucao, Conexao)
        oDSE = New DataSet
        oDAD.Fill(oDSE, tableName)
        ListarRegistros = oDSE.Tables("resultado")

    End Function

Answer 1

I believe you should create the datatable inside the dataset before inserting:

Dim ds As New DataSet
Dim dt = ds.Tables.Add("resultado")
Dim oCommand As New MySqlCommand(sSql.ToString, oConn)
Dim oAdapter As New MySqlDataAdapter
Try
    oAdapter.SelectCommand = oCommand
    oAdapter.Fill(ds, "resultado")
    Return ds
Catch ex As Exception
    Throw ex
End Try