Create in-memory Excel file and open it in Excel without using COM Interop

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

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