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);
            }
        }

最后

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