Reading excel using apache poi in maven project

[pradeepdubey82]

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

[Tethich]

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-depende... . Maybe this gives you a lead.

[pradeepdubey82]

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]

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

[Tethich]

@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.

[daniel-strmecki]

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

[pradeepdubey82]

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>

[pradeepdubey82 · Accepted solution]

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