tFileExcelWorkbookSave - workbook cannot be null! error

[talendtester]

Is it not possible to load items from a file to a context variable and pass the variable to custom component tFileExcelWorkbookSave?
It works for the first pass, but then on the second row of the file I get:
java.lang.IllegalArgumentException: workbook cannot be null!
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetFile.setWorkbook(SpreadsheetFile.java:278)

[talendtester]

jlolling,
Is it a requirement that a tFileExcelWorkbookOpen must be right before the tFileExcelWorkbookSave?
I have a tFileExcelWorkbookOpen at the start of my job and do a bunch of stuff before the tFileExcelWorkbookSave, but it always fails the SECOND pass if I don't have the tFileExcelWorkbookOpen must be right before the tFileExcelWorkbookSave.

[Anonymous]

Can you show me your job design?
After saving the workbook you cannot use it anymore. I can change the design in a way the workbook is usable after saving. Could you describe your use case to give me an idea why do you need that.

[talendtester]

jlolling,
I get a daily Excel workbook with 70 worksheets, I need to remove about half of them from the file. I would like to be able to process multiple days' files to catch up if I don't run the job for a few days.
To improve the speed of the job, can the connection to the Excel file in tFileExcelWorkbookOpen and tFileExcelWorkbookSave stay open for the entire length of the job?
I would like to be able to get a list of workbook names from tFileList to iterate through the list.
Use the tFileExcelWorkbookOpen to open the current workbook
Use the tFileExcelSheetList to get the list of worksheets in each workbook, pass to tMap and tFilterRow to get only the worksheets I want to remove.
Pass the name of the worksheet to remove to tContextLoad then tFlowToIterate
Use tFileExcelWorkbookSave to delete the current worksheet.
Desired Job design:
tFileList iterate tFileExcelWorkbookOpen iterate tFileExcelSheetList > tMap > tFilterRow filter tContextLoad > tFlowToIterate iterate tFileExcelWorkbookSave

[Anonymous]

OK I got it.
We need a component to manage sheet: delete, rename.
We need a workbook which can stay useable even when the file is saved. This way you could manipulate the file, save it, manipulate, save and so on...
I will fill theses gaps in the next 2 week (I hope ;-)
Here an idea with a tJavaRow component (assuming you have your sheet name to delete in the schema column sheet_name_to_delete. Probably change the component index for the tFileExcelWorkbookOpen.

org.apache.poi.ss.usermodel.Workbook workbook = (org.apache.poi.ss.usermodel.Workbook) globalMap.get("workbook_tFileExcelWorkbookOpen_1");
int sheetIndex = workbook.getSheetIndex(input_row.sheet_name_to_delete);
if (sheetIndex >= 0) {
    workbook.removeSheetAt(sheetIndex);
}

[talendtester]

I have been trying all sorts of ways using the code in tJavaRow but I get Exception in component tJavaRow_1 java.lang.NullPointerException or index out of range when I have tFileExcelWorkbookOpen and tFileExcelWorkbookSave after the tJavaRow.
Do I need to save the workbook in the tJavaRow?
Is there a way to delete multiple sheets at a single time like sheet index 0 .. 34?

[talendtester]

Hi jlolling,
Do you think an updated version of tFileExcelWorkbookSave will be available soon?

Thanks

[talendtester]

Hi jlolling,
I downloaded and installed the May 2014 version of tFileExcelWorkbookSave, but I am still getting the "workbook cannot be null!" error, has the updates been made?

[Anonymous]

talendster,
I came here after having the exact same issue, I had a workaround where I moved all the subjobs to another and then run those as child jobs, it is working fine so far for me,
Very handy component by the way jlolling

[Anonymous]

Then reason because I have decided to make the workbook unusable after saving are caused by some problems within the POI API it self. At this time I experienced some strange problems with corrupted documents after changing an already saved document. I will test if the issue is now solved and will enable the change of the workbook after it is written.
To be absolute sure nobody change the workbook after write it I remove it from memory -> this causes your NullPointerException. I should catch this state and create a better understandable error message.
@talendtester could you provide a screenshot of your job?