I couldn't get the euro symbol to work in a DataFormat. The formatting String that Excel uses for the "Financial, EU 123" format is: "_([$\u20ac-2]\\ * #,##0.00_);_([$\u20ac-2]\\ * \\(#,##0.00\\);_([$\u20ac-2]\\ * \"-\"??_);_(@_)" I could read this with the HSSF usermodel classes from a workbook created by Excel, but when trying to create a workbook the character would change into a logical-not symbol. After some searching in the hssf.record package I found the class FormatRecord which has a method setUnicodeFlag, but this method isn't called when the usermodel creates a DataFormat. I fixed it by changing the class org.apache.poi.hssf.model.Workbook (not HSSFWorkbook) In its method "public short createFormat( String format )" I added the line: rec.setUnicodeFlag( true ); After this change, euro symbols work fine in a DataFormat.
Your solution works fine, I've tested the fix in my XLS reports with IBM JRE 1.3.1. and Excel 2000. Euro char in format doesn't make problems anymore. Nice. Thanks.
Someone needs to write some tests to ensure that this fix does not break other functionality. I'm paranoid!
I would say, it might be good to have a more general look to string conversions Unicode -> Excel and think about how to find a solution to generally avoid such problems - there are some more bugs related to Unicode and/or special characters: 22957, 27921, 30810, 22873 I aggree with 22957, that Unicode shoud be always default option if calling anything from Java.
There is a difference between ordinary texts in a cell and the text in a format record, and they are not handled identically. I like POI. I've used it in several projects, but I've always had to patch it. I live in the Netherlands, and customers need the euro sign in generated sheets; they always need to include financial information. Europe has almost 700 million inhabitants, and a lot of software developers, so allowing POI to be used in Europe instantly gives it a lot of mindshare that it doesn't have now. May I - respectfully - suggest that the Euro character deserves specific attention. More attention than support for esoteric character sets in general, in my opinion. I hope you agree.
Renamed bug : 'Euro symbol' instead of 'Unicode' to more properly express the nature of the issue.
This bug is really annoying. Could someone apply the proposed workaround/fix? This bug is open for more than a year and the fix looks trivial.
I have implemnted a fix in SVN, however it is not as suggested in the Workbook, but rather in the low level FormatRecord. Confirmed that it works with the following code: Please check on a euro excel version, but ill close since i think that it is now fixed. import org.apache.poi.hssf.usermodel.*; import java.io.*; public class Test { public static void main(String[] args) { try { long time1 = System.currentTimeMillis(); // HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(new File( // "c:/workbook-orig.xls"))); // wb.write(new FileOutputStream(new File("c:/workbook-out.xls"))); // HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat df = wb.createDataFormat(); short fmt = df.getFormat("_([$\u20ac-2]\\\\\\ * #,##0.00_);_([$\u20ac-2]\\\\\\ * \\\\\\(#,##0.00\\\\\\);_([$\u20ac-2]\\\\\\ *\\\"\\-\\\\\"??_);_(@_)"); HSSFSheet s = wb.createSheet("TestSheet"); HSSFRow r = s.createRow(0); HSSFCell c = r.createCell((short)1); c.setCellValue(12.34); c.getCellStyle().setDataFormat(fmt); wb.write(new FileOutputStream(new File("c:/test1_out.xls"))); System.out.println("Elapsed :"+(System.currentTimeMillis()-time1)); } catch (Exception ex) { ex.printStackTrace(); } } }
poi-2.5.1-final-20040804 has this bug. Which version should I get to get the corrections? Are binaries available? Thx
Created attachment 17967 [details] Correction to the org.apache.poi.hssf.record.FormatRecord class I have seen the corrections made to FormatRecord class and this really solved the problem. However, I am not confident to get the whole source tree from CVS/SVN and build it myself. As all I wanted was to have the Euro symbol in my sheets created with release 2.5.1, this seemed to be an easy workaround. By placing the attached jar in the CLASSPATH *BEFORE* poi-2.5.1-20040804.jar Euro symbols will be stored correctly in Excel cells (and maybe other Unicode problems).
Created attachment 17968 [details] Source to the above attachment This is the source for the jar above. It was created by checking out the source for RELEASE_2_5_1 and adding the corrections to setFormatString. As in release 2.5.1 there was no StringUtil.hasMultibyte() method, that method was added to FormatRecord.