Code Snippet: Export DataGridView To Excel With Columns

25. January 2011 16:12

C# Code Snippets  5 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

Comments (5)

thenndral thenndral
12/7/2011 3:27:33 PM #

Hai, Thanks for a such a code. I have a questions. I'm using VS2010[WPF/C#], Can I use this code in my project? thanks, thenndral

prashant prashant
12/8/2011 8:20:28 PM #

I haven't tried that but maybe if you are using System.Windows.Forms.DataGridView control in your WPF project.

dblwhooper dblwhooper
3/26/2012 12:59:53 PM #

Hi, May i know the codes i need to call this function from an export to excel button in VS2010? i tried something like this: private void exportexcelbtn_Click(object sender, EventArgs e) { this.Export2Excel(datagridview, captions); } but it doesnt work. Sorry im a C# newbie :O

Prashant Prashant
3/26/2012 3:55:52 PM #

You are calling the method correctly, but you are not passing the parameters correctly. The first parameter should be the [b]Name[/b] of the DataGridView control. Yo can find the [b]Name[/b] of the DataGridView in the properties window. Select datagridview and press F4 to get the properties window. The second parameter accepts only boolean values ie. [b]true[/b] and [b]false[/b]. If you want the captions in the top of your exported excel sheet then pass [b]true[/b] and if you don't want the captions then pass [b]false[/b].

dblwhooper dblwhooper
3/27/2012 7:45:21 AM #

Thanks alot bro! It sorta work but now i encounter 'Error: Object Reference not set to an instance of an object. Line: Vending Machine.' Dang.

Add Comment

Visit blogadda.com to discover Indian blogs Computers Blogs