使用EPPlus库以Excel中的值替换公式 [英] Using EPPlus library to replace formulas by their values in Excel

问题描述

我已经阅读到 Microsoft.Office.Interop.Excel 是用Excel中的值替换公式的最简单方法，但是它需要安装Office.由于我将需要在Windows Server(2008或2012)上进行部署，因此我正在寻找使用 EPPlus 的最佳和/或最简单的方法.

I've read that Microsoft.Office.Interop.Excel would be the easiest way to replace formulas by their values in Excel but it requires to have Office installed. Since I will need to deploy on a Windows Server (2008 or 2012), I am looking for the best and/or simplest way to accomplish that using EPPlus.

添加公式已得到充分证明，例如

Adding formulas is well documented, e.g.

currentWorksheet.Cells["C4"].Formula = "SUM(C2:C3)";

但是我找不到用其等效值替换整个公式工作表的任何示例.基本上，在Excel中是复制"后面是选择性粘贴"选项.

But I cannot find any example of replacing entire worksheets of formulas by their equivalent values. Basically the Copy followed by the Paste Special option in Excel.

推荐答案

我认为Epplus内置的任何功能都无法为您实现.但是，您可以利用以下事实，即 Worksheet 的 Cells 集合仅包含具有内容的单元格的条目.因此，在性能方面，这样的事情应该不会太痛苦:

I dont think there is any kind of function built into Epplus that will do that for you en masse. But you can take advantage of the fact that the Cells collection of the Worksheet only contains entries for cells with content. So something like this should not be too painful performance-wise:

currentWorksheet.Cells["C2"].Value = 5;
currentWorksheet.Cells["C3"].Value = 15;
currentWorksheet.Cells["C4"].Formula = "SUM(C2:C3)";

currentWorksheet.Cells["D2"].Value = 15;
currentWorksheet.Cells["D3"].Value = 25;
currentWorksheet.Cells["D4"].Formula = "SUM(D2:D3)";

//Calculate the formulas and the overwrite them with their values
currentWorksheet.Cells.Calculate();
foreach (var cell in currentWorksheet.Cells.Where(cell => cell.Formula != null))
    cell.Value = cell.Value;

这篇关于使用EPPlus库以Excel中的值替换公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！