topic Re: [resolved] tFileExcelSheetOutput - how to preserve format of template xlsx? in Talend Studio

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

Anonymous — Sat, 01 Aug 2015 22:40:12 GMT

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

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

Anonymous — Mon, 03 Aug 2015 08:32:05 GMT

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

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

Anonymous — Mon, 03 Aug 2015 08:54:52 GMT

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.

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

Anonymous — Mon, 03 Aug 2015 15:16:52 GMT

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

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

_AnonymousUser — Tue, 11 Aug 2015 15:38:21 GMT

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

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

Anonymous — Tue, 11 Aug 2015 16:50:36 GMT

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

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

Anonymous — Tue, 11 Aug 2015 17:30:44 GMT

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

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

sum1 — Mon, 18 Nov 2019 06:38:10 GMT

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.

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

crtatnall — Mon, 19 Jul 2021 23:06:14 GMT

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?