|Summary:||Displays Trademark as "small box" in Excel (HSSF)|
|Product:||POI||Reporter:||Thomas Hessellund Nielsen <jorddims>|
|Component:||HSSF||Assignee:||POI Developers List <dev>|
|Attachments:||Contains the code to read from an excel containing the map|
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. /Thomas
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. -Andy
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 things. 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.setContentType("application/vnd.ms-excel"); response.addHeader("Content-Disposition", "filename=\"wank.xls\""); DataOutput output = new DataOutputStream( response.getOutputStream() ); byte bytes = buffer.toByteArray(); response.setContentLength(bytes.length); 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. /Tom
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.