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

Creating Static Methods in C#

Creating Static Methods in C#

Introduction:

      Static Methods are methods that can be accessed without creating instances of the class. Static methods supply the easy way to call the methods based on the flexibility in our code.

 Static Methods does not require Object:

  

     Static Methods does not require creating instances of the class object. So we don't instantiate

    MyClass obj= new MyClass ();

 Creating a static Method:

  

     Creating a static method is as simple as creating a normal method in C#. The only different from a normal method is the word static.

In this example, I have created a new class named StatMethod that defines a new static Method Add.

public static int Add(int val1, int val2)
{
    return val1 + val2;
}

 The above code defines a static method Add which accpets integer paramters and returns the sum of the Input given.

 Calling a static Method:

  

     Since static Methods do not require creating instance of the Class, the static method can be called using the 

Syntax :

classname.methodname ( )

To call our Add Method in the Class StatMethod,

StatMethod.Add(1,2) 

As this is a static Method,we don't require creating a new object of the class StatMethod and we can call the Method Add straight way.

Static methods provides a way to quick access to the method.

Points to Remember:
 

 A static method does not require creating instances.

Regarding Performance, Static methods are normally faster to invoke on the call stack than instance method