Export C# DataTable to MS Excel

Here, in this post, we are going to show you an C# code example of exporting a C# datatable data to MS Excel with headers.

Step1: Add reference to Microsoft Excel Interop in your application. Follow the steps 1, 2, 3 and 4 mentioned in our previous article to add reference.

Step2: Define the function with the name you desire. Here we created with the name DataTableToExcel. This is a parameterised function. While calling this function we will pass datatable to be exported in excel.

public void DataTableToExcel(DataTable DT)
 {

 }

Step3: Write the code inside the function as given below. Comments have been added above each code line to help you understand the functionality.

public void DataTableToExcel (DataTable DT)
 {
// declare the path to save excel workbook
string savePath = @"D:\Test\Example.xlsx";
// creates excel application object            
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

// creates new excel workbook object
Microsoft.Office.Interop.Excel.Workbook wb;

// creates new excel sheet object
Microsoft.Office.Interop.Excel.Worksheet ws;

app.Visible = false;
app.DisplayAlerts = false;

wb = app.Workbooks.Add(Type.Missing);

// assigning the active sheet to worksheet object
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
// Renaming the active sheet to desired name            
ws.Name = "DataTable Data";

  // Loop for writing column names to excel                
  for (int i = 0; i < DT.Columns.Count; i++)
   {
     ws.Cells[1, (i + 1)] = DT.Columns[i].ColumnName;
   }
  // Loop for writing data to excel cells
  for (int i = 0; i < DT.Rows.Count; i++)
   {
    for (int col = 0; col < DT.Columns.Count; col++)
     {
      ws.Cells[(i + 2), (col + 1)] = DT.Rows[i][col];
     }
   }
    
wb.SaveAs(savePath);
app.Workbooks.Close();
app.Quit();
 }

Step4: Call this function at the desired place in your code. This will export the data stored in data table to an excel workbook of name “Example.xlsx” in “D:\Test\” location of your system.

Leave a Reply Cancel reply