XLSX

mash.xlsx · Files & Storage

mash.xlsx reads the values out of an Excel workbook and builds a new one from rows of data. It’s the quick way to turn a spreadsheet someone sent you into rows a script can use, or to hand back a simple report. To fill in an existing workbook and keep its charts, formatting and formulas, use mash.sheet instead.

How it works

read opens a workbook you already have. Give it a File. A Google Sheet is exported first. You get an object you can ask for rows(0), the rows of the first sheet, as lists of cell values, and sheetNames(). It reads values only. A formula’s last saved result is what you get, with no recalculation.

create builds a new workbook in memory. Pass rows (a list of lists) and, if you like, a name for the sheet. make does the same thing. The result is not a file yet. Write it with a folder’s write, as you would any other content.

A workbook is a new file, not an edit. create never changes an existing workbook, and the result has no formatting beyond what the data gives it.

Common tasks

Read a sheet and use its header row:

set book to mash.xlsx.read(mash.files.get("Finance", "Imports/prices.xlsx"))
set rows to mash.collect(book.rows(0))
set header to rows.first()
printline "Columns: {{mash.collect(header).join(', ')}}"
each rows.slice(1) as row
    printline "{{row[0]}} costs {{row[1]}}"
end

Build a report and save it:

set book to mash.xlsx.create([
    ["Item", "Qty"],
    ["Bolt", 12],
    ["Nut", 40]
], "Stock")
set folder to mash.files.get("Finance", "Reports")
folder.write("stock.xlsx", book)

Watch out for

• read fails on an empty or unreadable file. It raises “failed to get file contents”.
• Read a workbook with several sheets by index. rows(0) is the first, rows(1) the second.
• Don’t add a row by reading a workbook and writing a new one. Two scripts doing it at once lose rows, and formatting is lost. For an existing workbook use mash.sheet.

See also

mash.csv for CSV, Collections, and Files and folders.

Methods

create

xlsx.create(data?: array = [[]], sheetName?: string)

Create a new Excel file from data, optionally naming the first sheet

make

xlsx.make(data?: array = [[]], sheetName?: string)

Create a new Excel file from data (alias)

read

xlsx.read(file: File)

Read an Excel (.xlsx) file