topic Re: Excel Output - Absolute Y Pos (keeping old rows) in Talend Studio

Excel Output - Absolute Y Pos (keeping old rows)

hgromek — Tue, 14 May 2019 13:59:56 GMT

Hi - I have 2 issues -I have the columns loading the correct information however

1st - the number of rows fluctuates (about 300-800). So sometimes it can be 1500 rows, next update is 1000 rows. How (without replacing the sheet, since there are formulas attached to data) can I remove the data at the end. It loads but leaves old data.

2nd - the excel formulas don't auto-calculate, I need to resend them to trigger them.

Can anyone help with either of these? I would appreciate it!

Re: Excel Output - Absolute Y Pos (keeping old rows)

JaneYu — Tue, 14 May 2019 16:26:14 GMT

you can try to download Jan Lolling's tFileExcel components from https://exchange.talend.com/

Then use an excel template to keep the formula, check the option in the tFileExcelWorkbookSave: evaluate all formulas

Re: Excel Output - Absolute Y Pos (keeping old rows)

hgromek — Wed, 15 May 2019 19:06:34 GMT

I really like these components, I had no idea they existed!

Can you show me how to (within the updated excel table), to push the table formulas to autofill if the data entered is greater than it was, I have the table auto growing, however, the formulas do not fill in the table?

Re: Excel Output - Absolute Y Pos (keeping old rows)

JaneYu — Wed, 15 May 2019 19:11:36 GMT

you could prepare an excel file with formula, column headers and format, but without data. Then use this file as a template (use tFileCopy to copy the template and rename to your output file name). In the tFileExcelSheetOutput, check: append to the existing rows

Re: Excel Output - Absolute Y Pos (keeping old rows)

hgromek — Wed, 15 May 2019 21:02:29 GMT

Thanks - I used a macro instead as it was allowed & simple, but thank you for turning me on to these components!