This article explains how to export DataGridView data to Excel in C# without saving to a Local File System.
1. Create a new project in Visual C# and select "Windows Forms Application" from the list of available templates and name your project "ExportToExcel".
![Excel1.jpg]()
2. Design your form by placing a DataGridView control on it. Here I have designed it like this:
![Excel2.jpg]()
3. Next we will load the data into dataGridView1 using the method:
LoadGridData();
The LoadGridData() method is here:
private void LoadGridData()
{
DataTable dt = new DataTable();
dt.Columns.Add("EmpNo.", typeof(int));
dt.Columns.Add("Emp.Name", typeof(string));
dt.Columns.Add("Hiredate", typeof(DateTime));
dt.Columns.Add("Department", typeof(int));
dt.Columns.Add("Salary", typeof(double));
DataRow dr = dt.NewRow();
dr["EmpNo."] = 1;
dr["Emp.Name"] = "Vinoth";
dr["Hiredate"] = DateTime.Now;
dr["Department"] = 20;
dr["Salary"] = 15000;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["EmpNo."] = 2;
dr["Emp.Name"] = "Krishnan";
dr["Hiredate"] = DateTime.Now.AddMinutes(1);
dr["Department"] = 30;
dr["Salary"] = 17000;
dt.Rows.Add(dr);
dataGridView1.DataSource = dt;
}
Here we have created an instance of the DataTable class and added the columns EmpNo., Emp.Name, Hiredate, Department and Salary. Since each row in a DataTable is represented by a DataRow we have created the DataRow object and added the DataRow dr to DataTable using the Add method.
And finally assigned the DataTable (dt) to dataGridView1 using the DataSource property.
![Excel3.jpg]()
4. Next we add a reference to the Microsoft.Office.Interop DLL to our project from "Project" -> "Add Reference".
![Excel4.jpg]()
5. Now we create an Object for the Excel Application using the ApplicationClass.
Microsoft.Office.Interop.Excel.ApplicationClass XcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
6. Next, we create a new workbook using the Add method of the Object:
XcelApp.Application.Workbooks.Add(Type.Missing);
7. Now, we need to store the Header details for the DataGridView in Excel using code as in the following:
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
XcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
8. Next, we loop through the rows and columns of the dataGridView1 to store the values in Excel.
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
XcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
9. Here, next we set the properties of the Workbook column to AutoFit:
XcelApp.Columns.AutoFit();
10. Then we open Excel with the workbook using the Visible property as in the following:
XcelApp.Visible = true;
11. Move to the Designer and click the Export button. Now, a new Excel workbook is opened with the DataGridView details in it.
![Excel5.jpg]()
Here is the Source Code of the project:
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;
namespace ExportToExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
LoadGridData();
}
private void LoadGridData()
{
DataTable dt = new DataTable();
dt.Columns.Add("EmpNo.",typeof(int));
dt.Columns.Add("Emp.Name",typeof(string));
dt.Columns.Add("Hiredate",typeof(DateTime));
dt.Columns.Add("Department",typeof(int));
dt.Columns.Add("Salary",typeof(double));
DataRow dr = dt.NewRow();
dr["EmpNo."] = 1;
dr["Emp.Name"] = "Vinoth";
dr["Hiredate"] = DateTime.Now;
dr["Department"] = 20;
dr["Salary"] = 15000;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["EmpNo."] = 2;
dr["Emp.Name"] = "Krishnan";
dr["Hiredate"] = DateTime.Now.AddMinutes(1);
dr["Department"] = 30;
dr["Salary"] = 17000;
dt.Rows.Add(dr);
dataGridView1.DataSource = dt;
}
private void btn_Cancel_Click(object sender, EventArgs e)
{
this.Close();
}
private void btn_Export_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.ApplicationClass XcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
XcelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
XcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
XcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
XcelApp.Columns.AutoFit();
XcelApp.Visible = true;
}
}
}
}
Conclusion
In this article we discussed how to export data from DataGridView to Excel using the Microsoft.Office.Interop namespace without saving to the file system.