One Star

Excel Formulas not effecting when Data is exported

Hi
I require some help with a Basic Job. I?m importing data from a .cvs file to an excel file. I used tMap to JOIN in the Data.
I used an excel template (TimeSheet_Report_Test.xlsx) with some formulas in it. The Data exports to the excel just fine, but the formulas in the excel do not take effect when the data is exported to the excel file.
Hence the Formulated Columns to not change.
Does someone have a solution for refreshing the formulas in the column so they can take effect on the data?

I'm using File Delimited > tMap > tFileOutputExcel

10 REPLIES
Four Stars

Re: Excel Formulas not effecting when Data is exported

After executing the job, when you open your excel file, whether you see your formulas entered while creating template?
Whether the position of data insertion and formula available in the cell is conflicting?
As soon as you open the worksheet, formula's should take into the effect to evaluate the calculations.
Probably, your formulas are wiped off after writing data into the excel sheet... please confirm.
Thanks
Vaibhav
One Star

Re: Excel Formulas not effecting when Data is exported

Hi Vaibhav
The Data is imported into the correct field. I just oped the excel and then double click on the affect all columns and then only the does the formula take effect on the field. The formulas are still present in the excel after executing the Export.
Could it have something to do with me checking the "Write excel2007 file format(xlsx)" checkbox.
If I don't check the checkbox then I'm not able to import the Data into the excel. I'm not sure why the formulas are not taking effect.
Let me know if you know what the problem could be.
Four Stars

Re: Excel Formulas not effecting when Data is exported

Could it have something to do with me checking the "Write excel2007 file format(xlsx)" checkbox.
>>
This is required only when your output file is in .xlsx format what is the format (.xls/.xlsx) of your template?
Do you have onclick macro to apply formulas?
Vaibhav
One Star

Re: Excel Formulas not effecting when Data is exported

The Template is .xlsx
I don't have a Marco to apply the formulas. The Formulas are just present in the Sheet.
I thought the Formulas will effect the Cell once the Data is imported.
Four Stars

Re: Excel Formulas not effecting when Data is exported

If your template is .xlsx, then you have to enable that check box... and test again.
One Star

Re: Excel Formulas not effecting when Data is exported

I have checked the xlsx box and ran the Job. Everything is importing fine but the Formulas are still not taking effect.
Have any idea?
One Star

Re: Excel Formulas not effecting when Data is exported

The template is also in the .xlsx format.
Four Stars

Re: Excel Formulas not effecting when Data is exported

i have the same problem, formula wont work Smiley Sad
One Star

Re: Excel Formulas not effecting when Data is exported

I have the same problem. 
But, if I click "Enable Editing" when I open the excel, the formulas will be refreshed.
However, it would be great if these could refresh without this.
One Star

Re: Excel Formulas not effecting when Data is exported

Problem solved. I checked "Recalculate Formula"  https://help.talend.com/search/all?query=tFileOutputExcel&content-lang=en