How to export excel from dataset or datatable in c #?

Question

I want to export data from a dataset or data table to an Excel file in C # without using Gridview.

Answer 1

Get More Ways: 9 Solutions to Export Data to Excel for ASP.NET

I have this code, but for this you need to enable the Excel Com component

Add the Microsoft.Office.Interop.Excel.dll link to your project will do the task for you.

 using Excel = Microsoft.Office.Interop.Excel; public static bool ExportDataTableToExcel(DataTable dt, string filepath) { Excel.Application oXL; Excel.Workbook oWB; Excel.Worksheet oSheet; Excel.Range oRange; try { // Start Excel and get Application object. oXL = new Excel.Application(); // Set some properties oXL.Visible = true; oXL.DisplayAlerts = false; // Get a new workbook. oWB = oXL.Workbooks.Add(Missing.Value); // Get the Active sheet oSheet = (Excel.Worksheet)oWB.ActiveSheet; oSheet.Name = "Data"; int rowCount = 1; foreach (DataRow dr in dt.Rows) { rowCount += 1; for (int i = 1; i < dt.Columns.Count + 1; i++) { // Add the header the first time through if (rowCount == 2) { oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName; } oSheet.Cells[rowCount, i] = dr[i - 1].ToString(); } } // Resize the columns oRange = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[rowCount, dt.Columns.Count]); oRange.EntireColumn.AutoFit(); // Save the sheet and close oSheet = null; oRange = null; oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oWB.Close(Missing.Value, Missing.Value, Missing.Value); oWB = null; oXL.Quit(); } catch { throw; } finally { // Clean up // NOTE: When in release mode, this does the trick GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } return true; } 

Answer 2

I would recommend EPPlus - this solution does not require COM and interop dll and is very fast. Ideal for web scenarios.

http://epplus.codeplex.com/

http://nuget.org/packages/EPPlus

 private void DumpExcel(DataTable tbl) { using (ExcelPackage pck = new ExcelPackage()) { //Create the worksheet ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo"); //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 ws.Cells["A1"].LoadFromDataTable(tbl, true); //Format the header for column 1-3 using (ExcelRange rng = ws.Cells["A1:C1"]) { rng.Style.Font.Bold = true; rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue rng.Style.Font.Color.SetColor(Color.White); } //Example how to Format Column 1 as numeric using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1]) { col.Style.Numberformat.Format = "#,##0.00"; col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; } //Write it back to the client Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=ExcelDemo.xlsx"); Response.BinaryWrite(pck.GetAsByteArray()); } } } } 

Answer 3

you can use

 using Microsoft.Office.Interop.Excel; 

work with excel "native" documents ...

Using C # to create an Excel document http://www.codeproject.com/Articles/20228/Using-C-to-Create-an-Excel-Document

But most general reports Generator / Designers can easily export to excel also check Reporting Services if you use SERVER SERVER

Answer 4

Just in case, someone used the selected solution and threw the exception "the object does not contain a definition for get_Range". I found a solution here: get_Range Worksheet exception . Hope this saves you some time.