Export DataSet to Excel with Spreadsheetgear

1

I'm trying to export 7 datatables to a worksheet, each datatable into a sheet using Spreadsheetgear. I'm having a hard time returning the data and exporting it to excel.

This is my DataSet where I load all datatables, but every time I run the program and I enter the dates when I get on the line RetornaPlanilha.Tables.Add(ListarRegistros(sb.ToString, oConn, "ATENDIMENTO")) Returns the error Argument Table can not 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 the line. I'm doing something wrong in this part, that I do not 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

Stripe Method Record

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
    
asked by anonymous 10.07.2015 / 12:43

1 answer

0

I believe you should create the datatable within 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
    
11.07.2015 / 08:24