• XLS.XLS
• XLS.autoSizeColumn
• XLS.close
• XLS.getCell
• XLS.getCellStyler
• XLS.getCellValue
• XLS.getCellValueRaw
• XLS.getCellValues
• XLS.getDataFormat
• XLS.getNewDataFormat
• XLS.getNewFont
• XLS.getSheet
• XLS.getTable
• XLS.getWorkbook
• XLS.setCell
• XLS.setColumnWidth
• XLS.setDataFormat
• XLS.setJSON
• XLS.setRowHeight
• XLS.setTable
• XLS.toDate
• XLS.toName
• XLS.toNumber
• XLS.writeFile

XLS.XLS(aObject) : XLS

Creates a new instance. You can optionally provide a filename to load or use as a template and/or an array of bytes (aObject). Example:

var xls = new XLS("c:/test.xlsx");

XLS.autoSizeColumn(aSheet, aColumn, useMergedCells)

Auto-sizes the given column on the given sheet. Optionally you can indicate to use merged cells on the auto-size operation. Example:

var xls = new XLS("spreadsheet.xlsx");
var sheet = xls.getSheet("Sheet4");
\  xls.autoSizeColumn(sheet, "B");
xls.writeFile("spreadsheet.xlsx");

XLS.close()

Closes the current workbook.

XLS.getCell(aSheet, aColumn, aRow, evaluateFormulas) : Object

Returns the value, on a given sheet (aSheet), located on the given column and row. Optionally the evaluated formula can be provided also. Example:

var xls = new XLS("spreadsheet.xlsx");
var sheet = xls.getSheet("Sheet4");

print(xls.getCell(sheet, "B", 3, true));
// {
//    "type": "FORMULA",
//    "formula": "=B1+B2",
//    "val": 34
// }

XLS.getCellStyler() : Object

Returns the current workbook CellStyle Java object to use to build cell styles.

XLS.getCellValue(aSheet, aColumn, aRow, evaluateFormulas) : Object

Returns the raw value from a given sheet (aSheet) on the given column and row. Optionally the evaluated formula can be provided also. Example:

var xls = new XLS("spreadsheet.xlsx");
var sheet = xls.getSheet("Sheet4");

print(xls.getCellValue(sheet, "B", 3, true));
// 34

XLS.getCellValueRaw(aXSSFCellObject, isFormulaEvaluated, shouldEvaluateFormula) : Object

Provides a low level conversion of the value of a cell given the corresponding XSSFCell Java object. Optionally you can provide the indication if the formula has been evaluated or not and if it should be re-evaluated.

XLS.getCellValues(aSheet, shouldEvaluateFormulas, startOnX, startOnY) : Object

Returns a map indexed by row and column with all the values and types on the current memory excel representation.  Example:

var excel = xls.getCellValues(sheet, true);
\  print(beautifier(excel[2]["C"]));
// { "type": "STRING", "val": "Example value" }

XLS.getDataFormat() : String

Gets the format conversion currently being use.

XLS.getNewDataFormat() : Object

Returns a new Java Data Format object to be used with CellStyle

XLS.getNewFont() : Object

Returns a new Java Font object to be used with CellStyle.

XLS.getSheet(aSheetRef) : Object

Returns the Apache Java POI Sheet object to be used together with other plugin functions whenever an operation  should be performed on this specific sheet (aSheetRef as a name or number). If the sheet doesn't exist it will try to create it. Example:

var xls = new XLS();
var sheet = xls.getSheet("Sheet4");

XLS.getTable(aSheet, shoudEvaluateFormulas, startOnColumn, startOnRow) : Object

Tries to retrieve JSON array representation of a table from the provided sheet starting on the provided column and row. Optionally existing formulas values can be evaluated so the result will be the value included in the returned JSON array. Example:

//        A        B         C
// 1   FieldA    FieldB    FieldC  
// 2     123       ON       =A2+1  
// 3     120       OFF      =A3+1

var tab = xls.getTable(sheet, true, "A", 1);
print(beautifier(tab));

// {
//    "table": [
//       { "FieldA": 123, "FieldB": "ON", "FieldC": 124 },
//       { "FieldA": 120, "FieldB": "OFF", "FieldC": 121 }
//    ]
// }

XLS.getWorkbook() : Workbook

Returns the Apache Java POI Workbook object used internally.

XLS.setCell(aSheet, aColumn, aRow, aValue, aStyle)

Sets a cell with the provided value given a sheet, column and row. Optionally a CellStyle Java object can be provided. Example:

xls.setCell(sheet, "B", 5, "An example");

XLS.setColumnWidth(aSheet, aColumn, aWidth)

Sets the column width of a column of the provided sheet.

XLS.setDataFormat(aNewDataFormat)

Sets the format conversion for date values (e.g. yyyy-m-d h:mm:ss).

XLS.setJSON(aSheet, aColumn, aRow, aMap, shouldFormatCells, aStyle) : Number

Provides a representation of a JSON map starting on the column and row of the provided sheet. Optionally it can perform minimal cell formatting by merging cells and correcting vertical alignment. A cell style to apply to every cell can also optionally be provided. Example:

var a = af.exec("DM.GetAllEntities");

xls.setJSON(sheet, "B", 2, a, true);

XLS.setRowHeight(aSheet, aRow, aHeight)

Sets the row height for a row of the provided sheet.

XLS.setTable(aSheet, aColumn, aRow, anArray, keyStyler, lineStyler)

Provides a tabular representation of a JSON array starting on the column and row of the provided sheet. Optionally you can provide a cell style for the headings (keyStyler) and lines (lineStyler). Example:

var arr = [
{ "FIELD A": 1, "FIELD B": 2, "FIELD C": "OFF" },
{ "FIELD A": 2, "FIELD B": 2, "FIELD C": "ON" }
];

xls.setTable(sheet, "B", 2, arr);

XLS.toDate(aDateValue) : String

Converts a excel date value into a string given the data format (see getDataFormat())

XLS.toName(aNumber) : String

Converts a column number into the corresponding column letter combination. Example:

var xls = new XLS();
xls.toName(32); // AF

XLS.toNumber(aName) : Number

Converts a column letter combination into the corresponding column number. Example:

var xls = new XLS();
xls.toNumber("AF"); // 32

XLS.writeFile(aFilename)

Writes the memory excel instance into a file. Example:

xls.writeFile("spreadsheet.xlsx");