Quite frequently in applications we write, we are tasked with processing Office documents in some fashion. There are many tools you can use to read/write these Office documents. Finding a tool that you can use with Azure Web Apps narrows that list down to only a handful of options. A lot of the tools out there require installation and configuration, something you just can’t do with an Azure Web App.
Recommendations
After some trial and error, I found that NPOI (see below) is the best option right now. NPOI is a rather large (4MB+) but considering all that it does, that size is not really that surprising. NPOI can read/write any Microsoft Office format without having Office installed. The major downside to NPOI is lack of documentation. You have to dig through examples to find out how certain things work. But the examples are pretty good. For the most part, you can use the documentation at the Apache POI project, from which NPOI is derived. Just remember that NPOI may be a bit behind in some areas that haven’t yet been ported from POI and you will have to translate code into NPOI equivalent code.
I often get questions like “Can NPOI create formulas in Excel sheets?”, “Can NPOI do formatting like this?” — and the answer is most likely “Yes, it can. But I don’t recommend it.” If your document requires complex formulas or uses a lot of styling, then I recommend creating a template document with formulas and formatting already in place. Make a copy of the template at runtime, then use NPOI to fill in the data where you need it. Replacing cell values in Excel is very easy. You can get creative with text replacement tokens to achieve simplicity in other formats like Word. NPOI can read/write formulas and formatting but doing so adds a lot of code to the application. Just formatting a single cell in an Excel sheet can take 10-15 lines of code depending on how complex you want the formatting to be. Using the template approach allows you to separate the final formatting (the template) from the content creation (the application + NPOI). The template also allows you to easily change formulas and styles without recompiling the application code.
Options to That Will Work
The list below shows the options that work in Azure Web applications.
- NPOI – https://github.com/tonyqus/npoi
NPOI is a .NET port of the POI Java project. With NPOI you can read/write Office files in any format. NPOI works in .NET standard and .NET Core as well. If you plan to deploy on a Linux agent, then make sure you read the instructions for Linux.
Combine with NPOI.Mapper if you want to read/write rows of data easily from Excel into strongly typed classes.
- Open XML SDK – https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk
Microsoft’s Open XML is an open standard for word-processing documents, presentations, and spreadsheets that can be freely implemented by multiple applications on different platforms.
- ExcelDataReader – https://github.com/ExcelDataReader/ExcelDataReader
Lightweight library used exclusively for reading Excel documents as a DataReader or DataTable.
Options That Will Not Work
The list below represents other methods you might have used in the past. These will not work for Azure Web Apps because they require installation or configuration options that are not available for Azure Web Apps.
- MS OLE DB (Jet.OLEDB.4.0 or ACE.OLEDB.12.0)
The OLE DB option will not work in Azure Web Apps because you simply don’t have enough control over the environment to try and install anything COM or OLE related on the box.
- Microsoft.Office.Interop
Office.Interop requires an installation of the app you want to use (Excel, Word, etc). So like the OLE DB option, you just don’t have enough control to install anything here.
- LinqToExcel – https://github.com/paulyoder/LinqToExcel
A very popular library that allows you to query Excel spreadsheets using LINQ. Unfortunately this solution relies on the ACE.OLEDB driver. Which as mentioned above isn’t an option for Azure Web Apps
Of course if you are finding frustration in these areas, Kopis is available to help answer your cloud environment queries.
