How to Read/Write Office Documents with .NET in Azure

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/npoiNPOI 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.

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.InteropOffice.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/LinqToExcelA 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.

Let’s Work Together

We believe you’ll discover a unique difference between our team and most custom software development firms. Much like them, our first few years, we were simply working hard “in the business.” However, after two decades, the business is in us.

We know the next few weeks, possibly months, will provide an unprecedented challenge for our clients, partners, friends and fellow local businesses. We want to make sure everyone is aware that despite COVID-19, Kopis remains stronger than ever, fully committed to providing the absolute best to those who have placed trust in us.

Kopis has complied with CDC recommendations, and have asked all employees to work-remotely during this outbreak. Our full team of over 40 technology experts are still working diligently on your projects, meeting important deadlines and remain dedicated to having a positive impact on your businesses. All meetings will be held virtually through Microsoft Teams. Our team members are available during regular hours and available to answer calls and emails as usual.

It’s a time of unity, and Kopis is proud to stand by our clients and help them continue to meet their goals through the challenges ahead. We remain grateful to our community, proud of our nation and hopeful for the future ahead in Greenville, South Carolina’s emerging tech scene.

We remain open. We remain fully equipped. We are here for you as you navigate these waters.