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

[Anonymous]

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)

[Anonymous]

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.

[Anonymous]

Thank you Shong!

[Anonymous]

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.

[Anonymous]

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!

[Anonymous]

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)

[Anonymous · Accepted solution]

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