Excel Activities

Version: 1.0.6  ·  ID: zoan-packages-excel  ·  Author: Zoan Software

The Excel package reads and writes Excel workbooks without Microsoft Excel installed: it works directly on the file, so it behaves the same on a Windows robot and on Zoan Cloud’s cloud robots. It is one of the most used packages in automation: almost every office process goes through a spreadsheet — reading a list, filling in a template, consolidating data, generating a report.

Before you start

There are two cross-cutting concepts worth understanding once: the two working modes (fast vs. session) and the three read/write levels (sheet, range, cell). We explain them here; each activity page assumes you know them.

Formats and requirements

Format	Read and write with the package	Convert to PDF (Excel to PDF)
.xlsx	Yes	Yes
.xlsm (macro-enabled)	Yes — macros are not run	Yes
.xltx / .xltm (templates)	Yes	Yes
.xls (Excel 97-2003)	No	Yes
.csv	No — use the CSV package	Yes
.ods	No	Yes

• No activity needs Microsoft Excel, except Excel to PDF, which needs LibreOffice or Microsoft Excel on the robot’s machine (Zoan Cloud’s cloud robots already include LibreOffice).
• Passing a .xls or .csv to a read or write activity fails with Extension 'xls' is not supported. Supported extensions are '.xlsx', '.xlsm', '.xltx' and '.xltm'. To work with a .xls, convert it to .xlsx first (for example, by saving it from Excel).
• Formulas are read as their result (not the formula text). What the package writes are values, not formulas.

Concept 1: fast mode vs. session mode

Almost every activity accepts one of two parameters to say which file to work on: path or session.

Fast mode (path)

You pass the file path directly to the activity. It opens the file, performs the operation, saves (when it writes) and closes it automatically. Ideal for a one-off operation.

Read Excel Sheet   path = asset("customers.xlsx")   → output: table

• Read activities (and Clear Excel Range) require the file to exist; otherwise they fail with File not found.
• Write activities create the file (and any missing folders) if it does not exist.

Session mode (session) — for several operations

If you are going to perform several operations on the same file, opening and closing it every time is slow. Instead, open it once with Excel Open, get a session (ZoanExcelSession), and pass it to the other activities through session. At the end, close it with Excel Close (which saves).

Excel Open   path = asset("report.xlsx")   → output: workbook
Write Excel Sheet   session = workbook   sheet = "Data"      data = table
Write Excel Cell    session = workbook   sheet = "Summary"   cell = "B2"   value = total
Excel Close  session = workbook

In session mode the workbook lives in memory: writes do not reach the disk until you save with Excel Save or Excel Close (or until the body of Excel Open ends).

Which one should I use?

• A single operation on the file → fast mode (path). Simpler.
• Several operations on the same file → session mode (session). More efficient, and every operation sees the others’ changes before saving.

Excel Open also offers a body: the activities inside it work on the workbook without setting session or path and, when they finish, it is saved and closed automatically — a convenient middle ground.

If you set both session and path, session wins. Inside the body of an Excel Open, an activity with neither session nor path uses the body’s workbook; if you give it a path, it works on that other file in fast mode.

Concept 2: sheet, range or cell

You can read and write at three levels of detail, depending on what you need:

Level	Read	Write	Data format
Sheet	Read Excel Sheet	Write Excel Sheet	DataTable with headers — the most convenient
Range	Read Excel Range	Write Excel Range	DataTable: when reading, with or without headers; when writing, values only
Cell	Read Excel Cell	Write Excel Cell	A single value, with its type

Start with the sheet

In most cases, Read Excel Sheet (which returns a DataTable with headers) is the most practical option: loop through it with For Each, read cells with str(row["Column"]), and send it to another destination without converting it.

How the data arrives

• DataTable read from Excel (Read Excel Sheet, Read Excel Range): since version 1.0.6 each cell keeps its type: numbers arrive as numbers (double), dates as dates (DateTime), booleans as booleans and text as text, regardless of the robot’s regional settings. A column that only has text (or is empty) is a text column; if it holds numbers, dates or booleans it accepts any value, so you can still assign text to it later. Empty cells are DBNull. That is why reading a sheet and writing it back keeps numbers and dates as numbers and dates in the file.
• Single cell (Read Excel Cell, and the value key of Find Excel Cell): keeps its type — number (double), date (DateTime), boolean or text; null when empty.
• When writing: numbers, dates and booleans are stored with their type; text is stored as text even if it looks like a number ("1500"). Write Excel Range and Append Excel Range also take lists: a list of rows (arr(arr("a", 1), arr("b", 2))) or a single row (arr("a", 1)). Null or DBNull values are not written: the cell keeps what it had.

Numbers in a DataTable

Use num(row["Total"]): it returns the cell’s number as is and 0 when it is empty. If the cell holds the number as text (a text-formatted cell in Excel, or a CSV), num reads "1500,5", "1.500,5" and "1500.5" correctly, and if the text is ambiguous ("1.500") it fails instead of returning a different number; then give the culture: num(row["Total"], "es-CO"). See how num reads a number written as text.

Cell and sheet notation

• Cells: A1 notation — A1, B3, AA10 (upper or lower case, no $).
• Ranges: A1:D50 (from the top-left cell to the bottom-right one). Avoid whole-column ranges such as A:A: they span 1,048,576 rows.
• Sheets: by name ("Data", case-insensitive) or by 1-based index (1 = first sheet). By default, the first sheet.

Sheets with numeric names

A sheet value that is a number — including the text "2024" — is always treated as an index, not a name. For a sheet called 2024, use its position (sheet = 3) or give it a name that is not just a number ("Year 2024").

Sheets that do not exist

• When reading (and in Clear Excel Range): the activity fails with There isn't a worksheet named '…'. (by name) or There isn't a worksheet associated with that position. (by index).
• When writing: the sheet is created. By name, with that name; by an index greater than the number of sheets, with the name Sheet<n> (for example Sheet5). Without sheet, the first sheet is used, or Sheet1 is created if the workbook is new.

Activities

Session (open, save, close)

Activity	Type	Output	What it does
Excel Open (Session)	excel-open	ZoanExcelSession	Opens a workbook and returns a session
Excel Save (Session)	excel-save	—	Saves the session to disk (or to another path)
Excel Close (Session)	excel-close	—	Saves and closes the session

Read

Activity	Type	Output	What it does
Read Excel Sheet	excel-read-sheet	DataTable	Reads a sheet into a DataTable
Read Excel Range	excel-read-range	DataTable	Reads an exact range into a DataTable
Read Excel Cell	excel-read-cell	object	Reads one cell
Get Excel Sheet Names	excel-get-sheet-names	List	Names of all sheets
Get Excel Used Range	excel-get-used-range	Dictionary	Dimensions of the area with data
Find Excel Cell	excel-find-cell	Dictionary	Finds the first cell with a value

Write

Activity	Type	Output	What it does
Write Excel Sheet	excel-write-sheet	—	Writes a DataTable to a sheet
Write Excel Range	excel-write-range	—	Writes values starting at a cell
Write Excel Cell	excel-write-cell	—	Writes a value to a cell
Append Excel Range	excel-append-range	—	Adds rows below the existing data
Append Excel Rows	excel-append-rows	—	Adds DataTable rows at the end (header only when empty)
Clear Excel Range	excel-clear-range	—	Clears the contents of a range or the sheet

Convert

Activity	Type	Output	What it does
Excel to PDF	excel-to-pdf	string	Converts a workbook to PDF (requires LibreOffice or Microsoft Excel)

A typical flow

Read a list, process it and write a report:

Read Excel Sheet   path = asset("orders.xlsx")   sheet = "Orders"   → output: orders

For Each   items = orders   itemVariable = row
 └─ activities:
      ... process each order ...

Write Excel Sheet   path = "C:/output/report.xlsx"   sheet = "Result"   data = orders

Common errors across the package

Message	Cause and fix
'session' or 'path' is required.	The activity received neither a session nor a path, and is not inside the body of an Excel Open. Set one of them.
File not found.	A fast-mode read points to a file that does not exist. Check the path, or validate first with File Exists.
Extension 'xls' is not supported. Supported extensions are '.xlsx', '.xlsm', '.xltx' and '.xltm'.	The file is .xls, .csv or another format. Convert it to .xlsx; for CSV use the CSV package.
There isn't a worksheet named '…'.	The sheet does not exist when reading. Check the name or list the sheets with Get Excel Sheet Names.
The process cannot access the file '…' because it is being used by another process.	The file is open in Excel (or another program) when the robot saves. Close it; in unattended processes, wrap the write in a Retry.

Next steps

• Excel Open — start working with a file in session mode.
• Read Excel Sheet — the most common read.
• Data types › DataTable — work with the data you read.