5 ms·

For over 10 years I've written and maintained Excel automation that integrates with a web-based research database. Beyond not being supported by the EULA, Micro

by jrcii 10y ago

For over 10 years I've written and maintained Excel automation that integrates with a web-based research database. Beyond not being supported by the EULA, Microsoft flat out says in its documentation do not do this COM is not designed for this purpose.

teh_klev 10y ago
It's not COM that's the problem. When you instantiate Excel you're firing up an out of process instance of Excel which you communicate with over DCOM. The problem for web apps (for example written in Classic ASP or ASP.NET) is that each request can create its own instance of Excel, and if the request that did this didn't complete and cleanly close down Excel then you end up with loads of orphaned Excel instances lurking in the background. When this happens eventually the web server runs out of memory. Excel is definitely not intended for this type of use. Some people get lucky because they know what they're doing, 99% of the rest should just stay away.
- mistermann 10y ago
  Can't you just write it as a Windows service then and use some sort of an asynchronous queue (like a file folder)?
  - rbag 10y ago
    Worked with this kind of tech for a few years... And trash it to go with Aspose components. It's slow, and really hard to debug when it's not working (not only pdf output but merge fields).
    - teh_klev 10y ago
      Aspose is a complete no brainer, yet so many clients complain about the extra cost. Somehow they don't see the hours of their own time debugging this stuff as invariably costing far more.
  - intrasight 10y ago
    Close. You don't even need a Windows service. Just us a serialized queue using MSMQ. Write the queue listener in Javascript and register with COM+. Excel automation on the server is easy if you do it right, and impossible if you do it wrong.
    - teh_klev 10y ago
      Most of the devs I've encountered over the years who want to use Excel server side have never heard of queuing. > Just us a serialized queue using MSMQ. Write the queue listener in Javascript and register with COM+. Sure, but now you have MSMQ and COM+ to deal with...and debugging/fixing when it breaks (under load), or isn't secured properly. The smart folks shell out for a copy of Aspose Cells and get the job finished on time.
      - intrasight 10y ago
        Subsequent to my MSMQ approach, I did a project with Aspose. It's a pretty good toolkit, and I would recommend it over the MSMQ/COM+ complexity - Unless you absolutely have to automate Office, which is sometimes true. On more recent projects in last ~12 months, I have used OpenXML.
- ourmandave 10y ago
  I use Java RMI to connect AIX to W2K3 running a service that uses Jacob COM to call Excel. It reads a CSV file off a Samba share on the AIX box and saves back an XLS. The lock file is on the AIX side because Win files aren't atomic. It's a bit Rube Goldberg-ish and has low traffic, but it's rock solid.
  - taspeotis 10y ago
    That seems ... unnecessary. CSV and XLS parsing are built into Windows. https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/standard-excel/ https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/s... https://www.connectionstrings.com/microsoft-text-odbc-driver/standard/ https://www.connectionstrings.com/microsoft-text-odbc-driver...
- douche 10y ago
  I have done similar crazy things with Lync, back before Microsoft opened up their APIs on the Office 365 side so that they were marginally usable. I wouldn't recommend it...
ZenoArrow 10y ago
Just out of interest, have you explored using the Apps for Office functionality? I think it could provide a more robust solution (robust in the sense that it's supported by Microsoft) for integrating web interfaces into Excel. Basically it allows you to build Excel extensions using HTML/CSS/JS. I could understand using COM before Apps for Office was available. http://dev.office.com/docs/add-ins/overview/office-add-ins http://dev.office.com/docs/add-ins/overview/office-add-ins

by jrcii 10y ago

teh_klev 10y ago

It's not COM that's the problem. When you instantiate Excel you're firing up an out of process instance of Excel which you communicate with over DCOM. The problem for web apps (for example written in Classic ASP or ASP.NET) is that each request can create its own instance of Excel, and if the request that did this didn't complete and cleanly close down Excel then you end up with loads of orphaned Excel instances lurking in the background. When this happens eventually the web server runs out of memory. Excel is definitely not intended for this type of use. Some people get lucky because they know what they're doing, 99% of the rest should just stay away.

mistermann 10y ago
Can't you just write it as a Windows service then and use some sort of an asynchronous queue (like a file folder)?
- rbag 10y ago
  Worked with this kind of tech for a few years... And trash it to go with Aspose components. It's slow, and really hard to debug when it's not working (not only pdf output but merge fields).
  - teh_klev 10y ago
    Aspose is a complete no brainer, yet so many clients complain about the extra cost. Somehow they don't see the hours of their own time debugging this stuff as invariably costing far more.
- intrasight 10y ago
  Close. You don't even need a Windows service. Just us a serialized queue using MSMQ. Write the queue listener in Javascript and register with COM+. Excel automation on the server is easy if you do it right, and impossible if you do it wrong.
  - teh_klev 10y ago
    Most of the devs I've encountered over the years who want to use Excel server side have never heard of queuing. > Just us a serialized queue using MSMQ. Write the queue listener in Javascript and register with COM+. Sure, but now you have MSMQ and COM+ to deal with...and debugging/fixing when it breaks (under load), or isn't secured properly. The smart folks shell out for a copy of Aspose Cells and get the job finished on time.
    - intrasight 10y ago
      Subsequent to my MSMQ approach, I did a project with Aspose. It's a pretty good toolkit, and I would recommend it over the MSMQ/COM+ complexity - Unless you absolutely have to automate Office, which is sometimes true. On more recent projects in last ~12 months, I have used OpenXML.
ourmandave 10y ago
I use Java RMI to connect AIX to W2K3 running a service that uses Jacob COM to call Excel. It reads a CSV file off a Samba share on the AIX box and saves back an XLS. The lock file is on the AIX side because Win files aren't atomic. It's a bit Rube Goldberg-ish and has low traffic, but it's rock solid.
- taspeotis 10y ago
  That seems ... unnecessary. CSV and XLS parsing are built into Windows. https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/standard-excel/ https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/s... https://www.connectionstrings.com/microsoft-text-odbc-driver/standard/ https://www.connectionstrings.com/microsoft-text-odbc-driver...
douche 10y ago
I have done similar crazy things with Lync, back before Microsoft opened up their APIs on the Office 365 side so that they were marginally usable. I wouldn't recommend it...

ZenoArrow 10y ago

Just out of interest, have you explored using the Apps for Office functionality? I think it could provide a more robust solution (robust in the sense that it's supported by Microsoft) for integrating web interfaces into Excel. Basically it allows you to build Excel extensions using HTML/CSS/JS. I could understand using COM before Apps for Office was available. http://dev.office.com/docs/add-ins/overview/office-add-ins http://dev.office.com/docs/add-ins/overview/office-add-ins