Apache POI 3.x-逻辑公式产生#VALUE！ (错误)仅在MS Excel 2010中 [英] Apache POI 3.x - Logical formula resulting in #VALUE! (error) only in MS Excel 2010

问题描述

我已经开发了使用Apache POI 3.9创建XLS文件的代码.
该工作表有两列，仅包含布尔值，如下所示:

I've developed a code that creates a XLS file using Apache POI 3.9.
The sheet has two columns that contains only boolean values, as shown below:

生成的XLS文件是使用MS Excel 2010葡萄牙语(pt-BR)打开的，因此将VERDADEIRO设置为TRUE，将FALSO设置为FALSE.第三列包含公式INT(AND(L2,NOT(M2))) 每行更改参考L2和M2.此公式是一个逻辑表达式，其结果转换为整数.但是，使用MS Excel 2010打开文件时，该公式将导致错误(由#VALUE！表示).我已经使用Mac的OpenOffice Calc，LibreOffice Calc和MS Excel 2011测试了相同的文件，并且它们对于两个文件都工作正常.
当我按F2然后为每个单元格按Enter时，错误消失并且显示了正确的值.

The generated XLS file was opened using a MS Excel 2010 in Portuguese (pt-BR), thus consider VERDADEIRO as TRUE and FALSO as FALSE. The 3rd column contains the formula INT(AND(L2,NOT(M2))) varying references L2 and M2 for each row. This formula is a logical expression that has its result converted to integer. However, when the file is opened using MS Excel 2010 the formula results in error (represented by #VALUE!). I've tested the same file using OpenOffice Calc, LibreOffice Calc and MS Excel 2011 for Mac and it worked fine for both of them.
The error disappeared and the correct value was displayed when I pressed F2 and then hit Enter for each cell.

推荐答案

我更改了在函数调用NOT(M2)周围添加括号的公式
INT(AND(L2,NOT(M2))) ====> INT(AND(L2,(NOT(M2))))，问题已解决.

I changed the formula adding parenthesis around the function call NOT(M2)
INT(AND(L2,NOT(M2))) ====> INT(AND(L2,(NOT(M2)))) and the problem was solved.

这篇关于Apache POI 3.x-逻辑公式产生#VALUE！ (错误)仅在MS Excel 2010中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！