Skip to content

xls

Jump to bottom
Nuno Aguiar edited this page Dec 29, 2017 · 2 revisions

XLS.XLS

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

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

XLS.close()

Closes the current workbook.

XLS.getCell

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

XLS.getCellStyler() : Object

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

XLS.getCellValue

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

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

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

XLS.getDataFormat() : String

Gets the format conversion currently being use.

XLS.getNewDataFormat

XLS.getNewDataFormat() : Object

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

XLS.getNewFont

XLS.getNewFont() : Object

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

XLS.getSheet

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

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

XLS.getWorkbook() : Workbook

Returns the Apache Java POI Workbook object used internally.

XLS.setCell

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

XLS.setColumnWidth(aSheet, aColumn, aWidth)

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

XLS.setDataFormat

XLS.setDataFormat(aNewDataFormat)

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

XLS.setJSON

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

XLS.setRowHeight(aSheet, aRow, aHeight)

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

XLS.setTable

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

XLS.toDate(aDateValue) : String

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

XLS.toName

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

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

XLS.writeFile(aFilename)

Writes the memory excel instance into a file. Example:

xls.writeFile("spreadsheet.xlsx");

Clone this wiki locally