无法使用EPPLUS计算公式 [英] Formula not calculating with EPPLUS

问题描述

我想将 Datatable 传送给excel并计算总和.为此，我正在使用epplus.这是我的代码:

I would like to transfer a Datatableto excel and calculate a sum. For this I'm using epplus. Here is my code:

Sheet.Cells["A1"].LoadFromDataTable(dsExcel.Tables[0], true, TableStyles.Medium9);
Sheet.Cells[Sheet.Dimension.Address].AutoFitColumns();
Sheet.View.FreezePanes(3, 3);

int totalCols = Sheet.Dimension.End.Column;
int letztezeile = Sheet.Dimension.End.Row;
var range = Sheet.Cells[1, 1, 1, totalCols];

for (int i = 3; i < totalCols; i++)
{
   if (range[1, i].Value.ToString().Contains("Durchfluss")) Sheet.Column(i).Style.Numberformat.Format = "#,##0.00";
 }

 Sheet.InsertRow(2, 1);
 Sheet.Cells["A2"].Value = "Summe";
 for (int i = 4; i <= totalCols; i++)
 {
     Sheet.Cells[2, i, 2, i].Formula = "SUMME(" + range[3, i].Address + ":" + range[letztezeile + 1, i].Address + ")";
 }

这是我的输出:

我必须输入单元格D2并按Enter键，然后公式才起作用:

I have to enter the cell D2 and press Enter, then the formula is working:

那么，该公式从一开始就应该做什么呢?谢谢

So what must I change that the formula is working from the beginning? Thanks

推荐答案

您应将"SUMME"更改为"SUM"，本地化的公式名称由Excel GUI处理.底层xml(与Excel产生的结果相同)假定公式名称为英文.

You should change "SUMME" to "SUM", localized formula names is handled by the Excel GUI. The underlying xml (same as Excel produces) assumes that the formula names are in english.

从EPPlus公式calc文档中:

From EPPlus Formula calc documentation:

• 请勿使用本地化的函数名称.仅支持英文名称(例如SUM，IF，VLOOKUP等).
• 请勿使用分号作为函数参数之间的分隔符.仅支持逗号.
• 请勿在公式中添加前导=符号."= SUM(A1:A2)"是错误的，"SUM(A1:A2)"是正确的

这篇关于无法使用EPPLUS计算公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！