Bug 55844 - Inline custom colors not correctly extracted by HSSFCellStyle#getFillForegroundColorColor
Summary: Inline custom colors not correctly extracted by HSSFCellStyle#getFillForegrou...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-12-04 17:02 UTC by bknippen
Modified: 2013-12-08 20:30 UTC (History)
0 users



Attachments
sample xls with inline custom colors created in Excel 2013 (25.00 KB, application/vnd.ms-excel)
2013-12-04 17:02 UTC, bknippen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description bknippen 2013-12-04 17:02:55 UTC
Created attachment 31091 [details]
sample xls with inline custom colors created in Excel 2013

The attached color.xls is created with excel 2013. cell 0,0 has a custom foreground fill color applied with rgb values 0, 100, 255.

Extracting that color with the below sample code gives 0:0:FFFF, (which is not the color in the actual workbook).

Note that when this same book is saved as xlsx, using the XSSF code allows you to extract the correct RGB value.

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;

public class TestColor {
  public static void main(String[] args) throws Exception {
    FileInputStream in = new FileInputStream(args[0]);
    Workbook wb = WorkbookFactory.create(in);
    Sheet sheet = wb.getSheetAt(0);
    Cell cell = sheet.getRow(0).getCell(0);
    System.out.println(((HSSFColor)cell.getCellStyle().getFillForegroundColorColor()).getHexString());
  }
}
Comment 1 Dominik Stadler 2013-12-08 12:47:38 UTC
In LibreOffice, the sample file uses color "blue" from the default color palette and I could not find a record in the xls with sid 0x0092 which would indicate a custom palette. Blue is 0:0:255, which is rendered as hex-string 0:0:FFFF currently, so from this angle POI seems to be correct. 

So either the newer Excel stores the colors differently or the sample file is not actually setting the fill-color that you describe, I will try on a Windows box to verify.
Comment 2 Dominik Stadler 2013-12-08 19:34:54 UTC
I verified now that on Windows in Excel 2010 the file shows a custom color of 0:100:255 as you state, further investigation indicates that this is likely a feature that POI does not fully support yet. 

The BiffViewer shows the following record which seems to contain this data:

Offset=0x00001BC8(7112) recno=178 sid=0x087D size=0x0041(65)
[XFEXT] (0x87D)
  rawData=[7D, 08, 00, 00, 00, 00, 00, 00, 00, 00, 00, 00, 00, 00, 3E, 00, 00, 00, 03, 00, 0D, 00, 14, 00, 03, 00, 00, 00, 01, 00, 00, 00, 30, 30, 5C, 29, 3B, 5F, 28, 2A, 0E, 00, 05, 00, 02, 04, 00, 14, 00, 02, 00, 00, 00, 00, 64, FF, FF, 3B, 5F, 28, 40, 5F, 29, 20, 20]
[/XFEXT]


The sid 087D is listed as "not yet supported" in UnknownRecord.java, not sure if there is enough public description of this record-type to implement support for it in POI.
Comment 3 Dominik Stadler 2013-12-08 20:30:17 UTC
Bug 46938 talks about similar issues, it seems Excel 2007 introduced this extended record. 

See http://msdn.microsoft.com/en-us/library/dd925873%28v=office.12%29.aspx for details about the record, however it seems to be not fully up to date as it specifies fields as "reserved and must be 0" which are set in this file...