如何触发Excel动态溢出数组自动调整大小（无需手动按回车）

问题根源 #

用EPPlus程序化插入数据后，Excel默认不会自动触发动态数组公式的溢出范围重计算，导致仅首行更新，后续行未展开。

解决方案 #

1. 通过EPPlus代码强制触发计算

在保存工作簿前，添加代码强制工作簿执行完整计算，并设置自动计算模式：

// 假设workbook是你的ExcelPackage.Workbook对象
// 插入数据到Data工作表的代码...

// 强制计算所有工作表
workbook.Calculate();
// 设置工作簿为自动计算模式
workbook.CalcMode = ExcelCalcMode.Automatic;

// 保存工作簿
package.SaveAs(new FileInfo("output.xlsx"));

也可以单独计算衍生工作表：

var derivedSheet = workbook.Worksheets["你的衍生工作表名称"];
derivedSheet.Calculate();

注意：确保使用EPPlus 5.8.0及以上版本，该版本开始支持Excel 365的动态数组函数。

2. 模板中预先设置自动重算

打开你的xlsx模板文件，按以下步骤设置：

• 点击「文件」→「选项」→「公式」
• 勾选「自动重算」，取消勾选「手动重算」
• 保存模板文件
  这样用户打开插入数据后的文件时，Excel会自动执行完整重算，动态数组会自动溢出展开。

3. 用Excel表格（List Object）承载动态数组公式

在衍生工作表中创建表格来容纳动态数组结果，表格会自动扩展行以匹配溢出范围：

• 选中衍生工作表中要放置公式的单元格（比如B2）
• 点击「插入」→「表格」，勾选「我的表格有标题」（如果需要标题行）
• 在表格的首列单元格输入公式：=UNIQUE(Data!A2:A10000,FALSE,FALSE)
  当数据更新后，表格会自动扩展，显示所有动态数组结果。

额外优化建议 #

• 将公式中的固定范围Data!A2:A10000替换为动态范围，比如Data!$A:$A，或者用INDEX(Data!$A:$A,2):INDEX(Data!$A:$A,COUNTA(Data!$A:$A))来精准定位有数据的行，提升计算效率。

内容的提问来源于stack exchange，提问作者jonh