为什么除非我双击一个单元格，否则这个公式不会计算? [英] Why won't this formula calculate unless i double click a cell?
问题描述
请参阅此 .xlsx 文件.当然没有宏！
当您打开文件时，您将看到以下单元格:
[系列][10][20][30][ ][ ][=总和(B3:B7)]总和不会计算，除非您双击，然后在底部三个单元格之一(例如两个空白，=SUM(B3:B7))中按 Enter 键
<小时>更奇怪的是，如果您保存工作簿并重新打开它，那么一旦您将公式变为现实"，它就会表现得很好.
我已经尝试将扩展名更改为 .zip，并尝试使用 Xml 以查看是否可以在它开始工作后通过保存来识别所做的更改，但我只能看到版本号已更改.
我们.红外线.
<小时>此外，我尝试将其保存为早期的 Excel 格式，但问题仍然存在.
<小时>我只想重申 - 附加的电子表格是一个 .xlsx 文件，因此没有任何宏！如果没有附加的电子表格，真的没有其他方法可以解释这个问题.希望通过将 50 点声誉放在线上，有人会相信我不是脚本小子并看看！
<小时>我使用 EPPlus 和以下 C# 代码创建了该文件:
public void Generate(string outputPath){使用 (FileStream fsTemplate = new FileStream(TemplatePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)){使用 (FileStream fsReport = new FileStream(outputPath, FileMode.Create, FileAccess.ReadWrite, FileShare.None)){使用 (ExcelPackage ep = new ExcelPackage(fsReport, fsTemplate)){使用 (ExcelWorksheet ws = ep.Workbook.Worksheets[TemplateSheetName]){//int iHeadingsRow = 2;int dataRowIndex = iHeadingsRow + 1;//列表<十进制>list = new List() { 10, 20, 30 };//foreach(列表中的变量编号){ws.Cells[dataRowIndex, 2].Value = list[dataRowIndex - iHeadingsRow - 1];//ws.InsertRow(dataRowIndex + 1, 1, iHeadingsRow + 1);//数据行索引++；}//ep.Save();}}}}} 我想强调的是，这不是 EPPlus 的问题！这是 Excel 打开有效工作簿并拒绝计算公式的常见问题.将单元格格式化为数字没有区别.几个月来我一直在使用 EPPlus 创建可工作的 Excel 文件，而且我还没有安装任何 EPPlus 更新，所以肯定是 Excel 有问题，对吧?！
解决办法一定是原文件和被踢进生命然后保存的版本之间的差异.我似乎无法准确指出这种区别是什么......
<小时>=SUM(B3:B7) 已经存在于模板文件中，以及文本系列".该代码仅将 {10,20,30} 项添加到电子表格中.
您正在向已经包含 =SUM(B3:B7) 公式的现有 Excel 工作表添加数据.
我猜是一个 EPPlus 问题.也就是说，问题在于您没有在之后调用 Calculate() 方法输入数据:
<块引用>...您可以让 EPPlus 计算工作簿中公式的结果.
这是通过调用Calculate() 方法完成的，该方法可用于工作簿、工作表和范围级别.当调用Calculate() 时，EPPlus 将计算公式的结果并将结果存储为单元格的值——就像Excel 那样.
在 Excel 中键入公式后，Excel 会计算该值并将其与该值一起存储在文件中.
例如，您有一张像:
100200=总和(A1:A2)Excel 存储这个(我删除了不相关的 XML 属性):
<第r行＝1"><c r ＝A1"><v>100</v></c></row><第r行＝2"><c r ＝A2"><v>200</v></c></row><行r=3"><c r =A3"><f>SUM(A1:A2)</f><v>300</v></c></row></sheetData> 当您在没有值的情况下将数据从外部插入到 XLSX 文件时，Excel 尚未存储缓存值并显示 0.您的 XLSX 文件(您上传的文件)包含在 sheet1.xml 中:
<c r=B8"><f>SUM(B3:B7)</f><v>0</v></c></row>
在类似的Java库中有一些解释Apache 兴趣点:
<块引用>有时 Excel 会注意到自己，并在加载时触发重新计算，但除非您知道自己使用的是易失性函数，否则通常最好触发重新计算.
还有一个类似的问题从 Java 的角度来看，Excel 单元格显示零而不是计算/引用的值.
See this .xlsx file. No macros, of course!
When you open the file, you will see the following cells:
[Series ]
[10 ]
[20 ]
[30 ]
[ ]
[ ]
[=SUM(B3:B7)]
The sum does not calculate unless you double click, and then press enter, in one of the bottom three cells (eg two blank, =SUM(B3:B7))
Weirder still, once you 'kicked the formula into life' if you save the workbook and reopen it, it behaves fine.
I've tried changing the extension to .zip, and playing around with Xml to see if I could identify the changes made by saving it after it's started working, but I could only see that a version number had changed.
We. Ird.
Also, I tried saving this in an earlier Excel format, and the problem remained.
I just want to reiterate - the attached spreadsheet is a .xlsx file, and therefore does not have any macros! There really is no other way of explaining this problem without the attached spreadsheet. Hopefully, by putting 50 points of reputation on the line, someone will trust that I'm not a script kiddie and have a look!
I created the file using EPPlus, and the following C# code:
public void Generate(string outputPath)
{
using (FileStream fsTemplate = new FileStream(TemplatePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (FileStream fsReport = new FileStream(outputPath, FileMode.Create, FileAccess.ReadWrite, FileShare.None))
{
using (ExcelPackage ep = new ExcelPackage(fsReport, fsTemplate))
{
using (ExcelWorksheet ws = ep.Workbook.Worksheets[TemplateSheetName])
{
//
int iHeadingsRow = 2;
int dataRowIndex = iHeadingsRow + 1;
//
List<decimal> list = new List<decimal>() { 10, 20, 30 };
//
foreach (var number in list)
{
ws.Cells[dataRowIndex, 2].Value = list[dataRowIndex - iHeadingsRow - 1];
//
ws.InsertRow(dataRowIndex + 1, 1, iHeadingsRow + 1);
//
dataRowIndex++;
}
//
ep.Save();
}
}
}
}
}
I would like to emphasise that this is not an EPPlus issue! This is a common problem with Excel opening a valid workbook, and refusing to calculate a formula. Formatting the cells as numeric makes no difference. I have been using EPPlus to create working Excel files for months, and I have not installed any updates to EPPlus, so it must be problem with Excel, right?!
The solution must lie in the difference between the original file, and the version that has been kicked into life and then saved. I just can't seem to pinpoint exactly what that difference is...
The =SUM(B3:B7) is already present in the template file, along with the text 'Series'. The code only adds the {10,20,30} items into the spreadsheet.
You are adding data to an already existing Excel sheet which already contains the =SUM(B3:B7) formula.
I guess that is an EPPlus issue. Namely, the issue is that you are not calling the Calculate() method after entering the data:
... you can let EPPlus calculate the results of the formulas in a workbook.
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.
After you type a formula in Excel, Excel calculates the value and stores it in the file together with the value.
For example you have a sheet like:
100
200
=SUM(A1:A2)
Excel stores this (I removed the non-relevant XML attributes):
<sheetData>
<row r="1">
<c r="A1">
<v>100</v>
</c>
</row>
<row r="2">
<c r="A2">
<v>200</v>
</c>
</row>
<row r="3">
<c r="A3">
<f>SUM(A1:A2)</f>
<v>300</v></c>
</row>
</sheetData>
When you insert the data to the XLSX file externally without the value, Excel does not have the cached value stored yet and displays 0. Your XLSX file (the one which you uploaded) contains in sheet1.xml:
<row r="8">
<c r="B8">
<f>SUM(B3:B7)</f>
<v>0</v>
</c>
</row>
There is some explanation in the similar Java library Apache POI:
Sometimes Excel will notice itself, and trigger a recalculation on load, but unless you know you are using volatile functions it's generally best to trigger a Recalculation.
and there is a similar problem Excel cell displaying zero instead of a calculated / referenced value from the Java perspective.
这篇关于为什么除非我双击一个单元格，否则这个公式不会计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
