Bug 46938

Summary: Existing color palette is broken(or not used) when using cell styles from opened workbook and creating new
Product: POI Reporter: Maksym Symonov <Maksym_Symonov>
Component: HSSFAssignee: POI Developers List <dev>
Status: REOPENED ---    
Severity: normal CC: Karl.Eilebrecht
Priority: P2    
Version: 3.5-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: test xls file for a test case
a file generated by the posted code that doesn't exhibit the problem
foo.xls opened in Excel 2003
foo.xls opened in Excel 2007
Keep Excel2007-colors patch

Description Maksym Symonov 2009-03-30 07:11:52 UTC
After opening existing workbook with some created styles in Excel and applying them to new cells, and then creating any new style by calling HSSFWorkbook.createCellStyle() all existing styles lose their foreground colors. Seems like they are painted with default palette colors.
After code exploring of createCellStyle() it appears that one new ExtendedFormatRecord is created and added to HssWorkbook.workbook.records, where palette record is stored. So somehow existing palette is overrided or hided by default.
Comment 1 Maksym Symonov 2009-03-30 07:31:57 UTC
Created attachment 23426 [details]
test xls file for a test case

InputStream in = null;
		OutputStream out = null;
		try
		{
			in = new FileInputStream( new File("D://foo.xls") );
			HSSFWorkbook workbook  = new HSSFWorkbook( in );
			HSSFSheet existingSheet = workbook.getSheet( "existing" );
			HSSFCellStyle style = existingSheet.getRow( 0 ).getCell( 0 ).getCellStyle();
			
			HSSFSheet sheet = workbook.createSheet( "test" );
			workbook.createCellStyle();
			sheet.createRow( 0 ).createCell( 0 ).setCellStyle( style );
			
			workbook.setActiveSheet( 1 );
			out = new BufferedOutputStream( new FileOutputStream( new File("D://new.xls") ) ); 
			workbook.write( out );
		} catch( Exception e )
		{
			e.printStackTrace();
		}
	 finally
		{
			IOUtils.closeQuietly( in );
			IOUtils.closeQuietly( out );
		}
Comment 2 Yegor Kozlov 2009-03-30 08:48:18 UTC
Created attachment 23427 [details]
a file generated by the posted code that doesn't exhibit the problem
Comment 3 Yegor Kozlov 2009-03-30 08:48:56 UTC
I can't reproduce the problem. What version of POI and Excel are you using? 

I'm looking at new.xls created by your sample code and all styles are there. A1 cells both on the existing and new sheets have the same style with white foreground. I attached the generated file. Please confirm that the foreground is lost. 

Tested with trunk and Excel 2003.

Regards,
Yegor
Comment 4 Maksym Symonov 2009-03-31 00:23:43 UTC
Yegor, problem is in row:
workbook.createCellStyle();
as you see created style isn't set to any of cells. Please, try commenting this line in example code and look into results generated. Both cells A1 on on both sheets should have light blue foreground as in existing.xls file which is some kind of template for generating new.xls. If this row is commented all works fine, style is fully copied to A1 on "test" sheet, if it is not both cells loose their color and it becomes white.
After some more code exploring it was found that palette from original existing.xls file after it is loaded by POI is stored at HSSFWorkbook.workbook.records in a record of class UnknownRecord which has toString() interpretation
[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, 04, 5F, 00, 2E, 00, 5F, 00, 2D, 0E, 00, 05, 00, 02, 04, 00, 14, 00, 02, 00, 00, 00, E7, EC, F4, FF, 23, 00, 30, 00, 2E, 00, 30, 00]
[/XFEXT]

needed light blue color is in this record in a rawdata field E7, EC, F4
Comment 5 Yegor Kozlov 2009-03-31 05:58:18 UTC
Created attachment 23429 [details]
foo.xls opened in Excel 2003

Excel 2003 does not recognize custom palette saved in the compatibility mode in Excel 2007. The foreground in A1 should be light blue.
Comment 6 Yegor Kozlov 2009-03-31 05:58:35 UTC
Created attachment 23430 [details]
foo.xls opened in Excel 2007
Comment 7 Yegor Kozlov 2009-03-31 06:12:52 UTC
Maksym,

The problem seems to be specific to Excel 2007. Did you create the template in Office 2007 and saved in the compatibility mode? Were there any warnings? 

If I open foo.xls in Excel 2007 then the foreground is light blue. If I open it in Excel 2003 the foreground is white. It's not a bug of POI, rather a "feature" of Excel. When saving custom colors in the .xls format, Excel 2007 does not translate them into the standard palette. That's why the foreground is not recognized by Excel 2003.

The [XFEXT] (0x87D) record is not a part of the BIFF8 specification. It's a new stuff introduced by Excel 2007. If you are fancy to decode it - patches are welcome.  Otherwise, I would recommend you to create your templates in Excel 2003. This way it should always work.

Regards,
Yegor
Comment 8 Reid 2009-10-06 15:12:55 UTC
Not related to HSSF, but the color palette itself can be changed in Excel 2007 to work with earlier versions of Excel (and the current version of HSSF).

This way you don't need Excel 2003 to create your color templates.  

The process is explained in Microsoft support: 
http://support.microsoft.com/kb/288412
Comment 9 Karl Eilebrecht 2009-11-01 02:51:05 UTC
Hi, 
I ran into the same problem and must agree with the previous speakers, this "bug" is an unsupported behaviour of Excel 2007, not an error of POI.

However, the situation is extremely disappointing for me, since I'll have to support Excel 2007 for editing templates - and colors are very important for some customers.

After a weekend of tracing the serialization of records and trial+error I tried the following hack:

using POI 3.5-FINAL
Workbook.class, line 812
public ExtendedFormatRecord createCellXF() {
    int insertPos = records.getXfpos() + 1;
    if (insertPos < records.size()) {
    	while (records.get(insertPos) instanceof UnknownRecord) {
    		insertPos++;
    		if (insertPos == records.size()) {
    			break;
    		}
    	}
    }
    records.add(insertPos, xf);
    records.setXfpos( insertPos );
    numxfs++;
    return xf;
}

Obviously this is rather a hack than a solution. It seems(!) to help.
And if so, this workaround could be useful for many people using POI.

I'd be pleased if some of the gurus could check if this "happy reordering" is in conflict with any known rule of the excel format and thus may be discouraged.
Especially testing with different excel versions (I only have 2007) would be important.

Thanks!

Regards.
Karl
Comment 10 Karl Eilebrecht 2009-11-02 09:49:40 UTC
Uh-oh! I overlooked a method.


    public ExtendedFormatRecord getExFormatAt(int index) {
        int xfptr = records.getXfpos() - (numxfs - 1);
    ...
    }

With my patch from the last post I created a "hole" in the table which breaks the index. I'll have to think about that, maybe I can fix that, too.
Comment 11 Karl Eilebrecht 2009-11-14 07:54:12 UTC
Created attachment 24537 [details]
Keep Excel2007-colors patch

Keep color palette for following situation:
- create Excel workbook (.xls) with Excel 2007
- modify this workbook with POI
- reopen the workbook with Excel 2007
Comment 12 Karl Eilebrecht 2009-11-14 07:54:38 UTC
Finally, my results regarding this issue are a little disappointing.

The main problem cannot be fixed because the color models of Excel 2007 is simply incompatible to that of earlier versions.
There are several discussions in the web, for example:
http://en.allexperts.com/q/Excel-1059/Excel-2007-color-palette-1.htm
http://www.eggheadcafe.com/software/aspnet/31785528/excel-2007-color-backward.aspx
http://www.excelforum.com/excel-2007-help/703936-fill-color-compatibility-excel-2007-to-excel-2003-a.html
Especially the last link seems to be interesting for template creators to completely avoid the problem in advance.

However for my situation the attached modified Workbook-class helped.
We use Excel 2007 but old Excel .xls-format (NOT .xlsx). This is far from being perfect, but I did not have the time yet to migrate to XSSF. 

The attached patch is experimental but I think it works.
The idea is to save the positions of UnknownRecords containing the new color information, so that Excel 2007 will show the original colors when reopening a POI-modified workbook instead of replacing them with "similar colors". 
Unfortunately earlier versions of Excel (I tested with Excel 2000) will still show replacement colors, if a workbook contains "new color-model colors".

Regards.
Karl
Comment 13 Karl Eilebrecht 2009-11-16 05:36:32 UTC
Remark:

Today I tested (successfully) with a more complex excel sheet and found out that the color-palette corruption problem may return if you modify cell styles that came with the original file. Strange ...

To avoid this do not modify original cell styles but copy them (only once(!), you do not have to duplicate them for single usage).

Problematic:
HSSFCellStyle myStyle = workbook.getCellStyleAt(idx);
myStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS);
myStyle.setFillForegroundColor(HSSFColor.WHITE.index);
myStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//...
//use myStyle n-times

Better:
HSSFCellStyle myStyle = workbook.createCellStyle();
myStyle.cloneStyleFrom(workbook.getCellStyleAt(idx));
//...
//use myStyle n-times

Regards.
Karl