span8
span4
span8
span4
I'm looking for a workaround to a small bug in FME that currently exists (亚搏在线Safe already know about the issue).
I need to write some values into a spreadsheet,from which a formula calculates a result.I then need to read back the result in another process.However,it seems that the result is only available to FME if you open the Excel sheet manually and save the spreadsheet and then close it again.If you try to write the values and then read the result straight back without opening the spreadsheet you don't get back the calculated value.This is therefore no good as it makes an automated process difficult.
You can see the issue demonstratedhere.
Does anyone have any magic workarounds to force a commit or a save in the spreadsheet behind the scenes,without the user having to intervene?
I think you'd need a shutdown python script in FME (or a SystemCaller after a feature writer) that would spin off a PowerShell script.I see some clues herehttps://support.jamsscheduler.com/hc/en-us/articles/206191918-How-to-Automate-the-opening-of-an-Excel-Spreadsheet-in-Powershelland more than you need herehttps://stackoverflow.com/questions/27798567/powershell-excel-save-and-close-after-run.If someone gets this working do let us know.
The issue is that the library we use (and I suspect all libraries) don't themselves contain an execution engine that evaluates Excel expressions.Normally it doesn't matter because as soon as someone opens the file in Excel all the values are calculated.But cases such as the one here then become an issue.The only way I can imagine you can win is to "remote control" Excel to open and save the file.
from win32com.client import Dispatchxl = Dispatch('Excel.Application')wb = xl.Workbooks.Open(r'...FILE PATH\name.xlsx...')wb.RefreshAll()wb.Close(True)
Could you use a Python shutdown script to open and save the XLS file?
A Python library is available at:
Thanks Erik,we had a go at this initially but failed to get it to work.I'm not sure if that was the library we used though,so we'll have a look at that one.Cheers.
© 2019 亚搏在线Safe Software Inc |Legal