Export DataGridView Data to Excel in C# Without Saving to File System

Shankar M

Visual C#DataGridView controlExcel in C#Export DataGridView dataMicrosoft.Office.Interop

8 Comments

Join the conversation! Your thoughts help the community grow.

ADITYA SHUKLA7y ago
Error CS0119: 'NullReferenceException' is a type, which is not valid in the given context
Dharmendra Kumar Pandit7y ago
Working Fine.
yaser alshikh9y ago
Thanks. Very nice, simple, and helpful example.
Salman Mushtaq9y ago
Hello, I Face error "Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded. Use the applicable interface instead." How to resolve this issue? And Also this code give exception.
Subash10y ago
Nice one
Former member13y ago
You can also do that with Aspose.Cells for .NET Library : http://www.aspose.com/.net/excel-component.aspx
Kiran Kumar Talikoti13y ago
Nice article.Helped a lot..
Gakenh0113y ago
Thanks. Very nice, simple, and helpful example. I might try it out for a tough report I have coming up.

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".

2. Design your form by placing a DataGridView control on it. Here I have designed it like this:

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.

4. Next we add a reference to the Microsoft.Office.Interop DLL to our project from "Project" -> "Add Reference".

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.

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 Microsoft.Office.Interop namespace without saving to the file system.