/// <summary>
/// 生成EXECL文件，通过读取DataTable和列头映射信息
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="excelInfo">Excel导出信息</param>
/// <returns>文件流</returns>
public static MemoryStream Export(DataTable dt, ExcelInfo excelInfo)
{
if (dt == null || excelInfo == null || excelInfo.ColumnInfoList == null)
{
throw new ArgumentNullException();
}
bool isMoreHeader = excelInfo.GroupHeader != null && excelInfo.GroupHeader.Count > 0;
if (isMoreHeader)
{
return(CreateMoreHeaderXls(dt, excelInfo));
}
int rowHeight = 20;
List <ColumnInfo> ColumnInfoList = excelInfo.ColumnInfoList;
//每个标签页最多行数
int sheetRow = 65536;
HSSFWorkbook 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;
//寻找列头和DataTable之间映射关系
foreach (DataColumn col in dt.Columns)
{
ColumnInfo info = ColumnInfoList.FirstOrDefault(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
if (info != null)
{
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 sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / MAX_ROW_INDEX);
int total = dt.Rows.Count;
//超链接字体颜色
IFont blueFont = workbook.CreateFont();
blueFont.Color = HSSFColor.Blue.Index;
//最多生成5个标签页的数据
sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
ICell cell = null;
object cellValue = null;
//标题头索引
int headIndex = string.IsNullOrEmpty(excelInfo.Remark) ? 0 : 1;
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
{
ISheet sheet = workbook.CreateSheet();
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(excelInfo.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 = sheetIndex * (sheetRow - 1);
int endRow = (sheetIndex + 1) * (sheetRow - 1);
endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;
for (int rowIndex = startRow; rowIndex < endRow; rowIndex++)
{
IRow row = sheet.CreateRow(iRow);
row.HeightInPoints = rowHeight;
i = 0;
foreach (var item in ColumnInfoList)
{
cell = row.CreateCell(i);
if (item.IsMapDT)
{
cellValue = dt.Rows[rowIndex][item.Field];
Type columnType = dt.Columns[item.Field].DataType;
cell.SetCellValue(cellValue, columnType);
cell.CellStyle = item.Style;
if (item.IsLink)
{
cellValue = dt.Rows[rowIndex][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.SetFont(blueFont);
}
}
}
i++;
}
iRow++;
}
//自适应列宽度
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);
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms);
}
/// <summary>
/// 生成EXECL文件，通过读取DataTable和列头映射信息
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="excelInfo">Excel导出信息</param>
/// <returns>文件流</returns>
public static MemoryStream CreateMoreHeaderXls(DataTable dt, ExcelInfo excelInfo)
{
int rowHeight = 20;
List <ColumnInfo> ColumnInfoList = excelInfo.ColumnInfoList;
HSSFWorkbook 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;
//首行样式
ICellStyle HeaderStyle = workbook.CreateCellStyle();
HeaderStyle.FillPattern = FillPattern.SolidForeground;
HeaderStyle.FillForegroundColor = HSSFColor.LightCornflowerBlue.Index;
HeaderStyle.BorderTop = BorderStyle.Thin;
HeaderStyle.BorderLeft = BorderStyle.Thin;
HeaderStyle.BorderRight = BorderStyle.Thin;
HeaderStyle.BorderBottom = BorderStyle.Thin;
HeaderStyle.TopBorderColor = HSSFColor.Black.Index;
HeaderStyle.LeftBorderColor = HSSFColor.Black.Index;
HeaderStyle.RightBorderColor = HSSFColor.Black.Index;
HeaderStyle.BottomBorderColor = HSSFColor.Black.Index;
IFont font = workbook.CreateFont();
font.Boldweight = short.MaxValue;
HeaderStyle.SetFont(font);
HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
HeaderStyle.Alignment = HorizontalAlignment.Center;
Dictionary <string, int> dictGroupMap = new Dictionary <string, int>(StringComparer.OrdinalIgnoreCase);
Dictionary <ColumnInfo, bool> dictColumn = new Dictionary <ColumnInfo, bool>();
//寻找列头和DataTable之间映射关系
foreach (DataColumn col in dt.Columns)
{
ColumnInfo info = ColumnInfoList.FirstOrDefault <ColumnInfo>(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
if (info != null)
{
dictColumn[info] = col.DataType == typeof(int) || col.DataType == typeof(float) || col.DataType == typeof(double) ||
col.DataType == typeof(long);
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 index = 0;
foreach (var item in ColumnInfoList)
{
if (excelInfo.GroupHeader.FirstOrDefault(e => e.StartColumnName.Equals(item.Field, StringComparison.OrdinalIgnoreCase)) != null)
{
dictGroupMap[item.Field] = index;
}
index++;
}
List <int> listColumnIndex = new List <int>(ColumnInfoList.Count);
for (int i = 0; i < ColumnInfoList.Count; i++)
{
listColumnIndex.Add(i);
}
foreach (var item in excelInfo.GroupHeader)
{
int startCol = dictGroupMap[item.StartColumnName];
int lastCol = startCol + item.NumberOfColumns - 1;
for (int j = startCol; j <= lastCol; j++)
{
listColumnIndex.Remove(j);
}
}
int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / MAX_ROW_INDEX);
int total = dt.Rows.Count;
//超链接字体颜色
IFont blueFont = workbook.CreateFont();
blueFont.Color = HSSFColor.Blue.Index;
//最多生成5个标签页的数据
sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
ICell cell = null;
object cellValue = null;
//标题头索引
int headIndex = string.IsNullOrEmpty(excelInfo.Remark) ? 0 : 1;
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
{
ISheet sheet = workbook.CreateSheet();
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(excelInfo.Remark);
}
//输出表头
IRow firstHeaderRow = sheet.CreateRow(headIndex);
IRow secondHeaderRow = sheet.CreateRow(headIndex + 1);
//设置行高
firstHeaderRow.HeightInPoints = rowHeight;
secondHeaderRow.HeightInPoints = rowHeight;
//输出表头信息 并设置表头样式
int i = 0, groupIndex = 0;
MoreHeader groupheader = null;
foreach (var data in ColumnInfoList)
{
cell = secondHeaderRow.CreateCell(i);
cell.SetCellValue(data.Header.Trim());
cell.CellStyle = HeaderStyle;
cell = firstHeaderRow.CreateCell(i);
cell.SetCellValue(data.Header.Trim());
cell.CellStyle = HeaderStyle;
groupheader = excelInfo.GroupHeader[groupIndex];
if (groupheader.StartColumnName.Equals(data.Field, StringComparison.CurrentCultureIgnoreCase))
{
cell.SetCellValue(groupheader.TitleText.Trim());
groupIndex++;
if (groupIndex >= excelInfo.GroupHeader.Count)
{
groupIndex--;
}
}
i++;
}
foreach (var item in listColumnIndex)
{
sheet.AddMergedRegion(new CellRangeAddress(headIndex, headIndex + 1, item, item));
}
int startCol, lastCol;
foreach (var item in excelInfo.GroupHeader)
{
startCol = dictGroupMap[item.StartColumnName];
lastCol = startCol + item.NumberOfColumns - 1;
sheet.AddMergedRegion(new CellRangeAddress(headIndex, headIndex, startCol, lastCol));
}
//冻结列 行
sheet.CreateFreezePane(excelInfo.FixColumns, headIndex + 2, excelInfo.FixColumns, headIndex + 2);
//开始循环所有行
int iRow = 2 + headIndex;
int startRow = sheetIndex * (MAX_ROW_INDEX - 1);
int endRow = (sheetIndex + 1) * (MAX_ROW_INDEX - 1);
endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;
for (int rowIndex = startRow; rowIndex < endRow; rowIndex++)
{
IRow row = sheet.CreateRow(iRow);
row.HeightInPoints = rowHeight;
i = 0;
foreach (var item in ColumnInfoList)
{
cell = row.CreateCell(i);
if (item.IsMapDT)
{
cellValue = dt.Rows[rowIndex][item.Field];
Type columnType = dt.Columns[item.Field].DataType;
cell.SetCellValue(cellValue, columnType);
cell.CellStyle = item.Style;
if (item.IsLink)
{
cellValue = dt.Rows[rowIndex][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.SetFont(blueFont);
}
}
}
i++;
}
iRow++;
}
//自适应列宽度
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);
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms);
}
