/// <summary>
/// 将查询出的DataSet导出为Excel。
/// </summary>
/// <param name="dataSet">要导出的DataSet数据集，可以包含多个DataTable数据，使用DataSetName作为导出的文件名</param>
public static void ExportDataSet(DataSet dataSet)
{
if (dataSet == null)
{
throw new ArgumentException("dataSet");
}
if (dataSet.Tables.Count == 0)
{
return;
}
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
//首先清空原有的Sheet
var allSheet = doc.WorkbookPart.Workbook.Descendants <Sheet>();
if (allSheet.Count() > 0)
{
doc.WorkbookPart.Workbook.Sheets.RemoveAllChildren();
}
//构造Excel
foreach (DataTable dt in dataSet.Tables)
{
WorksheetPart sheetPart = SpreadsheetWriter.InsertWorksheet(doc, dt.TableName);
WorksheetWriter sheetWriter = new WorksheetWriter(doc, sheetPart);
//打印列名
int colIndex = 0;//当前第几列，从0开始
foreach (DataColumn col in dt.Columns)
{
string reference = GetExcelColumnHeader(colIndex) + "1";
sheetWriter.PasteText(reference, col.ColumnName);
colIndex++;
}
sheetWriter.PasteDataTable(dt, "A2");
SpreadsheetWriter.Save(doc);
}
//Write to response stream
System.Web.HttpContext context = System.Web.HttpContext.Current;
context.Response.Clear();
string UserAgent = context.Request.ServerVariables["http_user_agent"].ToLower();
string FileName = dataSet.DataSetName;
if (UserAgent.IndexOf("firefox") == -1)
{//非火狐浏览器
FileName = context.Server.UrlEncode(FileName);
}
context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", FileName + ".xlsx"));
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
stream.WriteTo(context.Response.OutputStream);
context.Response.End();
}
public void AddWorksheet(string sheetName)
{
var wPart = SpreadsheetWriter.InsertWorksheet(_doc, sheetName);
_writer = new WorksheetWriter(_doc, wPart);
}
