My Experience with Microsoft Excel During IT Projects

During my extensive career in IT, I often ran into Microsoft Excel. One of my first projects was leveraging Excel to create documentation for a telco for site surveys. I build a solution with Visual Basic for Applications, a programming language for Excel, and all the other Microsoft Office programs like Word and PowerPoint. With VBA, I could generate multiple worksheets in a Workbook filled with static and dynamic data – from a user’s input or configuration file. Once populated with data and rendered, the Workbook was converted to a Portable Document Format (PDF).

Over the last couple of years, I had other projects involving Excel. In this post, I will dive into the details of implementations (use cases) concerning Excel Workbooks. One project involved processing Excel files in a Container running on an Azure Kubernetes Service (AKS) cluster, the other generating an Excel Workbook for reporting purposes orchestrated by an Azure Logic App.

Use Case – Processing an Excel Workbook in a Container

The use case was as follows. In short, I was working on a project for a client a few years ago that required processing a standardized Excel template that their customers could provide for enrichment. The data in the excel file needed to end in a database for further processing (enrichment) so that it could be presented back to them.  The diagram below shows the process of a customer uploading an Excel file via an API. The API would store the Excel in an Azure storage container and trigger code inside a container responsible for processing (parsing the Excel to JSON). The second container had code persist the data in SQL Azure.

Use Case 1

The code snippet (as an example) responsible for processing the Excel file:

For creating the Excel Workbook and its sheet with data, I found the EPPlus library, a spreadsheet library for the .NET framework and .NET core. In the project, I imported the EPPlus NuGet package – specifically, I used the ExcelPackage class.

Now let’s move on to the second use case.

Use Case – Generating an Excel Report in Azure

In a recent project for another customer, I had to generate a report of products inside D365 that needed to be an Excel File (a workbook containing a worksheet with data). The file had to be written to an on-premises file share to allow the target system to consume it. The solution I built was using a Logic App to orchestrate the project of generating the Excel file.

Below you see a diagram visualizing the steps from triggering a package in D365 until the writing of the Excel file in a file share on-premises.

Use Case 2

The steps are:

  1. Logic App triggering a package in D365 (schedule trigger).
  2. Executing the package to retrieve and export data to a SQL Azure Database.
  3. Query by the same Logic App that triggered the package to retrieve the data from the SQL Azure Database.
  4. Passing the data to (the result of the query) to an Azure Function, which will create an Excel Workbook with one sheet containing the data in a given format. The function will write the Excel to an Azure Storage container.
  5. Subsequently, the Logic App will download and write the file to the on-premises file share (leveraging the On-Premises Data Gateway – ODPGW).

The sequence diagram below shows the flow (orchestration) of the process.

Sequence diagram

And below is a part of the Logic App workflow definition resembling the sequence diagram above.

The code snippet (as an example) in the Azure Function responsible for creating the Excel file:

For the creation of the Excel Workbook and sheet with data, I used NPOI – an open-source project which can help you read/write XLS, DOC, and PPT file extensions. In Visual Studio, I imported NPOI NuGet Package. The package covers most of the features of Excel like styling, formatting, data formulas, extracting images, etc. In addition, it does not require the presence of Microsoft Office. Furthermore, I used the StorageAccountClass to write the Excel file.

Conclusion

Microsoft Excel is a popular product available for decades and used by millions of people ranging from businesses heavily relying on Excel to home users for basic administration. Moreover, in IT, Excel is used in many scenarios such as project planning, environment overviews, project member administration, reporting, etc. As said earlier, I have encountered Microsoft Excel various times in my career and built solutions involving the product. The two use-cases are examples of that.

In the first example, I faced a challenge finding a library that supported .NET Core 2.0. I found EPPlus, which did the job for us after experimenting with it first. In the second example, the cost and simplicity were the benefits of using the NPOI library. There were constraints in the project to use solutions with a cost (subscription-based or one-off). Furthermore, the solution proved to be stable enough to generate the report.

Note that the libraries I found are not the only ones available to work with Excel. For instance, SpreadsheetGear, and others, which are listed here. In Logic Apps, you can find connectors that can do the job for you, such as CloudMersive (API you connect to convert, for instance, CSV to Excel).

I do feel with code you have the most flexibility when it comes to dealing with Excel. A standard, of-the-shelve can do the job for you, however, cost (licensing) might be involved or other considerations. What you choose in your scenarios depends on the given context and requirements.

Some Cloud IT Trends in 2022

We are a few weeks into 2022, and you might have seen or read articles and reports on trends for this year. I also like to outline the few significant IT trends in this blog post from my point of view based upon my work as Cloud Editor for InfoQ and experiences in the consulting field.

First of all, the importance of Artificial Intelligence (AI). You can see that Microsoft, for example, is structurally building these kinds of capabilities into their entire platform. Its intelligence is increasing rapidly, and you can already see with enterprises that they can quickly make valuable business applications with it.

Microsoft Azure AI Platform

Microsoft is already implementing it in their Azure environment. For example, monitoring users’ login behavior is a straightforward example: they continuously keep track of which user logs in when and from which location. They also immediately pass all the data they collect through an AI robot, which will make connections. Furthermore, other examples are that the company enhanced its Translator service and launched the Azure OpenAI service. And it’s not just Microsoft as other public cloud vendors AWS and Google are on board too.

The second trend I see is that more and more companies are looking at options for customizing applications without really having to program, with no code or low code. This has been in the spotlight for some time now, especially among larger companies that would like to facilitate their so-called citizen developers to develop software for use in their own work.

To this end, Microsoft has developed the Power Platform over the past two to three years into a mature low-code platform, which is also interesting for larger organizations. However, you do have to look closely at governance; you can’t just release that completely to users, and you have to build in-game rules, frameworks, and guidelines.

Microsoft Power Platform

We also see increasing adoption of that platform among enterprises, especially with Dynamics 365. The combination of Dynamics 365, Office 365, and Power Platform is becoming a compelling low-code platform for building business applications. Microsoft has an excellent starting position in the low-code market space with competitors like OutSystems, Mendix, and offerings by AWS (HoneyCode, Amplify) and Google (AppSheets). Also, I recommend reading the InfoQ article: Low-Code Platforms and the Rise of the Community Developer: Lots of Solutions, or Lots of Problems?

The third major trend is cloud integration. In recent years, many organizations have moved to the cloud with their applications and data or will move in the wake of COVID-19. Moreover, organizations that have moved to the cloud are now discovering that as you adopt more cloud technology, the need for integration between those systems increases.

Assume you have a CRM from Microsoft, an ERP from SAP, and a data warehouse on Azure. Your business processes run across all those systems. So you must therefore ensure that these systems can exchange data with each other. And you have to make sure that if you have a CRM in the cloud and a customer portal based on customization, you can also see your customer data in that portal. Or some data needs to enter a system on-premise. So, in the end, you need to integrate that!

Therefore, the need for cloud integration is increasing, especially among organizations increasingly investing in the cloud. Microsoft has an answer to that, too, with a perfect and very complete integration platform on Azure named Azure Integration Services (AIS). As a result, even the most demanding enterprises can meet their needs with this.

Azure Integration Services

Recent analyst reports from Gartner and Forrester showed the services are leading. For example, Microsoft was among the leaders in the latest Forrester Wave for Enterprise Integration-Platform-as-a-Service (iPaaS) 2021. In addition, it has been in the leader quadrant of iPaaS reports from Gartner consistently over the last couple of years and that also accounts for API Management.

Lastly, with the last trend, the need for integration increases, and so will the demand for supporting and monitoring them.