topic [resolved] Can the tFileExcel components open/write to macro enabled workbooks? in Talend Studio

[resolved] Can the tFileExcel components open/write to macro enabled workbooks?

Anonymous — Tue, 13 Nov 2012 20:47:59 GMT

I get errors when I try to open/save an .xlsm macro enabled work book. I'm needing a macro to run when the book opens to update all tables/formulas with the latest data.
Do the tFileExcel components not work with .xlsm books? Am I missing something?
This is the error I get when I try to open my macro-enabled workbook:
Exception in component tFileExcelWorkbookOpen_1
java.lang.IllegalStateException: workbook must be initialized!
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetFile.setupDataFormatStyle(SpreadsheetFile.java:234)
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetFile.initializeWorkbook(SpreadsheetFile.java:174)
at test.lcr_last_month_team_stats_0_1.LCR_Last_Month_Team_Stats.tFileExcelWorkbookOpen_1Process(LCR_Last_Month_Team_Stats.java:1192)
at test.lcr_last_month_team_stats_0_1.LCR_Last_Month_Team_Stats.runJobInTOS(LCR_Last_Month_Team_Stats.java:7316)
at test.lcr_last_month_team_stats_0_1.LCR_Last_Month_Team_Stats.main(LCR_Last_Month_Team_Stats.java:7157)
Exception in component tFileExcelWorkbookSave_3
java.lang.IllegalArgumentException: workbook cannot be null!
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetFile.setWorkbook(SpreadsheetFile.java:179)
at test.lcr_last_month_team_stats_0_1.LCR_Last_Month_Team_Stats.tFileExcelWorkbookSave_3Process(LCR_Last_Month_Team_Stats.java:6833)
at test.lcr_last_month_team_stats_0_1.LCR_Last_Month_Team_Stats.runJobInTOS(LCR_Last_Month_Team_Stats.java:7328)

Re: [resolved] Can the tFileExcel components open/write to macro enabled workbooks?

Anonymous — Wed, 14 Nov 2012 03:36:02 GMT

Hi
You are using a custom component shared by jlolling on Talend Exchange. I am going to notify this topic to the author of the component, hope he will come back to you.

Re: [resolved] Can the tFileExcel components open/write to macro enabled workbooks?

Anonymous — Wed, 14 Nov 2012 03:48:03 GMT

Thank you Shong!

Re: [resolved] Can the tFileExcel components open/write to macro enabled workbooks?

Anonymous — Wed, 14 Nov 2012 08:12:08 GMT

There is a missing feature in this component. Unfortunately xlsm is currently not supported but it works with a workaround (rename file extension to .xlsx). I work on it and as soon as my tests will succeed I will provide a new release.
The bug in this issue is the misleading error message and handling of unsupported file types.
But there is another problem which I am unable to solve. The current POI library (and all others I know or Talend uses) does not provide the run of macros (but macros will survive the processing). As fare as I know only Excel or OpenOffice/LibreOffice are able to run macros.

Re: [resolved] Can the tFileExcel components open/write to macro enabled workbooks?

Anonymous — Sat, 17 Nov 2012 15:09:57 GMT

Many thanks to you jlolling! Your last update corrected the issues I was having. I also realized, after you suggested removing the components completely and re-installing them that I had originally had to copy the POI jar dependencies to the talend/lib folder for talend to see them. Once I replaced the older dependencies with the newest, the other errors I was seeing cleared also.
Your component has solved a several months problem we've had with generating automated excel reports. Thank you for your contribution to the community!

Re: [resolved] Can the tFileExcel components open/write to macro enabled workbooks?

Anonymous — Thu, 01 Sep 2016 15:17:28 GMT

I get errors when I try to job
This is the error :
Exception in component tFileExcelWorkbookOpen_1
org.apache.poi.EncryptedDocumentException: Export Restrictions in place - please install JCE Unlimited Strength Jurisdiction Policy files
at org.apache.poi.poifs.crypt.CryptoFunctions.getCipher(CryptoFunctions.java:208)
at org.apache.poi.poifs.crypt.CryptoFunctions.getCipher(CryptoFunctions.java:182)
at org.apache.poi.poifs.crypt.agile.AgileDecryptor.hashInput(AgileDecryptor.java:269)
at org.apache.poi.poifs.crypt.agile.AgileDecryptor.verifyPassword(AgileDecryptor.java:116)
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetFile.initializeWorkbook(SpreadsheetFile.java:341)
at reporting.copy_of_releve_journalier_masterjob_1_0.Copy_of_releve_journalier_masterjob.tFileExcelWorkbookOpen_1Process(Copy_of_releve_journalier_masterjob.java:1328)
at reporting.copy_of_releve_journalier_masterjob_1_0.Copy_of_releve_journalier_masterjob.tFileList_1Process(Copy_of_releve_journalier_masterjob.java:1654)
at reporting.copy_of_releve_journalier_masterjob_1_0.Copy_of_releve_journalier_masterjob.runJobInTOS(Copy_of_releve_journalier_masterjob.java:2065)
at reporting.copy_of_releve_journalier_masterjob_1_0.Copy_of_releve_journalier_masterjob.main(Copy_of_releve_journalier_masterjob.java:1773)

Re: [resolved] Can the tFileExcel components open/write to macro enabled workbooks?

Anonymous — Fri, 09 Sep 2016 14:41:03 GMT

a-elghazi - you might need to install the JCE. You can download it from Oracle..

http://www.oracle.com/technetwork/java/javase/downloads/jce-7-download-432124.html