Bug 56295

Summary: Cell styles are not copied from one workbook to another
Product: POI Reporter: nestor.urquiza
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: critical CC: andrei.astrouski, apache.org
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: All   
OS: All   
Bug Depends on:    
Bug Blocks: 58996    
Attachments: java sources and resources
expected result
received result
v2

Description nestor.urquiza 2014-03-20 19:34:05 UTC
Attached you will find a zip file containing a JUnit test to replicate the issue. Basically we open a first workbook and add to it a new sheet where we clone the content from a second workbook sheet. We are expecting the style from the second to appear in the first however that does not happen as the screenshots show.

1. Download poi-3.10-FINAL

2. Download the junit-4.11.jar which includes hamcrest (POI lib ships the version without hamcrest)

3. Download the zip file and uncompress it somewhere

4. Compile and run the test as per the below (Note I downloaded everything to my ~/Downloads MAC directory):
$ cd xlsx-xslm-style-lost-on-merge/
$ javac -cp ".:/Users/nu/Downloads/poi-3.10-FINAL/*:/Users/nu/Downloads/poi-3.10-FINAL/lib/*" *.java
$ java -cp ".:/Users/nu/Downloads/poi-3.10-FINAL/*:/Users/nu/Downloads/poi-3.10-FINAL/lib/*:/Users/nu/Downloads/poi-3.10-FINAL/ooxml-lib/*:/Users/nu/Downloads/junit-4.11.jar" org.junit.runner.JUnitCore XlsxSheetCopyUtilTest

5. Look into the output.xlsm file to confirm the styles from pet.xlsx are lost when its sheet is copied to zoom200.xlsm file.

BTW the attached files are coming from https://issues.apache.org/bugzilla/show_bug.cgi?id=52348 with small modifications.

Thanks,
- Nestor
Comment 1 nestor.urquiza 2014-03-20 19:35:03 UTC
Created attachment 31418 [details]
java sources and resources
Comment 2 nestor.urquiza 2014-03-20 19:35:26 UTC
Created attachment 31419 [details]
expected result
Comment 3 nestor.urquiza 2014-03-20 19:35:55 UTC
Created attachment 31420 [details]
received result
Comment 4 Nick Burch 2014-03-28 11:27:17 UTC
Looking at your program, I can't spot any logic where you clone the styles from the old workbook to the new one. If you want the styles to come across, you need to use the cloneStyleFrom method:
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/CellStyle.html#cloneStyleFrom%28org.apache.poi.ss.usermodel.CellStyle%29

Can you try that and see if it resolves the problem?
Comment 5 nestor.urquiza 2014-04-02 02:19:48 UTC
Nick thanks for the suggestion. I chenged the below in XlsxSheetCopyUtil#copyCell:
//newCell.setCellStyle(newCellStyle);
newCell.getCellStyle().cloneStyleFrom(newCellStyle);


However I got the failure below even though files and code are Excel 2013 compatible as far as I understand:

There was 1 failure:
1) testMergeXlslsWithStyles(XlsxSheetCopyUtilTest)
java.lang.IllegalArgumentException: Can only clone from one XSSFCellStyle to another, not between HSSFCellStyle and XSSFCellStyle
	at org.apache.poi.xssf.usermodel.XSSFCellStyle.cloneStyleFrom(XSSFCellStyle.java:186)
	at XlsxSheetCopyUtil.copyCell(XlsxSheetCopyUtil.java:113)
	at XlsxSheetCopyUtil.copyRow(XlsxSheetCopyUtil.java:80)
	at XlsxSheetCopyUtil.copySheets(XlsxSheetCopyUtil.java:49)
	at XlsxSheetCopyUtil.copySheets(XlsxSheetCopyUtil.java:34)
	at XlsxSheetCopyUtilTest.testMergeXlslsWithStyles(XlsxSheetCopyUtilTest.java:28)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
	at org.junit.runners.Suite.runChild(Suite.java:127)
	at org.junit.runners.Suite.runChild(Suite.java:26)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:138)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:117)
	at org.junit.runner.JUnitCore.runMain(JUnitCore.java:96)
	at org.junit.runner.JUnitCore.runMainAndExit(JUnitCore.java:47)
	at org.junit.runner.JUnitCore.main(JUnitCore.java:40)

There is no reference to HSSF at all as you can see:
Nestors-MacBook-Pro:xlsx-xslm-style-lost-on-merge nu$ find ./ -name "*.java"
.//CellRangeAddressWrapper.java
.//XlsxSheetCopyUtil.java
.//XlsxSheetCopyUtilTest.java
Nestors-MacBook-Pro:xlsx-xslm-style-lost-on-merge nu$ find ./ -name "*.java"|xargs grep HSSF
Nestors-MacBook-Pro:xlsx-xslm-style-lost-on-merge nu$ 

Any ideas?

Thanks!
- Nestor
Comment 6 nestor.urquiza 2014-04-04 12:53:28 UTC
I have commented out the whole old code and leverage only to the cloneStyleFrom() as shown below. No errors but the style is still not there in the output. I will attach the whole project again with the latest changes with the hope you guys can quickly replicate and determine why the code below has no effect:

newCell.getCellStyle().cloneStyleFrom(oldCell.getCellStyle());

Thanks!
- Nestor
Comment 7 nestor.urquiza 2014-04-04 12:54:57 UTC
Created attachment 31475 [details]
v2
Comment 8 nestor.urquiza 2014-04-04 12:55:52 UTC
See v2 file and follow the original instructions to run the project. Let me know please if you need any more information. Thanks, - Nestor
Comment 9 Dominik Stadler 2015-03-14 19:37:08 UTC
This was caused by a combination of two bugs in POI and one in your code:
* POI did not handle the absence of the XML attribute "applyFill" as default "true", but it seems Excel and LibreOffice do although I could not find any answer for this in the Ecma spec 
* POI: When cloning styles, the Fills were not cloned over correctly
* Your code did not correctly create the new style, but somehow relied on a style being availabe, which did not work correctly, 

Updating to a build of rr1666736 or newer and adjusting the cloning of the style in your code as follows did make the sample work for me:

        //newCell.getCellStyle().cloneStyleFrom(oldCell.getCellStyle());
        CellStyle newCellStyle = newCell.getRow().getSheet().getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);
Comment 10 Dominik Stadler 2015-03-18 20:28:43 UTC
*** Bug 50956 has been marked as a duplicate of this bug. ***
Comment 11 Dominik Stadler 2015-04-15 14:07:44 UTC
SVN link is r1666736
Comment 12 chinafool 2015-04-18 10:42:31 UTC
I hava met this problem recently.
The method "cloneStylefrom()" doesn't  work.
Can you give a temp way to solve this problem?
Much Thanks.
Comment 13 Greg Woolsey 2017-06-20 18:20:17 UTC
*** Bug 53262 has been marked as a duplicate of this bug. ***
Comment 14 Marco Sulla 2019-05-31 08:52:18 UTC
It continues to be a bug in 4.1.0. When the patch will be released?
Comment 15 Dominik Stadler 2019-05-31 09:36:53 UTC
The changes done in this ticket are applied and released since a long time, probably since version 3.12, if you have a similar problem in recent versions than please report a new bug with necessary information to let us reproduce the problem. E.g. sample files, sample code, ... greatly helps in reproducing and fixing such problems.