Code Snippet: Export DataGridView To Excel With Columns

25. January 2011 16:12

C# Code Snippets  Comments

This is among one of the best code snippet I have and using since long to export the datagridview to excel. The best part of this code snippet is that it just don't uses the excel interop, so you need not to worry about the Office version or PIAs installed at user's machine. What actualy is needed is just a Reflection namespace - System.Reflection and it will bind the data to the instance of the excel application.

 

private void Export2Excel(DataGridView datagridview, bool captions)
{
    if (datagridview.ColumnCount > 0)
    {
        string[] strArray = new string[datagridview.ColumnCount];
        string[] strArray2 = new string[datagridview.ColumnCount];
        int num = 0;
        int index = 0;
        int num3 = 0;
        for (index = 0; index < datagridview.ColumnCount; index++)
        {
            strArray[index] = datagridview.Rows[0].Cells[index].OwningColumn.Name.ToString();
            if ((num > 0) && ((index % num) == 0))
            {
                num = 0;
            }
            num3 = index / 0x1a;
            if (num3 == 0)
            {
                num = index + 0x41;
                strArray2[index] = Convert.ToString((char)num);
            }
            else
            {
                int num4 = num3 + 0x40;
                num = (index % 0x1a) + 0x41;
                strArray2[index] = Convert.ToString((char)num4) + Convert.ToString((char)num);
            }
        }
        try
        {
            object obj7;
            object target = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));
            object obj4 = target.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, target, null);
            object obj3 = obj4.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, obj4, null);
            object obj5 = obj3.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, obj3, null);
            object[] args = new object[] { 1 };
            object obj6 = obj5.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, obj5, args);
            if (captions)
            {
                index = 0;
                while (index < datagridview.ColumnCount)
                {
                    args = new object[] { strArray2[index] + "1", Missing.Value };
                    obj7 = obj6.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, obj6, args);
                    args = new object[] { strArray[index] };
                    obj7.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, obj7, args);
                    index++;
                }
            }
            for (num = 0; num < datagridview.RowCount; num++)
            {
                for (index = 0; index < datagridview.ColumnCount; index++)
                {
                    args = new object[] { strArray2[index] + Convert.ToString((int)(num + 2)), Missing.Value };
                    obj7 = obj6.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, obj6, args);
                    args = new object[] { datagridview.Rows[num].Cells[strArray[index]].Value.ToString() };
                    obj7.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, obj7, args);
                }
            }
            args = new object[] { true };
            target.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, target, args);
            target.GetType().InvokeMember("UserControl", BindingFlags.SetProperty, null, target, args);
        }
        catch (Exception exception)
        {
            MessageBox.Show(("Error: " + exception.Message) + " Line: " + exception.Source, "Error");
        }
      }
    }
Currently rated 5.0 by 2 people