4 ms·

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

by 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 cnvogel 11y ago

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