You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2009/03/30 16:11:53 UTC
DO NOT REPLY [Bug 46938] New: Existing color palette is broken(or
not used) when using cell styles from opened workbook and creating new
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
Summary: Existing color palette is broken(or not used) when
using cell styles from opened workbook and creating
new
Product: POI
Version: 3.2-FINAL
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: Maksym_Symonov@epam.com
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.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
--- Comment #5 from Yegor Kozlov <ye...@dinom.ru> 2009-03-31 05:58:18 PST ---
Created an attachment (id=23429)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=23429)
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.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
Karl Eilebrecht <Ka...@freenet.de> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |Karl.Eilebrecht@freenet.de
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
--- Comment #12 from Karl Eilebrecht <Ka...@freenet.de> 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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
--- Comment #1 from Maksym Symonov <Ma...@epam.com> 2009-03-30 07:31:57 PST ---
Created an attachment (id=23426)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=23426)
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 );
}
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
--- Comment #13 from Karl Eilebrecht <Ka...@freenet.de> 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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
Karl Eilebrecht <Ka...@freenet.de> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |REOPENED
Version|3.2-FINAL |3.5-FINAL
Resolution|WONTFIX |
--- Comment #9 from Karl Eilebrecht <Ka...@freenet.de> 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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |WONTFIX
--- Comment #7 from Yegor Kozlov <ye...@dinom.ru> 2009-03-31 06:12:52 PST ---
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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
--- Comment #11 from Karl Eilebrecht <Ka...@freenet.de> 2009-11-14 07:54:12 UTC ---
Created an attachment (id=24537)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=24537)
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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
--- Comment #2 from Yegor Kozlov <ye...@dinom.ru> 2009-03-30 08:48:18 PST ---
Created an attachment (id=23427)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=23427)
a file generated by the posted code that doesn't exhibit the problem
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
Maksym Symonov <Ma...@epam.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #4 from Maksym Symonov <Ma...@epam.com> 2009-03-31 00:23:43 PST ---
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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
--- Comment #10 from Karl Eilebrecht <Ka...@freenet.de> 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.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
--- Comment #8 from glackk@gmail.com 2009-10-06 15:12:55 PDT ---
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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
--- Comment #6 from Yegor Kozlov <ye...@dinom.ru> 2009-03-31 05:58:35 PST ---
Created an attachment (id=23430)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=23430)
foo.xls opened in Excel 2007
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 46938] Existing color palette is broken(or not
used) when using cell styles from opened workbook and creating new
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=46938
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #3 from Yegor Kozlov <ye...@dinom.ru> 2009-03-30 08:48:56 PST ---
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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org