1#region Export to Excel ...
2
3/// <summary>
4/// Exports a passed datagridview to an Excel worksheet.
5/// If captions is true, grid headers will appear in row 1.
6/// Data will start in row 2.
7/// </summary>
8/// <param name="datagridview"></param>
9/// <param name="captions"></param>
10private void Export2Excel(DataGridView datagridview, bool captions)
11{
12object objApp_Late;
13object objBook_Late;
14object objBooks_Late;
15object objSheets_Late;
16object objSheet_Late;
17object objRange_Late;
18object[] Parameters;
19string[] headers = new string[datagridview.ColumnCount-1];
20string[] columns = new string[datagridview.ColumnCount-1];
21
22int i = 0;
23int c = 0;
24for (c = 0; c < datagridview.ColumnCount - 1; c++)
25{
26headers[c] = datagridview.Rows[0].Cells[c].OwningColumn.Name.ToString();
27i = c + 65;
28columns[c] = Convert.ToString((char)i);
29}
30
31try
32{
33// Get the class type and instantiate Excel.
34Type objClassType;
35objClassType = Type.GetTypeFromProgID("Excel.Application");
36objApp_Late = Activator.CreateInstance(objClassType);
37//Get the workbooks collection.
38objBooks_Late = objApp_Late.GetType().InvokeMember("Workbooks",
39BindingFlags.GetProperty, null, objApp_Late, null);
40//Add a new workbook.
41objBook_Late = objBooks_Late.GetType().InvokeMember("Add",
42BindingFlags.InvokeMethod, null, objBooks_Late, null);
43//Get the worksheets collection.
44objSheets_Late = objBook_Late.GetType().InvokeMember("Worksheets",
45BindingFlags.GetProperty, null, objBook_Late, null);
46//Get the first worksheet.
47Parameters = new Object[1];
48Parameters[0] = 1;
49objSheet_Late = objSheets_Late.GetType().InvokeMember("Item",
50BindingFlags.GetProperty, null, objSheets_Late, Parameters);
51
52if (captions)
53{
54// Create the headers in the first row of the sheet
55for (c = 0; c < datagridview.ColumnCount - 1; c++)
56{
57//Get a range object that contains cell.
58Parameters = new Object[2];
59Parameters[0] = columns[c] + "1";
60Parameters[1] = Missing.Value;
61objRange_Late = objSheet_Late.GetType().InvokeMember("Range",
62BindingFlags.GetProperty, null, objSheet_Late, Parameters);
63//Write Headers in cell.
64Parameters = new Object[1];
65Parameters[0] = headers[c];
66objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
67null, objRange_Late, Parameters);
68}
69}
70
71// Now add the data from the grid to the sheet starting in row 2
72for (i = 0; i < datagridview.RowCount; i++)
73{
74for (c = 0; c < datagridview.ColumnCount - 1; c++)
75{
76//Get a range object that contains cell.
77Parameters = new Object[2];
78Parameters[0] = columns[c] + Convert.ToString(i+2);
79Parameters[1] = Missing.Value;
80objRange_Late = objSheet_Late.GetType().InvokeMember("Range",
81BindingFlags.GetProperty, null, objSheet_Late, Parameters);
82//Write Headers in cell.
83Parameters = new Object[1];
84Parameters[0] = datagridview.Rows[i].Cells[headers[c]].Value.ToString();
85objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
86null, objRange_Late, Parameters);
87}
88}
89
90//Return control of Excel to the user.
91Parameters = new Object[1];
92Parameters[0] = true;
93objApp_Late.GetType().InvokeMember("Visible", BindingFlags.SetProperty,
94null, objApp_Late, Parameters);
95objApp_Late.GetType().InvokeMember("UserControl", BindingFlags.SetProperty,
96null, objApp_Late, Parameters);
97}
98catch (Exception theException)
99{
100String errorMessage;
101errorMessage = "Error: ";
102errorMessage = String.Concat(errorMessage, theException.Message);
103errorMessage = String.Concat(errorMessage, " Line: ");
104errorMessage = String.Concat(errorMessage, theException.Source);
105
106MessageBox.Show(errorMessage, "Error");
107}
108}
109#endregion
110