One Star

[resolved] Problem with tFileExcelSheetOutput

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Seventeen Stars

Re: [resolved] Problem with tFileExcelSheetOutput

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

Re: [resolved] Problem with tFileExcelSheetOutput

Hi,
tFileExcelWorkbook is a custom component shared by jlolling on Talend Exchange. You'd better contact to the author jlolling directly.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Problem with tFileExcelSheetOutput

Ok ok , thanks!!
Seventeen Stars

Re: [resolved] Problem with tFileExcelSheetOutput

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

Re: [resolved] Problem with tFileExcelSheetOutput

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

Thanks!
One Star

Re: [resolved] Problem with tFileExcelSheetOutput

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

Re: [resolved] Problem with tFileExcelSheetOutput

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

Re: [resolved] Problem with tFileExcelSheetOutput

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