public ExcelWriter()
{
_stream = SpreadsheetReader.Create();
_doc = SpreadsheetDocument.Open(_stream, true);
var worksheetPart = SpreadsheetReader.GetWorksheetPartByName(_doc, "Sheet1");
_writer = new WorksheetWriter(_doc, worksheetPart);
}
/// <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();
}
/// <summary>
/// Create the spreadsheet.
/// </summary>
/// <param name="documentName">Excel file name.</param>
/// <param name="excelWorkSheetName">Excel worksheet name: default: sheet1.</param>
/// <param name="rowData">Row data to write.</param>
/// <param name="headerData">Header data.</param>
/// <param name="rowPointers">Row pointers.</param>
/// <param name="styleSheet">Style sheet.</param>
/// <returns>Memory stream.</returns>
private static MemoryStream CreateSpreadSheet(string documentName, string excelWorkSheetName, IEnumerable <T> rowData, string[] headerData, string[] rowPointers, Stylesheet styleSheet)
{
int rowNum = 0;
int colNum = 0;
int maxWidth = 0;
int minCol = 1;
int maxCol = rowPointers == null ? minCol : rowPointers.Length;
maxCol = maxCol == 1 && headerData == null ? 1 : headerData.Length;
MemoryStream xmlStream = SpreadsheetReader.Create();
SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xmlStream, true);
SetSheetName(excelWorkSheetName, spreadSheet);
if (styleSheet == null)
{
SetStyleSheet(spreadSheet);
}
else
{
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet = styleSheet;
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(spreadSheet, excelWorkSheetName);
WriteHeaders(headerData, out rowNum, out colNum, out maxWidth, spreadSheet, worksheetPart);
AddCellWidthStyles(Convert.ToUInt32(minCol), Convert.ToUInt32(maxCol), maxWidth, spreadSheet, worksheetPart);
if (rowPointers == null || rowPointers.Length == 0)
{
WriteRowsFromHeaders(rowData, headerData, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
else
{
WriteRowsFromKeys(rowData, rowPointers, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
// Save to the memory stream
SpreadsheetWriter.Save(spreadSheet);
spreadSheet.Close();
spreadSheet.Dispose();
return(xmlStream);
}