Generate header when exporting data to excel

Question

I did an export routine for the excel, using StreamWriter, but it causes me a problem. I can’t get the name of the columns that come in select. To make the header, I had to put it in the hand. This would not be the problem, the question 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 from this? Below 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");
        } 

Answer 1

I’ll show you two different ways. This question happened to me and I solved it with mode 1. After your question, I went in search of a new way and implemented method 2 in my application.

1) The simplest of these: 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 that case, we will make use of Microsoft.Office.Interop.Excel and we will have the possibility to save the data in cell by cell.

Add the reference [my version is that of the image, yours may be different]:

And implement the code

Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = false;
object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook book = excelApp.Workbooks.Add(oMissing);
//Criando uma planilha
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
//Definindo o formato de saída [opcional]
excelApp.DefaultSaveFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12;
//Nomeando a planilha
excelSheet.Name = "Pesquisa";

The above code will create a file of type [that you define] and the section below, will be the release of the data in sheet 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.