Generate header when exporting data to excel

Question

Generate header when exporting data to excel

Navigation

#1 by (2 votes)

7

I did an export routine for excel , using StreamWriter , but it gives me a problem. I can not get the name of the columns that come in the select. For me to do the header, I had to put it in the hand. This would not be the problem, the issue is when opening the file, it gives a message that the file is not compatible, but then it opens normally. Does anyone have a different path? Below is the code that generates excel, based on a select in Oracle .

protected void btnExport_Click(object sender, EventArgs e)
        {
            string caminho = @"C:\Teste_Xls\ListaCliente.xlsx";
            string error = string.Empty;

            DataExport dataExport = new DataExport();

            var connection = ConfigurationManager.ConnectionStrings["DTOP"].ConnectionString;

            OracleConnection conn = new OracleConnection(connection);

            using (StreamWriter sw = File.CreateText(caminho))
            {
                using (var connectionOracle = new OracleConnection(connection))
                {
                    try 
                    {
                        var cmd = new OracleCommand(query, conn);
                        conn.Open();
                        using (OracleDataReader reader = cmd.ExecuteReader())
                        {
                            sw.WriteLine("Data Inclusao" + "\t" + "Tipo de Tabela" + "\t" + "Tabela" + "\t" + "Codigo" + "\t" + "TUSS");
                            while (reader.Read())
                            {
                                DateTime dtInclusao = Convert.ToDateTime(reader[0]);
                                string tipoTabela = reader[1].ToString();
                                string tabela = reader[2].ToString();
                                Int64 codigo = Convert.ToInt64(reader[3]);
                                string tuss = reader[4].ToString();

                                sw.WriteLine(dtInclusao + "\t" + tipoTabela + "\t" + tabela + "\t" + codigo + "\t" + tuss);
                            }
                        }
                    }
                    catch(Exception ex)
                    {
                        error = ex.Message;
                    }

                }


            }
            //CreateExcelFile.CreateExcelDocument(listaDataExport, @"C:\Teste_Xls\ListaCliente.xlsx");
        }

c# oracle excel

asked by anonymous 12.01.2016 / 11:21

1 answer

Send via Json data to the controller How to put side menus on ionic?

score 2 · Answer 1

I'll show you two different modes. This question happened to me and I solved it with mode 1. After your question, I went in search of a new path and implemented Method 2 in my application.

1) The simplest: Instead of saving as .xlsx, save as .csv, and consequently divide the fields with ";". The file will open without showing any message.

2) More complete: In this case, we will use Microsoft.Office.Interop.Excel and we will be able to save the data in cell by cell.

Add the reference [my version is the one from the image, yours may be different]:

Andimplementthecode

Microsoft.Office.Interop.Excel.ApplicationClassexcelApp=newMicrosoft.Office.Interop.Excel.ApplicationClass();excelApp.Visible=false;objectoMissing=System.Reflection.Missing.Value;Microsoft.Office.Interop.Excel.Workbookbook=excelApp.Workbooks.Add(oMissing);//CriandoumaplanilhaMicrosoft.Office.Interop.Excel.WorksheetexcelSheet=(Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.Add(oMissing,oMissing,oMissing,oMissing);//Definindooformatodesaída[opcional]excelApp.DefaultSaveFormat=Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12;//NomeandoaplanilhaexcelSheet.Name="Pesquisa";

The code above will create a file of the type that you define and the section below will be the data entry in worksheet 1.

//Salva na primeira linha, o cabeçalho da planilha
string[] cabecalho = {"Data Inclusao", "Tipo de Tabela", "Tabela", "Codigo,TUSS"};
for (int i = 0; i < cabecalho.Length; i++)
{
excelSheet.Cells[1, i + 1] = cabecalho[i];
}
//Salva o conteúdo da tabela nas células
int linha = 0;
while (reader.Read())
{
    linha++;                          
    //Cells[linha, coluna]
    excelSheet.Cells[linha, 1] = Convert.ToDateTime(reader[0]);
    excelSheet.Cells[linha, 2] = reader[1].ToString();
    excelSheet.Cells[linha, 3] = reader[2].ToString();
    excelSheet.Cells[linha, 4] = Convert.ToInt64(reader[3]);
    excelSheet.Cells[linha, 5] = reader[4].ToString();                           
}
//Finalize salvando e fechando o excel da memória.
excelSheet.SaveAs(caminho);
excelApp.Quit();

Note that we will no longer need StreamWriter .