Reading excel using apache poi in maven project | Community
Skip to main content
P
pradeepdubey82
Level 5
November 7, 2024
Solved

Reading excel using apache poi in maven project

  • November 7, 2024
  • 2 replies
  • 2516 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by pradeepdubey82

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

2 replies

Tethich
Community Advisor
TethichCommunity Advisor
Community Advisor
November 7, 2024

Hi @pradeepdubey82 

 

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-dependency/m-p/713501#M176005 . Maybe this gives you a lead.

P
pradeepdubey82Author
Level 5
November 7, 2024

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.

 

anupampat
Community Advisor
anupampatCommunity Advisor
Community Advisor
November 7, 2024

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

daniel-strmecki
Community Advisor and Adobe Champion
daniel-strmeckiCommunity Advisor and Adobe Champion
Community Advisor and Adobe Champion
November 7, 2024

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

P
pradeepdubey82Author
Level 5
November 7, 2024

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>

 

 

 

P
pradeepdubey82AuthorAccepted solution
Level 5
November 7, 2024

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

    Terms & ConditionsAccessibility statement

    Loading footer...