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

Question

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

Navigation

#1 by (1 votes)

1

I read sheet by sheet of a sheet, and I stored it in a DataSet . I'm having trouble passing this DataSet data to another worksheet. 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);

c# excel

asked by anonymous 11.09.2014 / 14:56

1 answer

How to decrease the response time of an unsuccessful connection when using Ksoap2? Alternative for File API for Outdated Browser

score 1 · Answer 1

Try using EPPlus for this . More simple.

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());
}