Microsoft Excel Files
XLSX files are files used in Microsoft Excel, a spreadsheet application that uses tables to organize, analyze, and store data. Each cell can contain text or numerical data, including incorporating mathematical formulas. Foldr can read and write data from XLSX files.
Under the hood MaSH uses the SimpleXLSX and SimpleXLSXGen packages from Sergey Shuchkin. You can read more about the package here.
Reading XLSX Files
To read an XLSX file we call the read method on the mash.xlsx object. This returns an instance of the SimpleXLSX object.
read
mash.xlsx.read(File: file) -> SimpleXLSX
Creates a SimpleXLSX object.
Parameters
file
A Foldr file object whose content will be parsed as a XLSX document.
Natural
set file to mash.file(12, "Spreadsheets/Data.xlsx")
set xlsx to mash.xlsx.read(file)
each xlsx.readRows(1) as row
printline row
endStandard
file = mash.file(12, "Spreadsheets/Data.xlsx")
xlsx = mash.xlsx.read(file)
each xlsx.readRows(1) as row
printline(row)
endOutput
Array [
"OrderDate",
"Region",
"Rep",
"Item",
"Units",
"Unit Cost",
"Total"
]
Array [
"2021-01-06 00:00:00",
"East",
"Jones",
"Pencil",
95,
1.99,
189.05
]
Array [
"2021-01-23 00:00:00",
"Central",
"Kivell",
"Binder",
50,
19.99,
999.4999999999999
]
Array [
"2021-02-09 00:00:00",
"Central",
"Jardine",
"Pencil",
36,
4.99,
179.64000000000001
]
Array [
"2021-02-26 00:00:00",
"Central",
"Gill",
"Pen",
27,
19.99,
539.7299999999999
]
Array [
"2021-03-15 00:00:00",
"West",
"Sorvino",
"Pencil",
56,
2.99,
167.44
]
Array [
"2021-04-01 00:00:00",
"East",
"Jones",
"Binder",
60,
4.99,
299.40000000000003
]
Array [
"2021-04-18 00:00:00",
"Central",
"Andrews",
"Pencil",
75,
1.99,
149.25
]
Array [
"2021-05-05 00:00:00",
"Central",
"Jardine",
"Pencil",
90,
4.99,
449.1
]
Array [
"2021-05-22 00:00:00",
"West",
"Thompson",
"Pencil",
32,
1.99,
63.68
]
Array [
"2021-06-08 00:00:00",
"East",
"Jones",
"Binder",
60,
8.99,
539.4
]
Array [
"2021-06-25 00:00:00",
"Central",
"Morgan",
"Pencil",
90,
4.99,
449.1
]
Array [
"2021-07-12 00:00:00",
"East",
"Howard",
"Binder",
29,
1.99,
57.71
]
Array [
"2021-07-29 00:00:00",
"East",
"Parent",
"Binder",
81,
19.99,
1619.1899999999998
]
Array [
"2021-08-15 00:00:00",
"East",
"Jones",
"Pencil",
35,
4.99,
174.65
]
Array [
"2021-09-01 00:00:00",
"Central",
"Smith",
"Desk",
2,
125,
250
]
Array [
"2021-09-18 00:00:00",
"East",
"Jones",
"Pen Set",
16,
15.99,
255.84
]
Array [
"2021-10-05 00:00:00",
"Central",
"Morgan",
"Binder",
28,
8.99,
251.72
]
Array [
"2021-10-22 00:00:00",
"East",
"Jones",
"Pen",
64,
8.99,
575.36
]
Array [
"2021-11-08 00:00:00",
"East",
"Parent",
"Pen",
15,
19.99,
299.84999999999997
]
Array [
"2021-11-25 00:00:00",
"Central",
"Kivell",
"Pen Set",
96,
4.99,
479.04
]
Array [
"2021-12-12 00:00:00",
"Central",
"Smith",
"Pencil",
67,
1.29,
86.43
]
Array [
"2021-12-29 00:00:00",
"East",
"Parent",
"Pen Set",
74,
15.99,
1183.26
]
Array [
"2022-01-15 00:00:00",
"Central",
"Gill",
"Binder",
46,
8.99,
413.54
]
Array [
"2022-02-01 00:00:00",
"Central",
"Smith",
"Binder",
87,
15,
1305
]
Array [
"2022-02-18 00:00:00",
"East",
"Jones",
"Binder",
4,
4.99,
19.96
]
Array [
"2022-03-07 00:00:00",
"West",
"Sorvino",
"Binder",
7,
19.99,
139.92999999999998
]
Array [
"2022-03-24 00:00:00",
"Central",
"Jardine",
"Pen Set",
50,
4.99,
249.5
]
Array [
"2022-04-10 00:00:00",
"Central",
"Andrews",
"Pencil",
66,
1.99,
131.34
]
Array [
"2022-04-27 00:00:00",
"East",
"Howard",
"Pen",
96,
4.99,
479.04
]
Array [
"2022-05-14 00:00:00",
"Central",
"Gill",
"Pencil",
53,
1.29,
68.37
]
Array [
"2022-05-31 00:00:00",
"Central",
"Gill",
"Binder",
80,
8.99,
719.2
]
Array [
"2022-06-17 00:00:00",
"Central",
"Kivell",
"Desk",
5,
125,
625
]
Array [
"2022-07-04 00:00:00",
"East",
"Jones",
"Pen Set",
62,
4.99,
309.38
]
Array [
"2022-07-21 00:00:00",
"Central",
"Morgan",
"Pen Set",
55,
12.49,
686.95
]
Array [
"2022-08-07 00:00:00",
"Central",
"Kivell",
"Pen Set",
42,
23.95,
1005.9
]
Array [
"2022-08-24 00:00:00",
"West",
"Sorvino",
"Desk",
3,
275,
825
]
Array [
"2022-09-10 00:00:00",
"Central",
"Gill",
"Pencil",
7,
1.29,
9.030000000000001
]
Array [
"2022-09-27 00:00:00",
"West",
"Sorvino",
"Pen",
76,
1.99,
151.24
]
Array [
"2022-10-14 00:00:00",
"West",
"Thompson",
"Binder",
57,
19.99,
1139.4299999999998
]
Array [
"2022-10-31 00:00:00",
"Central",
"Andrews",
"Pencil",
14,
1.29,
18.060000000000002
]
Array [
"2022-11-17 00:00:00",
"Central",
"Jardine",
"Binder",
11,
4.99,
54.89
]
Array [
"2022-12-04 00:00:00",
"Central",
"Jardine",
"Binder",
94,
19.99,
1879.06
]
Array [
"2022-12-21 00:00:00",
"Central",
"Andrews",
"Binder",
28,
4.99,
139.72
]Writing XLSX Files
To create an XLSX file we call the create method on the mash.xlsx object. This returns an instance of the SimpleXLSXGen object.
create
mash.xlsx.create(Array|Collection: data) -> SimpleXLSXGen
Creates a SimpleXLSXGen object.
Parameters
data
An array or collection containing data for the XLSX document.
Natural
set outputFolder to mash.file(12, "Spreadsheets")
set header to ['first name', 'last name', 'email']
set records to [
header,
['grace', 'hopper', '[email protected]'],
['ada', 'lovelace', '[email protected]'],
]
set xlsx to mash.xlsx.make(records)
set xlsxFile to outputFolder.write("New Users.xlsx", xlsx)
printline xlsxFileStandard
outputFolder = mash.file(12, "Spreadsheets")
header = ['first name', 'last name', 'email']
records = [
header,
['grace', 'hopper', '[email protected]'],
['ada', 'lovelace', '[email protected]'],
]
xlsx = mash.xlsx.make(records)
xlsxFile = outputFolder.write("New Users.xlsx", xlsx)
printline(xlsxFile)Output
File {
"name": "New Users.xlsx",
"path": "Spreadsheets/New Users.xlsx",
"modified": "2022-03-10T12:29:36+00:00",
"size": 3992,
"is_dir": false,
"mimetype": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"hidden": false,
"extra": [],
"share": {
"id": 12,
"name": "Office Docs"
}
}