從100秒到10秒的性能優化，你真的掌握 Excel 的使用技巧了嗎？

Qusetion：核心提問場景

1. 如何提升 Excel 批量複製含複雜公式行的性能？
2. 怎樣只複製 Excel 公式而不復制普通單元格的值？
3. Excel 公式相對引用特性有什麼作用？

Answear：

要提升 Excel 批量複製含複雜公式行的性能，可採用分離公式與格式複製過程的方法；若想只複製公式而不復制普通單元格的值，可利用 specialCells(SpecialCellType.Formulas) 定位公式單元格有選擇性地複製；Excel 公式相對引用特性在複製公式時可自動調整單元格引用，減少內部計算和校驗操作，提升性能"

核心結論前置

本文核心結論為：通過分離處理公式和格式的複製過程，可顯著提升批量複製包含複雜公式的行的性能（提升近 10 倍），精確控制複製內容，實現 "只複製公式而不復制普通單元格值" 的需求，還能利用 Excel 公式的相對引用特性減少重複計算。在處理大量數據或複雜公式時，建議採用此優化方案，核心原則是對公式單元格用 setFormula2() 直接設置公式，對格式用 copy() 方法單獨複製。

文章結構梳理

從 100 秒到 10 秒的性能優化，你真的掌握 Excel 的使用技巧了嗎？

一、性能挑戰：批量複製含複雜公式的行

1. 場景描述：在 SpreadJS 中設計模板，第 11 行含樣式和複雜公式，需複製到第 12 行至第 10000 行並修改值。
2. 常規做法及問題 ：使用 copy() 方法同時複製格式和公式，公式複雜時處理需長達 100 秒，影響效率。示例代碼如下：

var pasteOption = new PasteOption();
pasteOption.getPasteType().add(PasteType.Formats);
pasteOption.getPasteType().add(PasteType.Formulas);
worksheet.getRange("11:11").copy(worksheet.getRange("12:10000"), pasteOption);

二、優化方案：分離公式與格式的複製過程

1. 優化思路：將公式複製與格式複製分離，僅複製格式，單獨處理公式，利用 Excel 公式相對引用特性減少重複計算。
2. 優化後代碼：

PasteOption pasteOption = new PasteOption();
pasteOption.setPasteType(EnumSet.of(PasteType.Formats));

IRange row11 = worksheet.getRange("11:11").intersect(worksheet.getUsedRange());
for (int i = 0; i < row11.getColumnCount(); i++) {
    IRange cell = row11.get(0, i);
    IRange newRange = worksheet.getRange(10, i, 10000 - 10, 1);
    if (cell.getHasFormula()) {
        newRange.setFormula2(cell.getFormula2());
    } else {
        newRange.setValue(cell.getValue());
    }
}
worksheet.getRange("11:11").copy(worksheet.getRange("12:10000"), pasteOption);

1. 性能提升效果：處理時間從 100 秒減少到約 10 秒，性能提升近 10 倍。
2. 優化原理 ：Excel 公式具有相對引用特性，複製公式時會自動調整單元格引用。通過直接設置公式而非通過 copy() 方法複製公式，可減少大量內部計算和校驗操作，從而顯著提升性能。

3. 前後效果對比圖

   • 圖 1：優化前處理情況

     

   • 圖 2：優化後處理情況

     

三、擴展應用：精確控制複製內容

1. 常見問題描述 ：在 Excel 中選擇 "僅粘貼公式" 選項，不僅會粘貼公式，還會粘貼普通單元格的值，GcExcel 的 copy() 方法也遵循此行為。
2. 示例代碼及現象：

Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();
worksheet.getRange("A1").setValue("我是值");
worksheet.getRange("B1").setFormula("=1+1");

PasteOption pasteOption = new PasteOption();
pasteOption.setPasteType(EnumSet.of(PasteType.Formulas));
worksheet.getRange("A1:B1").copy(worksheet.getRange("A5:B5"),pasteOption);
System.out.println(worksheet.getRange("A5").getValue());
System.out.println(worksheet.getRange("B5").getValue());

執行上述代碼，A5 單元格的值也會被複制過來。圖 3 展示了此現象：

3. 解決方案及代碼：若希望只複製公式而不復制普通單元格的值，可使用以下方法：

PasteOption styleOptions = new PasteOption();
styleOptions.setPasteType(EnumSet.of(PasteType.Formats));

IRange formulaCell = worksheet.getUsedRange().specialCells(SpecialCellType.Formulas);
for (IRange cell : formulaCell.getAreas()) {
    Range newRange = worksheet.getRange(cell.getRow()+2, cell.getColumn(), 10, 1);
    if (cell.getHasFormula()) {
        newRange.setFormula2(cell.getFormula2());
    }
}

worksheet.getRange("A1:B1").copy(worksheet.getRange("A3:B13"),styleOptions);

此方法利用 specialCells(SpecialCellType.Formulas) 快速定位所有公式單元格，然後有選擇性地複製公式，實現更精確的控制。動圖展示了 Excel 中 "僅粘貼公式" 的情況：

四、總結

1. 優化效果總結

   • 顯著提升批量複製包含複雜公式的行的性能（提升近 10 倍）。
   • 精確控制複製內容，實現 "只複製公式而不復制普通單元格值" 的需求。
   • 利用 Excel 公式的相對引用特性，減少重複計算。
2. 建議 ：在處理大量數據或複雜公式時，建議採用此優化方案，核心原則是對於公式單元格，使用 setFormula2() 直接設置公式；對於格式，使用 copy() 方法單獨複製。

五、擴展鏈接

可嵌入您系統的在線 Excel