Bug 30319 - Euro symbol in usermodel DataFormat (+ FIX)
Summary: Euro symbol in usermodel DataFormat (+ FIX)
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: Other other
: P3 normal with 11 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2004-07-26 09:58 UTC by ejb
Modified: 2006-03-24 05:38 UTC (History)
1 user (show)

Correction to the org.apache.poi.hssf.record.FormatRecord class (4.79 KB, application/octet-stream)
2006-03-24 13:34 UTC, Douglas Atique
Source to the above attachment (7.64 KB, text/plain)
2006-03-24 13:38 UTC, Douglas Atique

Note You need to log in before you can comment on or make changes to this bug.
Description ejb 2004-07-26 09:58:30 UTC
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
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.
Comment 1 Rostro 2005-01-31 20:35:11 UTC
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. 
Comment 2 Avik Sengupta 2005-01-31 20:41:19 UTC
Someone needs to write some tests to ensure that this fix does not break other
functionality. I'm paranoid!
Comment 3 Rostro 2005-01-31 21:14:43 UTC
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.
Comment 4 ejb 2005-02-01 10:43:52 UTC
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.
Comment 5 ejb 2005-02-13 17:37:13 UTC
Renamed bug : 'Euro symbol' instead of 'Unicode' to more properly express the
nature of the issue.
Comment 6 Oliver Siegmar 2005-10-04 22:33:27 UTC
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.
Comment 7 Jason Height 2006-01-03 08:29:56 UTC
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

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);
      wb.write(new FileOutputStream(new File("c:/test1_out.xls")));
      System.out.println("Elapsed :"+(System.currentTimeMillis()-time1));
    } catch (Exception ex) {
Comment 8 Douglas Atique 2006-03-06 16:06:32 UTC
poi-2.5.1-final-20040804 has this bug. Which version should I get to get the
corrections? Are binaries available?
Comment 9 Douglas Atique 2006-03-24 13:34:31 UTC
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
Comment 10 Douglas Atique 2006-03-24 13:38:35 UTC
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