Export from a datatable to an excel using the Microsoft.Office.Interop.Excel
As MS office is the most popular office processing tool and widely used it has become important to know how to work with these.There are your managers and senior leadership teams favourite tool too.There are a few scenarios in the real world where we are often required to export data to excel.
I am sharing one such example where we will export data from a datatable to excel. I am using a datatable because any data can be easily put into a datatable. For e.g :- take a scenario where the list items in a share point needs to be download and exported to an excel from an windows application. By default the sharepoint list web service returns data in an xml format which can be converted to a datatable and then exported to excel.
The example is as follows.
We need to add an reference of Microsoft.Office.Interop.Excel to our application.
If you don’t have the dll’s you can download the Microsoft office Primary Interop Assemblies Redistributable from the link
We need to run the exe file” PIARedist.exe” which we need to run and the following msi is extracted
o2010pia.msi we need to install this msi to our machine.
Once that is done add a reference of Microsoft.Office.Interop.Excel to your application.
public void ExportToExcel(System.Data.DataTable dtList)
{
try
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
string cellValue = string.Empty;
int RowNumber = 1;
if (excelApp == null)
{
return;
}
string filePath = @"C:\Blog\DemoExcel.xlsx";
string sheetName = "Sheet1";
if (!File.Exists(filePath))
{
MessageBox.Show(@"File not found");
System.Windows.Forms.Application.Exit();
}
Workbook demoWorkBook = excelApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets demoWorkSheet = demoWorkBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet demoSheet = null;
demoSheet = (Microsoft.Office.Interop.Excel.Worksheet)demoWorkSheet.get_Item(sheetName);
foreach (DataRow row in dtList.Rows)
{
for (int ColumnNumber = 0; ColumnNumber < row.ItemArray.Length; ColumnNumber++)
{
cellValue = row[ColumnNumber].ToString().Trim();
demoSheet.Cells[RowNumber + 1, ColumnNumber + 1] = cellValue;
}
RowNumber++;
}
demoWorkBook.Save();
demoWorkBook.Close();
}
catch (Exception)
{
MessageBox.Show("File Operation error");
System.Windows.Forms.Application.Exit();
}
}
Note: - It doesn’t add the header name which I think we can add it manually.
The contents of the data list are as follows which is called on a button click event:-
private void button1_Click(object sender, EventArgs e)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Employee ID", typeof(int));
dt.Columns.Add("Employee Name", typeof(string));
dt.Columns.Add("Designation", typeof(string));
dt.Rows.Add(1, "Employee 1", "Software Engineer");
dt.Rows.Add(2, "Employee 2", "Manager");
dt.Rows.Add(3, "Employee 3", "Senior Manager");
ExportToExcel(dt);
}
No comments:
Post a Comment