NPOI Excel操作实例：常见操作演示

1. 创建空白工作簿及工作表

IWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet();

2. 设置单元格样式

ICellStyle style = workbook.CreateCellStyle();style.Alignment = HorizontalAlignment.CENTER;IFont font = workbook.CreateFont();font.Boldweight = short.MaxValue;style.SetFont(font);

3. 设置单元格宽高

sheet.SetColumnWidth(0, 256 * 30); // 单元格宽度设置sheet.SetRowHeight(0, 20 * 30); // 单元格高度设置

4. 合并单元格

CellRangeAddress address = new CellRangeAddress(0, 0, 0, 10);sheet.AddMergedRegion(address);

5. 添加公式

ICell cell = sheet.CreateRow(1).CreateCell(0);cell.CellFormula = "HYPERLINK(\"路径\",\"显示文本\")";

6. 写入文件

using (FileStream fs = new FileStream("文件名.xlsx", FileMode.Create, FileAccess.Write)) {    workbook.Write(fs);}

7. 数字格式问题解决

sheet.CreateRow(1).CreateCell(2).SetCellValue(123);

8. 下拉列表实现

// 创建辅助表HSSFSheet sheet2 = workbook.CreateSheet();sheet2.CreateRow(0).CreateCell(0).SetCellValue("项A");sheet2.CreateRow(1).CreateCell(0).SetCellValue("项B");sheet2.CreateRow(2).CreateCell(0).SetCellValue("项C");// 创建名称引用HSSFName name = workbook.CreateName();name.Reference = "ShtDictionary!$A1:$A3";name.NameName = "dicRange";// 设置数据有效性CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);sheet1.AddValidationData(validation);