Bug 51236 - Font color set incorrectly for black and white color using RGB values
Font color set incorrectly for black and white color using RGB values
Status: NEW
Product: POI
Classification: Unclassified
Component: XSSF
3.8-dev
PC Windows XP
: P2 critical with 5 votes (vote)
: ---
Assigned To: POI Developers List
:
Depends on:
Blocks:
  Show dependency tree
 
Reported: 2011-05-20 22:01 UTC by jxz164
Modified: 2014-03-27 22:15 UTC (History)
2 users (show)



Attachments
Java code to show that Font colors are incorrect for black and white (1.71 KB, application/octet-stream)
2011-05-20 22:01 UTC, jxz164
Details
Two xlsx files to check the bug (7.88 KB, application/octet-stream)
2011-05-24 19:30 UTC, jxz164
Details
A test file showing how Excel interprets colours for fonts and backgrounds. (5.97 KB, application/octet-stream)
2011-09-29 07:11 UTC, Jim Talbut
Details
Test file demonstrating that POI 3.7 does not have the problem. (5.98 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2011-09-29 07:12 UTC, Jim Talbut
Details
Source file used for generating the two test files. (6.95 KB, application/octet-stream)
2011-09-29 07:14 UTC, Jim Talbut
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jxz164 2011-05-20 22:01:33 UTC
Created attachment 27044 [details]
Java code to show that Font colors are incorrect for black and white

I am using POI 3.8 beta 2 to create cells with black and white font colors using RGB values.

The RGB value for white color should be FFFFFF and for black should be 000000. 

However, in the Excel file that was created, Font colors were reversed for black and white. The RGB values from the Font Color icon are reversed.


Attached is the source code to reproduce this issue.


package org.apache.poi.ss.examples;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFont;

import java.io.FileOutputStream;

public class TestFontColor {
	protected static XSSFColor getXSSFColor(String RGB) {

		int red = Integer.parseInt(RGB.substring(0,2), 16);
		int green = Integer.parseInt(RGB.substring(2,4), 16);
		int blue = Integer.parseInt(RGB.substring(4,6), 16);
				   
		return new XSSFColor(new byte[] { (byte) red, (byte) green, (byte) blue });
	}

    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();  //or new HSSFWorkbook();
        Sheet sheet = wb.createSheet("Fonts");

        Font font0 = wb.createFont();
        Font font1 = wb.createFont();
        
        XSSFColor color_white = getXSSFColor("FFFFFF");
        XSSFColor color_black = getXSSFColor("000000");
        
        ((XSSFFont) font0).setColor(color_white); //FFFFFF returns black
        ((XSSFFont) font1).setColor(color_black); //000000 returns white
        
        CellStyle style0 = wb.createCellStyle();
        CellStyle style1 = wb.createCellStyle();
        style0.setFont(font0);
        style1.setFont(font1);

        Cell cell0 = sheet.createRow(0).createCell(0);
        cell0.setCellValue("FFFFFF");
        cell0.setCellStyle(style0);

        Cell cell1 = sheet.createRow(1).createCell(0);
        cell1.setCellValue("000000");
        cell1.setCellStyle(style1);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("C:\\workspace\\TestFontColor.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}
Comment 1 Nick Burch 2011-05-23 09:41:10 UTC
I seem to recall that Excel does something special with XSSF font colours and black/white

If you set the colour in POI, and read it back, do you get the right one or the wrong one?

(I've a feeling that we just need to replicate the black/white switch in the read logic at write time, but need to check)
Comment 2 jxz164 2011-05-23 14:52:11 UTC
If I read the color back, I will get the same RGB value as what I set. For example, if I set the RGB value to FFFFFF, I will also get the RGB value FFFFFF when reading it back. But the problem is FFFFFF which supposes to mean white actually gets black in Font color. 

Is this the special thing about XSSF Color of black/white? Do you think this is not a POI bug?
Comment 3 jxz164 2011-05-23 19:41:35 UTC
I have looked at the styles.xml and confirmed that the color value in the styles.xml 

<color rgb="000000"/>

when RGB value is set to "FFFFFF" as the input in the java code.

But when I read it back, it returns "FFFFFF" instead of "000000". This is wrong to me.
Comment 4 Nick Burch 2011-05-23 19:44:52 UTC
When you create a file in excel with a black cell, and look at the xml, is it not the wrong way round? That's what there's specific code for in POI already
Comment 5 Nick Burch 2011-05-23 19:54:48 UTC
I've done the matching black/white fix on set as well as get in r1126696, does that solve it?
Comment 6 jxz164 2011-05-24 19:30:56 UTC
Created attachment 27053 [details]
Two xlsx files to check the bug

I am not sure whether your new check in will fix the underlying problem. Please test the following two Excel files.

(1) The first one is TestFontColor.xlsx which is created from the POI library directly.
(2) The second one is TestFontColorModified.xlsx which is modified from TestFontColor.xlsx in Excel by adding a new row.

When I read the Font Colors of these two files, I found that the results are not consistent for the first two rows.
Comment 7 Nick Burch 2011-05-25 15:24:48 UTC
Could you please retry with the recent change and see if it did help? If not, please re-do your files so we can take a look knowing they're from the latest code
Comment 8 jxz164 2011-05-25 15:52:04 UTC
Your recent changes of correctRGB to reverse black and white are for rgb.length = 3. In my code, I have worked around it by adding alpha value. Please see my code attached.

Now the problem is when reading the xlsx file created back, the color is wrong.

package org.apache.poi.ss.examples;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFont;

import java.io.FileOutputStream;

public class TestFontColor {
	protected static XSSFColor getXSSFColor(String RGB) {

		int red = Integer.parseInt(RGB.substring(0,2), 16);
		int green = Integer.parseInt(RGB.substring(2,4), 16);
		int blue = Integer.parseInt(RGB.substring(4,6), 16);

		 //add alpha to avoid bug 51236
		byte[] rgb = new byte[] { (byte) -1, (byte) red, (byte) green, (byte) blue };
		   
		return new XSSFColor(rgb);
	}

    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();  //or new HSSFWorkbook();
        Sheet sheet = wb.createSheet("Fonts");

        Font font0 = wb.createFont();
        Font font1 = wb.createFont();
        
        XSSFColor color_white = getXSSFColor("FFFFFF");
        XSSFColor color_black = getXSSFColor("000000");
        
        ((XSSFFont) font0).setColor(color_white); //FFFFFF returns black
        ((XSSFFont) font1).setColor(color_black); //000000 returns white
        
        font0.setFontHeightInPoints((short) 18);
        CellStyle style0 = wb.createCellStyle();
        CellStyle style1 = wb.createCellStyle();
        style0.setFont(font0);
        style1.setFont(font1);

        Cell cell0 = sheet.createRow(0).createCell(0);
        cell0.setCellValue("FFFFFF");
        cell0.setCellStyle(style0);

        Cell cell1 = sheet.createRow(1).createCell(0);
        cell1.setCellValue("000000");
        cell1.setCellStyle(style1);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("C:\\workspace\\TestFontColor.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}
Comment 9 Nick Burch 2011-05-25 15:57:02 UTC
The original bug report was that it was only wrong for 3 part colours, not 4 part colours. 

Are you able to create a file with excel that contains both 3 part and 4 part colours? (Themes normally have 3 part colours, manual styling is normally 4 part, but this isn't always the case...). It'd need white, black, and some other colours too. We can then be sure once and for all exactly what excel does in the various cases!
Comment 10 jxz164 2011-06-13 19:55:51 UTC
I am unable to create 4 part color for the Font using the POI library. For example, 

XSSFColor CW = new XSSFColor(new byte[] { (byte) -1, (byte) 255, (byte) 255, (byte) 255 });

and set the Font Color to CW.

The generated xlsx file contains

<color rgb="FFFFFF"/>

instead of

<color rgb="FFFFFFFF"/>

in the styles.xml file. The problem with this is that when you unload such color representation, you will get rgb value 000000 (which is wrong)!

If you have a way to get the 4-part color for the Font color, please let me know. 

However, such 4-color part seems to work for Foreground. Please see code attached.

package org.apache.poi.ss.examples;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFont;

import java.awt.Color;
import java.io.FileOutputStream;
import java.util.HashMap;

public class TestFontColor {

	private static HashMap createStyles(Workbook wb){
		  
		HashMap m = new HashMap();
        XSSFColor CW = new XSSFColor(new byte[] { (byte) -1, (byte) 255, (byte) 255, (byte) 255 });
        XSSFColor CB = new XSSFColor(new byte[] { (byte) -1, (byte) 0, (byte) 0, (byte) 0 });

		XSSFCellStyle styleX = (XSSFCellStyle)wb.createCellStyle(); 
		XSSFFont fontX = (XSSFFont) wb.createFont();
        fontX.setColor(CW); //do it again
        fontX.setFontHeightInPoints((short) 29);
        styleX.setFont(fontX);
        styleX.setFillForegroundColor(CB);
        styleX.setFillPattern((short) 1);
        m.put("WHITE", styleX);
       
        XSSFCellStyle styleY = (XSSFCellStyle) wb.createCellStyle();
		XSSFFont fontY = (XSSFFont) wb.createFont();
        fontY.setColor(CB); //FFFFFF returns black
        fontY.setFontHeightInPoints((short) 23);
        styleY.setFont(fontY);	        
        m.put("BLACK", styleY);

        return m;
		  
	  }

    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();  //or new HSSFWorkbook();
        Sheet sheet = wb.createSheet("Fonts");

        HashMap map = createStyles(wb);

        Cell cell0 = sheet.createRow(0).createCell(0);
        cell0.setCellValue("A1234567");
        cell0.setCellStyle((CellStyle) map.get("WHITE"));

        Cell cell1 = sheet.createRow(1).createCell(0);
        cell1.setCellValue("B7654321");
        cell1.setCellStyle((CellStyle) map.get("BLACK"));

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("C:\\workspace\\TestFontColor.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}
Comment 11 Jim Talbut 2011-09-28 19:30:48 UTC
I've got the same problem with v3.8-beta4, if there is anything I can do to help resolve it please let me know.
Comment 12 Nick Burch 2011-09-28 22:42:23 UTC
(In reply to comment #11)
> I've got the same problem with v3.8-beta4, if there is anything I can do to
> help resolve it please let me know.

We're after a file with:
* 4 part white
* 4 part black
* 4 part <something else>
* 3 part white
* 3 part black
* 3 part <something else>

We can then unpick that, compare how excel renders them to what one would expect, and put in suitable workarounds. (It seems that in some cases excel reverses white and black, but not all, and we need a single test file with all the cases in it to be sure what happens when)
Comment 13 Jim Talbut 2011-09-29 07:11:49 UTC
Created attachment 27629 [details]
A test file showing how Excel interprets colours for fonts and backgrounds.

Attachign a single file demonstrating Excel's handling of colours for fonts and backgrounds.
The point is that everything is correct except black/white font.
However given that the background colour is generated exactly the same as the font colour something somewhere is reversing black and white for just fonts.

I think what's happening is that XSSF is always swapping black and white, and Excel is not swapping them back for fonts.
Comment 14 Jim Talbut 2011-09-29 07:12:35 UTC
Created attachment 27630 [details]
Test file demonstrating that POI 3.7 does not have the problem.

POI 3.7 gets the black/white problem right.
Comment 15 Jim Talbut 2011-09-29 07:14:53 UTC
Created attachment 27631 [details]
Source file used for generating the two test files.

I hope I didn't go too far overboard with the number of colours in the test files, let me know if you want a single other colour.
Comment 16 Nick Burch 2011-09-29 15:41:58 UTC
Thanks for the test file. Looks like we're getting everything correct *except* three colour font (foreground) black/white, is that correct?
Comment 17 Jim Talbut 2011-09-29 15:55:16 UTC
I think so, yes.
Comment 18 Yoann Rodière 2014-03-24 14:07:33 UTC
Hello,

It seems the bug is still present in 3.10-Final. I'm experiencing it in one of my projects.

It's going to be quite long, so here's a summary of the following: there are some issues with the previous patch, mainly because it wasn't focusing on the proper class, which could trigger problems with other types of colors (background, borders). I think the fix should be located in some class related to the font (XSSFFont) or to the XML tag (a new CTFontColorImpl class) instead.

-- Long (but more reasoned) version
Since it's been a while, it may be for the best to start all over again:

There is a "bug" (or at least inconsistency) in Microsoft Excel, which interprets font colors strangely: for colors without alpha component, the black and white are switched.

A fix has been commited (by  Nick Burch, if I'm correct). This fix works fine when using the setter/getter for the RGB value. Unfortunately, only XSSF#setRgb and #getRbg methods have been fixed, and not the constructors ; which means that when you write "new XSSFColor(Color.WHITE)", the fix will be ineffective. That's one thing, and it explains that Jim Talbut reported that the fix didn"t work: Jim was using the XSSFColor constructor, which wasn't fixed.
Another thing is, this fix directly switches black and white for ALL colors of ANY purpose. It will switch, for instance, black and white for background colors, or border colors. This is a real problem, since background colors and border colors are not affected by the bug in Microsoft Excel. So all in all, we'll have correct font colors, but incorrect background colors and border colors.

That's why I think that the fix in XSSFColor should at least be moved to XSSFFont#setColor(XSSFColor) and XSSFFont#getXSSFColor. However, it might be a bit hard to do it in such a way that clients never see the "buggy" (Excel-translated) color...
Maybe it would be even more appropriate to have a CTFontColorImpl extend CTColorImpl and implement the fix. Indeed, it seems the XML color tags do not have the same meaning when dealing with borders/backgrounds as when dealing with fonts, so maybe it would make sense to define two different Java representations of the XML elements. Besides, the color switching only really matters when reading/writing the file, not when manipulating data. Of course, I don't know much about the internals of POI, and it might not be feasible...
Comment 19 Jo Desmet 2014-03-27 22:15:09 UTC
We have switched from 3.7 (which did not show the problem) to 3.9 (20121203) that does manifest the problem. I did not test on 3.10-FINAL.