Bug 56295 - Cell styles are not copied from one workbook to another
Summary: Cell styles are not copied from one workbook to another
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: unspecified
Hardware: All All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
: 50956 53262 (view as bug list)
Depends on:
Blocks: 58996
  Show dependency tree
 
Reported: 2014-03-20 19:34 UTC by nestor.urquiza
Modified: 2017-06-20 18:20 UTC (History)
1 user (show)



Attachments
java sources and resources (101.73 KB, application/zip)
2014-03-20 19:35 UTC, nestor.urquiza
Details
expected result (27.28 KB, image/png)
2014-03-20 19:35 UTC, nestor.urquiza
Details
received result (34.47 KB, image/png)
2014-03-20 19:35 UTC, nestor.urquiza
Details
v2 (105.73 KB, application/zip)
2014-04-04 12:54 UTC, nestor.urquiza
Details

Note You need to log in before you can comment on or make changes to this bug.
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. ***