4 ms·

As a developer (or someone with some scripting or DBA skills), it's pretty easy to scoff at Excel being used as a way to internalise business rules but the thin

by danjc 11y ago

As a developer (or someone with some scripting or DBA skills), it's pretty easy to scoff at Excel being used as a way to internalise business rules but the thing about it is that it's this ubiquitous language that a lot of people understand. We've saved ourselves a lot of hassle implementing application integration solutions by outsourcing domain-specific number crunching/mapping to a spreadsheet in our integration platform. It enables us to treat the customers' business rules as a black box - feed inputs against named ranges and read out named ranges (https://developers.flowgear.net/kb/Node:Excel_Workbook https://developers.flowgear.net/kb/Node:Excel_Workbook).

morgante 11y ago
How did you go about converting the Excel workbooks into a usable program which you can feed data into and get back computed results?
- cnvogel 11y ago
  I think Joel Spolsky (?) is often quoted by saying that the easiest way to parse ms office file formats is to just install it on a server and use the documented API to Word, Excel,... I used this approach regularly about 4 years ago in my previous job to interact, query,... with data stored in "excel databases". So, while I'm not the parent commenter, my suggestion would be to have a windows server that runs both my interface software, and excel. You load the workbook, populate input fields, let excel do the processing and query output fields/workbooks/... Of course the excel sheet could also contain VBA code actively pulling out data from a DB, heck it could even call code in custom shared libraries. But I doubt that passing around workbooks full of code amongst it illiterate users will do any good. The less magic they contain outside of stock cell formulas the less chance of users messing up the computation. If you want to play with it, look up the win32 modules shipping directly with ActiveState's python distribution.
  - danjc 11y ago
    That's about how we do it. Excel (and its COM API) was never intended for multi-tenancy or consumption from a service. That said, if you run the service under a named user account (as opposed to SYSTEM or NETWORK) and make sure you won't have users accessing the file and locking it, you're good to go. If you need to invoke the same sheet repeatedly, you can build a cache of range output values keyed on a hash of range input values. This enables you to bypass Excel for a state that you've previously seen. It's also pretty easy to inject and execute script (as opposed to invoking existing scripts) - that deals with the problem of users being able to see or edit VBA.
    - cnvogel 11y ago
      > users accessing the file and locking it I'd say that having such a file on a network location accessible by your users is a pretty bad idea. As always, a proper develop/test/release process should be in place, otherwise great confusion about the actual revision of a "Excel Program" in production will ensue with absolute certainty. Treating it as a binary blob doesn't preclude one from implementing at least a basic form of revision control. You'll not be able to create useful diffs between revisions, but at least one can identify them.
  - cnvogel 11y ago
    [Link to the ActiveState Example] http://docs.activestate.com/activepython/3.3/pywin32/html/com/win32com/HTML/QuickStartClientCom.html http://docs.activestate.com/activepython/3.3/pywin32/html/co...

by danjc 11y ago

morgante 11y ago

How did you go about converting the Excel workbooks into a usable program which you can feed data into and get back computed results?

cnvogel 11y ago
I think Joel Spolsky (?) is often quoted by saying that the easiest way to parse ms office file formats is to just install it on a server and use the documented API to Word, Excel,... I used this approach regularly about 4 years ago in my previous job to interact, query,... with data stored in "excel databases". So, while I'm not the parent commenter, my suggestion would be to have a windows server that runs both my interface software, and excel. You load the workbook, populate input fields, let excel do the processing and query output fields/workbooks/... Of course the excel sheet could also contain VBA code actively pulling out data from a DB, heck it could even call code in custom shared libraries. But I doubt that passing around workbooks full of code amongst it illiterate users will do any good. The less magic they contain outside of stock cell formulas the less chance of users messing up the computation. If you want to play with it, look up the win32 modules shipping directly with ActiveState's python distribution.
- danjc 11y ago
  That's about how we do it. Excel (and its COM API) was never intended for multi-tenancy or consumption from a service. That said, if you run the service under a named user account (as opposed to SYSTEM or NETWORK) and make sure you won't have users accessing the file and locking it, you're good to go. If you need to invoke the same sheet repeatedly, you can build a cache of range output values keyed on a hash of range input values. This enables you to bypass Excel for a state that you've previously seen. It's also pretty easy to inject and execute script (as opposed to invoking existing scripts) - that deals with the problem of users being able to see or edit VBA.
  - cnvogel 11y ago
    > users accessing the file and locking it I'd say that having such a file on a network location accessible by your users is a pretty bad idea. As always, a proper develop/test/release process should be in place, otherwise great confusion about the actual revision of a "Excel Program" in production will ensue with absolute certainty. Treating it as a binary blob doesn't preclude one from implementing at least a basic form of revision control. You'll not be able to create useful diffs between revisions, but at least one can identify them.
- cnvogel 11y ago
  [Link to the ActiveState Example] http://docs.activestate.com/activepython/3.3/pywin32/html/com/win32com/HTML/QuickStartClientCom.html http://docs.activestate.com/activepython/3.3/pywin32/html/co...