Dear members,
I need to use apache poi library to read excel file in AEM6.5 instance. I have created sample maven project and included all dependencies required to read excel file in maven project.
Getting below error while running sample java class which reads the excel file
Exception in thread "main" java.lang.NoSuchMethodError: org.apache.poi.util.XMLHelper.newDocumentBuilder()Ljavax/xml/parsers/DocumentBuilder;
at org.apache.poi.ooxml.util.DocumentHelper.newDocumentBuilder(DocumentHelper.java:47)
at org.apache.poi.ooxml.util.DocumentHelper.<clinit>(DocumentHelper.java:36)
at org.apache.poi.openxml4j.opc.internal.ContentTypeManager.parseContentTypesFile(ContentTypeManager.java:392)
at org.apache.poi.openxml4j.opc.internal.ContentTypeManager.<init>(ContentTypeManager.java:104)
at org.apache.poi.openxml4j.opc.internal.ZipContentTypeManager.<init>(ZipContentTypeManager.java:54)
at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:258)
at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:721)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:274)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:180)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:323)
at com.test.aem.ExcelTest.main(ExcelTest.java:21)
However same code is working fine in plain java project with below jar files included in java class path
I am using jdk 11 and AEM6.5 SP21
Any help is highly appreciated.
Thanks,
Pradeep
Solved! Go to Solution.
Views
Replies
Total Likes
Here we have the solution, uber jar 6.5.5 in using apache poi 4.0.1 version internally. So we need to include same version of poi-ooxml dependency
Only this dependency is suffice
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
It worked finally in local standalone class and AEM container as well
Thanks to all the contributors.
Regards,
Pradeep
Have you made the necessary configs to instruct Maven to include your third-party library dependency to the bundle during deployment ?
Some thread on the embedding topic: https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/import-third-party-depende... . Maybe this gives you a lead.
Views
Replies
Total Likes
Hi Tethich,
I am not deploying the code to AEM container, just written below code in a java class and trying to run.
XSSFWorkbook wb = new XSSFWorkbook(new File("/Users/pradeepdubey/students.xlsx"));
XSSFSheet sheet = wb.getSheetAt(0);
Added all required maven dependencies in pom file. Still no luck.
Views
Replies
Total Likes
Hi @pradeepdubey82 ,
Did you try creating the XSSFWorkbook object from an inputStream ?
FileInputStream file = new FileInputStream("C:\\Users\\<userName>\\Desktop\\Book2.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(file);
XSSFSheet sh = wb.getSheet("Sheet1");
Regards,
Anupam Patra
Views
Replies
Total Likes
@pradeepdubey82 Usually a NSME error occurs when a method exists during compiletime but not during runtime. So it means that whatever POI library your app is trying to use, it trying during compile time. Maybe is not using the one from your Maven config. Or maybe you can check the POI version from your pom file and see if that version actually contains the newDocumentBuilder() method.
Views
Replies
Total Likes
Hi @pradeepdubey82,
the following POI dependencies are working fine for me on AEMaaCS:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
I am using the following method to create a Workbook:
private Workbook createWorkbook(final InputStream data) {
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(data);
} catch (final IOException e) {
log.error("Problem while creating Workbook object form .xlsx file!", e);
} catch (final EmptyFileException e) {
log.error("Excel file is missing or does not contain any data!", e);
}
return workbook;
}
Good luck,
Daniel
Views
Replies
Total Likes
I have tried below 3 ways to create workbook and obtain sheet
//OPCPackage pkg = OPCPackage.open(new File("/Users/pradeepdubey/students.xlsx"));
//XSSFWorkbook wb1 = new XSSFWorkbook(pkg);
FileInputStream file = new FileInputStream("/Users/pradeepdubey/students.xlsx");
XSSFWorkbook wb1 = new XSSFWorkbook(file);
XSSFSheet sheet = wb1.getSheetAt(0);
//XSSFWorkbook wb1 = new XSSFWorkbook(new File("/Users/pradeepdubey/students.xlsx"));
//XSSFSheet sheet = wb1.getSheetAt(0);
None of those are working, I am using AEM6.5 uber jar version 6.5.5
Added below dependencies in pom file, no luck.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
Views
Replies
Total Likes
Here we have the solution, uber jar 6.5.5 in using apache poi 4.0.1 version internally. So we need to include same version of poi-ooxml dependency
Only this dependency is suffice
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
It worked finally in local standalone class and AEM container as well
Thanks to all the contributors.
Regards,
Pradeep