Excel / Apache POI: How to escape strings for Excel cells?

Question

I'm trying to generate XLSX files as reports for customers, with the raw data coming from our system.

I'm facing the issue that I cannnot simply call setCellValue(myText) because the variable myText may contain characters which need to be escaped for excel. For example:

• strings which start with = are interpreted as formulas
• strings containing ' somewhere in the text are interpreted as validation constraints (which is weird)
• ...

In summary, I need a way to write the content of myText into a cell, regardless of any special Excel "magic". I always need the content of the resulting cell to be a text, it should never end up as a formula (also, I need to prevent "formula injection"). So I need to escape the string content somehow. The important thing is: I do not control the content of the input string - it can be literally anything.

Is there some standard escaping method for Excel? I'm working with the Apache POI library.

Answer 1

It turns out, with the help of this answer, that no "escaping" in the traditional sense is necessary.

Using cell.getCellStyle().setQuotePrefixed(true) solves the issue.