Hello
Hope somebody can help :)
I am using HSSF to generate an 600kb xls file with 4 columns and 3500
rows. All cells contain short string values. The file usually generates
without problem and can be opened in MS Excel.
Recently, I wanted to add a bit of functionality to the code, where
substrings of the cell values would be colour-coded.
The main processing loop for the file creation iterates over the values
I get from the business layer of my application and at each column for a
given row calls:
cell.setCellValue(buildHssfRichStringValue(data));
Where 'data' is a collection containing the model object beans I'm
working with to generate the file. The method to build the
HSSFRichTextString iterates over the data items and, based on certain
parameters of the data item, decorates substrings with a certain font.
Here's a bit of pseudo-code:
private HSSFRichTextString buildHssfRichStringValue(Collection data) {
StringBuilder cellValue = new StringBuilder();
ArrayList<StringDecorator> decorator = new ArrayList();
if (data != null) {
int startIndex;
int endIndex;
//iterate over each data object {
//get initial position
startIndex = cellValue.length();
//update string builder
cellValue.append(dataItem.value());
//get length of added string
endIndex = cellValue.length();
if (dataItem.isDeleted()) {
decorator.add( new StringDecorator(
startIndex,
endIndex,
DELETED_XREF_FONT));
}
if (dataItem.isLogical()) {
decorator.add( new StringDecorator(
startIndex,
endIndex,
LOGICAL_XREF_FONT));
}
//StringDecorator is a sinple bean that contains the
//start-end coordinates for the substring and a reference
//to a global font object to use.
} //end iteration loop
//create string object
HSSFRichTextString hssfTextString =
new HSSFRichTextString(cellValue.toString());
//use proper text formatting colours
//for given substrings
for (StringDecorator stringDecorator : decorator) {
hssfTextString.applyFont(
stringDecorator.getStartPos(),
stringDecorator.getEndPos(),
stringDecorator.getFont()
);
}
return hssfTextString;
}
So basically, as I'm building the string to put in the cell, I'm also
keeping note of which locations need to be highlighted.
The problem comes with this last bit of code, when I call
hssfTextString.applyFont(). I tried a small test set of data (5 rows,
each cell having two or three data items to be processed). It worked
perfectly. I then tried it with a production test set (3500 rows, each
cell having 4-5 data items). That's when I found that MS Excel could no
longer open the document and gave an error message saying "Excel cannot
complete this task with available resources. Choose less data or close
other applications." If I comment out the applyFont calls, the file is
generated without problem and can be opened without problem.
The ironic thing is that OpenOffice can open the generated file without
problem and will display the contents perfectly.
Does anybody have any suggestions as to what is happening, and why? Am I
using the API in a silly fashion? I've tried using
poi-3.0-rc4-20070503.jar as well as poi-3.0.1-alpha1-20070619.jar,
neither of which worked.
Please advise, I'm completely stumped.
Cheers,
Rc
--
Richard Cote
Software Engineer - PRIDE Project Team (Sequence Database Group)
European Bioinformatics Institute
Wellcome Trust Genome Campus [EMAIL PROTECTED]
Hinxton, Cambridge CB10 1SD Phone: (+44) 1223 492610
United Kingdom Fax : (+44) 1223 494468
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
