- A+
所属分类:.NET技术
背景
- 需要对Excel进行读取和写入,目前使用Dotnet6开发环境,故直接使用。
- 达到的效果:兼容
.xls
和.xlsx
,识别行为空自动跳过,识别显示值,识别格式内容
步骤
Dotnet 6
Nuget 安装NPOI
, 具体版本2.6.1
,tips: 搜索资料时,可能NPOI 1 与 NPOI 2 可能有出入。
使用方法
- 获取相应文档对象
public static IWorkbook OpenWorkbook(string path) { using (var stream = File.OpenRead(path)) { if (Path.GetExtension(path) == ".xls") return new HSSFWorkbook(stream); else return new XSSFWorkbook(stream); } }
- 根据序号获取相应sheet对象,并复制该sheet,添加到原Excel第一个
public static void MoveAndCopySheet(string path, int index = 0) { var workbook = OpenWorkbook(path); var sheet = workbook.GetSheetAt(index); if (sheet == null) throw new Exception($"Sheet 'At {index}' not found."); var clonedSheet = workbook.CloneSheet(0); var cloneSheetName = $"{sheet.SheetName}_copy_{DateTime.Now.ToShortTimeString().Replace(":","-")}"; workbook.SetSheetName(workbook.GetSheetIndex(clonedSheet), cloneSheetName); workbook.SetSheetOrder(cloneSheetName, 0); using (var stream = File.OpenWrite(path)) { workbook.Write(stream); } }
- 根据sheet名称,复制,并移动到第一个
public static void MoveAndCopySheet(string path, string sheetName) { var workbook = OpenWorkbook(path); var sheet = workbook.GetSheet(sheetName); if (sheet == null) throw new Exception($"Sheet '{sheetName}' not found."); workbook.SetSheetOrder(sheetName, 0); var clonedSheet = workbook.CloneSheet(0); workbook.SetSheetName(workbook.GetSheetIndex(clonedSheet), sheetName + "_copy"); using (var stream = File.OpenWrite(path)) { workbook.Write(stream); } }
- 循环行,判断对应单元格内容类型
public static void ProcessRows(string path, string sheetName) { var workbook = OpenWorkbook(path); var sheet = workbook.GetSheet(sheetName); if (sheet == null) throw new Exception($"Sheet '{sheetName}' not found."); for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++) { var row = sheet.GetRow(rowIndex); if (row == null) continue; for (int cellIndex = 0; cellIndex < row.LastCellNum; cellIndex++) { var cell = row.GetCell(cellIndex); if (cell == null) continue; var isMerged = sheet.GetMergedRegion(cell.RowIndex) != null; var showValue = cell.ToString(); var isFormula = cell.CellType == CellType.Formula; var format = isFormula ? cell.CellFormula : showValue; //Console.WriteLine($"[{isMerged}, {showValue}, {isFormula ? format : showValue}]"); } } }
- 循环行,对单元格进行处理,赋值后重新写入
public static void AddAndMapColumn(string path, string sheetName, int columnIndex) { var workbook = OpenWorkbook(path); var sheet = workbook.GetSheet(sheetName); if (sheet == null) throw new Exception($"Sheet '{sheetName}' not found."); for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++) { var row = sheet.GetRow(rowIndex); if (row == null) continue; var cell = row.GetCell(columnIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); var newCell = cell == null ? row.CreateCell(columnIndex + 1) : row.GetCell(columnIndex + 1, MissingCellPolicy.CREATE_NULL_AS_BLANK); if (cell != null && cell.CellType == CellType.Numeric) newCell.SetCellValue(cell.NumericCellValue + 1); else if (cell != null && cell.CellType == CellType.String) newCell.SetCellValue(cell.StringCellValue); } using (var stream = File.OpenWrite(path)) { workbook.Write(stream); } }
最后
- 使用语言大模型会很快得到答案,但结果需要自行判断验证,有些时候无法保证,只能继续“拼凑”可用代码,但相对已经很好用了。愿以后大模型越来越好用,国产的要跟上。