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