Excel Output - Absolute Y Pos (keeping old rows)

[hgromek]

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! 

[hgromek]

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! 

[JaneYu · Accepted solution]

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 

[hgromek]

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? 

[JaneYu]

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

[hgromek]

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