C# Export Table to Excel
Solution 1
You have to import Microsoft.Office.Interop.Excel.dll library from here. Add new class file in your project say ExcelUtility. Just write down the following code in it.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExcelDemo
{
public class ExcelUtility
{
public static void CreateExcel(DataSet ds, string excelPath)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
try
{
//Previous code was referring to the wrong class, throwing an exception
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
}
}
xlWorkBook.SaveAs(excelPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
}
catch (Exception ex)
{
throw ex;
}
}
private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
}
}
Now in main code just pass the dataset object and excel path as below.
ExcelUtility.CreateExcel(ds, "D:\\Demo.xls");
I have already tested and using this in my projects.
Solution 2
I know that there is already an answer, although here is another approach
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell("A1").Value = "Hello World!";
workbook.SaveAs("HelloWorld.xlsx");
whats also really amazing about this approach is that you can put your dataset table or table into the workbook like such
var wb = new XLWorkbook();
var dataTable = GetTable("Information");
// Add a DataTable as a worksheet
wb.Worksheets.Add(dataTable);
wb.SaveAs("AddingDataTableAsWorksheet.xlsx");
the dll is here
MoralesJosue
I'm not a great programmer, I'm almost certainly not even a good programmer, but I'm better than I was, and I hope, too become better each day, currently working on a small company making some projects to make faster data communication using visualization reports. Hoping to finish my computer system engineer bachelor degree.
Updated on August 23, 2022Comments
-
MoralesJosue almost 2 years
How can I export this data table to excel using: "Microsoft.Office.Interop.Excel" I have this code witch grabs all the data form Master table and want to export it to excel for better view, don't want to use datagrid. There are a lot of post regarding this topic I think, but usually just recommend using some ad on like "closedxml"
OleDbConnection mycon; DataTable Table = new DataTable("AllData"); mycon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\jm11321\Documents\DB.mdb;Persist Security Info=False"); string command = "Select *From Master"; OleDbCommand oleDbCmd = new OleDbCommand(command,mycon); OleDbDataAdapter adapt = new OleDbDataAdapter(oleDbCmd); mycon.Open(); adapt.Fill(Table); mycon.Close();
Any help is appreciated.
-
MoralesJosue almost 10 yearsI get an error in "Excel.Application xlApp;" error "Error 2 Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded. Use the applicable interface instead." @Dinesh Maind
-
MoralesJosue almost 10 yearsEdit-1 "Excel._Application xlApp = new Excel.Application();" this fixed that problem, but nothing seems to happen.
-
Jonny almost 10 yearsJust remember to add a table name to your dataset tables and datatables.
-
Rahul Bhat about 4 yearsThis code doesn't add Datatable column names to the exported Excel though