Adding user defined header row in the Excel file

[RSA1598789664]

Hello Everyone,

Could some one please help me on the below requirement.

I have set of Excel files and each Excel files has 2 sheets with in it.

One of the Excel file name : Europe_talend 

Sheets of Europe_talend.xlsx as below 

Requirement

I want to create a single .xlsx file with below structure

Tab Name = XSLXname_TabName

 The first row of the Excel should have Heading: Data for XLSXName - TabName

 The data should follow the heading

i want to insert header row as "filename-sheetname" (highlighted as yellow in below snapshot) in everysheet of an Excel feel.

Expected output file :

Regards,

Anil Rawat

[Anonymous]

Hi

Is the sheet name changed in each Exce file? If so, you can try to use a tFileList to iterate each excel file, set the sheet name using position on tFileInputExcel. There is a global variable that stores the current sheet name,

(String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET")

this global variable can be used as the header value.

Regards

Shong

[RSA1598789664]

Hi Shong,

Thanks for replying on my post !

I have already used tFileList to iterate each excel file. and set the sheet name using position on tFileInputExcel.

My question is - how can i insert a header row in the tFileoutputExcel as fFileOutputExcel has checkbox option to include header and does not allow to use global variable which can be used as header value.

Below is the job flow for my requirement.

Regards,

Anil

[RSA1598789664]

Hi Shong,

I have added global variable current sheet name as one of the global variable in tFixedFlowInput

and got the result like this

but i want Europe_talend-Paris as my first row and the data of the excel sheet should follow the heading as below

Could you please share your thoughts on the same?

Regards,

Anil

[RSA1598789664]

@Shicong Hong​ . Could you please share your expert advice on the same ?

[Anonymous · Accepted solution]

Add separately headers to the Excel files before writing data to it. eg:

tFixedFlowInput--main--tFileOutputExcel1 (write Europe_talend-Paris )

|onsubjobok

tFixedFlowInput--main--tFileOutputExcel1 (append existing file, write Talend_ID, Talend_Location, Servers)

|onsubjobok

write data to existing file.