Export Data from DataGridView to Excel in C# Windows Forms

A very common requirement as a .Net developer would receive from customer is to export the data from DataGridView to excel. As developers we don’t particularly like Excel because it lacks of centralized control, poor metadata awareness. awkward email/shared network sharing etc. But we have to admit Excel is a great tool and our customers are so used to it, this is not something we should ban customers from using, on the contrary we should help customers to use excel with high quality data. Here is where this requirement coming from. I have been researching on the internet for a stable solution, however I didn’t find one. I found one from here which looks very good but contains a few bugs, I updated the code to make it fully work. It works fine in my environment, feel free to comment if you see a problem.

using System;
using System.Windows.Forms;
using System.Reflection;


namespace Demo
{
    class CommonFunctions
    {
        /// Exports a passed datagridview to an Excel worksheet.
        /// If captions is true, grid headers will appear in row 1.
        /// Data will start in row 2.        
        public static void ExportToExcel(DataGridView datagridview, bool captions)
        {
            object objApp_Late;
            object objBook_Late;
            object objBooks_Late;
            object objSheets_Late;
            object objSheet_Late;
            object objRange_Late;
            object[] Parameters;
            string[] headers = new string[datagridview.ColumnCount];
            string[] columns = new string[datagridview.ColumnCount];

            int i = 0;
            int c = 0;
            int t = 0;
            int j = 0;


            try
            {
                for (c = 0; c <= datagridview.ColumnCount - 1; c++)
                {
                    headers[c] = datagridview.Rows[0].Cells[c].OwningColumn.Name.ToString();
                    if (c < 26)
                    {
                        t = c;
                        i = t + 65;
                        columns[c] = Convert.ToString((char)i);
                    }
                    else
                    {
                        t = c % 26;
                        j = c / 26 - 1;
                        j = j + 65;
                        i = t + 65;
                        columns[c] = Convert.ToString((char)j) + Convert.ToString((char)i);
                    }
                }
                // Get the class type and instantiate Excel.
                Type objClassType;
                objClassType = Type.GetTypeFromProgID("Excel.Application");
                objApp_Late = Activator.CreateInstance(objClassType);
                //Get the workbooks collection.
                objBooks_Late = objApp_Late.GetType().InvokeMember("Workbooks",
                BindingFlags.GetProperty, null, objApp_Late, null);
                //Add a new workbook.
                objBook_Late = objBooks_Late.GetType().InvokeMember("Add",
                BindingFlags.InvokeMethod, null, objBooks_Late, null);
                //Get the worksheets collection.
                objSheets_Late = objBook_Late.GetType().InvokeMember("Worksheets",
                BindingFlags.GetProperty, null, objBook_Late, null);
                //Get the first worksheet.
                Parameters = new Object[1];
                Parameters[0] = 1;
                objSheet_Late = objSheets_Late.GetType().InvokeMember("Item",
                BindingFlags.GetProperty, null, objSheets_Late, Parameters);

                if (captions)
                {
                    // Create the headers in the first row of the sheet
                    for (c = 0; c <= datagridview.ColumnCount - 1; c++)
                    {
                        //Get a range object that contains cell.
                        Parameters = new Object[2];
                        Parameters[0] = columns[c] + "1";
                        Parameters[1] = Missing.Value;
                        objRange_Late = objSheet_Late.GetType().InvokeMember("Range",
                        BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                        //Write Headers in cell.
                        Parameters = new Object[1];
                        Parameters[0] = headers[c];
                        objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
                        null, objRange_Late, Parameters);
                    }
                }

                // Now add the data from the grid to the sheet starting in row 2
                for (i = 0; i < datagridview.RowCount; i++)
                {
                    for (c = 0; c <= datagridview.ColumnCount - 1; c++)
                    {
                        //Get a range object that contains cell.
                        Parameters = new Object[2];
                        Parameters[0] = columns[c] + Convert.ToString(i + 2);
                        Parameters[1] = Missing.Value;
                        objRange_Late = objSheet_Late.GetType().InvokeMember("Range",
                        BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                        //Write Headers in cell.
                        Parameters = new Object[1];
                        Parameters[0] = datagridview.Rows[i].Cells[headers[c]].Value.ToString();
                        objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
                        null, objRange_Late, Parameters);
                    }
                }

                //Return control of Excel to the user.
                Parameters = new Object[1];
                Parameters[0] = true;
                objApp_Late.GetType().InvokeMember("Visible", BindingFlags.SetProperty,
                null, objApp_Late, Parameters);
                objApp_Late.GetType().InvokeMember("UserControl", BindingFlags.SetProperty,
                null, objApp_Late, Parameters);
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }
    }
}