XSSF: Problem with CellUtil and custom RGB background color

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

XSSF: Problem with CellUtil and custom RGB background color

Anders Åberg
Hi guys
I am trying to set RGB background colors on cells and in addition set
borders on regions using RegionUtil. When doing this, all the cells with
borders end up completely black.

It seems that using XSSFColor on XSSFCellStyle causes problems with the way
CellUtil.setCellStyleProperties works. CellUtil is used by RegionUtil.

This is a minimal test to reproduce the problem:

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();

    Cell cell = CellUtil.getCell(CellUtil.getRow(0, sheet), 0);

    XSSFColor color = new XSSFColor(Color.RED);
    XSSFCellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(color);
    style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cell.setCellStyle(style);

    // Everything is fine at this point, cell is red

    Map<String, Object> properties = new HashMap<>();
    properties.put(CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN);
    CellUtil.setCellStyleProperties(cell, properties);

    // Now the cell is all black

Does anybody know if this is supposed to work? Any suggestions on
workarounds? It is of course possible to avoid the CellUtil methods, but
they contain some clever logic to avoid creating new styles for every cell
and eventually hit a threshold in Excel.

All help will be very appreciated!

Thanks, Anders
Reply | Threaded
Open this post in threaded view
|

Re: XSSF: Problem with CellUtil and custom RGB background color

Javen O'Neal
What version of POI are you using?
On May 2, 2016 03:25, "Anders Åberg" <[hidden email]> wrote:

> Hi guys
> I am trying to set RGB background colors on cells and in addition set
> borders on regions using RegionUtil. When doing this, all the cells with
> borders end up completely black.
>
> It seems that using XSSFColor on XSSFCellStyle causes problems with the way
> CellUtil.setCellStyleProperties works. CellUtil is used by RegionUtil.
>
> This is a minimal test to reproduce the problem:
>
>     XSSFWorkbook workbook = new XSSFWorkbook();
>     XSSFSheet sheet = workbook.createSheet();
>
>     Cell cell = CellUtil.getCell(CellUtil.getRow(0, sheet), 0);
>
>     XSSFColor color = new XSSFColor(Color.RED);
>     XSSFCellStyle style = workbook.createCellStyle();
>     style.setFillForegroundColor(color);
>     style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
>     cell.setCellStyle(style);
>
>     // Everything is fine at this point, cell is red
>
>     Map<String, Object> properties = new HashMap<>();
>     properties.put(CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN);
>     CellUtil.setCellStyleProperties(cell, properties);
>
>     // Now the cell is all black
>
> Does anybody know if this is supposed to work? Any suggestions on
> workarounds? It is of course possible to avoid the CellUtil methods, but
> they contain some clever logic to avoid creating new styles for every cell
> and eventually hit a threshold in Excel.
>
> All help will be very appreciated!
>
> Thanks, Anders
>
Reply | Threaded
Open this post in threaded view
|

Re: XSSF: Problem with CellUtil and custom RGB background color

Anders Åberg
I'm using 3.14.

-Anders

On 2 May 2016 at 16:35, Javen O'Neal <[hidden email]> wrote:

> What version of POI are you using?
> On May 2, 2016 03:25, "Anders Åberg" <[hidden email]> wrote:
>
> > Hi guys
> > I am trying to set RGB background colors on cells and in addition set
> > borders on regions using RegionUtil. When doing this, all the cells with
> > borders end up completely black.
> >
> > It seems that using XSSFColor on XSSFCellStyle causes problems with the
> way
> > CellUtil.setCellStyleProperties works. CellUtil is used by RegionUtil.
> >
> > This is a minimal test to reproduce the problem:
> >
> >     XSSFWorkbook workbook = new XSSFWorkbook();
> >     XSSFSheet sheet = workbook.createSheet();
> >
> >     Cell cell = CellUtil.getCell(CellUtil.getRow(0, sheet), 0);
> >
> >     XSSFColor color = new XSSFColor(Color.RED);
> >     XSSFCellStyle style = workbook.createCellStyle();
> >     style.setFillForegroundColor(color);
> >     style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
> >     cell.setCellStyle(style);
> >
> >     // Everything is fine at this point, cell is red
> >
> >     Map<String, Object> properties = new HashMap<>();
> >     properties.put(CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN);
> >     CellUtil.setCellStyleProperties(cell, properties);
> >
> >     // Now the cell is all black
> >
> > Does anybody know if this is supposed to work? Any suggestions on
> > workarounds? It is of course possible to avoid the CellUtil methods, but
> > they contain some clever logic to avoid creating new styles for every
> cell
> > and eventually hit a threshold in Excel.
> >
> > All help will be very appreciated!
> >
> > Thanks, Anders
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: XSSF: Problem with CellUtil and custom RGB background color

Javen O'Neal
This looks like a bug in the CellUtil.get/setFormatProperties methods,
which assumes the fill foreground color is an indexed color (short
data type), and falls back to IndexedColors.AUTOMATIC (black) if not
indexed.

Could you open a bug at https://bz.apache.org/bugzilla with your test
case, quote the sentence above, and a link to
http://apache-poi.1045710.n5.nabble.com/XSSF-Problem-with-CellUtil-and-custom-RGB-background-color-td5722925.html?

XSSFCellStyle.java:
    https://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java?revision=1737895&view=markup#l455
    @Override
    public short getFillForegroundColor() {
        XSSFColor clr = getFillForegroundXSSFColor();
        return clr == null ? IndexedColors.AUTOMATIC.getIndex() :
clr.getIndexed();
    }

    @Override
    public XSSFColor getFillForegroundColorColor() {
       return getFillForegroundXSSFColor();
    }

    https://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java?revision=1737895&view=markup#l1027
    @Override
    public void setFillForegroundColor(short fg) {
        XSSFColor clr = new XSSFColor();
        clr.setIndexed(fg);
        setFillForegroundColor(clr);
    }

    public void setFillForegroundColor(XSSFColor color) {
        CTFill ct = getCTFill();

        CTPatternFill ptrn = ct.getPatternFill();
        if(color == null) {
            if(ptrn != null && ptrn.isSetFgColor()) ptrn.unsetFgColor();
        } else {
            if(ptrn == null) ptrn = ct.addNewPatternFill();
            ptrn.setFgColor(color.getCTColor());
        }

        addFill(ct);
    }

On Mon, May 2, 2016 at 8:15 AM, Anders Åberg <[hidden email]> wrote:

> I'm using 3.14.
>
> -Anders
>
> On 2 May 2016 at 16:35, Javen O'Neal <[hidden email]> wrote:
>
>> What version of POI are you using?
>> On May 2, 2016 03:25, "Anders Åberg" <[hidden email]> wrote:
>>
>> > Hi guys
>> > I am trying to set RGB background colors on cells and in addition set
>> > borders on regions using RegionUtil. When doing this, all the cells with
>> > borders end up completely black.
>> >
>> > It seems that using XSSFColor on XSSFCellStyle causes problems with the
>> way
>> > CellUtil.setCellStyleProperties works. CellUtil is used by RegionUtil.
>> >
>> > This is a minimal test to reproduce the problem:
>> >
>> >     XSSFWorkbook workbook = new XSSFWorkbook();
>> >     XSSFSheet sheet = workbook.createSheet();
>> >
>> >     Cell cell = CellUtil.getCell(CellUtil.getRow(0, sheet), 0);
>> >
>> >     XSSFColor color = new XSSFColor(Color.RED);
>> >     XSSFCellStyle style = workbook.createCellStyle();
>> >     style.setFillForegroundColor(color);
>> >     style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
>> >     cell.setCellStyle(style);
>> >
>> >     // Everything is fine at this point, cell is red
>> >
>> >     Map<String, Object> properties = new HashMap<>();
>> >     properties.put(CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN);
>> >     CellUtil.setCellStyleProperties(cell, properties);
>> >
>> >     // Now the cell is all black
>> >
>> > Does anybody know if this is supposed to work? Any suggestions on
>> > workarounds? It is of course possible to avoid the CellUtil methods, but
>> > they contain some clever logic to avoid creating new styles for every
>> cell
>> > and eventually hit a threshold in Excel.
>> >
>> > All help will be very appreciated!
>> >
>> > Thanks, Anders
>> >
>>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: XSSF: Problem with CellUtil and custom RGB background color

Javen O'Neal
And unfortunately, HSSFColor/HSSFExtendedColor and XSSFColor behave
pretty differently, so it will take some work making this work on
Common SS.

I opened bug 59442 [1] to start working towards a fix.

[1] https://bz.apache.org/bugzilla/show_bug.cgi?id=59442

On Sun, May 8, 2016 at 2:00 PM, Javen O'Neal <[hidden email]> wrote:

> This looks like a bug in the CellUtil.get/setFormatProperties methods,
> which assumes the fill foreground color is an indexed color (short
> data type), and falls back to IndexedColors.AUTOMATIC (black) if not
> indexed.
>
> Could you open a bug at https://bz.apache.org/bugzilla with your test
> case, quote the sentence above, and a link to
> http://apache-poi.1045710.n5.nabble.com/XSSF-Problem-with-CellUtil-and-custom-RGB-background-color-td5722925.html?
>
> XSSFCellStyle.java:
>     https://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java?revision=1737895&view=markup#l455
>     @Override
>     public short getFillForegroundColor() {
>         XSSFColor clr = getFillForegroundXSSFColor();
>         return clr == null ? IndexedColors.AUTOMATIC.getIndex() :
> clr.getIndexed();
>     }
>
>     @Override
>     public XSSFColor getFillForegroundColorColor() {
>        return getFillForegroundXSSFColor();
>     }
>
>     https://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java?revision=1737895&view=markup#l1027
>     @Override
>     public void setFillForegroundColor(short fg) {
>         XSSFColor clr = new XSSFColor();
>         clr.setIndexed(fg);
>         setFillForegroundColor(clr);
>     }
>
>     public void setFillForegroundColor(XSSFColor color) {
>         CTFill ct = getCTFill();
>
>         CTPatternFill ptrn = ct.getPatternFill();
>         if(color == null) {
>             if(ptrn != null && ptrn.isSetFgColor()) ptrn.unsetFgColor();
>         } else {
>             if(ptrn == null) ptrn = ct.addNewPatternFill();
>             ptrn.setFgColor(color.getCTColor());
>         }
>
>         addFill(ct);
>     }
>
> On Mon, May 2, 2016 at 8:15 AM, Anders Åberg <[hidden email]> wrote:
>> I'm using 3.14.
>>
>> -Anders
>>
>> On 2 May 2016 at 16:35, Javen O'Neal <[hidden email]> wrote:
>>
>>> What version of POI are you using?
>>> On May 2, 2016 03:25, "Anders Åberg" <[hidden email]> wrote:
>>>
>>> > Hi guys
>>> > I am trying to set RGB background colors on cells and in addition set
>>> > borders on regions using RegionUtil. When doing this, all the cells with
>>> > borders end up completely black.
>>> >
>>> > It seems that using XSSFColor on XSSFCellStyle causes problems with the
>>> way
>>> > CellUtil.setCellStyleProperties works. CellUtil is used by RegionUtil.
>>> >
>>> > This is a minimal test to reproduce the problem:
>>> >
>>> >     XSSFWorkbook workbook = new XSSFWorkbook();
>>> >     XSSFSheet sheet = workbook.createSheet();
>>> >
>>> >     Cell cell = CellUtil.getCell(CellUtil.getRow(0, sheet), 0);
>>> >
>>> >     XSSFColor color = new XSSFColor(Color.RED);
>>> >     XSSFCellStyle style = workbook.createCellStyle();
>>> >     style.setFillForegroundColor(color);
>>> >     style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
>>> >     cell.setCellStyle(style);
>>> >
>>> >     // Everything is fine at this point, cell is red
>>> >
>>> >     Map<String, Object> properties = new HashMap<>();
>>> >     properties.put(CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN);
>>> >     CellUtil.setCellStyleProperties(cell, properties);
>>> >
>>> >     // Now the cell is all black
>>> >
>>> > Does anybody know if this is supposed to work? Any suggestions on
>>> > workarounds? It is of course possible to avoid the CellUtil methods, but
>>> > they contain some clever logic to avoid creating new styles for every
>>> cell
>>> > and eventually hit a threshold in Excel.
>>> >
>>> > All help will be very appreciated!
>>> >
>>> > Thanks, Anders
>>> >
>>>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]