Bug 11132 - Displays Trademark as "small box" in Excel (HSSF)
Summary: Displays Trademark as "small box" in Excel (HSSF)
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 1.5.1
Hardware: Sun Solaris
: P3 major with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2002-07-24 15:27 UTC by Thomas Hessellund Nielsen
Modified: 2004-11-16 19:05 UTC (History)
0 users

Contains the code to read from an excel containing the map (1.10 KB, text/plain)
2003-09-24 14:06 UTC, Amu

Note You need to log in before you can comment on or make changes to this bug.
Description Thomas Hessellund Nielsen 2002-07-24 15:27:29 UTC
When you generate a document with HSSF, and put in a trademark(ascii 153), 
Microsoft Excel 2000 shows it as a tiny box(much like internet explorer).

But when you copy/paste it into ultraedit and copy/paste it back into excel, or 
simply enter a trademark normally, it shows the Trademark just fine.

So I'm thinking this is a bug at file creation time.
Comment 1 Thomas Hessellund Nielsen 2002-07-29 06:52:01 UTC
™ or ascii 0153 is not displayed properly in the Excel sheets I am creating 
with POI. All that is displayed is the well known box.

So I tried the newest available version, but there were other bugs that 
hampered my work.

Comment 2 Thomas Hessellund Nielsen 2002-07-29 06:55:40 UTC
There seems to be a problem when trying to read or insert Trademarks, ™, ascii 
0153. They only get shown as a square box, and this seems to be a bug. I tried 
a couple of manual fixes, but nothing really seemed to do the trick.

When you write or copy paste the Trademark into Excel, there are no problems, 
it is only when creating or reading with HSSF that this occurs.
Comment 3 Thomas Hessellund Nielsen 2002-07-29 07:07:08 UTC
Ok, so the Comments are shown in the bottom of this page, so I didn't notice 
until I finished the third one. That makes me excentric, not stupid, right?
Comment 4 Andy Oliver 2002-07-29 13:41:58 UTC
Can you do the equivilent (on your OS) of
"java org.apache.poi.hssf.dev.BiffViewer wrong.xls > output.foo"
"java org.apache.poi.hssf.dev.BiffViewer right.xls > output2.foo"
"diff output.foo output2.foo"

where wrong.xls = the HSSF generated file with the incorrect output
and right.xls = the Excel generated file with the correct output.

You should do this on an otherwise blank sheet in the same cell to make the
output as close as possible.

I suspect the characterset is different or something.

Comment 5 Thomas Hessellund Nielsen 2002-07-29 15:06:52 UTC
Yeah I've done the equivalent of it with UltraEdit, and they ARE different. 
So I tried to make a manual byte filter, where I simply write the "correct" 
bytes out(ServletOutputStream), but nothing helped, and I've tried alot of 

So I'm thinking something might be wrong with the cell-formatting.
Comment 6 Thomas Hessellund Nielsen 2002-07-30 09:23:58 UTC
I've done a nasty hack now for writing the trademark, but it works.

I use the following code:

response.addHeader("Content-Disposition", "filename=\"wank.xls\"");
DataOutput output = new DataOutputStream( response.getOutputStream() );
byte[] bytes = buffer.toByteArray();

where the buffer is a ByteArrayOutputStream, and I write bytes[] out in a for-
loop. After looking, comparing and so on I found out that the tm is represented 
as the byte value -103.
Normally the Hex representation(as seen in ultraedit) of a trademark is a " 
followed by a !.
A tm written with hssf is represented by a tm and a ".".
Here's an example from ultraedit:

HSSF written:   ...99 00...

EXCEL normally: ...22 21...

what caused a great deal of confusion with me was the fact that the trademark 
is here written as hex 99(deci 153 which is the ascii value), and that 153 
doesn't occur in the outputstream. 

Maybe I'm stupid, but this is probably because I've forgotten all of my 
schooling through massive consumption of alcohol. I suppose it's because of 
binary conversion:

-103 = 01100111 (conversion)=> 10011000+1 = 10011001 = 153

But anyway, if I look at every single byte, and replace the value pattern 99 0 
by 22 21, the tm's in EXCEL are displayed allright. I suppose all ascii 
characters between 129 and 159 will have this problem.

If there are questions as to what the hell I'm talking about, please don't 
hesitate to ask here, I'll get an e-mail about it... 
Comment 7 Thomas Hessellund Nielsen 2002-08-08 06:55:30 UTC
I've tested it and I can say for a fact that HSSF doesn't write ascii 
characters correctly between 129 and 159. Excel displays them in a false way.

Instead of HSSF writing the hexidecimal value for e.g. 153, the value should be 
the unicode value, e.g. not 99 00 but 22 21 for trademark.
Comment 8 Thomas Hessellund Nielsen 2002-08-08 10:11:47 UTC
Now when I substitute e.g. trademark with this value ™ it works, writing 
\u2122, which it states that you should on the POI website, does not work.

Comment 9 Amu 2003-09-24 14:06:41 UTC
Created attachment 8328 [details]
Contains the code to read from an excel containing the map
Comment 10 vikas 2003-10-28 09:46:01 UTC
the bug is resolved after 2.0-pre3. the code is working fine now.