topic Re: Excel Formulas not effecting when Data is exported in Talend Studio

Excel Formulas not effecting when Data is exported

Anonymous — Mon, 23 Jun 2014 10:01:32 GMT

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

Re: Excel Formulas not effecting when Data is exported

Anonymous — Mon, 23 Jun 2014 11:47:48 GMT

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

Re: Excel Formulas not effecting when Data is exported

Anonymous — Mon, 23 Jun 2014 13:17:46 GMT

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.

Re: Excel Formulas not effecting when Data is exported

Anonymous — Mon, 23 Jun 2014 13:23:06 GMT

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

Re: Excel Formulas not effecting when Data is exported

Anonymous — Mon, 23 Jun 2014 13:48:18 GMT

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.

Re: Excel Formulas not effecting when Data is exported

Anonymous — Mon, 23 Jun 2014 14:01:56 GMT

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

Re: Excel Formulas not effecting when Data is exported

Anonymous — Mon, 23 Jun 2014 14:04:39 GMT

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?

Re: Excel Formulas not effecting when Data is exported

Anonymous — Mon, 23 Jun 2014 14:19:11 GMT

The template is also in the .xlsx format.

Re: Excel Formulas not effecting when Data is exported

richie1985 — Mon, 16 Feb 2015 21:14:02 GMT

i have the same problem, formula wont work

Re: Excel Formulas not effecting when Data is exported

Anonymous — Wed, 30 Mar 2016 08:32:33 GMT

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.

Re: Excel Formulas not effecting when Data is exported

Anonymous — Wed, 30 Mar 2016 08:58:01 GMT

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