Convert Datatable to Excel

Question

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.

Jaimin Shethiya · Answer

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.

Mageshwaran R · Answer

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/

Amit Gupta · Answer

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 Kaushik · Answer

OleDB only supports 255 columns

Sapna Girish · Answer

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 Gupta · Answer

Use oledb connection to export, its much faster than interop reference

Convert Datatable to Excel

Answers (6)