SHow HTML text in one of the excel cell

Discussion:

Me Simple

2010-07-12 21:37:05 UTC

Hi,

I am using Apachi POI to create a excel file on runtime. The excel file may
have variable number columns (number of columns depends on run time). The
value for One of the column has HTML parsed html string as shown below:

Listing 1:

<html>My String</html>

However, when I open the excel file, the HTML column appears as is and
doesn't appear parsed like this one.

Listing 2:

My String

Is there a way to display the html as shown in listing 2 in run time.

thanks.

The code used for creating a the cell is shown below:

HSSFCellStyle headerCellStyle;
HSSFWorkbook workBook = null;

workBook = new HSSFWorkbook();

HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString textString = new HSSFRichTextString(value);
headerCellStyle = workBook.createCellStyle();
headerCellStyle.setFont(getHeaderFont());
headerCellStyle.setWrapText(false);

cell.setCellStyle(headerCellStyle);
cell.setCellValue("<html>My String</html>");

--
View this message in context: http://old.nabble.com/SHow-HTML-text-in-one-of-the-excel-cell-tp29144294p29144294.html
Sent from the POI - User mailing list archive at Nabble.com.

MSB

2010-07-13 06:56:20 UTC

Permalink

Can I just check to make sure that I am clear about what you want to do, you
wish to enter some HTML and have Excel parse that within a cell to present,
in this case, the phrase 'My String' rendered in bold text? Well, I know
that Excel does support this - if I cut you example test out of the message
and then paste it into an Excel cell, Excel will embolden the String - but I
am not sure how to accomplish this using POI and need to play with some code
today.

One thing I did note - and this is by no way a criticism - you create the
HSSFRichTextString and then never use it. It may be worthwhile - as a test -
refactoring your code to look like this;

HSSFCellStyle headerCellStyle;
HSSFWorkbook workBook = null;
String value = "<html>My String</html>";

workBook = new HSSFWorkbook();

HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString textString = new HSSFRichTextString(value);
headerCellStyle = workBook.createCellStyle();
headerCellStyle.setFont(getHeaderFont());
headerCellStyle.setWrapText(false);

cell.setCellStyle(headerCellStyle);
cell.setCellValue(value);

to see if that has any effect - and I do not know that it will.

Secondly, see what happens when you do not explcitly apply a cell style to
the cell, so;

HSSFWorkbook workBook = null;
String value = "<html>My String</html>";

workBook = new HSSFWorkbook();

HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString textString = new HSSFRichTextString(value);

cell.setCellValue(value);

as I am wondering if this has any impact on the way Excel parses the file.
If this turns out to be one of the problems, then try applying the style
'treatments' to the cell incrementally to discover which affects the cells
contants detrimentally.

Will post again if I make any progress.

Yours

Mark B

Post by Me Simple
Hi,
I am using Apachi POI to create a excel file on runtime. The excel file
may have variable number columns (number of columns depends on run time).
The value for One of the column has HTML parsed html string as shown
<html>My String</html>
However, when I open the excel file, the HTML column appears as is and
doesn't appear parsed like this one.
My String
Is there a way to display the html as shown in listing 2 in run time.
thanks.
HSSFCellStyle headerCellStyle;
HSSFWorkbook workBook = null;
workBook = new HSSFWorkbook();
HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString textString = new HSSFRichTextString(value);
headerCellStyle = workBook.createCellStyle();
headerCellStyle.setFont(getHeaderFont());
headerCellStyle.setWrapText(false);
cell.setCellStyle(headerCellStyle);
cell.setCellValue("<html>My String</html>");

--
View this message in context: http://old.nabble.com/SHow-HTML-text-in-one-of-the-excel-cell-tp29144294p29147186.html
Sent from the POI - User mailing list archive at Nabble.com.

MSB

2010-07-13 11:19:52 UTC

Permalink

Just a quick update, I have not yet had the opportunity to play with any POI
code and will be unlikely to do so today as I am attending a concert
tonight, straight after work. However, following a bit of experimentation,
thanks to the heavy rain preventing us from digging the pond here, I found
that Excel will only parse the html mark-up for you if it is cut and pasted
from another source. By this, I mean that if I type into a cell the
following;

<html><em>Emphasise</em></html>

and then click on the Enter button (the green tick) in the formula bar or
press the Enter key on my keyboard, all Excel will do is enter the text into
the cell just as I have typed it. The application will not parse the mark-up
and ensure that the cell contains the word 'Emphasise' with the necessary
formatting applied. Further, I found that if I copied the contents of that
cell and tried to paste it into another cell on the same worksheet, all I
got was an exact copy of the first cell's contents. Again, Excel did not
parse the html for me. As I have indicated above, the only way to
'encourage' Excel to parse the mark-up was to copy the html from another
document - I used Notepad and WordPad to test the theory - and paste it into
an Excel cell; suggesting the formatting is a feature of the pasting process
in my opinion.

In light of this, I think it is safe to say that the only way Excel is
likely to parse html mark-up for you is if it is pasted into a cell. Reading
the mark-up from a file - and files are all POI can create/edit/read - is
unlikely to result in the effect you are seeking. To my mind, and in the
absence of further testing, the only way to accomplish what you are after
using the POI library, is to parse the html mark-up yourself, detect what
formatting should be applied to the cells contents and then create a
CellStyle object to accomplish this task.

Parsing the mark-up should be fairly straightforward as there are lots of
tools to assist you in this endeavour - the
javax.swing.text.html.parser.Parser class which is a part of the core Java
API. The one big hurdle to my mind would be the number of CellStyle objects
this might create. Excel places a limit on the number of styles a workbook
can contain and it is common practice when using POI to create a cell style
and use/re-use that object to style all identically formatted cells.
However, there is an easy way to get around this problem to my mind; using a
map object to contain the cell styles you create and generating a key from
the element tags that, together, define the styles attributes. Therefore, to
return to the example above, the parsing method would return a cell style
whose key was 'em'. You could use this key to check the map for a matching
value and use that cell style if a match is found. If no match is found, you
could add the newly created style to the map with it's accompanying key of
course, and then style the cell.

When I have the chance, I will look to see if there is a way around this
using POI but do not hold out any real hope of finding one. If I do manage
to discover anything else, I will post again.

Yours

Mark B

--
View this message in context: http://old.nabble.com/SHow-HTML-text-in-one-of-the-excel-cell-tp29144294p29149229.html
Sent from the POI - User mailing list archive at Nabble.com.

Me Simple

2010-07-13 17:21:18 UTC

Permalink

Firstly, thank you for your reply. Its very informative and helpful.

1) Can I just check to make sure that I am clear about what you want to do,
you wish to enter some HTML and have Excel parse that within a cell to
present, in this case, the phrase 'My String' rendered in bold text?

My Reply: Yes. I want the phrase "My String" to be rendered in bold.

2) MS Excel parses the html and renders the phrase as bold when we paste the
content into a cell.

3) Please see the modified code which i am using for writing to excel.

HSSFCellStyle headerCellStyle;
HSSFWorkbook workBook = null;

HSSFFont headerFont = null;

workBook = new HSSFWorkbook();

headerFont = workBook.createFont();
headerFont.setFontName(headerFont.FONT_ARIAL);
headerFont.setBoldweight(headerFont.BOLDWEIGHT_BOLD);

String value = "<html>< b>My String </html>";

HSSFCell cell = row.createCell(0);
HSSFRichTextString textString = new HSSFRichTextString(value);
headerCellStyle = workBook.createCellStyle();
headerCellStyle.setFont(headerFont);
headerCellStyle.setWrapText(false);

cell.setCellStyle(headerCellStyle);
cell.setCellValue(textString);

4) Your suggestion to parse the html using parser and detect the formatting
and re-create the effect using CellStyle object may work, however, we may
have to handle lots of html tags and map to appropriate CellStyles.

5) Also, I am trying to copy the html string from a excel file and writing
to another column to see if it renders the parsed html. Will post back the
result in the forum once I have the result.

Thanks for your reply.

--
View this message in context: http://old.nabble.com/SHow-HTML-text-in-one-of-the-excel-cell-tp29144294p29152517.html
Sent from the POI - User mailing list archive at Nabble.com.

MSB

2010-07-14 06:34:53 UTC

Permalink

If you already have the html parsed and held in a cell of an Excel workbook
then yes, you should be able to copy it successfully. Remember though that
you will need to copy both the text AND the style.

It is the automatic creation of the style by the paste operation - along
with stripping off the html mark-up of course - that you will have to
emulate. I have not had the time to do anything extensive but my initial
testing with POI indicates that there is no obvious way to achieve what you
are after using the API without modifcation. It seems as though you will
have to create a method to parse the html for you, creating CellStyles and
stripping the text from the mark-up so that you can write that to the cell
and then apply the necessary formatting. If you do decide to follow this
route, and assuming your compaines policies allow this, I am sure there are
others here who would welcome your putting together some example code and
submitting it to the examples section of the project. Remember to clear this
with your employer though as any code you do submit will become 'public
property'.

Yours

Mark B

Post by Me Simple
Firstly, thank you for your reply. Its very informative and helpful.
1) Can I just check to make sure that I am clear about what you want to
do, you wish to enter some HTML and have Excel parse that within a cell to
present, in this case, the phrase 'My String' rendered in bold text?
My Reply: Yes. I want the phrase "My String" to be rendered in bold.
2) MS Excel parses the html and renders the phrase as bold when we paste
the content into a cell.
3) Please see the modified code which i am using for writing to excel.
HSSFCellStyle headerCellStyle;
HSSFWorkbook workBook = null;
HSSFFont headerFont = null;
workBook = new HSSFWorkbook();
headerFont = workBook.createFont();
headerFont.setFontName(headerFont.FONT_ARIAL);
headerFont.setBoldweight(headerFont.BOLDWEIGHT_BOLD);
String value = "<html>< b>My String </html>";
HSSFCell cell = row.createCell(0);
HSSFRichTextString textString = new HSSFRichTextString(value);
headerCellStyle = workBook.createCellStyle();
headerCellStyle.setFont(headerFont);
headerCellStyle.setWrapText(false);
cell.setCellStyle(headerCellStyle);
cell.setCellValue(textString);
4) Your suggestion to parse the html using parser and detect the
formatting and re-create the effect using CellStyle object may work,
however, we may have to handle lots of html tags and map to appropriate
CellStyles.
5) Also, I am trying to copy the html string from a excel file and writing
to another column to see if it renders the parsed html. Will post back the
result in the forum once I have the result.
Thanks for your reply.

--
View this message in context: http://old.nabble.com/SHow-HTML-text-in-one-of-the-excel-cell-tp29144294p29158728.html
Sent from the POI - User mailing list archive at Nabble.com.

MSB

2010-07-15 15:54:29 UTC

Permalink

Sorry to say this, but I am certain now that you will need to write code to
parse the html, strip out the tags and create a cell style to apply to the
cell that contains the mark-up. To convince myself of this, the final test I
unbertook was to manually edit the zipped xml of an OOXML based Excel
workbook file. That way, I was able to ascertain that the mark-up was not
parsed as the file was read and that it is the process of pasting that
allows Excel to render the mark-up. Sorry.

Yours

Mark B

--
View this message in context: http://old.nabble.com/SHow-HTML-text-in-one-of-the-excel-cell-tp29144294p29174657.html
Sent from the POI - User mailing list archive at Nabble.com.