private void toExcel(IEnumerable<ExportColumnsInfoModel> models, DataTable dt, string templateFile, string fileName, int ToExcelRowMax)
{
try
{
var fs = File.OpenRead(templateFile);
var wk = new HSSFWorkbook(fs);
fs.Close();
#region 表头样式
ICellStyle styleTitle = wk.CreateCellStyle();
//设置单元格的样式:水平对齐居中
styleTitle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontHeightInPoints = 22;
styleTitle.SetFont(font);
styleTitle.FillPattern = FillPattern.SolidForeground;
styleTitle.FillForegroundColor = HSSFColor.Green.Index;
#endregion
#region 列标题头样式
ICellStyle styleHeader = wk.CreateCellStyle();
//设置单元格的样式:水平对齐居中
styleHeader.Alignment = HorizontalAlignment.Center;
styleHeader.FillPattern = FillPattern.SolidForeground;
styleHeader.FillForegroundColor = HSSFColor.Grey25Percent.Index;
#endregion
int n = 0;
if (dt.Rows.Count > ToExcelRowMax)
{
if (dt.Rows.Count % ToExcelRowMax == 0)
n = dt.Rows.Count / ToExcelRowMax - 1;
else
n = dt.Rows.Count / ToExcelRowMax;
}
for (int page = 0; page <= n; page++)
{
int visiblefalse = 0;
Int32 row = 1;
var sheet = wk.GetSheetAt(page);
wk.SetSheetName(page, (dt.TableName.Trim().ToString().Equals("") ? "Sheet" : dt.TableName.Trim().ToString()) + (page + 1).ToString());
//DataGridView.Name若已赋值则作为EXCEL表的首行标题
if (!dt.TableName.Trim().ToString().Equals(""))
{
//第一行为报表名称
//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
sheet.CreateRow(row - 1).CreateCell(0).SetCellValue(dt.TableName.Trim().ToString());
row = row + 1;
}
//第二行为报表列名
int showColumn = 0;
IRow rowHeader = null;
foreach (ExportColumnsInfoModel model in models)
{
if (model.visible.ToString().ToLower().Equals("true"))
{
if (rowHeader == null)
rowHeader = sheet.CreateRow(row - 1);
rowHeader.CreateCell(showColumn++).SetCellValue(model.header);
rowHeader.GetCell(showColumn-1).CellStyle = styleHeader;
}
else
visiblefalse++;
}
if (!dt.TableName.Trim().ToString().Equals(""))
{
if (models.Count() > 0 && models.Count() - visiblefalse > 0)
{
//标题栏的合并单元格,居中
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, models.Count() - visiblefalse - 1));
sheet.GetRow(0).GetCell(0).CellStyle = styleTitle;
}
}
//逐行写入数据::过滤掉不显示的列
int i = 0;
for (; i < ToExcelRowMax && i < dt.Rows.Count - ToExcelRowMax * page; i++)
{
showColumn = 0;
var detailRow = sheet.CreateRow(row + i);
foreach (ExportColumnsInfoModel model in models)
{
if (model.visible.ToString().ToLower().Equals("true") && dt.Columns.Contains(model.field))
{
detailRow.CreateCell(showColumn++).SetCellValue(dt.Rows[i + page * ToExcelRowMax][model.field].ToString());
}
}
}
for (int columnNum = 0; columnNum <= models.Count() - visiblefalse - 1; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行
{
IRow currentRow = sheet.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}//若当前单元格内容宽度大于列宽,则调整列宽为当前单元格宽度,后面的+1是我人为的将宽度增加一个字符
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
//分页
if (page + 1 <= n)
{
if (wk.NumberOfSheets <= page + 1)
wk.CreateSheet();
}
}
using (FileStream fsNew = File.OpenWrite(fileName)) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件!
{
wk.Write(fsNew); //向打开的这个xls文件中写入mySheet表并保存。
}
}
catch (Exception ex)
{
throw ex;
}
}
{
try
{
var fs = File.OpenRead(templateFile);
var wk = new HSSFWorkbook(fs);
fs.Close();
#region 表头样式
ICellStyle styleTitle = wk.CreateCellStyle();
//设置单元格的样式:水平对齐居中
styleTitle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontHeightInPoints = 22;
styleTitle.SetFont(font);
styleTitle.FillPattern = FillPattern.SolidForeground;
styleTitle.FillForegroundColor = HSSFColor.Green.Index;
#endregion
#region 列标题头样式
ICellStyle styleHeader = wk.CreateCellStyle();
//设置单元格的样式:水平对齐居中
styleHeader.Alignment = HorizontalAlignment.Center;
styleHeader.FillPattern = FillPattern.SolidForeground;
styleHeader.FillForegroundColor = HSSFColor.Grey25Percent.Index;
#endregion
int n = 0;
if (dt.Rows.Count > ToExcelRowMax)
{
if (dt.Rows.Count % ToExcelRowMax == 0)
n = dt.Rows.Count / ToExcelRowMax - 1;
else
n = dt.Rows.Count / ToExcelRowMax;
}
for (int page = 0; page <= n; page++)
{
int visiblefalse = 0;
Int32 row = 1;
var sheet = wk.GetSheetAt(page);
wk.SetSheetName(page, (dt.TableName.Trim().ToString().Equals("") ? "Sheet" : dt.TableName.Trim().ToString()) + (page + 1).ToString());
//DataGridView.Name若已赋值则作为EXCEL表的首行标题
if (!dt.TableName.Trim().ToString().Equals(""))
{
//第一行为报表名称
//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
sheet.CreateRow(row - 1).CreateCell(0).SetCellValue(dt.TableName.Trim().ToString());
row = row + 1;
}
//第二行为报表列名
int showColumn = 0;
IRow rowHeader = null;
foreach (ExportColumnsInfoModel model in models)
{
if (model.visible.ToString().ToLower().Equals("true"))
{
if (rowHeader == null)
rowHeader = sheet.CreateRow(row - 1);
rowHeader.CreateCell(showColumn++).SetCellValue(model.header);
rowHeader.GetCell(showColumn-1).CellStyle = styleHeader;
}
else
visiblefalse++;
}
if (!dt.TableName.Trim().ToString().Equals(""))
{
if (models.Count() > 0 && models.Count() - visiblefalse > 0)
{
//标题栏的合并单元格,居中
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, models.Count() - visiblefalse - 1));
sheet.GetRow(0).GetCell(0).CellStyle = styleTitle;
}
}
//逐行写入数据::过滤掉不显示的列
int i = 0;
for (; i < ToExcelRowMax && i < dt.Rows.Count - ToExcelRowMax * page; i++)
{
showColumn = 0;
var detailRow = sheet.CreateRow(row + i);
foreach (ExportColumnsInfoModel model in models)
{
if (model.visible.ToString().ToLower().Equals("true") && dt.Columns.Contains(model.field))
{
detailRow.CreateCell(showColumn++).SetCellValue(dt.Rows[i + page * ToExcelRowMax][model.field].ToString());
}
}
}
for (int columnNum = 0; columnNum <= models.Count() - visiblefalse - 1; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行
{
IRow currentRow = sheet.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}//若当前单元格内容宽度大于列宽,则调整列宽为当前单元格宽度,后面的+1是我人为的将宽度增加一个字符
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
//分页
if (page + 1 <= n)
{
if (wk.NumberOfSheets <= page + 1)
wk.CreateSheet();
}
}
using (FileStream fsNew = File.OpenWrite(fileName)) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件!
{
wk.Write(fsNew); //向打开的这个xls文件中写入mySheet表并保存。
}
}
catch (Exception ex)
{
throw ex;
}
}