Excel - sigma-rt

new Excel(file, sheetNameopt) → {Excel}

Excel object constructor, allowing operations on Excel files,Initializes with a file path and an optional sheet name, Create an Excel file object, supporting both "xls" and "xlsx" formats..

Example

// Example worksheet Sheet1:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 101 | Tom | 80.5 |
// | 2  | 102 | Ann | 90.0 |
// | 3  | 103 | Max | 75.5 |
// +----+-----+-----+------+

var { Excel } = require('sigma/excel');
// Create an xls format worksheet object and get the content of cell C2
var xls = new Excel("E:/File/test.xls","Sheet1");
var ret = xls.get("C2");
print("The content of cell C2 is: " + ret);
// Releases the Excel document
xls.close();
result: The content of cell C2 is: Ann

Parameters:

string	`file`		The path of the Excel file.
string	`sheetName`	<optional>	The name of the sheet to load. Defaults to the first sheet if not provided.

Returns:

Excel

An excel object that can be operated on or null if invalid.

Members

cells

Read data from the specified location in the worksheet, support "xls" and "xlsx" formats. You can get the data of a row or get the data of a cell in the worksheet; support Apache POI function call. Get the entire row of data in the excel table, for example: cells[5]; Get the data of the specified cell, for example: cells[0][5]; Note: Get the data for a blank cell in Excel and automatically fill it to null after getting it.

Example

// Example worksheet Sheet1:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 101 | Tom | 80.5 |
// | 2  | 102 | Ann | 90.0 |
// | 3  | 103 | Max | 75.5 |
// +----+-----+-----+------+

var { Excel } = require('sigma/excel');
// Create Excel object
var xls = new Excel('E:/File/test.xls', 'Sheet1');
// Get the data of the third row of the Excel worksheet sheet1
var ret = xls.cells[2];
// Output the data of the third row of the Excel worksheet
print("The data of the third row: " + ret);
for(var i=0; i<ret.length; i++) {
    var num=i+1;
    print("The "+num+" data of the array is: " + ret[i]);
}
// Releases the Excel document
xls.close();
//If it executes successfully, it will return:
The data of the third row: org.apache.poi.hssf.usermodel.HSSFSheet@328c3b7f
The 1 data of the array is: 103.0
The 2 data of the array is: Max
The 3 data of the array is: 75.5

close

Releases the Excel workbook and associated resources.

Example

var { Excel } = require('sigma/excel');
// Create Excel object
var xls = new Excel("E:/File/test.xls","Sheet2");
// Releases the Excel document
xls.close();

get

Retrieves the value of a specified cell or range of cells.

Example

// Example worksheet Sheet1:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 101 | Tom | 80.5 |
// | 2  | 102 | Ann | 90.0 |
// | 3  | 103 | Max | 75.5 |
// +----+-----+-----+------+

var { Excel } = require('sigma/excel');
// Create Excel object
var xls = new Excel('E:/File/test.xls', 'Sheet1');
// Get the content of "C2" in worksheet
var ret = xls.get("C2");
print("The content of cell C2 is: " + ret);
// Releases the Excel document
xls.close();
//If it executes successfully, it will return:
The content of cell C2 is: Ann

getSheet

Get the worksheet object in the excel workbook,Supports both "xls" and "xlsx" formats, also supports Apache POI function call. Note: The coordinates are counted from 0, and the first position in the upper left corner is [0][0].

Example

// Example worksheet Sheet1:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 101 | Tom | 80.5 |
// | 2  | 102 | Ann | 90.0 |
// | 3  | 103 | Max | 75.5 |
// +----+-----+-----+------+

var { Excel } = require('sigma/excel');
// Create Excel object
var xls = new Excel("E:/File/test.xls","Sheet1");
// Get the Excel worksheet object
var ret = xls.getSheet();
if (ret != null) {
    //POI API: getFirstRowNum(),getLastRowNum(),getCell(3),getRow(2);
    // Get the index of the first actual line
    var rowF = ret.getFirstRowNum();
    print("The index of the first actual line: " + rowF);
    // Get the index of the last actual line
    var rowL = ret.getLastRowNum();
    print("The index of the last actual line: " + rowL);
    // Get the 3rd row of the worksheet, the coordinates start from 0
    var row2 = ret.getRow(2);
    // Get the data in the 3rd row and 3th column of the worksheet, the coordinates are from 0
    var cells22 = row2.getCell(2);
    print("The data in the 3rd row and 3th column of the worksheet is: " + cells22);
    // Releases the Excel document
    xls.close();
} else {
    print(lastError());
}
//If it executes successfully, it will return:
The index of the first actual line: 0
The index of the last actual line: 2
The data in the 3rd row and 3th column of the worksheet is: 75.5

getSheetNames

Get all sheet names in the workbook.

Example

// Example workbook with 3 sheets:
// Sheet1, Sheet2, Summary

var { Excel } = require('sigma/excel');
var xls = new Excel('E:/File/test.xls');
var names = xls.getSheetNames();
print(names);
// Releases the Excel document
xls.close();
console.log(names); // ["Sheet1", "Sheet2", "Summary"]

getVersion

Get Excel version, support "xls" and "xlsx" formats.

Example

var { Excel } = require('sigma/excel');
// Create Excel object
var xls = new Excel('E:/File/test.xls', 'Sheet1');
// Get Excel version
var ret = xls.getVersion();
print("Excel version: "+ret);
// Releases the Excel document
xls.close();
// If it executes successfully, it will return:
Excel version: xls

getWorkbook

Gets the Excel workbook object, supports "xls" and "xlsx" formats, at the same time supports Apache POI function call.

Example

// Example worksheet Sheet1:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 101 | Tom | 80.5 |
// | 2  | 102 | Ann | 90.0 |
// | 3  | 103 | Max | 75.5 |
// +----+-----+-----+------+

// Example worksheet Sheet2:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 104 | Tom | 40.5 |
// | 2  | 105 | Ann | 50.0 |
// | 3  | 106 | Max | 65.5 |
// +----+-----+-----+------+

// Example worksheet Sheet3:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 107 | Tom | 10.5 |
// | 2  | 108 | Ann | 20.0 |
// | 3  | 109 | Max | 35.5 |
// +----+-----+-----+------+

var { Excel } = require('sigma/excel');
// Create Excel object
var xls = new Excel("E:/File/test.xls", "sheet1");
// Get Excel workbook object
var ret = xls.getWorkbook();
if (ret != null) {
    // Get the number of worksheets in test.xls
    var num = ret.getNumberOfSheets();
    print("Number Of Sheets：" + num );
} else {
    print(lastError());
}
// Releases the Excel document
xls.close();
// If it executes successfully, it will return:
Number Of Sheets：3

save

Saves the current state of the Excel workbook to a specified file. Note: the EXCEL document can be successfully saved only when it is closed and not opened by other software

Example

// Example worksheet Sheet1:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 101 | Tom | 80.5 |
// | 2  | 102 | Ann | 90.0 |
// | 3  | 103 | Max | 75.5 |
// +----+-----+-----+------+

var { Excel } = require('sigma/excel');
// Create Excel object
var xls = new Excel("E:/File/test.xls","Sheet1");
// Get the contents of row 2, column 3 of the worksheet
var cell = xls.cells[1][2];
cell.setCellValue("Test");
var ret = xls.save("E:/File/test.xls");
if (ret == true) {
     print("Save successfully");
} else {
     print("Sorry, Failed to save! The error is: " + lastError());
}
// Releases the Excel document
xls.close();

set

Sets the value of a specified cell.

Example

// Example worksheet Sheet1:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 101 | Tom | 80.5 |
// | 2  | 102 | Ann | 90.0 |
// | 3  | 103 | Max | 75.5 |
// +----+-----+-----+------+

var { Excel } = require('sigma/excel');
// Create Excel object
var xls = new Excel('E:/File/test.xls', 'Sheet1');
// Get the content of "C2" in worksheet
var cell = xls.get("C2");
// Change the content of "C2" to "Test"
var ret= xls.set("C2","Test");
if (ret != null) {
   print("Set value successfully");
   // Get the content of "C2" in worksheet
   var newCell = xls.get("C2");
   print("The content of C2 in worksheet: " + newCell);
   // Save changes to an Excel file in xls format
   xls.save("E:/File/test.xls");
} else {
   print("Sorry, Failed to set! The error is: " + lastError());
}
// Releases the Excel document
xls.close();
// If it executes successfully, it will return:
The content of C2 in worksheet: Ann
Set value successfully
The content of C2 in worksheet: Test
true

setSheet

Switch Excel worksheet, support "xls" and "xlsx" formats.

Example

// Example worksheet Sheet1:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 101 | Tom | 80.5 |
// | 2  | 102 | Ann | 90.0 |
// | 3  | 103 | Max | 75.5 |
// +----+-----+-----+------+

// Example worksheet Sheet2:

// +----+-----+-----+------+
// | A  | B   | C   | D    |
// +----+-----+-----+------+
// | 1  | 101 | TTT | 80.5 |
// | 2  | 102 | AAA | 90.0 |
// | 3  | 103 | CCC | 60.0 |
// +----+-----+-----+------+

var { Excel } = require('sigma/excel');
// Create Excel object
var xls = new Excel("E:/File/test.xls","sheet1");
// Get worksheet name
var sheetName = xls.getSheet().getSheetName();
// Get the content of cell "D3"
var cell = xls.get("D3");
print("Sheet Name: " + sheetName + ", Cell D3: " + cell);
var ret = xls.setSheet("Sheet2");
if (ret != null) {
    print("Change sheet successfully");
    // Get worksheet name
    var sheetNameNew = xls.getSheet().getSheetName();
    // Get the content of cell "D3"
    var cellNew = xls.get("D3");
    print("Sheet Name: " + sheetNameNew + ", Cell D3: " + cellNew);
} else {
    print("Sorry, Failed to change! The error is: " + lastError());
}
// Releases the Excel document
xls.close();
//If it executes successfully, it will return:
Sheet Name: Sheet1, Cell D3: 75.5
Change sheet successfully
Sheet Name: Sheet2, Cell D3: 60.0

exportRange(range) → {Array.<Array.<Object>>|null}

Export the value range by A1 notation (e.g., "A1:D10").

Example

// Example Excel content:

//   A       B       C       D
// +-------+-------+-------+--------+
// | ID    | Name  | Age   | Score  |
// | 101   | Tom   | 18    | 90     |
// | 102   | Ann   | 19    | 95     |
// | 103   | Max   | 20    | 88     |

var { Excel } = require('sigma/excel');
var xls = new Excel("E:/test.xls");
var data = xls.exportRange("A2:D4");

// Output: [
//   [Cell(ID), Cell(Name), Cell(Age), Cell(Score)],
//   [...],
//   [...]
// ]

Parameters:

string	`range`	A1 style range like "B2:E5".

Returns:

Array.<Array.<Object>> | null

A 2D array of cell values or null if error.

getUsedRange() → {Object}

Get the used data range in the worksheet.

Example

// Example Excel content:

//   A       B       C       D
// +-------+-------+-------+--------+
// | ID    | Name  | Age   | Score  |
// | 101   | Tom   | 18    | 90     |
// | 102   | Ann   | 19    | 95     |
// | 103   | Max   | 20    | 88     |
// +-------+-------+-------+--------+
var { Excel } = require('sigma/excel');
var xls = new Excel("E:/test.xls");
var range = xls.getUsedRange();
console.log(range); // { rows: 4, cols: 4 }

Returns:

Object

The number of rows and columns used.