前段时间项目里有个关于报表变更, 将导出的报表名要带有yyyyMMdd这种的时间戳. 整理了一个简单关于ClosedXML的小demo:
1. 首先要在项目中引入两个library:ClosedXML.dll 和 DocumentFormat.OpenXml.dll
2. 在项目代码中加入引用:usingClosedXML.Excel;
3. demo方法(在代码的最后部分使用了Response来设置export出excel的名字,当时修改这段代码时候出现了上下文不存在Response的错误,解决办法请参照:解决办法:The name 'Response' does not exist in the current context 本文代码已经解决该异常):
/// <summary>
/// Export an Excel using ClosedXML
/// http://blog.csdn.net/dietime1943
/// </summary>
/// <returns>Returns a byte array object.</returns>
public byte[] FunctionDownload()
{
string[] ReportColumn = { "A", "B" }; // A,B column
string[] ReportColumn_values = { "NAME", "ADDRESS" }; // VALUE
string nowTimeStamp = GetTimeStamp();
string basePath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
string path = basePath + Common.Constants.Template.LocalPath;
string sourceFile = path + "Export2ExcelForm.xlsx";
string destinationFile = path + "ReportForm" + nowTimeStamp + ".xlsx";
File.Copy(sourceFile, destinationFile, false);
using (XLWorkbook wb = new XLWorkbook(destinationFile))
{
var ws = wb.Worksheet(1);
int row = 1; // + result.IndexOf(resultone);
for (int m = 0; m < ReportColumn.Count(); m++)
{
ws.Cell(row, ReportColumn[m]).Value = ReportColumn_values[m]; // A,F column
}
//using (var ms = new MemoryStream())
//{
// wb.SaveAs(ms);
// File.Delete(destinationFile);
// return ms.ToArray();
//}
// 设置report保存的时候名称为: ReportName_yyyyMMdd.xlsx
using (var ms = new MemoryStream())
{
wb.SaveAs(ms);
//return ms.ToArray();
string printdate = Calendar.GetCurrentDate().ToString("yyyyMMdd");
string myName = "ReportForm" + "_" + printdate + ".xlsx";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + myName);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
// HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
File.Delete(destinationFile);
return ms.ToArray();
}
}
}
注:本文原创由`bluetata`发布于blog.csdn.net、转载请务必注明出处。