Automating Excel Files Without Installing Excel

Last updated on 

One very important aspect of automation development that is often overlooked is making automations as lightweight as possible. There's probably a better and more accurate term for this, but I personally say that an automation is lightweight when it uses as few dependencies and external applications as possible.

The more lightweight an automation is, the easier it is to maintain and distribute. One of the best examples from my personal experience is Excel. So many bots use Excel files, and some of them require Excel interaction that isn't possible with low code activities. When I started developing Excel bots, I opened Excel and used the UI for involved Excel steps. Now I shudder at the thought of using Excel's UI, but we all have to start somewhere.

Later on I started to use macros, which were much more reliable than using the UI. This seemed like the ideal solution, until a certain client asked if there was a way to automate Excel in a way where they didn't have to buy more Office licenses. After I thought about this for a bit, I realized the importance of making bots lightweight:

  • Every dependency that a bot uses has a small chance of possibly conflicting with other dependencies. Even if there are no conflicts, bots will still be cluttered if there are too many dependencies
  • Every external application that a bot uses has to be installed on all machines (both attended & unattended). This is costly if the software is paid (like Excel)

Another way to consider "lightweight" bots is "self-contained." If a bot can manage to be completely self-contained, using no external applications at all, more options open up for how to run the bot. It could run entirely in the background, in a cloud VM, or even in a serverless environment, depending on the situation.

So how can we go about making our bots lightweight? The typical process I follow is doing some research for whatever platform I'm using to see if any packages can do things that low-code activities can't. We'll use Excel as our example, and see if we can create Excel file automations without Excel.

For Robocorp, this is rather simple. The RPA Framework gives two Excel options: RPA.Excel.Application and RPA.Excel.Files. The latter supports xls and xlsx files without needing to install Excel.

For UiPath, this is a bit more involved. UiPath has both Classic and Modern Excel activities, but I'll focus on Classic. Classic has Workbook activities that do not need Excel installed, and Application activities that require Excel installed (using Excel Application Scope). The problem is that the Workbook activities only allow you to read and write xlsx files.

But thankfully there is a solution, conveniently included in UiPath.Excel.Activities. Two of its dependencies, ClosedXML and NPOI, can be used directly for advanced Excel interaction. ClosedXML is ideal for modifying modern xlsx files, and NPOI can modify legacy xls files. I recommend first converting xls files to xlsx whenever possible, so that you can add further changes in ClosedXML:

  • Use NPOI to read your xls file as an NPOI.HSSF.UserModel.HSSFWorkbook
  • Use the HSSFWorkbook to convert your sheet to a datatable
  • Use ClosedXML and your datatable to create a ClosedXML.Excel.XLWorkbook

Excel is just one example, but there are other external applications that you may be able to replace with packages. If you're working with PDF files, instead of installing and paying for Adobe Acrobat Pro, you could check if PDFsharp or RPA.PDF have what you need. Whatever your use case is, if you create lightweight bots, then you, your clients, and your coworkers will all benefit.