Insert data into excel sheet from DataTable

Question

I retrieve the data from the database in a DataTable and display it by binding it to the Repeater control. Now I need to copy the same data to an Excel spreadsheet. How can I use the same DataTable to populate the spreadsheet. Please suggest.

Answer 1

I would suggest creating a real excel file instead of an html table (which many people do). Therefore, I can recommend EPPlus ( LGPL license ).

Then it's easy. Assuming you have a BtnExportExcel button:

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

Answer 2

You can create a CSV file. It is quick and easy, and allows you to open it in excel.

This post applies to your:

Convert DataTable to CSV Stream