Dotnet6 NPOI操作Excel基本操作总结

背景

• 需要对Excel进行读取和写入，目前使用Dotnet6开发环境，故直接使用。
• 达到的效果：兼容.xls和.xlsx，识别行为空自动跳过，识别显示值，识别格式内容

步骤

• Dotnet 6Nuget 安装 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);             }         } 

最后

• 使用语言大模型会很快得到答案，但结果需要自行判断验证，有些时候无法保证，只能继续“拼凑”可用代码，但相对已经很好用了。愿以后大模型越来越好用，国产的要跟上。