topic Re: tFileExcelWorkbookSave - workbook cannot be null! error in Talend Studio

tFileExcelWorkbookSave - workbook cannot be null! error

talendtester — Thu, 17 Apr 2014 19:45:51 GMT

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)

Re: tFileExcelWorkbookSave - workbook cannot be null! error

talendtester — Thu, 17 Apr 2014 22:56:36 GMT

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.

Re: tFileExcelWorkbookSave - workbook cannot be null! error

Anonymous — Fri, 18 Apr 2014 23:33:54 GMT

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.

Re: tFileExcelWorkbookSave - workbook cannot be null! error

talendtester — Tue, 22 Apr 2014 18:59:44 GMT

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

Re: tFileExcelWorkbookSave - workbook cannot be null! error

Anonymous — Tue, 22 Apr 2014 23:19:18 GMT

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

Re: tFileExcelWorkbookSave - workbook cannot be null! error

talendtester — Wed, 30 Apr 2014 22:41:59 GMT

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?

Re: tFileExcelWorkbookSave - workbook cannot be null! error

talendtester — Mon, 12 May 2014 18:14:09 GMT

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

Thanks

Re: tFileExcelWorkbookSave - workbook cannot be null! error

talendtester — Tue, 03 Jun 2014 20:43:44 GMT

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?

Re: tFileExcelWorkbookSave - workbook cannot be null! error

Anonymous — Wed, 11 Jun 2014 17:36:44 GMT

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

Re: tFileExcelWorkbookSave - workbook cannot be null! error

Anonymous — Wed, 11 Jun 2014 21:23:52 GMT

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?

Re: tFileExcelWorkbookSave - workbook cannot be null! error

_AnonymousUser — Mon, 09 May 2016 22:59:59 GMT

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

Re: tFileExcelWorkbookSave - workbook cannot be null! error

talendtester — Mon, 09 May 2016 23:01:45 GMT

Abhi, I like your idea!

Re: tFileExcelWorkbookSave - workbook cannot be null! error

Anonymous — Wed, 11 May 2016 06:27:27 GMT

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

Re: tFileExcelWorkbookSave - workbook cannot be null! error

hgromek — Wed, 19 Jun 2019 21:21:24 GMT

Hi! I am currently experiencing this error, do you have any advice on how to fix?