tFileExcelWorkbookSave - workbook cannot be null! error

Nine Stars

tFileExcelWorkbookSave - workbook cannot be null! error

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)
Nine Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

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.
Seventeen Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

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.
Nine Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

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
Seventeen Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

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);
}
Nine Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

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?
Nine Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

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

Thanks
Nine Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

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?
One Star

Re: tFileExcelWorkbookSave - workbook cannot be null! error

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
Seventeen Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

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?
One Star

Re: tFileExcelWorkbookSave - workbook cannot be null! error

Dear jlolling, can u keep an option in the tfileExcelWorkbooksave component, like a small check box - close workbook, which will actually save and close workbook. If the check box is not selected, the workbook should only get saved not closed and should be usable in the later point of job.
Regards
Abhi
Nine Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

Abhi, I like your idea!
Seventeen Stars

Re: tFileExcelWorkbookSave - workbook cannot be null! error

Hi, yes it would be great. I will check that. My problem was in the past the write method from the POI API disrupts the in memory structure of the workbook and so I decided to make it unusable after saving. I will check if this problem is gone in the latest API release. To be honest I have not done this test for a year.
I will come back here next days.
Cheers