Writing MSOffice Files from C#
A Common Operation
You have a web site that delivers its payload to users as an Excel file. This is convenient and easy for users, they can use Excel or one of the substitutes like Google Sheets to import their data.
I am talking specifically about Excel files, but from the comments I have read the same issues described herein also happen with other Microsoft Office Interop packages.
So you read the documentation, install the Microsoft.Office.Interop.Excel NuGet package, create a class to write your data as a spreadsheet, being sure to add a using directive for the namespace. You write some preliminary code to create an application instance, a workbook, then a worksheet.
Being a savvy developer you decide to run a quick test before you get into the weeds of creating rows and populating cells; you add a temporary call to instantiate your spreadsheet writing class and call it after your query.
Hit the breakpoint. Hit F10 to invoke the code that instantiates your new Excel class.
Reading past the inscrutable assembly references and triple-dot version numbers you see that you have a mismatch between the Interop package and your local version of Excel.
I have to break in at this point to advise you not to waste your time searching in Stack Overflow for a solution to this version issue, and for godssake don’t bother with the perennially useless Microsoft documentation; you won’t get anywhere.
- You have the latest stable version of the NuGet package
- You have the latest version of Office
… and it just doesn’t work.
And it’s not going to.
But I’ve Seen It Work
Yup. One place I worked for three years we had a feature to import an uploaded sheet and, occasionally, download a user-specific template file. And we used the Interop to create that template. But this was on our server, where the Office version was never updated. Why should we? There has been no reason to upgrade Office in 20 years. The Interop package version worked with the Office installation. We left it alone.
Mind you, I am talking here about writing Excel files, not reading them. Reading uses a completely different NuGet package, actually two of them: ExcelDataReader and ExcelDataReader.Dataset. These were written by someone with a functioning brain. They do not use the Excel application. They don’t need to.
And the Excel writing package shouldn’t need to either. Allow me to explain …
Why This Is So Stupid
If you open an Excel file in Notepad you will see that it’s XML. While a new version of Office comes out as often as corporate buyers can be convinced to upgrade, the file format has not changed in many years.
And neither have the applications, aside from trivial user interface changes and new features that nobody cares about.
The file format can be regarded as frozen, which is why the ExcelDataReader package doesn’t puke and throw exceptions on application version mismatches. It doesn’t use Excel to operate the reading of the file; the file is XML, any XML parser can read it.
But Writing Doesn’t
Well. actually it’s the Microsoft.Office.Interop.* packages that don’t work. Obviously they can work in some very narrowly controlled conditions where the Interop accepts the locally installed version of Office, someone had to be able to pass a “unit test” and show that it worked on his machine. So he could mark his task “complete” and move on the next betrayal of developer customers.
I’m sure that if you had a folder full of previous versions of Office and you tried older versions of the Interop, dozens or hundreds of combinations, you could probably find a combination that worked. And the client or employer for whom you are writing this application is certain to be understanding that it’s all taking so long. The people signing the checks are nothing if not patient.
With some exploration you might discover that there is a COM assembly, not a NuGet package, that you can add to your project as a dependency; it has a version number of 126.96.36.199 while the latest stable Interop NuGet is 15 dot something dot something dot something. Discovering that you might think your troubles are over but I got the same exception as before, just with a different assembly reference.
Heaven forfend that the respective version numbers might be useful; The latest Interop is 15 something; the COM dependency is 16; my version of Office is 18 and some change. All are the latest versions, but that isn’t the problem; the problem is who makes them.
The point is to sell copies of Office. Office is Microsoft’s cash cow, Windows is its loss leader.
Fortunately, there are alternatives.
Office File Writers That Actually Work
After three days of chasing my tail trying to resolve this version issue I ran across this piece of joy:
How to write some data to excel file(.xlsx)
This is what i am trying to do. 1.Create excel file(.xlsx) c://test/files/work1_4.13.14.xlsx with name + value(date)…
which included this beautiful reference:
There are other libraries out there where you DON’T need Office installed, and are much simpler to use: NPOI, ClosedXml and EPPlus being some popular alternatives. I will leave the reader to decide which is the best according to their particular needs, but I would wholeheartedly recommend the worst of those three over the office.interop dll. — BKSpurgeon Nov 29 ’17 at 12:33
Glory. I installed the NPOI package, went to its support site, and I was instantly unblocked. After three frustrating days of trying to get Microsoft’s Interop package to work with Microsoft’s software.
As Mr. Spurgeon (thank you, sir, thank you) notes in his answer, none of these require Office to be installed so it is no leap of inference to figure out that they simply write XML.
I have a long way to go; I need to do checkboxes and radio buttons in my output file, but I am now making progress instead of getting the same exception over version numbers that should not even matter. I’m sure that all three of these alternatives simply write XML files as we all were able to do before JSON largely replaced XML.