Excel

Load

Load and parse an Excel spreadsheet. It can either return the entire spreadsheet or a selected range of cells. If the header option is set then the returned value will be be a map/object with the column names as keys - otherwise an array is used. If index is set then then values in the index column will be used as keys - otherwise an array is used. If both are set then both dimensions will use values as keys. See the examples below.

Parameters

• file path for an Excel spreadsheet to load
• options options for parsing the spreadsheet - use {} to return the entire spreadsheet
  • table define a table to return
    • range which range does the table reside in e.g. 'A1:D20'
    • header is a boolean to determine if the top row of the table is a header
    • index is a boolean to determine if the initial column is an index column
    • worksheet is the name of the sheet to load data from

Example with simple table

Given the following simple spreadsheet in the worksheet named ‘Sheet1’:

cell 1	cell 2
cell 3	cell 4

The following code will load the spreadsheet and pick out the value stored at cell1.

var table = Excel.load('myspreadsheet.xlsx', {});
var cell1 = table["Sheet1"][0][0];

Example with table with header defined by range

Given the table below, situated in worksheet “Sheet1” at A1:B3:

header 1	header 2
cell 1	cell 2
cell 3	cell 4

Use the following code to pick out cell4.

var table = Excel.load('myspreadsheet.xlsx', { table: { range: 'A1:B3', worksheet: 'Sheet1', header: true } });
var cell4 = table[2]['header 2']; // 3rd row (0 is first row), column with header 'header 2'

Example with both header and index

Given the table below, situated in worksheet “Sheet1” at A1:B3:

	header 1	header 2
I1	cell 1	cell 2
I2	cell 3	cell 4

Use the following code to pick out cell2.

var table = Excel.load('myspreadsheet.xlsx', { table: { range: 'A1:C4', worksheet: 'Sheet1', header: true, index: true } });
var cell2 = table['I1']['header 2'];

Delete a sheet

Removes a single sheet from the workbook.

Parameters

• filename the path to the excel file to be updated
• sheet the name of the sheet to delete

Example

Excel.deleteSheet('data.xlsx', 'Sheet1');

Update single cell

Update the value stored in a single cell in a spreadsheet.

Parameters

• filename the path to the excel file to be updated - if the file does not exist a new one will be created
• sheet the name of the sheet to update
• address an “address” to a cell, e.g. “A1”
• value the value to write into the cell
• options is an object which may contain the following properties:
  • formula (bool) to indicate that the value is a formula (not a scalar value)

Example

// write 1000 into A3 of Sheet1 in data.xlsx
Excel.updateCell('data.xlsx', 'Sheet1', 'A3', 1000);

Add a formula to A4:

Excel.updateCell('data.xlsx', 'Sheet1', 'A4', '=A3+100', { formula: true });

Update multiple cells

Update values stored in a spreadsheet. This method is a lot more performant than the single cell version if you need to store multiple values.

Parameters

• filename the path to the excel file to be updated - if the file does not exist a new one will be created
• sheet the name of the sheet to update
• address an “address” of the starting cell
• values the valued to write into the cells - this should be a 2 dimensional array (like a table)

Example

// The data to write
var data = [
  [10, 20, 30],
  [40, 50, 60]
];
// write data into data.xlsx, Sheet1 starting at A1
Excel.updateCells('data.xlsx', 'Sheet1', 'A1', data);

This will result in a table that looks like:

	A	B	C
1	10	20	30
2	40	50	60

Deleting rows and columns from a sheet

You can delete a single, multple or a range of rows from a sheet with the deleteRows method.

// Delete a *single* row - row 100
Excel.deleteRows('data.xlsx', 'Sheet1', 100);
// Delete *multiple* rows, rows 100, 150 and 155
Excel.deleteRows('data.xlsx', 'Sheet1', [100, 150, 155]);
// Detele a range of rows, rows from 100 to 150
Excel.deleteRows('data.xlsx', 'Sheet1', { from: 100, count: 50 });

Deleting columns is done with the deleteColumns method with the same semantics as above.