CODEWITHSUNDEEP
javaexcelapache-poi
ptstone
ptstone

Reputation: 558

Apache POI: Force celldata type to be NO formula?

Adding pure text from a different source, i noticed Apache POI (3.17) automatically assumes a String starting with = is interpreted as a formula. This is usually fine, but how can i make sure that in this special case, the cell is NOT a formula?

I assumed after adding the String starting with =, i can just tell Apache POI the type is CellType.STRING: 

cell.setCellType(CellType.STRING);

But this does not yield the expected result, the String is still read as a formula in the output .xlsx file (using LibreOffice). Currently, i'm checking if each added String starts with a = and replace it with '= but i would much prefer to avoid all those checks. Example how i currently handle it:

strOut = in_CSV(i,j);
strOut = strOut.startsWith("=") ? "'"+strOut : strOut;

So is there a better way to make sure my output is NOT a forumula in Apache POI?

Upvotes: 2

Views: 1250

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

The interpreting a String starting with = as a formula is done by Excel or Calc after opening the file. So apache poi can only prevent this if it uses the same markers in the file which Excel would use to mark strings starting with = as not a formula. This mark is called "quote prefix". It looks like an apostrophe in editor bar of Excel but is not really part of the cell content.

So simply putting an apostrophe in front of the cell content will be the wrong approach. This works while importing text (CSV) in Excel because there the leading apostrophe will automatically interpreted as quote prefix. But in a *.xls or *.xlsx file this automatic will not work properly in all cases. So there we need really setting CellFormat.QuotePrefix Property as Excel would do after typing a leading apostrophe to cell content in editor bar.

This can be done using apache poi by creating a CellStyle which has set setQuotePrefixed(true).

Upvotes: 5

Related Questions