Double quotes in Apache POI setCellFormula

1
votes

Using Apache POI 3.10.1 (Java) I have an issue with POI replacing double quotes in a String with ". Excel cannot read this as it expects real double-quotes.

This:

cell.setCellFormula("IF(A2=\"TEST\";1;2)");

Becomes this in the sheet1.xml (opening the text.xlsx (as a zip) and looking in sheet1.xml):

<f>IF(A2=&quot;TEST&quot;;1;2)</f>

Excel throws an error about errors in the sheet. Writing the formula in Excel, I can see from the XML that it expects this:

<f>IF(A2="TEST";1;2)</f>

I cannot seem to find anybody that has had a similar issue. Does anybody know if this is a bug or me doing something wrong?

2
javaapache-poi
Changing " into &quote; is something normally done by the XML library, at a lower level than Apache POI. Have you tried with other JVMs / other XML libraries / etc? – Gagravarr
No, I am constrained by the JVM on the platform and regarding libs I am just using Apache POI and the libs that it depend on. But I cannot imagine not being able to use double quotes in a formula. Somebody must have encountered that issue before me. – Anders
Apache POI has a large number of unit tests, including of formulas, and given those all pass there's a chance that it's your environment... – Gagravarr
Does anyone know if POI uses disable-output-escaping="yes" when creating the formula in the XML (zipped xlsx)? – Anders

2 Answers

2
votes

Probably this is due to semicolon

cell.setCellFormula("IF(A2=\"TEST\",1,2)");

This worked to me.

See the online help for details of the if-function and how to provide parameters.

0
votes

I found simillar issue in my project some time ago.

In my case I was not able to predict chars in raport that will be added to XML and then to PDF/MSExcel.

I used CDATA (import org.jdom.CDATA) funcionality:

final Element xmlIssueTitle = new Element(xmlMessageIssueTitle);
final CDATA xmlIssueTitleCDATA = new CDATA(issueObj.getTitle());
xmlIssueTitle.addContent(xmlIssueTitleCDATA);