/// <summary>
/// 通过多标签页数据将数据写入响应流
/// </summary>
/// <param name="multiSheet">多标签页数据</param>
/// <param name="Response">响应流</param>
public static void ExportToMutilSheet(ExportMultiSheet multiSheet, HttpResponse Response)
{
ExportToMutilSheet(multiSheet, Response.Body);
Response.Headers.Add("Content-Disposition", string.Format("attachment;filename={0}", Path.GetFileName(multiSheet.FileName)));
Response.ContentType = MimeHelper.GetMineType(multiSheet.FileName);
Response.Body.Flush();
Response.Clear();
}
/// <summary>
/// 导出数据到多个Excel标签页中
/// </summary>
/// <param name="multiSheet">多标签页信息</param>
/// <param name="s">输出流</param>
public static void ExportToMutilSheet(ExportMultiSheet multiSheet, Stream s)
{
if (multiSheet == null || multiSheet.ListSheet == null || multiSheet.ListSheet.Count == 0)
{
throw new ArgumentNullException();
}
string fileExt = ".xls";
if (string.IsNullOrEmpty(multiSheet.FileName))
{
multiSheet.FileName = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + fileExt;
}
else
{
if (!multiSheet.FileName.EndsWith(fileExt))
{
multiSheet.FileName = multiSheet.FileName + fileExt;
}
}
IWorkbook workbook = new HSSFWorkbook();
//文本样式
ICellStyle centerStyle = workbook.CreateCellStyle();
centerStyle.VerticalAlignment = VerticalAlignment.Center;
centerStyle.Alignment = HorizontalAlignment.Center;
ICellStyle leftStyle = workbook.CreateCellStyle();
leftStyle.VerticalAlignment = VerticalAlignment.Center;
leftStyle.Alignment = HorizontalAlignment.Left;
ICellStyle rightStyle = workbook.CreateCellStyle();
rightStyle.VerticalAlignment = VerticalAlignment.Center;
rightStyle.Alignment = HorizontalAlignment.Right;
//超链接字体颜色
IFont blueFont = workbook.CreateFont();
blueFont.Color = HSSFColor.Blue.Index;
ICellStyle leftStyleLink = workbook.CreateCellStyle();
leftStyleLink.VerticalAlignment = VerticalAlignment.Center;
leftStyleLink.Alignment = HorizontalAlignment.Left;
leftStyleLink.SetFont(blueFont);
ISheet sheet = null;
DataTable dt = null;
DataRow dr = null;
List <ColumnInfo> ColumnInfoList = null;
int rowHeight = 20;
object cellValue = null;
IRow row = null;
ICell cell = null;
//每写入100条数据进度更新一次
int totalData = multiSheet.ListSheet.Sum(e => e.Data.Rows.Count);
//写入总数
int writeTotal = 0;
foreach (ExportSheetInfo exportSheetInfo in multiSheet.ListSheet)
{
dt = exportSheetInfo.Data;
ColumnInfoList = exportSheetInfo.ColumnInfoList;
//寻找列头和DataTable之间映射关系
foreach (DataColumn col in dt.Columns)
{
ColumnInfo info = ColumnInfoList.FirstOrDefault <ColumnInfo>(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
if (info != null)
{
info.Align = info.Align ?? "left";
switch (info.Align.ToLower())
{
case "left":
info.Style = leftStyle;
break;
case "center":
info.Style = centerStyle;
break;
case "right":
info.Style = rightStyle;
break;
}
info.IsMapDT = true;
}
}
//标题头索引
int headIndex = string.IsNullOrEmpty(exportSheetInfo.Remark) ? 0 : 1;
int total = dt.Rows.Count;
int sheetNum = (int)Math.Ceiling(total * 1.0 / (MAX_ROW_INDEX - headIndex - 1));
int drIndex = 0;
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
{
string sheetName = string.IsNullOrEmpty(exportSheetInfo.SheetName) ? "Sheet " + workbook.NumberOfSheets : (sheetNum > 1 ? (exportSheetInfo.SheetName + sheetIndex) : exportSheetInfo.SheetName);
sheet = workbook.CreateSheet(sheetName);
sheet.CreateFreezePane(0, headIndex + 1, 0, headIndex + 1);
if (headIndex > 0)
{
//输出备注行
IRow RemarkRow = sheet.CreateRow(0);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, ColumnInfoList.Count - 1));
ICell rcell = RemarkRow.CreateCell(0);
ICellStyle remarkStyle = workbook.CreateCellStyle();
remarkStyle.WrapText = true;
remarkStyle.VerticalAlignment = VerticalAlignment.Top;
remarkStyle.Alignment = HorizontalAlignment.Left;
IFont rfont = workbook.CreateFont();
rfont.FontHeightInPoints = 12;
remarkStyle.SetFont(rfont);
rcell.CellStyle = remarkStyle;
RemarkRow.HeightInPoints = rowHeight * 5;
rcell.SetCellValue(exportSheetInfo.Remark);
}
//输出表头
IRow headerRow = sheet.CreateRow(headIndex);
//设置行高
headerRow.HeightInPoints = rowHeight;
//首行样式
ICellStyle HeaderStyle = workbook.CreateCellStyle();
HeaderStyle.FillPattern = FillPattern.SolidForeground;
HeaderStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
IFont font = workbook.CreateFont();
font.Boldweight = short.MaxValue;
HeaderStyle.SetFont(font);
HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
HeaderStyle.Alignment = HorizontalAlignment.Center;
//输出表头信息 并设置表头样式
int i = 0;
foreach (var data in ColumnInfoList)
{
cell = headerRow.CreateCell(i);
cell.SetCellValue(data.Header.Trim());
cell.CellStyle = HeaderStyle;
i++;
}
//开始循环所有行
int iRow = 1 + headIndex;
int startRow = iRow;
while (startRow < MAX_ROW_INDEX && drIndex < total)
{
row = sheet.CreateRow(startRow);
row.HeightInPoints = rowHeight;
i = 0;
dr = dt.Rows[drIndex];
foreach (var item in ColumnInfoList)
{
cell = row.CreateCell(i);
if (item.IsMapDT)
{
cellValue = dr[item.Field];
Type columnType = dt.Columns[item.Field].DataType;
cell.SetCellValue(cellValue, columnType);
cell.CellStyle = item.Style;
if (item.IsLink)
{
cellValue = dr[item.Field + "Link"];
if (cellValue != DBNull.Value && cellValue != null)
{
//建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
//给HSSFHyperlink的地址赋值 ，默认为该列加上Link
link.Address = cellValue.ToString();
cell.Hyperlink = link;
cell.CellStyle = leftStyleLink;
}
}
}
i++;
}
drIndex++;
startRow++;
writeTotal++;
}
//自适应列宽度
for (int j = 0; j < ColumnInfoList.Count; j++)
{
sheet.AutoSizeColumn(j);
int width = sheet.GetColumnWidth(j) + 2560;
sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
}
}
}
if (s is ZipOutputStream)
{
ZipOutputStream zs = s as ZipOutputStream;
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
byte[] m_buffer = ms.GetBuffer();
zs.Write(m_buffer, 0, m_buffer.Length);
}
}
else
{
workbook.Write(s);
}
}