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
Created attachment 31418 [details] java sources and resources
Created attachment 31419 [details] expected result
Created attachment 31420 [details] received result
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?
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
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
Created attachment 31475 [details] v2
See v2 file and follow the original instructions to run the project. Let me know please if you need any more information. Thanks, - Nestor
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);
*** Bug 50956 has been marked as a duplicate of this bug. ***
SVN link is r1666736
I hava met this problem recently. The method "cloneStylefrom()" doesn't work. Can you give a temp way to solve this problem? Much Thanks.
*** Bug 53262 has been marked as a duplicate of this bug. ***
It continues to be a bug in 4.1.0. When the patch will be released?
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.