Convert Datatable to Excel

Rahul Kaushik

I have datatable with 28,000 rows and 518 columns. How should I convert the Datatable to Excel in Faster way. P.s. This is a console application not Asp.net application.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
Please refer the below link, it might helps you
https://www.c-sharpcorner.com/UploadFile/deveshomar/exporting-datatable-to-excel-in-C-Sharp-using-interop/
Jaimin Shethiya7y ago
Hello Rahul,

can you try below code for generate excel file.

Added nuget package for generate the excel file. ClosedXML

command for install: Install-Package ClosedXML

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

let me know if you have anything else.

Thanks.
Amit Gupta7y ago
I haven't tried more than 255 columns but you can achieve this by creating csv file and then export to excel :), try this trick will help you to overcome the boundation of column limit
Rahul Kaushik7y ago
OleDB only supports 255 columns
Sapna Girish7y ago
refer to the following link

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html
Amit Gupta7y ago
Use oledb connection to export, its much faster than interop reference