Automating Excel using ClosedXml [Without Workbook & Excel Activities]

News Vote on Tutorials
, , ,
1

Working with Excel files is a core part of many automation projects. While UiPath offers built‑in Excel activities, sometimes you need more control, speed, or the ability to read an Excel file without having Excel installed.

That’s where ClosedXML becomes extremely useful.

1. What is ClosedXML?

ClosedXML is an open‑source .NET library that allows developers to read, write, and manipulate Excel files (.xlsx) without using Microsoft Excel or Interop.

Key characteristics

  • Works directly with OpenXML Excel files.
  • Fast, lightweight, and does not require Microsoft Office.
  • Supports:
    • Reading sheets, tables, cells, ranges
    • Creating and updating workbooks
    • Styling cells (fonts, colors, borders)
    • Formulas, data validation, and more

ClosedXML is widely used across .NET applications and integrates naturally into UiPath via Invoke Code, Invoke .NET Method or Assign activity.

2. Capabilities Relevant to UiPath Automation

When used inside UiPath, ClosedXML enables:

:check_mark: Reading Excel files without Excel

Useful for robots running unattended on servers where Excel is not installed.

:check_mark: Listing all sheet names quickly

A common requirement when validating input files or dynamically selecting a sheet.

:check_mark: High‑performance Excel processing

Especially helpful for:

  • Large Excel files (thousands of rows)
  • Multiple sheet manipulations
  • Automation scenarios requiring speed

:check_mark: Creating Excel files without using UiPath Excel activities

E.g., generating reports purely in code.

3. Prerequisites

Before using ClosedXML in UiPath Studio, ensure:

You have UiPath.Excel.Activities package installed which downloads the dependency - ClosedXML

image
image327×459 14.6 KB

4. How to Get Workbook Sheets Using ClosedXML in UiPath Studio

You can use Invoke Code activity and get everything done in a block of code but here I’m showing it using Assign activity which is very low code no code thing :smiley:

  • Declare two variables as below.
    One for Workbook Object and another to get Visible Sheets only.

    image

  • Assign - Workbook Object
    xlwbk_WorkbookObject = New ClosedXml.Excel.XLWorkbook("C:\Test Excel File.xlsx")

    image
    image481×127 4.25 KB

  • Get Visible Sheets only - Assign - Visible Sheets
    lst_VisibleSheets = xlwbk_WorkbookObject.Worksheets.AsEnumerable().Where(Function (sht) sht.Visibility = XLWorksheetVisibility.Visible).ToList()

    image
    image561×149 6.69 KB

  • Get Hidden Sheets Only
    You can control it using - XLWorksheetVisibility.Hidden

  • Get Very Hidden Sheets Only
    You can control it using - XLWorksheetVisibility.VeryHidden

  • Get All sheets including Hidden, Very Hidden, and Visible
    Declare a variable of Data Type - IXLWorksheets

    image
    image927×110 3.38 KB

    allSheets = xlwbk_WorkbookObject.Worksheets

    image
    image449×132 5.08 KB

  • Output:

    image
    image361×105 2.12 KB

  • The same result could be achieved using Invoke code:

    Dim workbook = New XLWorkbook(filePath)
Dim sheets As New List(Of String)

For Each ws In workbook.Worksheets
    sheets.Add(ws.Name)
Next

sheetNames = sheets

5. Tips and Best Practices

:check_mark: Use ClosedXML for unattended robots

It eliminates problems where Excel installation causes failures.

:check_mark: Always wrap file access in Try-Catch

ClosedXML throws exceptions if:

  • The file is locked
  • The file format is invalid
  • It’s not a real XLSX file

:check_mark: Use C# for best compatibility

ClosedXML examples are mostly in C#, making it easier to adapt code.

6. Limitation

  • ClosedXML does not support .xls.

7. ClosedXML Documentation

1. Official ClosedXML Documentation Site (Primary Docs)

This is the main and official documentation, maintained by the ClosedXML team.
It includes Quick Start, Concepts, Features, API reference, and migration guides.

:page_facing_up: Docs URL:
ClosedXML — ClosedXML 0.102.0 documentation
[docs.closedxml.io]

2. ClosedXML GitHub Repository (Source + Wiki + Docs Folder)

The GitHub repo contains:

  • Full source code
  • Examples
  • Docs in /docs folder
  • Wiki with how‑to guides
  • Issues, discussions, and migration notes

:page_facing_up: GitHub Repo:
GitHub - ClosedXML/ClosedXML: ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.
[github.com]

:blue_book: GitHub Wiki:
Home · ClosedXML/ClosedXML Wiki · GitHub
[github.com]

:page_facing_up: Docs folder in GitHub (Sphinx source):
ClosedXML/docs/index.rst at develop · ClosedXML/ClosedXML · GitHub
[github.com]

3. ClosedXML Official Website

This is a simpler documentation overview plus links to docs, NuGet, and examples.

:globe_with_meridians: https://closedxml.io
[closedxml.io]

4. ClosedXML ReadTheDocs Builds

Provides downloadable offline formats (PDF/ePub) for different versions.

:books: ClosedXML - Read the Docs Community
[readthedocs.org]

5. API Reference (Official)

Direct link to the API Index, including Workbook, Worksheet, Cell, Tables, etc.

:blue_book: API Index — ClosedXML 0.102.0 documentation

This opens lots of ways to automate your Excel files. Do explorer and share your thoughts.