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

最後

• 使用語言大模型會很快得到答案，但結果需要自行判斷驗證，有些時候無法保證，只能繼續“拼湊”可用代碼，但相對已經很好用了。願以後大模型越來越好用，國產的要跟上。