[resolved] Problem with tFileExcelSheetOutput

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

Ok ok , thanks!!

[Anonymous]

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.

[Anonymous]

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

Thanks!

[Anonymous]

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

[Anonymous · Accepted solution]

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).  

[Anonymous]

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

[Rajender]

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

[Anonymous]

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