62,046
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 使用粘贴板进行写入
/// 此函数需要依赖office实例,运行的OS要安装office
/// </summary>
/// <param name="data"></param>
/// <param name="filename"></param>
/// <param name="exportHeader"></param>
public void Export2Xls(DataTable data, string filename, bool exportHeader = true)
{
if (System.IO.File.Exists(filename))
System.IO.File.Delete(filename);
Excel14._Application xlsApp = null;
Excel14._Workbook xlsBook = null;
Excel14._Worksheet xstSheet = null;
try
{
//xlsApp = new Excel14.ApplicationClass();
//xlsApp = new Excel14.Application();
xlsApp = new Excel14.Application();
xlsBook = xlsApp.Workbooks.Add();
xstSheet = (Excel14._Worksheet)xlsBook.Worksheets[1];
var buffer = new StringBuilder();
if (exportHeader)
{
// Excel中列与列之间按照Tab隔开
foreach (DataColumn col in data.Columns)
buffer.Append(col.ColumnName + "\t");
buffer.AppendLine();
}
foreach (DataRow row in data.Rows)
{
foreach (DataColumn col in data.Columns)
buffer.Append(row[col].ToString() + "\t");
buffer.AppendLine();
}
System.Windows.Forms.Clipboard.SetDataObject("");
// 放入剪切板
System.Windows.Forms.Clipboard.SetDataObject(buffer.ToString());
var range = (Excel14.Range)xstSheet.Cells[1, 1];
range.Select();
xstSheet.Paste();
// 清空剪切板
System.Windows.Forms.Clipboard.SetDataObject("");
xlsBook.SaveAs(filename);
}
catch (Exception e)
{
throw e;
}
finally
{
try
{
if (xlsBook != null)
xlsBook.Close();
if (xlsApp != null)
xlsApp.Quit();
// finally里清空Com对象
lock(this)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xstSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
xstSheet = null;
xlsBook = null;
xlsApp = null;
GC.Collect();
}
}
catch (System.Exception ex)
{
throw ex;
}
}
}