Skip to content
Zoan Docs

Excel Online — Read sheet

Excel Online — Read sheet

Section titled “Excel Online — Read sheet”

Type: excelonline-read-sheet  ·  Package: Excel Online v1.0.4  ·  Output: DataTable

Reads all the data in a sheet (its used range) and returns it as a DataTable. It is the easiest way to load a whole sheet without specifying a range.

How it works

Section titled “How it works”

It works on the session created with Excel Online — Connect. Pass it in the session parameter, or place this activity inside the body of Excel Online — Connect to inherit it automatically.

It asks Microsoft 365 for the sheet’s used range and turns it into a table. With has-headers, the first row is used as column names; otherwise columns are named Column1, Column2, …

Keep in mind:

  • The used range starts at the first cell with content or formatting, which is not always A1: if the data starts at B3, the table starts there. Cells that only have formatting (borders, color) also count as used and may show up as empty rows or columns.
  • Every column of the table is text: numbers and booleans arrive converted to string (with the decimal separator of the robot’s regional settings) and dates as their Excel serial number ("45458"). Convert with Convert.ToDecimal(...) or DateTime.FromOADate(...) when needed.
  • Empty cells arrive as empty text. If the first row has an empty cell, that column gets an automatic name.
  • If the sheet is empty, Microsoft 365 reports A1 as the used range: with has-headers = true you get a table with no rows.

It is read-only: it does not modify the workbook.

Required parameters

Section titled “Required parameters”
ParameterTypeDefaultDescription
sheetstring—Sheet name, as shown on the workbook tab.

Optional parameters

Section titled “Optional parameters”
ParameterTypeDefaultDescription
sessionZoanExcelOnlineSession—Excel Online — Connect session. Inherited inside Connect’s body.
has-headersboolfalseIf true, the first row holds the column names; otherwise columns are Column1, Column2, … and the first row is treated as data.

Output

Section titled “Output”

Returns a DataTable with all the sheet’s data: one row per Excel row (without the header row when has-headers = true) and as many columns as the used range is wide. Loop over it with a For each and access by column name (row["Customer"]), or pass it to other DataTable or write activities. The row count is in table.Rows.Count.

Common errors

Section titled “Common errors”
MessageCause and fix
No Excel Online session available. Provide 'session' from an excelonline-connect activity, or place this activity inside the Connect activity's body so the session is inherited automatically.The activity is outside Connect’s body and did not receive session. Place it inside the body or pass session = workbook.
'sheet' is required.sheet is empty or its expression returned null.
itemNotFound (Microsoft Graph, 404)There is no sheet with that name. Check the name with Get sheet names.
A column named '…' already belongs to this DataTable.With has-headers = true, two headers in the first row have the same name. Rename them in the workbook or read without headers.

To retry on Microsoft 365 throttling or temporary outages (429, 503), use Retry.

Example

Section titled “Example”
Excel Online — Connect   credential = credential("m365-finance")   path = "Reports/Customers.xlsx"
 └─ body:
      Excel Online — Read sheet   sheet = "Customers"   has-headers = true   → output: customers
      For each   customer in customers
       └─ Log   message = customer["Name"] + " - " + customer["Email"]
Section titled “Related activities”