1

I have a problem with HSSFWorkbook object. I have a XLS template, where first row is dark gray, and next rows are light gray. During generation of the report, I want to change the background color of few cells (works perfectly) for red/blue. But after calling:

HSSFCellStyle style = workbook.createCellStyle();
style.setFillBackgroundColor(...)

or:

HSSFCellStyle style = workbook.getCellStyleAt(0) [default style of the workbook]
style.setFillBackgroundColor(...)

the styles of whole sheet is lost. The header becomes violet, and the rest cells becomes dark-dark-gray. I love violets, but I need my colours from the XLS not being changed.

Colours problem

In short - styles from my XLS template are not being preserved after I change style of one cell in the way described above.

I'm out of ideas. Please help.

Mateusz

Mateusz Moroz
  • 312
  • 2
  • 6
  • 19
  • Without seeing and analyzing some more of your code it's hard to say but I suspect that 1) you end up trashing the style(s) that already exist in the workbook - just create new separate styles for the red/blue cells and apply these just to the cells that need to be colored and 2) that you are not using the appropriate color indexes as defined in [HSSFColor](https://poi.apache.org/apidocs/org/apache/poi/hssf/util/HSSFColor.html) and last, try `setFillForegroundColor` - [here](http://stackoverflow.com/questions/2803841/setting-foreground-color-for-hssfcellstyle-is-always-coming-out-black) – fvu Apr 29 '14 at 17:21
  • Also, instead of working directly with POI, have a look at [jXLS](http://jxls.sourceforge.net) or the very comparable [JETT](http://jett.sourceforge.net). Both sit on top of POI and greatly simplify the task of creating reports for Excel in Java. Be sure to read the [section on styles](http://jett.sourceforge.net/tags/style.html) in the JETT doc, lots of useful information even if you choose to continue with straight POI. – fvu Apr 29 '14 at 17:26
  • Hi fvu. Thanks for answer. Honestly I don't understand, what you mean by "end up trashing the styles, that already exist in the workbook". I tried to create the style two ways (by `workbook.createCellStyle()` and `workbook.getCellStyleAt(0)`) and both methods fail. My code is very simple: create Workbook from file (XLS), then for the first sheet I iterate through rows and set values for cells with `HSSFRichTextString`. When I don't set any style to a cell, the output file is OK. After setting style to a single cell, the style for whole template changes. – Mateusz Moroz Apr 30 '14 at 09:34
  • I would willingly use the JETT, as it looks very good like. But poi is used in many places in project, and applying new library would need the acceptance of the customer. So I would first try my way with poi. – Mateusz Moroz Apr 30 '14 at 09:39
  • Styles are stored in a list, after reading the template you'll already have at least 2 entries in that list - verify with `getNumCellStyles()`. The red and blue styles should be *added* to that list, not replace existing entries. The fact that colors of existing elements (that referred the preexisting styles) changes makes me think you somehow did modify the existing styles... – fvu Apr 30 '14 at 09:42
  • JETT complements POI, it does not replace it. Think of it as a much simpler, higher level way of working with POI, not as a replacement. – fvu Apr 30 '14 at 09:43
  • Hi, fvu. After initiating the report, there is 67 styles in the list. The instruction `workbook.createCellStyle()` adds the style, not modifies (after creating 4 styles, the number increases to 71). Unfortunately the same instruction damages the style of the document. – Mateusz Moroz Apr 30 '14 at 12:06

1 Answers1

3

I think the problem will be in your .xls spreadsheet itself. Styles that you chose on your template are incompatible with the current file format (and spreadsheet could be converted to .xlsx, but then you'd probably have to change your implementation to XSSFWorkbook). You can check if styles are compatible by opening your template, making a minor change and save it - in your case warning should pop up that styles will be converted to the closest equivalents.

Solution: Edit your spreadsheet and pick compatible styles with your .xls format or upgrade to .xlsx format.

Dedemonn
  • 128
  • 10
  • Wow, that was it! The document was created in Excel 2007 and then saved in Excel 97 format. So the styles was incompatible with Excel 97 and after simple change of one cell, all colours have been adjusted by the Workbook. – Mateusz Moroz Apr 30 '14 at 13:15