How to pass data from a Dataset to an Excel spreadsheet C#?

Question

I read sheet by sheet from a flat, and stored in a Dataset. I’m having trouble passing on to another flat this data from Dataset. Can someone help me ? I have the second code:

 conn.Open();
                string strConexao = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\"", localArquivo);
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                DataSet output = new DataSet();

                foreach (DataRow row in dt.Rows)
                {
                    // obtem o noma da planilha corrente
                    string sheet = row["TABLE_NAME"].ToString();
                    // obtem todos as linhas da planilha corrente
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                    cmd.CommandType = CommandType.Text;
                    // copia os dados da planilha para o datatable
                    DataTable outputTable = new DataTable(sheet);
                    output.Tables.Add(outputTable);
                    new OleDbDataAdapter(cmd).Fill(outputTable);

Answer 1

Try using Epplus for this. Simpler.

using (var excelPackage = new ExcelPackage())
{
    excelPackage.Workbook.Properties.Author = "Leandro Diaz";
    excelPackage.Workbook.Properties.Title = "Minha Planilha";

    foreach (DataTable table in SuasTables)
    {
        var sheet = excelPackage.Workbook.Worksheets.Add(table.TableName);
        sheet.Name = table.TableName;

        // Títulos
        var i = 1;
        foreach (DataColumn column in table.Columns)
        {
            sheet.Cells[1, i++].Value = column;
        }

        var rowIndex = 2;
        foreach (DataRow row in table.Rows)
        {
            var col = 1;
            foreach (DataColumn column in table.Columns)
            {
                sheet.Cells[rowIndex, col++].Value = row[column].ToString();
            }

            rowIndex++;
        }
    }

    string path = @"C:\teste.xlsx";
    File.WriteAllBytes(path, excelPackage.GetAsByteArray());
}