Friday 6 March 2009

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");
}
}
}
}

4 comments:

  1. Gracias, este codigo me fue de mucha ayuda.

    ReplyDelete
  2. Good night, friend, I was behind this code is doing time, but I just wonder one thing: how do I play rather than float numbers he put into string? Because he's putting, for example, in a column that should be 02890345678, he puts so: 283EEE +13.

    I would like him to put everything to string in each cell in Excel, you know how?

    Thank you friend!

    ReplyDelete
  3. Here U had used "caption". But don't describe about caption..When I run this code it is give me a error message that missing caption so can u tell me why caption id used?

    ReplyDelete