0

We are using the Apache POI library to generate Excel file and writing out the created workbook to HttpServletResponse OutputStream to download the file.

After upgrading Apache poi version from 3.17 to 5.0.0, Excel(xlsx) file content is being corrupted.

I am seeing the below statement in version 5.0.0 change list.

Upgrade to ECMA-376 5th edition (transitional) schemas - expect API breaks when using XmlBeans directly some smaller changes are necessary when code is using the low-level CT... classes

Is it causing this issue? If yes, what changes need to done in my code?

Code:

final SXSSFWorkbook workbook = new SXSSFWorkbook(100);
final Sheet sheet = workbook.createSheet("Catalogue");

final CellStyle style = workbook.createCellStyle();
final Font font = workbook.createFont();
            
font.setBold(true);
font.setColor(IndexedColors.BLUE.getIndex());
style.setFont(font);
font.setFontHeightInPoints((short) 11);

int rowCount = 0;
final Row header = sheet.createRow(rowCount++);
final Cell c11 = header.createCell(0);
c11.setCellValue("Role");
c11.setCellStyle(style);

final Cell c12 = header.createCell(1);
c12.setCellValue("Business Process");
c12.setCellStyle(style);

int colcount=0;

final Row row = sheet.createRow(rowCount++);
row.createCell(colcount++).setCellValue(v.getRoleName());
row.createCell(colcount++).setCellValue(v.getBusinessProcess());

final OutputStream os = response.getOutputStream();
 
response.setContentType("application/xlsx");
response.addHeader("Content-Disposition", "attachment; filename=\"" + "RoleCatalogues" + ".xlsx\"");
OutputStream outputStream = new BufferedOutputStream(os);

workbook.write(outputStream));
response.flushBuffer();
workbook.dispose();

Error:

Caused by: java.lang.NoSuchMethodError: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFont.addNewFamily()Lorg/openxmlformats/schemas/spreadsheetml/x2006/main/CTIntProperty;

Question with same error

3.17 POI dependencies

+- org.apache.poi:poi:jar:3.17:compile
[INFO] |  \- commons-codec:commons-codec:jar:1.10:compile
[INFO] +- org.apache.poi:poi-ooxml:jar:3.17:compile
[INFO] |  +- org.apache.poi:poi-ooxml-schemas:jar:3.17:compile
[INFO] |  |  \- org.apache.xmlbeans:xmlbeans:jar:2.6.0:compile
[INFO] |  |     \- stax:stax-api:jar:1.0.1:compile
[INFO] |  \- com.github.virtuald:curvesapi:jar:1.04:compile

5.0.0 POI dependencies

+- org.apache.poi:poi:jar:5.0.0:compile
[INFO] |  +- commons-codec:commons-codec:jar:1.15:compile
[INFO] |  +- org.apache.commons:commons-math3:jar:3.6.1:compile
[INFO] |  \- com.zaxxer:SparseBitSet:jar:1.2:compile
[INFO] +- org.apache.poi:poi-ooxml:jar:5.0.0:compile
[INFO] |  +- org.apache.poi:poi-ooxml-lite:jar:5.0.0:compile
[INFO] |  |  \- org.apache.xmlbeans:xmlbeans:jar:4.0.0:compile
[INFO] |  |     \- xml-apis:xml-apis:jar:1.4.01:compile
[INFO] |  +- org.apache.commons:commons-compress:jar:1.20:compile
[INFO] |  \- com.github.virtuald:curvesapi:jar:1.06:compile
Ram
  • 423
  • 4
  • 26
  • 3
    In version 3.17 `workbook.write(outputStream)` had closed the `outputStream` after writing. Newer versions do not do this anymore. Try `outputStream.close()` after `workbook.write(outputStream)`. – Axel Richter Jan 07 '22 at 16:30
  • @AxelRichter , apologies for late reply. I am getting an error and I know which not related to your suggestion. Please see the updated question for error. – Ram Jan 10 '22 at 07:20
  • @AxelRichter, I saw your answer to this error in another question, there is no ooxml-schemas-1.4.jar in the classpath. – Ram Jan 10 '22 at 07:24
  • Apache POI 5 needs `poi-ooxml-lite...jar` or `poi-ooxml-full...jar` in class path. – Axel Richter Jan 10 '22 at 07:36
  • `workbook.write(outputStream)); outputStream.flush(); ...` is something too. – Joop Eggen Jan 10 '22 at 07:38
  • 1
    "there is no ooxml-schemas-1.4.jar in the classpath": According to the error there must be some old `ooxml-schemas` library in class path. Please do check. You can ask the `ClassLoader` where a special class (`org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFont` in your case) came from on run time. See https://stackoverflow.com/questions/69100632/apache-poi-unable-to-read-sheet-names/69102220#69102220. – Axel Richter Jan 10 '22 at 07:49
  • @AxelRichter, You are correct, I checked with -verbose:class option. It is loading old version(3.17) of POI jars as well even I did Maven update the project and Maven clean and build. I need to find the way how to stop old version jars from loading. – Ram Jan 10 '22 at 11:08
  • But, when I check under Maven Dependencies, old version jars are not appearing. – Ram Jan 10 '22 at 11:13
  • So it is a maven problem. Maybe apache poi 3.17 gets load because of transitive dependencies automatically? What else libraries are you using which may have apache poi as a dependency? Maybe you can update those too? – Axel Richter Jan 10 '22 at 11:53
  • updated question with both versions of POI dependencies. seems there are no transitive dependencies. – Ram Jan 10 '22 at 12:36
  • So the dependency apache poi 3.17 is in your POM? Why? – Axel Richter Jan 10 '22 at 13:41
  • Apologies for confusion, apache poi 3.17 version is upgraded to 5.0.0(not in POM). Just want to show what were the dependencies with 3.17 and with 5.0.0 (before and after up-gradation). – Ram Jan 10 '22 at 14:55
  • @AxelRichter, it worked in direct Tomcat. I think issue with running Tomcat with Ecipse. Please add your comments as an answer. Thanks. – Ram Jan 10 '22 at 16:49
  • I don't understand why the question had -1 rank this helped to see that someone else experience the issue, upvoting. – boaz levinson Jul 11 '22 at 11:48

1 Answers1

2

As per comments from @AxelRichter, in version 3.17 workbook.write(outputStream) had closed the outputStream automatically after writing. In newer versions, it has to be closed manually after writing to workbook like below :

workbook.write(outputStream);
workbook.dispose();
outputStream.close();
Ram
  • 423
  • 4
  • 26