Bug 48344 - setRowStyle() method of HSSFRow is not working
Summary: setRowStyle() method of HSSFRow is not working
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: unspecified
Hardware: PC All
: P1 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-12-07 01:28 UTC by Ayan Dutta
Modified: 2023-08-10 14:36 UTC (History)
4 users (show)



Attachments
setRowStyle bug (1.45 KB, text/plain)
2011-09-28 13:55 UTC, Denis Kurochkin
Details
Screenshot (4.49 KB, image/png)
2011-09-28 14:03 UTC, Denis Kurochkin
Details
log.xml (2.22 KB, application/xml)
2011-09-29 12:53 UTC, Denis Kurochkin
Details
TestRowStyle.java (3.36 KB, text/plain)
2011-09-29 12:54 UTC, Denis Kurochkin
Details
set cell style when cell is being created (3.35 KB, patch)
2023-01-11 15:43 UTC, lsq27
Details | Diff
set cell style when cell is being created (16.25 KB, patch)
2023-01-11 15:53 UTC, lsq27
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Ayan Dutta 2009-12-07 01:28:15 UTC
I have tried using the setRowStyle() method of a HSSFRow  object .
It is not working .
In a similar manner I have tried using setCellStyle() method of HSSFCell object ,that is working fine .
In some of the forums it was suggested that may be a misplaced jar files or conflict within the jars can be a reason . But I used only the 
poi-3.5-FINAL-20090928.jar as well as a combination of jar files such as 

1.poi-3.5-FINAL-20090928.jar
2.poi-contrib-3.5-FINAL-20090928.jar
3.poi-ooxml-3.5-FINAL-20090928.jar
4.poi-scratchpad-3.5-FINAL-20090928.jar

In both cases setRowStyle() is not working .
Comment 1 Nick Burch 2010-08-02 12:01:00 UTC
You're going to need to be more specific than "It is not working" ....

See org.apache.poi.hssf.usermodel.TestRowStyle for a number of unit tests which appear to show POI able to set, read and process row styles just fine

Please upload a failing unit test if you have problems, showing what bit isn't working, so we have something to work against.
Comment 2 Nick Burch 2011-02-25 16:55:22 UTC
Based on the various unit tests that pass, I believe this one is not a bug in the latest POI.

If you still have the problem with a recent nightly / poi 3.8 beta 1, please re-open the bug and provide details for how to reproduce the problem
Comment 3 Denis Kurochkin 2011-09-28 13:55:10 UTC
Created attachment 27627 [details]
setRowStyle bug

Code that reproduces setRowStyle bug
Comment 4 Denis Kurochkin 2011-09-28 13:56:17 UTC
I also encountered this problem.
I confirm that the method setRowStyle() is not working properly.
I try to paint background. But the background is not painted in created cells, but only empty ones.

Here is code which reproduces the bug.
Comment 5 Denis Kurochkin 2011-09-28 14:03:39 UTC
Created attachment 27628 [details]
Screenshot
Comment 6 Denis Kurochkin 2011-09-28 14:47:46 UTC
(In reply to comment #4)
> I also encountered this problem.
> I confirm that the method setRowStyle() is not working properly.
> I try to paint background. But the background is not painted in created cells,
> but only empty ones.
> 
> Here is code which reproduces the bug.

tested with both POI 3.7 and 3.8 beta4
Comment 7 Denis Kurochkin 2011-09-28 15:07:22 UTC
(In reply to comment #6)
> (In reply to comment #4)
> > I also encountered this problem.
> > I confirm that the method setRowStyle() is not working properly.
> > I try to paint background. But the background is not painted in created cells,
> > but only empty ones.
> > 
> > Here is code which reproduces the bug.
> 
> tested with both POI 3.7 and 3.8 beta4

If I replace all HSSF by XSSF in this example it works as expected. (3.8 only).
So HSSF and XSSF have different implementations of setRowStyle(). And old one (from HSSF) is buggy.
Comment 8 Nick Burch 2011-09-28 15:11:19 UTC
Is the problem with how POI sees the styling, or only with Excel?

i.e. if you style a row in POI, save it, re-open it and check the style, will POI see the correct styling even if Excel won't?

Additionally, if you run the Microsoft Binary File Format Validator against the POI styled file, does it pass?
Comment 9 Denis Kurochkin 2011-09-29 12:51:14 UTC
(In reply to comment #8)
> Is the problem with how POI sees the styling, or only with Excel?
> 
> i.e. if you style a row in POI, save it, re-open it and check the style, will
> POI see the correct styling even if Excel won't?

In reopened file:
sheet.getRow(2).getRowStyle(); // returns style which defined previosly by setRowStyle().
style = sheet.getRow(2).getCell(0).getCellStyle(); // returns default workbook style

> Additionally, if you run the Microsoft Binary File Format Validator against the
> POI styled file, does it pass?
log.xml for my example attached
Comment 10 Denis Kurochkin 2011-09-29 12:53:02 UTC
Created attachment 27633 [details]
log.xml

Log of Microsoft Binary File Format Validator against the
POI styled file
Comment 11 Denis Kurochkin 2011-09-29 12:54:47 UTC
Created attachment 27634 [details]
TestRowStyle.java

create and test setRowStyle()
Comment 12 Nick Burch 2011-09-29 13:24:06 UTC
(In reply to comment #9)
> In reopened file:
> sheet.getRow(2).getRowStyle(); // returns style which defined previosly by
> setRowStyle().
> style = sheet.getRow(2).getCell(0).getCellStyle(); // returns default workbook
> style

If you set a style on a row in Excel, what gets written for the cell styles for cells with the default style? Does excel go through and change all the cells in the row, or does it just inherit?
Comment 13 Nick Burch 2011-09-29 13:33:47 UTC
(In reply to comment #10)
> Created attachment 27633 [details]
> log.xml
> 
> Log of Microsoft Binary File Format Validator against the
> POI styled file

Looks like it's complaining about the IndexRecord not correctly holding the offset of the DefaultColWidthRecord in field 4. Any chance you could open a new bug for this?
Comment 14 Denis Kurochkin 2011-10-10 20:48:18 UTC
(In reply to comment #12)
> (In reply to comment #9)
> > In reopened file:
> > sheet.getRow(2).getRowStyle(); // returns style which defined previosly by
> > setRowStyle().
> > style = sheet.getRow(2).getCell(0).getCellStyle(); // returns default workbook
> > style
> 
> If you set a style on a row in Excel, what gets written for the cell styles for
> cells with the default style? Does excel go through and change all the cells in
> the row, or does it just inherit?

If I set a style on a row Excel sets same style for each cell in this row.
Iterate over all cells in a row shows that cells with default style have style equivalent to style of row:
rowstyle.getIndex()==stylecell.getIndex() -> true
rowstyle.equals(cellstyle) -> true
rowstyle.hashCode()==cellstyle.hashCode -> true
Comment 15 Bruno Negrão 2012-10-17 21:55:27 UTC
Hello,

I'm changing the status of this bug back to NEW because I'm also having this problem with poi 3.8.

I also ran the test file attached to this bug "TestRowStyle.java (3.36 KB, text/plain) 2011-09-29 12:54 UTC, Denis Kurochkin" and it successfully reproduces the bug in my environment. 

Could you please run "TestRowStyle.java" and see what is the result in your environment?

Thank you,
Bruno.
Comment 16 Jeff Tilby 2014-09-24 13:50:12 UTC
I have also had a problem with setRowStyle. It sets the style on the row OK but all the cells on the row have their style set to "Default", overriding the row style.

e.g. If save as XML:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Fred Bloggs</Author>
  <LastAuthor>tecdvp3</LastAuthor>
  <Created>2014-09-24T12:58:38Z</Created>
  <LastSaved>2014-09-24T12:58:38Z</LastSaved>
  <Version>14.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>9150</WindowHeight>
  <WindowWidth>14940</WindowWidth>
  <WindowTopX>360</WindowTopX>
  <WindowTopY>270</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Arial"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <Font ss:FontName="Eurostyle" ss:Bold="1" ss:Underline="Single"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Hello World">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:AutoFitWidth="0" ss:Width="102.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="204.75"/>
   <Row ss:StyleID="s21">
    <Cell ss:StyleID="Default"><Data ss:Type="String">Col 1</Data></Cell>
    <Cell ss:StyleID="Default"><Data ss:Type="String">Col 2</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Hello</Data></Cell>
    <Cell><Data ss:Type="String">World</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">&#45;&#45;&#45;&#45;-</Data></Cell>
    <Cell><Data ss:Type="String">&#45;&#45;&#45;&#45;-</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>300</HorizontalResolution>
    <VerticalResolution>300</VerticalResolution>
   </Print>
   <Selected/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
Comment 17 lsq27 2023-01-11 15:43:33 UTC
Created attachment 38460 [details]
set cell style when cell is being created

I meet this problem too.
Only column style is taken account of when cell is being created.

I make some changes as follows:
* In the constructor of HSSFCell and SXSSFCell, row style and column style are checked in order. If no style exists, fallback to the default style.
* In the constructor of XSSFCell, cell style, row style and column style are checked in order. If no style exists, fallback to the default style.
* When HSSFRow.setRowStyle(null) is invoked, row is unformatted and row style is set to default.
* When wrting SXSSFCell to file, cell style is always set.(When a cell has row style but not cell style, ms office render it default style but openoffice not, so explicitly setting cell style is necessary.)
Comment 18 lsq27 2023-01-11 15:53:37 UTC
Created attachment 38461 [details]
set cell style when cell is being created
Comment 19 PJ Fanning 2023-01-24 14:26:05 UTC
this patch causes a NPE in XSSFSheet and this breaks a lot of tests

the SheetDataWriter change needs to be done in a separate patch - the change is big enough and that SheetDataWriter change is unrelated
Comment 20 PJ Fanning 2023-01-24 15:03:44 UTC
I did a small code reorder that fixes the npe but still some xssf testing fails - r1906981