creating multiple sheet in tFileOutputExcel

[Anonymous]

i am using talend openstudio 6.3.  i want to read the employee,department data from oracle data base and data has to be populated into single excel file with 2 sheets ,one sheet for employee and second sheet for department.

what is the use of "Is absolute Y pos" and  append existing file/ append existing sheet option 

[TRF · Accepted solution]

Hi,

"Is absolute Y pos" define the row (from 0 to n) you want the rows to be written.

"Append existing file / Append existing sheet" options mean you want to write at the end of the file / sheet if it exists.

Carefull, if you try to push data to the same xlsx file to 2 different sheets in the same subjob, you'll lost the 1st one.

Here is a working example:

and one of the tFileOutputExec (both are same except the sheet name):

Hope this helps.

[Anonymous]

is there any alternative way to push data to the same xlsx file to 2 different sheets in the same subjob with out loosing data in any sheet? is this talend restriction?

[TRF]

@raffeemd, you can refer to the following post as a starting point

https://community.talend.com/t5/Design-and-Development/Create-Multiple-Tabs-with-single-excel-file-b...

I'll try to give a complete example as soon as possible.

[TRF]

Hi,

As expected, @shong answer is the good one to write into multiple sheets without having to use multiple subjob.

This solution is particulary usefull when number of sheet to be created is not known when the job start.

Here is what the whole job looks like:

tFixedFlowInput is here for the example.

It replace the normal input which can be a file, a database, and so on.

tFlowToIterate convert main flow to iterate flow and input fields into global variables:

tFixedFlowInput generate a new data flow for each iteration with only one row at a time, allowing to proceed with multiple sheets in the same subjob (and with a number of sheets which is not predefined).

The schema is the same (in this example) as it was for tFixedFlowInput and component is configured as it:

Finally, in tFileOutputExcel, you just have to check "Append existing file" and "Append existing sheet" use the global variables which contains the aggregate value as a sheetname (here the Flag variable):

Here is the result:

Hope this helps.

[Anonymous]

hello, I need every output to be a sheet . how can I do this ?

when I do it now it just erase the first 3 and contains the last one.

Thanks!

[Anonymous]

Are you using the "append" options?

[TRF]

I think (to be confirmed) you can't have more than 1 tFileOutputExcel for the same file in the same subjob or only the last writter will win.

In this thread I give a solution you can follow if each sheet has a specific schema.

[Anonymous]

Ah yes, that is right @TRF. Very much like you cannot several versions of an Excel file open to edit at the same time, you cannot do it in Talend either. Better to write to a tHash and then write the results to the Excel file in subsequent sub jobs.

[Anonymous]

each sheet has a specific schema but when I write to 4 fileoutput in a row,each one override the other and It just remain a single sheet, although I wrote a diffrenet sheet to every fileoutput.