Best way to export data from a Datatable to Excel using C#

Question

User-1382746017 posted

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?

Answer 1

User2103319870 posted

Osceria

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?

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

using ClosedXML;

Answer 2

User-1382746017 posted

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?

Answer 3

User2103319870 posted

osceria

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-Package DocumentFormat.OpenXml

You can check the below link to get details on how to install the dll using Package Manager Console

• Finding and Installing a NuGet Package Using the Package Manager Console

Answer 4

User-1382746017 posted

What are the other easiest ways to do this without any open source?

Answer 5

User-271186128 posted

Hi Osceria,

What are the other easiest ways to do this without any open source?

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

Answer 6

User526852946 posted

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.

Answer 7

User426857743 posted

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.

Answer 8

User2111221455 posted
I have good luck with EPPlus. I've been able to make some really complicated spreadsheet reports with it.