之前记录过如何判断 excel，word 的笔记，这里用到了就顺便写一下怎么构造与读取 excel ，毕竟在工作中用到的还是挺多的，导出文件，下载模板，读取文件都时挺常用的。话不多说，直接开始。
1. 构造 Excel 文件，从下载Npoi 的包开始，这个时候主要就是 Sheet表，Row行，Cell 列 ，以及 Style 等相关参数进行设置
//创建对象
HSSFWorkbook workbook = new HSSFWorkbook(); //创建电子表 ISheet sheet = workbook.CreateSheet(yearPeriodList[ii].YearPeriodName + ii);//创建首行 IRow rowA = sheet.CreateRow(0); //创建首行首列 ICell cell = rowA.CreateCell(0); //设置单元格样式 cell.CellStyle = ExcelHelper.SetFontAlign(workbook, "微软雅黑", 14); //设置单元格值 cell.SetCellValue(yearPeriodList[ii].YearPeriodName + ii); //合并单元格 首行 末行 首列 末列 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
2. 设置内容，样式等。有时候需要对单元格的字体，背景，以及下拉选项进行赋值
public class ExcelHelper {
//返回默认标题单元格样式 public static ICellStyle GetTitleCellStyle(HSSFWorkbook workbook) { ICellStyle style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); //创建一个字体样式对象 font.FontName = "微软雅黑"; //和excel里面的字体对应 font.FontHeightInPoints = 14; //字体大小 style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上框线 style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下框线 style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左框线 style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //右框线 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 style.SetFont(font); return style; } /// <summary> /// 设置字体颜色 /// </summary> /// <param name="style"></param> /// <param name="color"></param> public static ICellStyle SetFontColor(HSSFWorkbook workbook,ICellStyle style,short color) { //创建一个字体颜色 IFont font = workbook.CreateFont(); //设置字体颜色 font.Color = color; font.FontHeightInPoints = 12; style.BorderTop = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.SetFont(font); return style; } //设置单元格对齐方式,字体 public static ICellStyle SetFontAlign(HSSFWorkbook workbook, string fontStyle = "Arial", short fontSize = 10, HorizontalAlignment align0 = HorizontalAlignment.Center, VerticalAlignment align1 = VerticalAlignment.Center) { ICellStyle style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); //创建一个字体样式对象 font.FontName = fontStyle; //和excel里面的字体对应 font.FontHeightInPoints = fontSize; style.Alignment = align0; style.VerticalAlignment = align1; style.SetFont(font); style.IsLocked = true; return style; } //绑定下拉列表值--数据项较少 public static void SetCellDropdownList(ISheet sheet, int firstrow, int lastrow, int firstcol, int lastcol, string[] vals,bool bol = false) { if (bol) { for (int i = firstrow; i < lastrow; i++) { for (int j = firstcol; j < lastcol; j++) { sheet.CreateRow(i).CreateCell(j).CellStyle.IsLocked = false; } } } //设置生成下拉框的行和列 var cellRegions = new CellRangeAddressList(firstrow, lastrow, firstcol, lastcol); //设置 下拉框内容 DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals); //绑定下拉框和作用区域，并设置错误提示信息 HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate.ShowPromptBox = true; sheet.AddValidationData(dataValidate); } //绑定下拉列表值--数据项较多 Edit by fanzhang in 20200920 public static void SetCellDropdownListMul(HSSFWorkbook workbook, ISheet sheet, string name, int firstrow, int lastrow, int firstcol, int lastcol, string[] vals, int sheetindex = 1) { //先创建一个Sheet专门用于存储下拉项的值 ISheet sheet2 = workbook.CreateSheet(name); //隐藏 workbook.SetSheetHidden(sheetindex, true); int index = 0; foreach (var item in vals) { sheet2.CreateRow(index).CreateCell(0).SetCellValue(item); index++; } //创建的下拉项的区域： var rangeName = name + "Range"; IName range = workbook.CreateName(); range.RefersToFormula = name + "!$A$1:$A$" + index; range.NameName = rangeName; CellRangeAddressList regions = new CellRangeAddressList(firstrow, lastrow, firstcol, lastcol); //设置约束 DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(rangeName); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate.ShowPromptBox = true; sheet.AddValidationData(dataValidate); } }