Skip to main content
  • EN

Hi,

I'm trying to do some reporting in Excel using FME and an Excel file as a template.

The template is quite simple with some formatted fields, a logo, few combined fields.

The Excel writer writes the data after the formatted rows (B12).

Everything works fine, until I add a formula in the field D11 (as simple as =Sum(D12;D100)). Then, Excel throws an exception when loading the generated files and the formatted fields are no longer formatted.

The exceptions I get is in french but could translated to : "Sorry... We found a problem in the file "xxxxx.xlsx" but we can try to recover data if the file is safe". After a data recovery, the data is there, the sum is calculated, but the fields are not formatted anymore (probably because of the recovery...), the logo is gone. The recovery log is stating that the is a major failure at Line 1, Col 0. Nothing more.

Any ideas on what can cause this behavior ?

Thanks for your help.

Regards

Philippe

Hi,

Yes, it is possible to use an Excel template containing formulas.

Just a reminder if you have not used this. If you want the contents to be in specific cells then you have to use "update cells" instead of "insert cells" by using xlsx_row_id and column names in user attributes. In excel writer default option will be insert cells.

Hope this will help

Pratap

Hi,

Yes, it is possible to use an Excel template containing formulas.

Just a reminder if you have not used this. If you want the contents to be in specific cells then you have to use "update cells" instead of "insert cells" by using xlsx_row_id and column names in user attributes. In excel writer default option will be insert cells.

Hope this will help

Pratap

Thanks for your answer. I do know about the insert vs update write mode, but the problem I have is with a formula that is part of the template, above the writing area (all the date is written under the formatted template), in insert mode with a starting column and row defined. It seems that when I use the formula, it somehow corrupts the data in the output excel file.

Hi Philippe (@philsc)

I've looked for other reports of this problem and can't find any, so it seems to be unknown to us. I think it's best if you file this as a case with our support team (safe.com/support). You could mention that it might be related to PR#62687

Sorry I can't be more help with this. As far as I can see it should work and so we really need a copy of the workspace and data (if possible) to try and recreate the issue

Regards

Mark

Mark Ireland

Product Evangelist

Safe Software Inc.

Hi Philippe (@philsc)

I've looked for other reports of this problem and can't find any, so it seems to be unknown to us. I think it's best if you file this as a case with our support team (safe.com/support). You could mention that it might be related to PR#62687

Sorry I can't be more help with this. As far as I can see it should work and so we really need a copy of the workspace and data (if possible) to try and recreate the issue

Regards

Mark

Mark Ireland

Product Evangelist

Safe Software Inc.

Hi Mark,

For now, I changed my Excel files (template and output) from .xlsx to .xls and it worked.

I'll try to package something to the support team soon.

Thanks for your help

Philippe

I have found that i use a template excel sheet with the attribute names listed in the A1:A*, but for the complete formatting you will need the exact names within the template file to get the correct association. I also utilitize the rowID function to specify data summaries for fme_feature_type and the associated attribute values listed in the column headers. I will post an example that contains formulas, conditional formatting, and a few other goodies.

I have found that i use a template excel sheet with the attribute names listed in the A1:A*, but for the complete formatting you will need the exact names within the template file to get the correct association. I also utilitize the rowID function to specify data summaries for fme_feature_type and the associated attribute values listed in the column headers. I will post an example that contains formulas, conditional formatting, and a few other goodies.

Hi,

 

I can't get this to work for me at all. I keep just getting the attribute headers returned below what I have added in the cells of the template and with no entries, even though FME says it's returned 21!

 

Do you have a worked example by any chance?

 

I am a new FME user so any help is greatly received!

 

Thanks

 

Reply


Community Stats

30,099
Posts
114,259
Replies
37,729
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.