设置*两者*值&EPPlus中的公式 [英] Setting *both* Value & Formula in EPPlus

问题描述

是否可以通过任何方式设置两者和单元格的 Formula 属性，以便它们保留到写入的XLSX文件中?

Is there any way to set both the Value & Formula properties of a cell so that they persist into the written XLSX file?

由于EPPlus不会计算公式(它依靠excel来计算)，所以我正在为用户预先计算结果.这样，如果他们不打开文件，然后重新上传文件，则值"不为null.公式非常简单.

Since EPPlus does not calculate the formulas (it relies on excel to do so), I am pre-calculating the result for the user. This is so that if they do not open the file, but then re-upload it, the 'value' is not null. The formulas are really simple.

注意: sheet.SetValue()和 .Value 都重置 ExcelCell 类的Formula属性，因此必须先调用 .Value .请查看 ExcelCell.SetRichText()的源代码以了解它的发生.

Note: sheet.SetValue() and .Value both reset the Formula property of the ExcelCell class, so .Value must be called first. See the source code for ExcelCell.SetRichText() to see it happening.

这里几乎是测试案例中的确切代码:

Here is almost the exact code from the test case:

var package = new ExcelPackage(); // and setup code
var sheet = package.Workbook.Worksheets.First(); 
sheet.Cells["A2"].Value = 10; 
sheet.Cells["A2"].Formula = "=A1 * 3";
// Cell is OK now.  shows both ".Value" and ".Formula"

using (var s = new MemoryStream()) { 
    package.Save(s);
    var newPackage = new ExcelPackage(s);
    var newSheet = newPackage.Workbook.Worksheets.First();
    var cell = newSheet.Cells["A2"];
    // cell.Value == null
    // cell.Formula = "=A1 * 3"
}

推荐答案

从EpPlus版本4.0.1.1开始，存在扩展方法 Calculate(此ExcelRangeBase范围).调用它:

As of EpPlus version 4.0.1.1, there is an extension method Calculate(this ExcelRangeBase range). Invoke it:

cell.Calculate();

，然后 cell.Value 将返回预期结果.

and after that the cell.Value will return the expected result.

更新

来自官方文档:

EPPlus支持版本4中的公式计算.这意味着您可以让EPPlus在工作簿.

EPPlus supports formula calculation from version 4. This means that you can let EPPlus calculate the results of the formulas in a workbook.

这是通过调用Calculate()方法完成的，该方法可用于工作簿，工作表和范围级别.当Calculate()称为EPPlus时将评估公式的结果并将结果存储为单元格的价值-就像Excel一样.

This is done by calling the Calculate() method, which is available on Workbook, Worksheet and Range level. When Calculate() is called EPPlus will evaluate the result of the formula and store the result as the Value of the cell - just like Excel do.

示例

using(var package = new ExcelPackage(new FileInfo(@"c:\temp\tmp.xlsx")))
{
   // calculate all formulas in the workbook
   package.Workbook.Calculate();
   // calculate one worksheet
   package.Workbook.Worksheets["my sheet"].Calculate();
  // calculate a range
  package.Workbook.Worksheets["my sheet"].Cells["A1"].Calculate();
}

已知问题

EPPlus当前不支持Excel的以下功能当涉及到公式计算时:数组公式，相交操作员，引用外部工作簿.

The following features of Excel are currently not supported by EPPlus when it comes to Formula caluclation: Array Formulas, the Intersect operator, references to external workbooks.

这篇关于设置*两者*值&EPPlus中的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！