Introduction
This article explains how to export the DataSet into Excel using C# excel interop API.
Using the code
Before starting code make sure add excel interop reference (Microsoft.Office.Interop.Excel) from add references in visual studio.
Here I have create namespace alias Excel for the Microsoft.Office.Interop.Excel namepsace.
Usually in projects we need to export the data from DataSet to excel this would be a common task in most of the projects in such cases we can use the below code to export the DataSet into Excel.
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExportDataSetToExcel
{
class Program
{
static void Main(string[] args)
{
Program p = new Program();
DataTable employeeTable = new DataTable("Employee");
employeeTable.Columns.Add("Employee ID");
employeeTable.Columns.Add("Employee Name");
employeeTable.Rows.Add("1", "ABC");
employeeTable.Rows.Add("2", "DEF");
employeeTable.Rows.Add("3", "PQR");
employeeTable.Rows.Add("4", "XYZ");
DataTable departmentTable = new DataTable("Department");
departmentTable.Columns.Add("Department ID");
departmentTable.Columns.Add("Department Name");
departmentTable.Rows.Add("1", "IT");
departmentTable.Rows.Add("2", "HR");
departmentTable.Rows.Add("3", "Finance");
DataSet ds = new DataSet("Organization");
ds.Tables.Add(employeeTable);
ds.Tables.Add(departmentTable);
p.ExportDataSetToExcel(ds);
}
private void ExportDataSetToExcel(DataSet ds)
{
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(@"E:\Org.xlsx");
foreach (DataTable table in ds.Tables)
{
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
excelWorkSheet.Name = table.TableName;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
}
}
}
excelWorkBook.Save();
excelWorkBook.Close();
excelApp.Quit();
}
}
}