通过剪贴板实现将DataGridView中的数据导出到Excel

将datagridview中的数据导出到excel中有许多方法，常见的方法是使用office com组件将datagridview中的数据循环复制到excel cell对象中，然后再保存整个excel workbook。但是如果数据量太大，例如上万行数据或者有多个excel sheet需要同时导出，效率会比较低。可以尝试使用异步操作或多线程的方式来解决ui死锁的问题。

这里介绍一种直接通过windows剪贴板将数据从datagridview导出到excel的方法。代码如下：

using system;
using system.collections.generic;
using system.componentmodel;
using system.data;
using system.drawing;
using system.linq;
using system.text;
using system.windows.forms;
using excel = microsoft.office.interop.excel;
using system.reflection;
using microsoft.office.interop.excel;

namespace windowsformsapplication1
{
public partial class form1 : form
{
public form1()
{
initializecomponent();
this.savefiledialog1.filter = "excel workbook|*.xlsx|excel macro-enabled workbook|*.xlsm|excel 97-2003 workbook|*.xls";
this.savefiledialog1.filename = "demo.xlsx";

loaddata();
}

private void loaddata()
{
bindinglist<car> cars = new bindinglist<car>();

cars.add(new car("ford", "mustang", 1967));
cars.add(new car("shelby ac", "cobra", 1965));
cars.add(new car("chevrolet", "corvette sting ray", 1965));

this.datagridview1.datasource = cars;
}

private void toolstripbutton1_click(object sender, eventargs e)
{
string filepath = string.empty;
if (this.savefiledialog1.showdialog() == system.windows.forms.dialogresult.ok)
{
filepath = this.savefiledialog1.filename;
}
else
{
return;
}

this.datagridview1.selectall();
clipboard.setdataobject(this.datagridview1.getclipboardcontent());

excel.application objexcel = null;
excel.workbook objworkbook = null;
excel.worksheet objsheet = null;
try
{
objexcel = new microsoft.office.interop.excel.application();
objworkbook = objexcel.workbooks.add(missing.value);
objsheet = (excel.worksheet)objworkbook.activesheet;
objexcel.visible = false;

objexcel.get_range("a1", system.type.missing).pastespecial(xlpastetype.xlpasteall, xlpastespecialoperation.xlpastespecialoperationnone, type.missing, type.missing);
objsheet.name = "demo";
//set table properties
objexcel.cells.entirecolumn.autofit();//auto column width
objexcel.cells.verticalalignment = microsoft.office.interop.excel.constants.xlcenter;
objexcel.cells.horizontalalignment = microsoft.office.interop.excel.constants.xlleft;
objexcel.errorcheckingoptions.backgroundchecking = false;

//save file
objworkbook.saveas(filepath, missing.value, missing.value, missing.value, missing.value,
missing.value, excel.xlsaveasaccessmode.xlshared, missing.value, missing.value, missing.value,
missing.value, missing.value);
}
catch (exception error)
{
messagebox.show(error.message, "error", messageboxbuttons.ok, messageboxicon.error);
return;
}
finally
{
//dispose the excel related objects
if (objworkbook != null)
{
objworkbook.close(missing.value, missing.value, missing.value);
}
if (objexcel.workbooks != null)
{
objexcel.workbooks.close();
}
if (objexcel != null)
{
objexcel.quit();
}

objsheet = null;
objworkbook = null;
objexcel = null;
gc.collect(); // force final cleanup.
}
}
}

public class car
{
private string _make;
private string _model;
private int _year;

public car(string make, string model, int year)
{
_make = make;
_model = model;
_year = year;
}

public string make
{
get { return _make; }
set { _make = value; }
}

public string model
{
get { return _model; }
set { _model = value; }
}

public int year
{
get { return _year; }
set { _year = value; }
}
}
}

导出数据到excel的操作在事件toolstripbutton1_click中，代码的第49行和50行是将datagridview当前选中的行复制到系统剪贴板中，62行将剪贴板中的内容粘贴到excel默认sheet的a1单元格中。excel会自动格式化将粘贴的内容，如下图。


使用剪贴板导出数据过程比较简单，省去了对excel对象的遍历和操作，缺点是无法对导出的数据进行格式和样式的设置。如果需要对导出的数据进行样式设置，可以尝试使用openxml的方式来修改excel文件的样式，