Open-XML-SDK | Open XML SDK by Microsoft | SDK library

I'm trying to export a List or IEnumerable to an Excel file using the library Open XML of Microsoft.

Library: https://github.com/OfficeDev/Open-XML-SDK

Example of similar request: https://www.codeproject.com/Articles/692121/Csharp-Export-data-to-Excel-using-OpenXML-librarie

The example above works pretty fine but have a lot of code between (As far works neat, but I searching for an optimization)

But the documentation of this library does not extend (or hasn't many examples), and the examples that I saw around about how to export data to Excel it's using a DataTable or Dataset; so, they make the conversation from the List to DataTable to export the DataTable. And that seems pretty complex way to something that other libraries resolve easier.

So, if someone has a sort of example of how to make the export quickly and generic, it will be appreciated.

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