aspose file tools*
The moose likes Other Open Source Projects and the fly likes Set character formatting in Excel using POI Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Set character formatting in Excel using POI" Watch "Set character formatting in Excel using POI" New topic
Author

Set character formatting in Excel using POI

Thomas Greene
Ranch Hand

Joined: Aug 09, 2004
Posts: 125
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

I would suggest creating one HSSFFont object for each cell you want to apply a font to.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41517
    
  53
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.


Ping & DNS - my free Android networking tools app
Thomas Greene
Ranch Hand

Joined: Aug 09, 2004
Posts: 125
Thanks a lot. It works great
Thomas Greene
Ranch Hand

Joined: Aug 09, 2004
Posts: 125
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
Marshal

Joined: Mar 22, 2005
Posts: 41517
    
  53
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
Marshal

Joined: Mar 22, 2005
Posts: 41517
    
  53
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

Joined: Aug 09, 2004
Posts: 125
This is what I have done but it is pretty crude. Is there way to improve it

Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41517
    
  53
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

Joined: Aug 09, 2004
Posts: 125
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

Joined: Aug 09, 2004
Posts: 125
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
Marshal

Joined: Mar 22, 2005
Posts: 41517
    
  53
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?
 
jQuery in Action, 2nd edition
 
subject: Set character formatting in Excel using POI