从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