C# 利用剪贴板快速导入 datatable/dataset 的数据到 Excel

/// <summary>
/// 将DataSet里所有数据导入Excel.
/// 需要添加COM: Microsoft Excel Object Library.
/// using Excel;
/// </summary>
/// <param name="filePath"></param>
/// <param name="ds"></param>
private void ExportToExcel(string filePath, DataSet ds)
{
object oMissing = System.Reflection.Missing.Value;
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
try
{
//打开EXCEL文件
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,oMissing,oMissing);
// Excel.Workbook xlWorkbook=xlApp.Workbooks.只有Open属性，没有Write属性
Excel.Worksheet xlWorksheet;
//循环所有DataTable
for (int i = 0; i < ds.Tables.Count; i++)
{
//添加入一个新的Sheel页
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing, oMissing, 1, oMissing);
//以TableName作为新加的sheel页名
xlWorksheet.Name = ds.Tables[i].TableName;
//取出这个DataTable中的所有值，暂时存于stringBuffer中
string stringBuffer = "";
// for(int m=0;m<ds.Tab
for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
{
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
if (k < ds.Tables[i].Columns.Count - 1)
stringBuffer += "/t";
}
stringBuffer += "/n";
}
//利用系统剪贴板
System.Windows.Forms.Clipboard.SetDataObject("");
//将stringBuffer放入剪贴板
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
//选中这个sheel页中的第一个单元格
((Excel.Range)xlWorksheet.Cells[1, 1]).Select();
//粘贴
xlWorksheet.Paste(oMissing, oMissing);
//清空系统剪贴板
System.Windows.Forms.Clipboard.SetDataObject("");
}
//保存并关闭这个工作薄
xlWorkbook.Close(Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//释放...
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect();
}
}

}