Exception on tFileInputExcel and tFileOutput Excel with excel2007 file format (xlsx)

[Anonymous]

Hi everbody,

I'm using Talend Open Studio for Big Data 7.3.1.20200219_1130. When I try to read or write Excel Files in the 2007 version (xlsx) I get the following exceptions:

Input

Exception in component tFileInputExcel_1 (Report_BU5000_aufbereiten)

org.apache.poi.ooxml.POIXMLException: org.apache.xmlbeans.XmlOptions.setSaveAggressiveNamespaces()Lorg/apache/xmlbeans/XmlOptions;

at org.apache.poi.ooxml.POIXMLFactory.createDocumentPart(POIXMLFactory.java:66)

at org.apache.poi.ooxml.POIXMLDocumentPart.read(POIXMLDocumentPart.java:657)

at org.apache.poi.ooxml.POIXMLDocument.load(POIXMLDocument.java:180)

at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:282)

at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:88)

at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:116)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:339)

at org.apache.poi.ss.usermodel.WorkbookFactory.createXSSFWorkbook(WorkbookFactory.java:314)

at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:302)

at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:271)

at local_project.report_bu5000_aufbereiten_0_1.Report_BU5000_aufbereiten.tFileInputExcel_1Process(Report_BU5000_aufbereiten.java:1280)

at local_project.report_bu5000_aufbereiten_0_1.Report_BU5000_aufbereiten.runJobInTOS(Report_BU5000_aufbereiten.java:2541)

at local_project.report_bu5000_aufbereiten_0_1.Report_BU5000_aufbereiten.main(Report_BU5000_aufbereiten.java:2390)

Caused by: java.lang.reflect.InvocationTargetException

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

at java.lang.reflect.Constructor.newInstance(Unknown Source)

at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:56)

at org.apache.poi.ooxml.POIXMLFactory.createDocumentPart(POIXMLFactory.java:63)

... 16 more

Caused by: java.lang.NoSuchMethodError: org.apache.xmlbeans.XmlOptions.setSaveAggressiveNamespaces()Lorg/apache/xmlbeans/XmlOptions;

at org.apache.poi.ooxml.POIXMLTypeLoader.<clinit>(POIXMLTypeLoader.java:41)

at org.apache.poi.xssf.model.ThemesTable.<init>(ThemesTable.java:86)

... 22 more

Output

Exception in thread "main" java.lang.NoSuchMethodError: org.apache.xmlbeans.XmlBeans.typeSystemForClassLoader(Ljava/lang/ClassLoader;Ljava/lang/StringLorg/apache/xmlbeans/SchemaTypeSystem;

at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook.<clinit>(Unknown Source)

at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory.newInstance(Unknown Source)

at org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:456)

at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:259)

at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:253)

at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:241)

at org.apache.poi.xssf.streaming.SXSSFWorkbook.<init>(SXSSFWorkbook.java:240)

at org.apache.poi.xssf.streaming.SXSSFWorkbook.<init>(SXSSFWorkbook.java:210)

at org.apache.poi.xssf.streaming.SXSSFWorkbook.<init>(SXSSFWorkbook.java:185)

at org.apache.poi.xssf.streaming.SXSSFWorkbook.<init>(SXSSFWorkbook.java:272)

at org.talend.ExcelTool.prepareXlsxFile(ExcelTool.java:125)

at local_project.report_bu5000_aufbereiten_0_1.Report_BU5000_aufbereiten.tFileInputExcel_1Process(Report_BU5000_aufbereiten.java:1004)

at local_project.report_bu5000_aufbereiten_0_1.Report_BU5000_aufbereiten.runJobInTOS(Report_BU5000_aufbereiten.java:2096)

at local_project.report_bu5000_aufbereiten_0_1.Report_BU5000_aufbereiten.main(Report_BU5000_aufbereiten.java:1945)

I installed manually the necessary modules (xmlbeans-3.1.0.jar and poi-4.1.2.jar), but it doesn't fixed my problem. Any idea or help?

Best reagrds

Simon.

[Anonymous · Accepted solution]

Hi
I can not reproduce the issue on Windows with v7.3.1 in my end. It seems these required modules are not installed correctly. To do:
1. Go to <Studio installation directory>\configuration\.m2\repository\ and Search the folder where the jar file is located, eg:
D:\talend\product\731\studio1\Talend-Studio-20200219_1130-V7.3.1\configuration\.m2\repository\org\talend\libraries\poi-4.1.2
2. Delete the folder poi-4.1.2.
3. In studio, open the component settings of tFileOutputExcel and re-install the required jar files again.

Please try and let me if it works.

Regards
Shong

[Anonymous]

Thank you very much for your response.

I couldn't fix it this way. But I installed Talend on another machine and it works. I think the problem was relased to the Java Version on the first machine.

Best regards

Simon.

[Aadhi]

Hi Shong,

I​ am facing a permission issue while reading xlsx file in Talend 7.3 ( when calling a built job in unix server).

The file is getting placed in server with 644 Grant's.

We are actually migrating jobs from Talend 6.5 to Talend 7.3. The same job is running fine when built and executed in 6.5 version but failing in Talend 7.3

When I placed the file manually to the unix box and change permission to 646 ( giving permission of read/write to owner and others) job is getting executed.

I​ am not sure why Talend 7.3 is expecting a extra permission while the same is running fine in lower version.

Please help us on this.

Note : we are facing the issue only with xlsx. Other feed format files are getting processed in 7.3 without error.

Regards,

Aadhi

[ikhalifa1616682901]

I have the same problem. which java version you have installed in the new machine please.

[INESBK]

Hello,

I have this error "org.apache.poi.ss.usermodel.Cell cannot be resolved to a type" in Talend Open Studio for Data Integration 6.3.1 that indicates that the required library or JAR file for Apache POI is not included in myTalend project. How can I add Apache POI JAR files to my Talend project ?

Best regards.