One Star

Apply background color in tFileOutputExcel

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

Re: Apply background color in tFileOutputExcel

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
--
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: Apply background color in tFileOutputExcel

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.

 
Seventeen Stars

Re: Apply background color in tFileOutputExcel

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

Re: Apply background color in tFileOutputExcel

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 ?
Seventeen Stars

Re: Apply background color in tFileOutputExcel

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

Re: Apply background color in tFileOutputExcel

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.
Seventeen Stars

Re: Apply background color in tFileOutputExcel

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.