Inserting data into an excel sheet from a DataTable

Question

I'm extracting data from the database into a DataTable and displaying it by binding it to a Repeater control. Now I need to copy the same data into an excel spreadsheet. How can i use the same DataTable to the fill the spreadsheet. Please suggest.

Duplicate Question.... : http://stackoverflow.com/questions/2800563/exporting-data-from-a-gridview-to-different-excel-worksheets — joshua, May 11 '12 at 08:03
@Parminder in here the excel spreadsheet is an existing one or you are creating a new one. If you are creating a new sheet you can also use [NPOI](http://npoi.codeplex.com/) — Vinay, May 11 '12 at 08:07

Tim Schmelter · Accepted Answer · 2012-05-16T23:51:56.133

I would suggest to create a real excel-file instead of a html table(what many people do). Therefor i can warmly recommend EPPlus(LGPL license).

Then it is simple. Assuming that you have a button BtnExportExcel:

protected void BtnExcelExport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
    try {
        var pck = new OfficeOpenXml.ExcelPackage();
        var ws = pck.Workbook.Worksheets.Add("Name of the Worksheet");
        // get your DataTable
        var tbl = GetDataTable();
        ws.Cells["A1"].LoadFromDataTable(tbl, true, OfficeOpenXml.Table.TableStyles.Medium6);
        foreach (DataColumn col in tbl.Columns) {
            if (col.DataType == typeof(System.DateTime)) {
                var colNumber = col.Ordinal + 1;
                var range = ws.Cells[1, colNumber, tbl.Rows.Count, colNumber];
                // apply the correct date format, here for germany
                range.Style.Numberformat.Format = "dd.MM.yyyy";
            }
        }
        var dataRange = ws.Cells[ws.Dimension.Address.ToString()];
        dataRange.AutoFitColumns();

        Response.Clear();
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;  filename=NameOfExcelFile.xlsx");
        Response.BinaryWrite(pck.GetAsByteArray());
    } catch (Exception ex) {
        // log exception
        throw;
    } 
    Response.End();
}

score 0 · Answer 2 · edited May 23 '17 at 12:26

0

You can create a .csv file. This is quick and easy, and makes it possible to open it in excel.

This post relates to yours:

Convert DataTable to CSV stream

edited May 23 '17 at 12:26

Community

1
1

answered May 11 '12 at 08:07

soberga

289
7
21

Inserting data into an excel sheet from a DataTable

2 Answers2

Linked