excel

Excel automation module for Total Control scripting system.

This module allows reading, writing, editing, and navigating Excel files (both .xls and .xlsx)
programmatically without launching Excel. It leverages Apache POI and provides an easy-to-use interface
for accessing and modifying worksheets, individual cells, and data ranges.

It supports:

  • Reading full Excel sheets or specific cells
  • Writing 2D arrays to new or existing sheets
  • Switching between sheets
  • Saving and exporting Excel files
  • Getting metadata like version, sheet names, and used ranges

Additionally, it includes utility functions like readExcel() and writeExcel() for quick one-off operations.
The Excel object provides a powerful, object-oriented interface for manipulating spreadsheets, while
abstracting POI complexities.

Features

  • Supports both .xls and .xlsx formats
  • Access any cell using A1 notation (e.g., "B2", "C3:D5")
  • Update cell values and save files
  • Retrieve workbook and worksheet objects for POI-level control
  • Retrieve metadata such as sheet names and version
  • Export specific ranges or entire worksheets as 2D arrays
  • Switch between sheets dynamically

Example

var { Excel } = require("sigma/excel");
var excel = new Excel("E:/File/test.xls", "Sheet1");
var name = excel.get("C2");
print("Name in cell C2: " + name);
excel.set("C2", "Updated Name");
excel.save("E:/File/test_updated.xls");
excel.close();

Classes

Excel

(inner) readExcel(fileName, sheetName) → {Array}

Read the contents of the excel document, support "xls" and "xlsx" formats.

Example
var { readExcel } = require('sigma/excel');
// Read content of worksheet "Sheet1" in excel document "E:/File/test.xls"
var arrayStr = readExcel('E:/File/test.xls', 'Sheet1');
if (arrayStr != null) {
    print("The contents of the Excel table are as follows:");
    for (var i = 0; i < arrayStr.length; i++) {
        for(var j = 0;j < arrayStr[i].length;j++) {
            print(arrayStr[i][j]);
        }
    }
} else {
    print("Failed to read! The error is: " + lastError());
}
Parameters:
string fileName

Excel file name.
string sheetName

Sheet name.
Returns:
Array

If successful, returns the contents of the Excel table(Two-dimensional array). If it fails, you can get the specific error message through the lastError() function.

(inner) writeExcel(excelName, sheetName, startColId, startRowId, dataArr) → {Boolean}

Writes the contents of the two-dimensional array to the specified position of the specified sheet in the excel. Two formats ".xls" and ".xlsx" are supported. Note that you can only write the contents of a two-dimensional array to a brand new sheet in an Excel file and a new sheet table is automatically created.

Example
var { writeExcel } = require('sigma/excel');
// Define the data to be written to the EXCEL table.
var a = ["a1", "a2", "a3"];
var b = ["b1", "b2", "b3", "b4"];
var c = ["c1", "c2", "c3", "c4", "c5"];
var arr=[];
arr[0] = a;
arr[1] = b;
arr[2] = c;
// Write the data in the two-dimensional array arr to the "Sheet1" table in the file "E:/File/test.xls", starting from column 2 and row 3 of the "Sheet1" table
var ret = writeExcel("E:/File/test.xls", "Sheet1", 1, 2, arr);
if (ret == true) {
  print("Successfully written to excel");
} else {
  print("Failed to write! The error is: " + lastError());
}
//If it executes successfully, it will return:
Successfully written to excel
Parameters:
string excelName

Excel file name.
string sheetName

Sheet name.
number startColId

Start writing the column number of the content. Note that the sequence number starts at 0, 3 indicates that the contents of the array will be written from the fourth column.
number startRowId

Start writing the row number of the content. Note that the sequence number starts at 0, 4 indicates that the contents of the array will be written from the fifth row.
array dataArr

Two-dimensional array. The data content of the excel table is about to be written, and each row of data members in the parameter does not need to have the same number.
Returns:
Boolean

It returns true if successful, or false if there was an error, specific error information can be obtained by the lastError() function.