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

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.