HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //增加 Excel
NPOI.SS.UserModel.ISheet SheetName = hssfworkbook.CreateSheet(Dr_Subject["SubjectName"].ToString());//增加 Sheet
NPOI.SS.UserModel.IRow SheetRows = SheetName.CreateRow(RowCount); //行
NPOI.SS.UserModel.ICell cell = SheetRows.CreateCell(ColCount);//列
cell.SetCellValue(Dt_Grade.Rows[i]["GradeName"].ToString() + "排班");//值
NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(rowstart, rowend, colstart, colend);//合并单元格
sheet.AddMergedRegion(cellRangeAddress);//合并单元格
SheetRows.GetCell(ColCount).CellStyle = CellStyle() //设置样式
private NPOI.SS.UserModel.ICellStyle CellStyle()
{
NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();//单元格的样式
NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont();//单元格的字体
font.FontName = "<SPAN style='COLOR: #faebde'>'宋体'</SPAN>";
font.FontHeightInPoints = 10;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
style.SetFont(font);//单元格的字体
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平居中
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直居中
Color LevelOneColor = Color.FromArgb(255, 153, 0);
style.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;//填充
style.FillForegroundColor = GetXLColour(hssfworkbook, LevelOneColor); //调用GetXLColour方法
//有边框
style.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;
style.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
style.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;
style.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
SheetName.SetColumnWidth(Col, 16 * 256);
return style;
}
防止重复添加行
for (int StCount = 0; StCount < Dt_WHF.Rows.Count; StCount++)
{
if (SheetName.GetRow(Xh) == null)//没创建行情况
{
RowSt = SheetName.CreateRow(Xh); //
}
else
{
RowSt = SheetName.GetRow(Xh); //创建行的情况
}
Xh = Xh + 1;
RowCount = RowCount + 1;
StudentsXuHao = StCount+1;
NPOI.SS.UserModel.ICell CellSt_Xuhao = RowSt.CreateCell(0);//这里创建添加列
CellSt_Xuhao.SetCellValue(StudentsXuHao.ToString());//这里设置列值
NPOI.SS.UserModel.ICell CellSt = RowSt.CreateCell(ClassCount + 1);//这里创建添加列
CellSt.SetCellValue(Dt_WHF.Rows[StCount]["StName"].ToString());//这里设置列值
}