0

I'm currently using COM Interop in .NET to export the contents of a DataTable to Excel. It's a method I've been using for years, but the problem is that COM is sketchy and throws random, intermittent, untraceable exceptions. This hasn't been a huge issue, since it's always just been code on a machine dedicated for this purpose and the exceptions are pretty rare, but recently I wrote a front-end for a data warehouse and I've got 200+ users on it, and the problem has (as you would expect) grown exponentially.

The problem is finding a method that does what COM does without the problems that come with COM. Namely, creating an Excel file in-memory (no writing to disk) that is a new workbook, so that when the user hits "Save" for the first time, it will ask them where they want to save the file and what they want to call it.

I know I can export a DataTable to an Excel file using OpenXML: Export DataTable to Excel with Open Xml SDK in c#

And I know I can create an OpenXML Excel object in-memory without writing to a file on disk: How to create Excel file using OpenXML without creating a local file?

The problem is the last step: Opening that in-memory file in Excel (without using COM)

Getting this to work would be my ideal option.

Now, I would prefer not to write to a file, but that isn't a deal breaker if there are no other options, so as a backup option I've also considered using OpenXML to create an Excel Template, and opening the Template (using Process.Start).

The problem with that approach is that some users have reported that Templates sometimes open as xltx files in Edit mode by default, instead of opening as new xlsx files: https://answers.microsoft.com/en-us/office/forum/office_2007-customize/is-there-a-way-to-get-my-excel-template-xltx-file/ab36cd4d-f6b0-46e0-8f15-533a4acb357f?page=1

This would be an even bigger issue than the random errors I'm getting from using COM.

Any ideas are appreciated.

EDIT - These are the COM errors I get:

Cannot create ActiveX component.

COM target does not implement IDispatch.

Exception from HRESULT: 0x800AC472

Select method of Range class failed

Operation unavailable (Exception from HRESULT: 0x800401E3 (MK_E_UNAVAILABLE))

The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))

The remote procedure call failed. (Exception from HRESULT: 0x800706BE)

Unable to cast COM object of type 'System.__ComObject' to interface type 'System.Collections.IEnumerable'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{496B0ABE-CDEE-11D3-88E8-00902754C43A}' failed due to the following error: 'The requested object does not exist. (Exception from HRESULT: 0x80010114)' and the COM component does not support IDispatch::Invoke calls for DISPID_NEWENUM.

Unable to get the Add property of the Workbooks class

Activate method of Workbook class failed

I've received 107 errors over the course of a few months, but I've gotten more lately since I updated the feature to attach to any existing instance of Excel that the user might already have open instead of creating a new one. Before, when I was always creating a new Excel instance each time, the errors were much more rare (and were largely solved by making Excel invisible while I did the work behind the scenes).

WATYF
  • 409
  • 1
  • 5
  • 16
  • This doesn't really answer your question, but you could try saving the file to a temporary location and set it to Read-Only in the file system. The would force your users to do a SaveAs rather than a Save. You may be able to play "Current Working Directory" games when you start the app so that when they go to save it, they are in the right place (I have no idea if this might work). – Flydog57 Apr 15 '20 at 23:18
  • 1
    I'm not sure what your architecture is exactly, but I'll bite - what are the COM errors? – MacroMarc Apr 15 '20 at 23:26
  • You could probably try to specify the app > Process.Start("Excel.exe", "C:\\myPath\\myFile.xslx"); : See https://learn.microsoft.com/en-us/dotnet/api/system.diagnostics.process.start?view=netframework-4.8#System_Diagnostics_Process_Start_System_String_System_String_ – Martin Venter Apr 16 '20 at 05:56
  • 1
    COM itself doesn't throw random errors, you say that like it was a fact of life. Maybe you could try to fix this first, or understand why you get errors. – Simon Mourier Apr 16 '20 at 07:00
  • 2
    I've used Office.Interop.Excel without issue for years. You should try and find out why you're getting exceptions. That said, these days I prefer EPPlus (v4 is still free for commercial use). – WSC Apr 16 '20 at 08:20
  • I could create a file and set it as Read-Only, but when they hit save, it will give them an error that it can't be saved, and they'll have to use Save As instead. I'm trying to avoid any approach that will confuse the users, which is why my backup is to use a template (which would act just like a new Excel workbook). – WATYF Apr 16 '20 at 15:43
  • I've updated the post with a list of the errors (easier than posting them here in the comments). These occur for various users but not for all users and not persistently for the same users. It will occur, and then the next time they try the export (even if it's just a few seconds later) it will work. Now, I've always gotten intermittent HRESULT exceptions when working with COM, but they've been very rare. Now that I've got a lot of people doing the same procedure over and over, it's getting to be a bit much. – WATYF Apr 16 '20 at 15:57
  • Frankly, I'm not looking to deal with the COM errors. There are too many and since I can't reproduce any of them, tracking them all down and finding a solution for each one doesn't strike me as a good use of my time. What I'd really like to know is if it's possible to open an in-memory OpenXML xlsx document in Excel. That would solve all of my problems. – WATYF Apr 16 '20 at 15:59
  • 1
    When you startup Excel, you can pass in an xlsx filename and say "use this as a template" by setting the appropriate command line argument: https://support.office.com/en-us/article/Command-line-switches-for-Microsoft-Office-products-079164CD-4EF5-4178-B235-441737DEB3A6#ID0EAABAAA=Excel – Flydog57 Apr 16 '20 at 16:54
  • @Flydog57 Thanks, that helps with the backup option. The "/t" switch didn't work right (it would open the file as a template and also open a blank workbook, and it messed up one of the Add-Ins I have installed). But the "/n" switch worked just fine. I also tried the "/e" switch to hide the splash screen, but that opened Excel in a new instance that couldn't communicate with existing instances. Anyway, do you mind adding your comment as an answer so I can accept it? I'm skeptical that I'll ever find a way to open an in-memory OpenXML object so I'm thinking I might have go with the backup. – WATYF Apr 16 '20 at 19:30
  • Most errors you mention are probably communication error that COM just reports. They are not "COM errors". Excel is an out-of-process COM server with an End-User UI (especially since you seem to want to share that UI with the end-user). If the End-User shutdowns Excel or Excel is busy, or timeout occurs, etc., then you will get those errors. One solution is to retry gracefully on errors like that. Or use your own Excel instance(s) (don't make Excel visible). Even though, you will get errors, but probably much less. – Simon Mourier Apr 17 '20 at 10:51
  • @SimonMourier As it turns out, my solution up to this point has been to implement extensive retry logic. This is handling most issues, but I'd rather not have non-stop exceptions occurring if I can avoid it (even if the user never sees them). Originally I did create my own instance of Excel and make it invisible (which, as you expect, reduced the errors to a negligible amount), but users requested the ability to export data to Excel that could then interact with an existing instance of Excel. Once I implemented that, the errors increased quite a bit, which is why I'm looking for alternatives. – WATYF Apr 17 '20 at 19:18
  • Another way of doing it is to add logic into Excel as an addon (COM, VSTO, VBA, etc.) instead of talking to it with an external COM client. If the addon is inprocess, you have zero communication errors. – Simon Mourier Apr 17 '20 at 21:15

1 Answers1

1

This is a suggestion more than an answer. Consider trying to save the file to a temporary location and set it to Read-Only in the file system. The would force your users to do a SaveAs rather than a Save. Then use the Excel command line options (apparently /n is best) to open the XLSX file as if it were a template.

Those command line options are located here:Excel Command Line Options

Flydog57
  • 6,851
  • 2
  • 17
  • 18
  • Yes, it looks like I've have to use this as a backup option. It's not so bad. I don't even need to make the temp file Read-Only as using the "/n" switch is sufficient to force users to SaveAs when they click Save for the first time (and I can just delete the temp xlsx file once the "template" version of it is displayed to the user). – WATYF Apr 17 '20 at 19:32