Solved

Excel Reader - not finding reference cells in IF statements?

  • 19 April 2021
  • 4 replies
  • 4 views

Badge

Hi, I have created Excel xlsx files using FME (using a Template and adding values in, for various formulae to use the inputted values to calculate others), and it works really well!

My issue is then when I read that xlsx file back through FME, the formula (IF statements mainly) don't pick up the inserted values! I have them formatted as Numbers in both Excel and FME, and the statement is similar to '=IF(AB8=0,"No Maps",AB8)', which works well when opening in Excel, but every value is 'No Maps' for the above cells when opening in FME! Very frustrating!

 

I'm running FME Desktop 2020.2.4, 64 bit.

 

Thanks!

icon

Best answer by simon_maddern 19 April 2021, 16:28

View original

4 replies

Badge

Just as an update, I believe the xlsx file is retaining the old value (from the template - i.e. it will say 'No Maps' because the value in the Template was 0) - It's as if the new value is insert by FME, but because it is not opened specifically in Excel, the formaulae are not given a chance to calculate?

Python solution using the 'openpyxl' and 'excel-formulas-calculator' libraries:

from openpyxl import load_workbook
from efc.interfaces.iopenpyxl import OpenpyxlInterface
 
import fme
import fmeobjects
 
class FeatureProcessor(object):
    def __init__(self):
        pass
    def input(self,feature):
        filename = FME_MacroValues['ExcelFile'] # Parameter containing the path to the .xlsx file
        wb = load_workbook(filename)
        interface = OpenpyxlInterface(wb=wb,use_cache=True)
        
        # We're interested in the values in C6/C7 on the Overview sheet, change these accordingly and expose them (if needed)
        feature.setAttribute('C6',interface.calc_cell('C6','Overview')) 
        feature.setAttribute('C7',interface.calc_cell('C7','Overview'))
        
        self.pyoutput(feature)
    def close(self):
        pass

This is fairly slow at around ~30 seconds per cell being calculated, but the only working alternative I could find (using the win32 library) requires Excel to be installed, so would be trickier to get working on FME Cloud/Server.

Userlevel 2
Badge +17

Just as an update, I believe the xlsx file is retaining the old value (from the template - i.e. it will say 'No Maps' because the value in the Template was 0) - It's as if the new value is insert by FME, but because it is not opened specifically in Excel, the formaulae are not given a chance to calculate?

Hi @mlufkin

If you are using Windows and have Excel installed on the same machine as FME, I would recommend trying the latest 2021.1 beta.

Our development team addressed the behaviour you're seeing on Windows systems in FME 2021.1 build 21526+ by refreshing formulas and PivotTables on save. Please note that this fix is limited to Windows system that has Excel installed.

If using macOS, Linux, or Windows systems without Excel installed, then I would recommend using the method suggested by @simon_maddern

Badge

Hi @mlufkin

If you are using Windows and have Excel installed on the same machine as FME, I would recommend trying the latest 2021.1 beta.

Our development team addressed the behaviour you're seeing on Windows systems in FME 2021.1 build 21526+ by refreshing formulas and PivotTables on save. Please note that this fix is limited to Windows system that has Excel installed.

If using macOS, Linux, or Windows systems without Excel installed, then I would recommend using the method suggested by @simon_maddern

Many thanks!

Reply