Dotnet6 NPOI操作Excel基本操作总结

  • Dotnet6 NPOI操作Excel基本操作总结已关闭评论
  • 107 次浏览
  • A+
所属分类:.NET技术

背景

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

步骤

  • Dotnet 6Nuget 安装 NPOI, 具体版本 2.6.1,tips: 搜索资料时,可能NPOI 1 与 NPOI 2 可能有出入。
    Dotnet6 NPOI操作Excel基本操作总结

使用方法

  • 获取相应文档对象
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);             }         } 

最后

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