• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Set character formatting in Excel using POI

 
Thomas Greene
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to set some values in the excel sheet using POI. These values must have the required character formatting like bold, italics and underline.

Say, the value that I need to set in some cell in the excel could be something like

<i>italics </i>plain<b><i> boldItalics</i></b><b> allThree</b><b><i><u> plain</u></i></b>


Whatever is there inside
<i>..</i> should be set as italics
<b>..</b> should be set as bold
<u>..</u> should be set as underline

I was trying to set this formatting by harcoding the indexes (for the time being) using the following code


But it seems it cannot be done, once the font is set it is set for everything.
Can someone please tell me how to resolve this.
Thank You
 
Paul Clapham
Sheriff
Posts: 21322
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would suggest creating one HSSFFont object for each cell you want to apply a font to.
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The following code shows how it is done. As in reading the styles, the first run of text is the cell style, and the ones after that are set especially by rts.applyFont.

It's important to create new fonts, though, and not re-use the same one for all runs. As you've discovered, not the font attributes are stored, but the font object itself. So if you use a single font object and change it later, then all styles get changed.
 
Thomas Greene
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot. It works great
 
Thomas Greene
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you please help me develop the logic to apply it.

The String that needs to be set into excel would be something like

<i>italics </i>plain<b><i> boldItalics</i></b><b> allThree</b><b><i><u> plain</u></i></b>


Whatever is there inside
<i>..</i> should be set as italics
<b>..</b> should be set as bold
<u>..</u> should be set as underline

How can I traverse through this and set the appropriate style and also remove these HTML tags from the final output.
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The logic seems straightforward. You'd go through the string left to right, one character at a time. You'd need to keep track of which styles are currently active (e.g. in 3 booleans), and where the current run of text starts. Whenever you encounter a closing tag, you'd append the current run of text to the rich text string, and apply a font based on the booleans to it.

A minor complication would be that you also need to keep track of whether the current run is the first run, in which case you'd change the cell style font instead of applying a font to the rich text string.
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Little-known fun fact: the numbers of 400 for non-bold text and 700 for bold text are the same ones used in CSS style sheets. Go figure.
 
Thomas Greene
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is what I have done but it is pretty crude. Is there way to improve it

 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Didn't you like the approach I outlined before? It seems that it would need a lot fewer lines of code, and also be more stable (e.g. if there was whitespace between the HTML tags).
 
Thomas Greene
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ulf Dittmer:
Didn't you like the approach I outlined before? It seems that it would need a lot fewer lines of code, and also be more stable (e.g. if there was whitespace between the HTML tags).


I liked it but couldn't implement it.
 
Thomas Greene
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Thomas Greene:


I liked it but couldn't implement it.


My code will not work for something like



So its actually useless.
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My approach would be usable in that case too, and even in situations of a malformed tag structure like:

What specific difficulty did you encounter implementing it?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic