[resolved] tFileExcelSheetOutput - how to preserve format of template xlsx?

[Anonymous]

Using the terrific  tFileExcelSheetOutput component, everything works fine as documented.
Except:   tFileExcelSheetOutput does not keep the cell formats of the template xlsx-file, that I open with  tFileExcelWorkbookOpen
Cells are overwritten with none of the original formatting retained. Any ideas, what I can do to preserve the format of the template?
I use
TOS_ESB-win32-x86.exe
Excel 2013
Windows 8.1
Here's a screenshot of my tFileExcelSheetOutput settings:


Many thanks in advance for any hint!
Markus

[Anonymous]

I will check this. Actually in my tests the cell styles are preserved. Could you provide a test job with a test excel file, so I can better reproduce your issue.
You can send me your example to jan.lolling@gmail.com
Thanks and Best regards
Jan

[Anonymous]

I have taken a look into the component and you are right, the existing style will be overwritten.
I can remember why I have decided to overwrite the existing style. Every cell has a style - at least a default style. When I try to update this style with the necessary information it can affect all other cells to because Excel reuse styles as much as it can.
You could activate the option reuse style from the first written row but this does not help if you have individual styles in different rows.
I will check if I can detect a style as an individual style and preserve this style.

[Anonymous]

Hi Jan,
many thanks for your response! After some reasearch I discovered, that preserving formats indeed works as intended.
It was my mistake writing data with wrong data formats, such as numbers as strings, that prevented the correct format to be applied.

Markus

[_AnonymousUser]

Hi Mark,
I tried using the option ,'reuse style from the first written row' but this didn't work for me.
Did the option work fine for you?

I am trying to apply only red color for values > than some range.
Please help on this.


Thanks,
Jayanta

[Anonymous]

I suggest you create an template with styles for the first (or also possible) second row where you start writing. These styles will be reused with the option "Reuse styles from the first written row".
@Jayanta: could you send me your template excel file. I would like to see what wrong with it.
jan.lolling@gmail.com

[Anonymous · Accepted solution]

Jayanta,
check if the Talend data formats of the data, that you want to write are aligned with the data formats of the first row of the Excel sheet, whose formats you want to copy. When I aligned data formats correctly, both reusing the first row format and keeping the current format worked for me.
Start your test with a very simple record format with e.g. only string values.
Markus

[sum1]

Hi Jan,

I am trying to use write into excel template which has styling.

Upto 213 records its writing in existing style but after that its is not writing in format.

Styles in template are not applied to all written rows of output excel.(.xlsx) i am using.

i have downloaded all new excel components.Can you let me know how can i preserve the format of template for any number of output records.

.

[crtatnall]

Jan,

Thank you very much for sharing your tExcel* components! They've been very useful to me over the years.

I am currently having this same issue retaining formatting (Talend Open ESB 7.3.1 , JDK 1.8.0_251 , Windows 10 64-bit) --- and wondering if I could send you my job & workbooks to take a quick peek?