Error while checking excel sheet exists

Seven Stars

Error while checking excel sheet exists

Hi,

 

I am trying to read an Excel file that can have any of the three sheets required to be read.

To do so and not cause an error while reading for a sheet not existing, I am using the tFileExcelWorkbookOpen and tFileExcelSheetList components.

I have designed the job in the following way:FileExcelToCSV.png

The schema defined in the tFileInputExcel and tFileOutputDelimited components is "Dynamic" as I want to create a CSV file of the excel sheet that is read.

 

The file being read is an XLSX file, so for that I have enabled the "Memory saving mode (only for XLSX format, data can only be appended!)" property in the tFileExcelWorkbookOpen component and the "Read excel2007 file format(xlsx)" property in tFileInputExcel component.

 

 

Now when I run the job, I get the following exception:

Exception in thread "main" java.lang.NoSuchMethodError: org.apache.poi.ss.util.SheetUtil.getDefaultCharWidth(Lorg/apache/poi/ss/usermodel/WorkbookSmiley WinkI
at org.apache.poi.xssf.streaming.AutoSizeColumnTracker.<init>(AutoSizeColumnTracker.java:117)
at org.apache.poi.xssf.streaming.SXSSFSheet.<init>(SXSSFSheet.java:78)
at org.apache.poi.xssf.streaming.SXSSFWorkbook.createAndRegisterSXSSFSheet(SXSSFWorkbook.java:624)
at org.apache.poi.xssf.streaming.SXSSFWorkbook.<init>(SXSSFWorkbook.java:230)
at org.apache.poi.xssf.streaming.SXSSFWorkbook.<init>(SXSSFWorkbook.java:185)
at org.apache.poi.xssf.streaming.SXSSFWorkbook.<init>(SXSSFWorkbook.java:160)
at de.jlo.talendcomp.excel.SpreadsheetFile.initializeWorkbook(SpreadsheetFile.java:380)
at etl_dev.exceltocsvtest_0_1.ExcelToCSVTest.tFileExcelWorkbookOpen_1Process(ExcelToCSVTest.java:1324)
at etl_dev.exceltocsvtest_0_1.ExcelToCSVTest.runJobInTOS(ExcelToCSVTest.java:7693)
at etl_dev.exceltocsvtest_0_1.ExcelToCSVTest.main(ExcelToCSVTest.java:7501)

 

Alternately if I disable the "Read excel2007 file format(xlsx)" property in tFileInputExcel component, I get the following error:

[FATAL]: ga_etl_dev.exceltocsvtest_0_1.ExcelToCSVTest - tFileInputExcel_4 Unable to recognize OLE stream
Exception in component tFileInputExcel_4
jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
at jxl.read.biff.File.<init>(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:268)
at etl_dev.exceltocsvtest_0_1.ExcelToCSVTest.tFileExcelWorkbookOpen_1Process(ExcelToCSVTest.java:2148)
at etl_dev.exceltocsvtest_0_1.ExcelToCSVTest.runJobInTOS(ExcelToCSVTest.java:7627)
at etl_dev.exceltocsvtest_0_1.ExcelToCSVTest.main(ExcelToCSVTest.java:7435)

 

Can anyone tell me why I am getting these errors? Is it because of the XLSX file or the Dynamic Schema?

And is there a workaround for the same?

Seven Stars

Re: Error while checking excel sheet exists

I think I have found the issue.

The exception is thrown when the tFileExcelWorkBookOpen and tFileInputExcel components are used with the XLSX option enabled.

I think the HSSF POI JARs used by both the components are different versions and hence cause the issue.

 

Can anything be done for this?