topic Re: Apply background color in tFileOutputExcel in Talend Studio

Apply background color in tFileOutputExcel

Anonymous — Wed, 23 Sep 2015 15:39:08 GMT

hi,
I begin a new job, and i work now in some jobs talend.
I have a question : Is it possible to apply a background (color) in cell if some condition are true ?
I saw Tjava and TjavaRow component, and i saw we could use jxl.jar or anything else. But i really dont know java code, and i really dont know if i can do that.
Why ? my file excel need to apply some format when the job is over, and i apply manually some format. It very long, because my file is very heavy (more 100 000 lines)
So i would like to apply those formats automatically.
I though to use Macro Excel, but it's not a good solution ...
Any idea...?

Re: Apply background color in tFileOutputExcel

Anonymous — Thu, 24 Sep 2015 07:44:28 GMT

Hi sidewolf,
You requirement is not very clear for us. Do you want to mark your input/output data with background (color) by using Talend?
Best regards
Sabrina

Re: Apply background color in tFileOutputExcel

Anonymous — Thu, 24 Sep 2015 09:02:39 GMT

Hi sidewolf,
You requirement is not very clear for us. Do you want to mark your input/output data with background (color) by using Talend?
Best regards
Sabrina

Finaly i was clear lol ... yes i would like to mark your input/output data with background (color) by using Talend. How ? By using Tjava or TjavaRow with jxl.jar library.

Why ? Because my outputfile (excel file) require some filters. For example.
If Columns T Value = 1 then BackgroundColor columns B = Yellow

other example :

If Col_A.Value = NOK then delete lines

I think i could do a Macro VBA , except that my Talend job is running everyday and my file is overwrite everyday, so that solution is not possible.

So i would like to know if it's possible by using a job component like Tjava or TjavaRow or anything else.

Re: Apply background color in tFileOutputExcel

Anonymous — Thu, 24 Sep 2015 13:32:44 GMT

The build-in excel components are not able to handle styles.
The only way to apply styles (like conditional formatting) is using the component suite tFileExcel*
You need 3 components:
tFileExcelWorkbookOpen to open an existing file (containing your styles and formats) or create an new empty workbook
tFileExcelSheetOutput to create/write a sheet (fully documented, refer the resources to the component)
tFileExcelWorkbookSave to save the read or created workbook as new file.
Visit Talend Exchange and search for excel

Re: Apply background color in tFileOutputExcel

Anonymous — Thu, 24 Sep 2015 15:13:31 GMT

The build-in excel components are not able to handle styles.
The only way to apply styles (like conditional formatting) is using the component suite tFileExcel*
You need 3 components:
tFileExcelWorkbookOpen to open an existing file (containing your styles and formats) or create an new empty workbook
tFileExcelSheetOutput to create/write a sheet (fully documented, refer the resources to the component)
tFileExcelWorkbookSave to save the read or created workbook as new file.
Visit Talend Exchange and search for excel

I guest thoses 3 components are not available in Talend Open Studio for DI ?

Re: Apply background color in tFileOutputExcel

Anonymous — Thu, 24 Sep 2015 16:41:55 GMT

They are not out-of-the-box in the studio but Talend Exchange is supposed to provide such extensions.
Follow these instructions:
https://help.talend.com/pages/viewpage.action?pageId=190513200

Re: Apply background color in tFileOutputExcel

Anonymous — Fri, 25 Sep 2015 11:47:36 GMT

They are not out-of-the-box in the studio but Talend Exchange is supposed to provide such extensions.
Follow these instructions:

Ok i downloaded those components, and i succeeded to deploy them in TOS. However, i don't know how to use them to do what i want. Meaning, to color some cells if some condition are true.

Re: Apply background color in tFileOutputExcel

Anonymous — Fri, 25 Sep 2015 16:59:28 GMT

Yes the component it self cannot set styles yet. But the usually used way is to define conditional styles. If you build a excel file which contains in the first row what you are going to (re)write it uses these styles and can also extend them in case the styles affect a range.

Re: Apply background color in tFileOutputExcel

Anonymous — Thu, 24 Oct 2019 09:26:11 GMT

Hi team,

The below given link is not working. I want to format the output excel as shown in screenshot

i.e header backgroud, header text bold, data text in skyblue color etc

Please let me know how to achive it

Re: Apply background color in tFileOutputExcel

Anonymous — Thu, 24 Oct 2019 09:27:43 GMT

PFA is the screenshot

Style.PNG

Re: Apply background color in tFileOutputExcel

Anonymous — Tue, 29 Oct 2019 09:49:04 GMT

Hello @ashwindhiware

It 's a very old thread. Could you please give us a more bit information about your current requirement and description?

Best regards

Sabrina

Re: Apply background color in tFileOutputExcel

Ganshyam — Mon, 17 Aug 2020 10:18:36 GMT

Hi Jlolling,

I am using the above 3 components to generate conditional formatting in excel template. The data is getting populated from input file to template but don't have the data with conditional formatting in the excel template.For integer records in input file it is working fine. But the input format has data in % and want to conditional format this % values in conditional formatting in template.

Attached are the screenshot of the job and the component setting.

Let me know if i have missed out some configuration to talend component.

Regards

Ganshyam