Using C#, which is the best and easiest way to export all the data from a datatable to an excel to get it downloaded?
If you are OK with adding Open source dll then you can use
ClosedXML. I have tried the code provided in this
discussion and it works fine.
Prerequisites:
You need to have the DocumentFormat.OpenXml 2.5.0 dll for ClosedXML to work. You can add this dll to your project using Nugget
PM> Install-Package DocumentFormat.OpenXml
Once you added above dll then you can add the
ClosedXML dll to your solution
Sample Code
DataTable table = new DataTable();
table.Columns.Add("SampleColumn", typeof(string));
// Add Three rows with those columns filled in the DataTable.
table.Rows.Add("0");
table.Rows.Add("1");
table.Rows.Add("2");
table.Rows.Add("3");
table.Rows.Add("1");
table.Rows.Add("2");
table.Rows.Add("3");
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
wbook.Worksheets.Add(table, "tab1");
// Prepare the response
HttpResponse httpResponse = Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Provide you file name here
httpResponse.AddHeader("content-disposition", "attachment;filename=\"Samplefile.xlsx\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wbook.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
Ensure that you have added the below reference to your page prior to using above code
I have downloaded and added the dll - ClosedXML.dll in my project. But when I run the application, I get this error
Could not load file or assembly 'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
Could not load file or assembly 'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
Can you help?
You need to have the DocumentFormat.OpenXml 2.5.0 dll for ClosedXML to work. You can add this dll to your project using Nugget here
PM>Install-PackageDocumentFormat.OpenXml
You can check the below link to get details on how to install the dll using Package Manager Console
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
First, avoid building html with xls extension and export real Excel files (xlsx or xls). Besides not being an elegant solution, when the file is opened a warning is raised by MS Excel that the file format is invalid.
Also, Interop should be avoided on server side. See
considerations for server side Excel automation from Microsoft point of view. Interop and Automation comes with a full list of problems on server-side.
The best solution is to use an Excel library even if you will have to include an external dll in your application.
EasyXLS is a practical alternative.
Code sample:
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable);
ExcelDocument workbook = new EasyXLS.ExcelDocument();
ExcelWorksheet sheet = new ExcelWorksheet(“Sheet name”);
sheet.easy_insertDataSet(ds, false);
string fileName = "Excel.xls";
Response.AppendHeader("content-disposition", "attachment; filename=" + fileName);
Response.ContentType = "application/octetstream";
Response.Clear();
workbook.easy_WriteXLSFile(Response.OutputStream);//or easy_WriteXLSXFile for xlsx file
workbook.Dispose();
Response.End()
Another solution is to use SpreadsheetML, the XML schema for Excel file, but this is not so handy and implicates more code to write, but for exporting only data from the datatable, with no cell formatting, is an easier task.
This code is based on a .NET Excel library, you can get the
free version of it from Nuget and then reference the dll in your project. The free version has no limitations on .xlsx files.
Member
20 Points
63 Posts
Best way to export data from a Datatable to Excel using C#
Sep 03, 2015 01:15 PM|Osceria|LINK
Hi,
Using C#, which is the best and easiest way to export all the data from a datatable to an excel to get it downloaded?
All-Star
50841 Points
9895 Posts
Re: Best way to export data from a Datatable to Excel using C#
Sep 03, 2015 01:42 PM|A2H|LINK
If you are OK with adding Open source dll then you can use ClosedXML. I have tried the code provided in this discussion and it works fine.
Prerequisites:
You need to have the DocumentFormat.OpenXml 2.5.0 dll for ClosedXML to work. You can add this dll to your project using Nugget
Once you added above dll then you can add the ClosedXML dll to your solution
Sample Code
DataTable table = new DataTable(); table.Columns.Add("SampleColumn", typeof(string)); // Add Three rows with those columns filled in the DataTable. table.Rows.Add("0"); table.Rows.Add("1"); table.Rows.Add("2"); table.Rows.Add("3"); table.Rows.Add("1"); table.Rows.Add("2"); table.Rows.Add("3"); ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook(); wbook.Worksheets.Add(table, "tab1"); // Prepare the response HttpResponse httpResponse = Response; httpResponse.Clear(); httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //Provide you file name here httpResponse.AddHeader("content-disposition", "attachment;filename=\"Samplefile.xlsx\""); // Flush the workbook to the Response.OutputStream using (MemoryStream memoryStream = new MemoryStream()) { wbook.SaveAs(memoryStream); memoryStream.WriteTo(httpResponse.OutputStream); memoryStream.Close(); } httpResponse.End();Ensure that you have added the below reference to your page prior to using above code
Aje
My Blog | Dotnet Funda
Member
20 Points
63 Posts
Re: Best way to export data from a Datatable to Excel using C#
Sep 03, 2015 02:01 PM|Osceria|LINK
Hi,
I have downloaded and added the dll - ClosedXML.dll in my project. But when I run the application, I get this error
Could not load file or assembly 'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
Can you help?
All-Star
50841 Points
9895 Posts
Re: Best way to export data from a Datatable to Excel using C#
Sep 03, 2015 02:06 PM|A2H|LINK
You need to have the DocumentFormat.OpenXml 2.5.0 dll for ClosedXML to work. You can add this dll to your project using Nugget here
You can check the below link to get details on how to install the dll using Package Manager Console
Aje
My Blog | Dotnet Funda
Member
20 Points
63 Posts
Re: Best way to export data from a Datatable to Excel using C#
Sep 04, 2015 05:29 AM|Osceria|LINK
What are the other easiest ways to do this without any open source?
All-Star
45489 Points
7008 Posts
Microsoft
Re: Best way to export data from a Datatable to Excel using C#
Sep 24, 2015 05:47 AM|Zhi Lv - MSFT|LINK
Hi Osceria,
As for this issue, you could also try to export to excel using Interop. Here are some relevant articles, you could refer to them:
http://www.c-sharpcorner.com/UploadFile/deveshomar/exporting-datatable-to-excel-in-C-Sharp-using-interop/
http://www.codeproject.com/Reference/753207/Export-DataSet-into-Excel-using-Csharp-Excel-Inter
Besides, you could also try to use the following article:
http://www.codeproject.com/Articles/406704/Export-DataTable-to-Excel-with-Formatting-in-Cshar
Best regards,
Dillion
Member
20 Points
2 Posts
Re: Best way to export data from a Datatable to Excel using C#
Aug 10, 2017 02:24 PM|Popove|LINK
First, avoid building html with xls extension and export real Excel files (xlsx or xls). Besides not being an elegant solution, when the file is opened a warning is raised by MS Excel that the file format is invalid.
Also, Interop should be avoided on server side. See considerations for server side Excel automation from Microsoft point of view. Interop and Automation comes with a full list of problems on server-side.
The best solution is to use an Excel library even if you will have to include an external dll in your application. EasyXLS is a practical alternative.
Code sample:
DataSet dataSet = new DataSet(); dataSet.Tables.Add(dataTable); ExcelDocument workbook = new EasyXLS.ExcelDocument(); ExcelWorksheet sheet = new ExcelWorksheet(“Sheet name”); sheet.easy_insertDataSet(ds, false); string fileName = "Excel.xls"; Response.AppendHeader("content-disposition", "attachment; filename=" + fileName); Response.ContentType = "application/octetstream"; Response.Clear(); workbook.easy_WriteXLSFile(Response.OutputStream);//or easy_WriteXLSXFile for xlsx file workbook.Dispose(); Response.End()See also the simplest code to export datatable to Excel from C#.
Another solution is to use SpreadsheetML, the XML schema for Excel file, but this is not so handy and implicates more code to write, but for exporting only data from the datatable, with no cell formatting, is an easier task.
Member
50 Points
37 Posts
Re: Best way to export data from a Datatable to Excel using C#
Aug 11, 2017 02:39 AM|Leon Davis|LINK
I think this is the easiest way for exporting DataTable to Excel I've seen till now.
Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; sheet.InsertDataTable(datatable, true, 1, 1); workbook.SaveToFile("DataTable2Excel.xlsx", ExcelVersion.Version2013);This code is based on a .NET Excel library, you can get the free version of it from Nuget and then reference the dll in your project. The free version has no limitations on .xlsx files.
None
0 Points
1 Post
Re: Best way to export data from a Datatable to Excel using C#
Mar 15, 2018 03:46 AM|DBarsotta|LINK