StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I had the very same problem. After a few days googling and research, I came up with a solution. Unfortunately, it isn't nice, but it works:</p> <ol> <li>Make a copy of <code>org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler</code> class in your project.</li> <li>Find the interface <code>SheetContentsHandler</code> in the class.</li> <li>Add a new method definition: <code>String overriddenFormat(String cellRef, int formatIndex, String formatString);</code></li> <li>Find this method in the class: <code>public void endElement(String uri, String localName, String name) throws SAXException</code>.</li> <li>It has a long switch over the cell types.</li> <li>In the case <code>NUMBER</code> there is an if statement like this: <code>if (this.formatString != null) {...</code></li> <li><p>Before that, paste this code:</p> <pre><code>String overriddenFormat = output.overriddenFormat(cellRef, formatIndex, formatString); if (overriddenFormat != null) { this.formatIndex = -1; this.formatString = overriddenFormat; } </code></pre></li> <li><p>Follow this article/answer: <a href="https://stackoverflow.com/a/11345859">https://stackoverflow.com/a/11345859</a> but use your new class and interface.</p></li> <li>Now you can use unique date formats if it is needed.</li> </ol> <p>My use case was: In a given sheet I have date values in G, H, and I columns, so my implementation of <code>SheetContentsHandler.overriddenFormat</code> is:</p> <pre><code>@Override public String overriddenFormat(String cellRef, int formatIndex, String formatString) { if (cellRef.matches("(G|H|I)\\d+")) { //matches all cells in G, H, and I columns return "yyyy-mm-dd;@"; //this is the hungarian date format in excel } return null; } </code></pre> <p>As you can see, in the <code>endElement</code> method I have overridden the formatIndex and formatString. The possible values of the formatIndex are described in <code>org.apache.poi.ss.usermodel.DateUtil.isInternalDateFormat(int format)</code>. If the given value doesn't fit on these (and -1 does not fit), the formatString will be used through formatting the timestamp values. (The timestamp values are counted from about 1900.01.01 and have day-resolution.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload