topic Re: [resolved] Problem with tFileExcelSheetOutput in Talend Studio

[resolved] Problem with tFileExcelSheetOutput

Anonymous — Wed, 24 Sep 2014 10:12:13 GMT

Hi
I'm using these componentes (tFileExcelWorkbook) to generate medium-heavy excel files because the standard FileOutputExcel from Talend always crash by memory issues.
With tFileExcelWoorkbook I have two problems:
1. I have a template which I copy every time I want to generate the report. This template has a little header with a summary of the content (count of rows and total of a numeric colum). Under this data there are the column headers.
| TOTAL ROWS | TOTAL $ |
| 549.660 | 400,000,00 |
|
| COLUMN 1 | COLUMN 2 | COLUMN 3 |
| ###### |    ###### |    ###### |
| ###### |    ###### |    ###### |
| ###### |    ###### |    ###### |
I want to fill the two top cells (Total Rows and Total $). However, when i try to do it I configure tFileExcelSeetOutput with Row start index (starts with 1) = 2 (to fill the second row's cells). But appears this error:
Exception in component tFileExcelSheetOutput_2
java.lang.IllegalArgumentException: Attempting to write a row in the range that is already written to disk.
On the template these cells are empty and there is not any value, what can I do with it?

2. I want to generate an excel with 75.000 rows aprox (xlsx - 2007) and 27 columns (two of them have a lot of text). The job begins ok but suddenly an error stop the process: JAVA HEAP MEMORY. I've modify the configuration file Talend-Studio-win32-x86.ini to change the params Xms and Xmx but if I modify it with any value, Talend doesn't launch and appears a JVM error. What can we do? I'm using a 32bits machine and this configuration:
-vmargs
-Xms64m
-Xmx1000m
-XX:MaxPermSize=512m
-Dfile.encoding=UTF-8

Cheers and thanks.

Re: [resolved] Problem with tFileExcelSheetOutput

Anonymous — Wed, 24 Sep 2014 10:19:35 GMT

Hi,
tFileExcelWorkbook is a custom component shared by jlolling on Talend Exchange. You'd better contact to the author jlolling directly.
Best regards
Sabrina

Re: [resolved] Problem with tFileExcelSheetOutput

Anonymous — Wed, 24 Sep 2014 10:22:09 GMT

Ok ok , thanks!!

Re: [resolved] Problem with tFileExcelSheetOutput

Anonymous — Wed, 24 Sep 2014 11:30:57 GMT

It looks like your job writes the file before the actual output has been finished. This can happen because of a misused trigger (OnComponentOk at the output component is a common error).
You could also write the header information with a different component e.g. tFileExcelNamedCellOutput or tFileExcelReferencedCellOutput and avoid "confusing" the tFileExcelSheetOutput component.
Named cells would have the advantage you can locate them where you want and they will be always found.

Re: [resolved] Problem with tFileExcelSheetOutput

Anonymous — Wed, 24 Sep 2014 12:10:13 GMT

Hi Jan
Thanks, I'm going to try your idea to use tFileExcelNamedCellOutput or tFileExcelReferencedCellOutput

Thanks!

Re: [resolved] Problem with tFileExcelSheetOutput

Anonymous — Wed, 24 Sep 2014 12:22:54 GMT

jLolling, what is the cell name?? for example "A2"?? for second row and second col?

Re: [resolved] Problem with tFileExcelSheetOutput

Anonymous — Thu, 25 Sep 2014 10:50:06 GMT

You can set a name for a cell in Excel in the left upper input field where you will read per default the current address (like A1) .
A named cell keep it name unique for the whole Excel document, therefore the component tFileExcelNamedCellOutput does not need to know the sheet (index or name).
The problem above is caused by the Memory saving mode and its behaviour to allow writing cells only in the natural order and you cannot write a cell before a cell what is already written. This restriction is caused by the design of the Streaming Workbook (this is what the memory Saving Mode uses) which does not keep all cells/rows in the memory (but in a separate temporary file).

Re: [resolved] Problem with tFileExcelSheetOutput

Anonymous — Thu, 25 Sep 2014 11:59:09 GMT

jlolling thanks you very much for all your interest, patience and time
I'm checking other ways to get it using your custom components (very useful for any purpose).
Thanks
Cheers

Re: [resolved] Problem with tFileExcelSheetOutput

Rajender — Sun, 08 May 2022 09:27:05 GMT

Hi there,

I have a Excel report of size 16 MB which has multiple sheets. I have to update one sheet in Excel. This sheet has information and header set upto 14 rows.

Following is my job desing.

tfileExcelWorkbookOpen -->> tFileDelimitted -->> tMap -->> TFileExcelSheetOutput -->. tFileExcelWorkbookSave

In my Job advance set up, Java memory is set upto 8GB.

While running the job, I am getting OutOfMemory exception.

Please suggest, Is there any way to handle this.

Best Regards,

Rajender Prasad

Re: [resolved] Problem with tFileExcelSheetOutput

Anonymous — Mon, 09 May 2022 09:27:54 GMT

Hello,

Please have a look at this KB article about:

https://community.talend.com/s/article/OutOfMemory-Exception-WmtmQ

Feel free to let us know if it is helpful for your use case.

Best regards

Sabrina

Re: [resolved] Problem with tFileExcelSheetOutput

Anonymous — Mon, 09 May 2022 09:32:14 GMT

14 rows is nothing. I write much much more rows in my job. Could you please export your job design (Right click on the job and choose Export Item) and also an excel template if some is in usage and send it to me? I would like to see what went wrong here.

use my email address: jan.lolling@gmail.com

Re: [resolved] Problem with tFileExcelSheetOutput

Balakrishna — Thu, 10 Aug 2023 15:35:37 GMT

Hi Everyone,

Can someone please share or suggest where to download the component - tFileExcelSheetOutput

Thank you.