In my previous article I wrote how to Export DataGridView Data to Excel in C# Without Saving to File System In this article, we will learn how to export a Dataset to individual Excel sheets in a C# Windows application.
1. Create a new project in Visual C# and select "Windows Forms Application" from the list of available templates and name your project "ExportDataSetToExcel".
![Windows Forms Application]()
2. Now let us create two instances of DataTable.
Coding
DataTable 1 (Employee)
System.Data.DataTable table1 = new System.Data.DataTable("Employee");
table1.Columns.Add("EMPNO");
table1.Columns.Add("ENAME");
table1.Rows.Add("7786", "Vinoth");
table1.Rows.Add("8476", "Senthil");
table1.Rows.Add("1256", "Jayanth");
table1.Rows.Add("7896", "Ganesh");
table1.Rows.Add("4563", "Baskar");
table1.Rows.Add("7894", "Vimal");
DataTable 2 (Customer)
System.Data.DataTable table2 = new System.Data.DataTable("Customer");
table2.Columns.Add("CUSTID");
table2.Columns.Add("ITEMS");
table2.Rows.Add(1, "Gelusil");
table2.Rows.Add(1, "Amlong 4.5");
table2.Rows.Add(2, "Crocin");
table2.Rows.Add(3, "Strepsils");
3. Create a DataSet and put both tables in it using the Add method of DataSet as in the following:
dataSet = new DataSet("General");
dataSet.Tables.Add(table1);
dataSet.Tables.Add(table2);
4. Next we add a reference to the Microsoft.Office.Interop to the project from "Project" -> "Add Reference".
![add Reference Interop]()
5. Now we create an Object for the Excel Application using the ApplicationClass as in the following:
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
6. Next we loop through the DataTables in DataSet using the DataTableCollection class as in the following:
DataTableCollection collection = dataSet.Tables;
7. Now, we need to store the Header details for the DataTables in a DataSet in Excel using code as in the following:
for (int j = 1; j < table.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
8. And then we store each row and column values of the DataTables to an Excel Sheet as in the following:
// Storing Each row and column value to excel sheet
for (int k = 0; k < table.Rows.Count; k++)
{
for (int l = 0; l < table.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
table.Rows[k].ItemArray[l].ToString();
}
}
Complete Source Code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
namespace ExportDataSetToExcel
{
public partial class Form1 : Form
{
//Create a DataSet and put both tables in it.
System.Data.DataSet dataSet = null;
public Form1()
{
InitializeComponent();
}
private void btnclose_Click(object sender, EventArgs e)
{
this.Close();
}
private void btnExport_Click(object sender, EventArgs e)
{
// Create two DataTable instances.
System.Data.DataTable table1 = new System.Data.DataTable("Employee");
table1.Columns.Add("EMPNO");
table1.Columns.Add("ENAME");
table1.Rows.Add("7786", "Vinoth");
table1.Rows.Add("8476", "Senthil");
table1.Rows.Add("1256", "Jayanth");
table1.Rows.Add("7896", "Ganesh");
table1.Rows.Add("4563", "Baskar");
table1.Rows.Add("7894", "Vimal");
System.Data.DataTable table2 = new System.Data.DataTable("Customer");
table2.Columns.Add("CUSTID");
table2.Columns.Add("ITEMS");
table2.Rows.Add(1, "Gelusil");
table2.Rows.Add(1, "Amlong 4.5");
table2.Rows.Add(2, "Crocin");
table2.Rows.Add(3, "Strepsils");
// Create a DataSet and put both tables in it.
dataSet = new DataSet("General");
dataSet.Tables.Add(table1);
dataSet.Tables.Add(table2);
try
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// Loop over DataTables in DataSet.
DataTableCollection collection = dataSet.Tables;
for (int i = collection.Count; i > 0; i--)
{
Sheets xlSheets = null;
Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
System.Data.DataTable table = collection[i - 1];
xlWorksheet.Name = table.TableName;
for (int j = 1; j < table.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < table.Rows.Count; k++)
{
for (int l = 0; l < table.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
table.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
}
((Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
ScreenShots
![Table in Excel]()
![Customer Table]()
Conclusion
In this article we have explained how to Export Data from a DataSet to an Excel Sheet using Microsoft.Interop.Excel Library.