Question

Excel reading bug

  • 4 February 2016
  • 8 replies
  • 21 views
davideagle
Rank
Badge +10

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 demonstrated here.

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?

8 replies

erik_jan
Rank
Userlevel 2
Badge +12

Could you use a Python shutdown script to open and save the XLS file?

A Python library is available at:

https://openpyxl.readthedocs.org/en/2.3.3

davideagle
Rank
Badge +10

Could you use a Python shutdown script to open and save the XLS file?

A Python library is available at:

https://openpyxl.readthedocs.org/en/2.3.3

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.

philippeb
Badge +2

Hello! Did you solved this? I have the same issue here. FME 2017.1 Thanks!

fmelizard
Rank
Userlevel 4
Badge +13

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 here https://support.jamsscheduler.com/hc/en-us/articles/206191918-How-to-Automate-the-opening-of-an-Excel-Spreadsheet-in-Powershell and more than you need here https://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.

philippeb
Badge +2

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 here https://support.jamsscheduler.com/hc/en-us/articles/206191918-How-to-Automate-the-opening-of-an-Excel-Spreadsheet-in-Powershell and  more than you need here https://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.

Thank you for your answer! I've decided to integrate a Python script in the FME Workspace parameters/Scripting/Startup Python Script 

 

from win32com.client import Dispatch
xl = Dispatch('Excel.Application')
wb = xl.Workbooks.Open(r'...FILE PATH\name.xlsx...')
wb.RefreshAll()
wb.Close(True)
Then, in the Python Compatibility, I put Esri ArcGIS Desktop (Python 2.7).

 

 

On the machine running the script, I need pywin32 (depends on which FME version you're running : 32 vs 64 bits) and I needed to modify a parameter in Excel (it has to be installed on the machine running FME of course).In Excel, go to Options/Advanced Options/General and uncheck Update automatic links at open

 

0684Q00000ArMCbQAN.png

 

It works like a charm! Finally.

 

philippeb
Badge +2

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 here https://support.jamsscheduler.com/hc/en-us/articles/206191918-How-to-Automate-the-opening-of-an-Excel-Spreadsheet-in-Powershell and more than you need here https://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.

Oh and by the way, as nothing is simple in this world, if you want to run the FME script in a scheduled task, you'll have to create on the machine running FME, a desktop folder here (depends if you use 32 or 64 bits I suppose) : C:\\Windows\\System32\\config\\systemprofile\\desktop

 

 

I found this very strange/amazing solution on M$ forum

 

 

 

natalieatsafe
Rank
Badge +4

Just to add another piece to this puzzle, it was recently discovered that the PowerShell script solution offered above does work wonderfully in FME Desktop, but does not transfer over to FME Server well. Running such a workspace Server-side will throw the following error:

 

HRESULT: 0x80080005

 

This Microsoft forum post explains that "This [error] usually indicates that you run Office applications on the server side or any unattended application...Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component".

 

This second Microsoft article dives deeper into server-side use of MS Office, stating that "Office applications do not take into consideration the security problems that distributed components face. Office does not authenticate incoming requests...Office uses many client-side components...that can cache client authentication information."

 

Additionally, this second article mentions licensing concerns, where "Current licensing guidelines prevent Office applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA)."

 

The first linked article above provides a suggested workaround to this issue: "...consider using Open XML SDK, see Welcome to the Open XML SDK 2.5 for Office for more information".

 

philippeb
Badge +2

Just to add another piece to this puzzle, it was recently discovered that the PowerShell script solution offered above does work wonderfully in FME Desktop, but does not transfer over to FME Server well. Running such a workspace Server-side will throw the following error:

 

HRESULT: 0x80080005

 

This Microsoft forum post explains that "This [error] usually indicates that you run Office applications on the server side or any unattended application...Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component".

 

This second Microsoft article dives deeper into server-side use of MS Office, stating that "Office applications do not take into consideration the security problems that distributed components face. Office does not authenticate incoming requests...Office uses many client-side components...that can cache client authentication information."

 

Additionally, this second article mentions licensing concerns, where "Current licensing guidelines prevent Office applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA)."

 

The first linked article above provides a suggested workaround to this issue: "...consider using Open XML SDK, see Welcome to the Open XML SDK 2.5 for Office for more information".

 

Thanks for the update !

Reply


Community Stats

28,651
Posts
109,271
Replies
36,525
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.