Full API Reference

Classes

DataSetExcelWorkbookServoyExcelWorkbook

ExcelWorkbook

FoundSetExcelWorkbookServoyExcelWorkbook

Members

copies : Number

The number of copies

draft : Boolean

Whether it is in draft mode

fitHeight : Number

The number of pages high to fit the sheet in

fitWidth : Number

The number of pages high to fit the sheet in

landscape : Boolean

Whether to print in landscape

mergedRegionType : Object

noColor : Boolean

Whether it is black and white

paperSize : Number

The paper size

Functions

createPrintSetup()PrintSetup

Creates a PrintSetup object that can be used in ExcelSheet.setPrintSetup() or to set the default print setup used when workbooks are created from FoundSet or DataSet

createWorkbook([templateOrFileType])ExcelWorkbook

Returns an empty ExcelWorkbook

createWorkbookFromDataSet(dataset, [columns], [headers], [templateOrFileType], [sheetNameToUse])DataSetExcelWorkbook

Creates an ExcelWorkbook from the given dataset

If a template is provided, the dataset will be inserted in the given sheet

createWorkbookFromFoundSet(foundset, dataproviders, [headers], [templateOrFileType], [sheetNameToUse])FoundSetExcelWorkbook

Creates an ExcelWorkbook from the given foundset

If a templateOrFileType is provided, the foundset will be inserted in the given sheet

getCellReferenceFromRange(firstRow, lastRow, firstColumn, lastColumn)String

Creates a cell reference (e.g. "A4:C92") from the given range

getRangeFromCellReference(cellReference)Object

Converts a cell reference (e.g. "B4:AK234" or "C6") to an object holding first and last row and column

getWorkbook(original)ExcelWorkbook

Returns an ExcelWorkbook from the given file or media URL

isLoaded()Boolean

If true, all required libraries are present and the scope can be used

set()

setDefaultPrintSetup(setup)

Sets the default print setup used when workbooks are created from FoundSet or DataSet

DataSetExcelWorkbook ⇐ ServoyExcelWorkbook

Extends: ServoyExcelWorkbook

DataSetExcelWorkbook~dataFilled : Boolean

dataSetExcelWorkbook.cloneCellStyle(cellStyle) ⇒ ExcelCellStyle

Clones the given ExcelCellStyle

ParamType

cellStyle

dataSetExcelWorkbook.cloneFont(font) ⇒ ExcelFont

Clones the given font and returns a new ExcelFont

ParamType

font

dataSetExcelWorkbook.cloneSheet(indexToClone) ⇒ ExcelSheet

Creates an ExcelSheet from an existing sheet in the Workbook

Returns: ExcelSheet - clone

ParamTypeDescription

indexToClone

Number

one based

dataSetExcelWorkbook.close()

Closes this workbook

dataSetExcelWorkbook.createCellStyle() ⇒ ExcelCellStyle

Creates an empty ExcelCellStyle

dataSetExcelWorkbook.createColumnStyle(columnIndex) ⇒ ExcelCellStyle

Creates and returns an ExcelCellStyle used for a specific column

ParamType

columnIndex

Number

dataSetExcelWorkbook.createFont() ⇒ ExcelFont

Creates a font

dataSetExcelWorkbook.createHeaderStyle() ⇒ ExcelCellStyle

Creates and returns an ExcelCellStyle used for the header row

dataSetExcelWorkbook.createRowStyle() ⇒ ExcelCellStyle

Creates and returns an ExcelCellStyle used for a data row

dataSetExcelWorkbook.createSheet(sheetName) ⇒ ExcelSheet

Creates a sheet with the given name

This method makes sure that no illegal names are provided and might change the name if needed

ParamType

sheetName

String

dataSetExcelWorkbook.fillData()

Fills the sheet with the data of the foundset

This is automatically done when writeToFile() or getBytes() is called

Overrides: fillData

dataSetExcelWorkbook.getBytes() ⇒ [ 'Array' ].<byte>

Returns the data of this workbook as a byte[]

Returns: [ 'Array' ].<byte> - bytes

dataSetExcelWorkbook.getDataSet() ⇒ JSDataSet

Returns the dataset used to create this workbook

dataSetExcelWorkbook.getNumberOfSheets() ⇒ Number

Returns the number of spreadsheets in the workbook

dataSetExcelWorkbook.getSheet(sheetName) ⇒ ExcelSheet

Returns the sheet with the given name (case insensitive match)

ParamType

sheetName

String

dataSetExcelWorkbook.getSheetAt(index) ⇒ ExcelSheet

Returns the ExcelSheet object at the given index

ParamType

index

Number

dataSetExcelWorkbook.getSheetNameAt(index) ⇒ String

Returns the name of the sheet at the given index

Returns: String - sheetName

ParamType

index

Number

dataSetExcelWorkbook.getSheetNames() ⇒ [ 'Array' ].<String>

Returns all sheet names

dataSetExcelWorkbook.removeSheetAt(index)

Removes the sheet at the given index

ParamType

index

Number

dataSetExcelWorkbook.setFormatForColumn(columnIndex, format)

Sets a date or number format used for the given column

ParamType

columnIndex

Number

format

String

dataSetExcelWorkbook.setSheetNameAt(index, name)

Sets the sheet name

ParamType

index

Number

name

String

dataSetExcelWorkbook.writeToFile(targetFile) ⇒ Boolean

Writes this workbook to the given file

Returns: Boolean - success

ParamType

targetFile

String | plugins.file.JSFile

dataSetExcelWorkbook.autoSizeColumns : Boolean

Whether or not all data columns should be auto sized

dataSetExcelWorkbook.columnFormats : [ 'Array' ].<String>

dataSetExcelWorkbook.columnStyles : [ 'Array' ].<ExcelCellStyle>

dataSetExcelWorkbook.dataset : JSDataSet

The dataset used to create this workbook

dataSetExcelWorkbook.defaultDateFormat : String

The default format used to format date values

This can be overriden for specific columns by calling setFormatForColumn()

dataSetExcelWorkbook.defaultNumberFormat : String

The default format used to format number values

This can be overriden for specific columns by calling setFormatForColumn()

dataSetExcelWorkbook.freezeFirstRow : Boolean

Whether the header row is frozen or not

Overrides: freezeFirstRow

dataSetExcelWorkbook.headerStyle : ExcelCellStyle

The style used for the header of the data

Overrides: headerStyle

dataSetExcelWorkbook.rowStyle : ExcelCellStyle

The style used for a data cell

Overrides: rowStyle

dataSetExcelWorkbook.setAutoFilter : Boolean

Whether or not the data columns should be auto filtered or not

Overrides: setAutoFilter

dataSetExcelWorkbook.sheet : ExcelSheet

The ExcelSheet used or created

dataSetExcelWorkbook.sheetName : String

The name of the sheet to be used

When a template is used, data will be inserted in the sheet with this name or the first best if not found

dataSetExcelWorkbook.startColumn : Number

The first column where data will be inserted (one based)

dataSetExcelWorkbook.startRow : Number

The first row where data will be inserted (one based)

dataSetExcelWorkbook.wb : Packages.org.apache.poi.ss.usermodel.Workbook

The internal workbook object

dataSetExcelWorkbook.workbook : ExcelWorkbook

The ExcelWorkbook created

new DataSetExcelWorkbook(dataset, [columns], [headers], [templateOrFileType], [sheetNameToUse])

A DataSet based Excel workbook

ParamTypeDescription

dataset

JSDataSet

the dataset

[columns]

[ 'Array' ].<Number>

the column numbers to be included in the sheet

[headers]

[ 'Array' ].<String>

the text to be used as column headers

[templateOrFileType]

String | plugins.file.JSFile | Number

either file or media URL pointing to an existing Excel to be used as template or one of the FILE_FORMAT constants when creating empty workbooks

[sheetNameToUse]

String

when a template is used, this is the name of the sheet to be filled

Example

var query = datasources.db.example_data.orders.createSelect();
query.result.add(query.columns.customerid);	
query.result.add(query.columns.shipname);
query.result.add(query.columns.shipaddress);
query.result.add(query.columns.shipcity);
query.result.add(query.columns.shipcountry);
query.result.add(query.columns.shippeddate);
query.result.add(query.columns.freight);	
var dataset = databaseManager.getDataSetByQuery(query, -1);

var wb = scopes.svyExcelUtils.createWorkbookFromDataSet(dataset, [2,3,4,5,6,7], ["Company", "Address", "City", "Country", "Order date", "Freight"]);

wb.setFormatForColumn(5, "yyyy-MM-dd");
wb.setFormatForColumn(6, "#,##0.00");
wb.sheetName = "Dataset export";
wb.autoSizeColumns = true;
wb.freezeFirstRow = true;
wb.setAutoFilter = true;

var headerStyle = wb.createHeaderStyle();
headerStyle.setFont("Calibri,1,12");
headerStyle.setFillForegroundColor(scopes.svyExcelUtils.INDEXED_COLOR.LIGHT_CORNFLOWER_BLUE);
headerStyle.setFillPattern(scopes.svyExcelUtils.FILL_PATTERN.SOLID_FOREGROUND);

wb.writeToFile("d:\\dataset.xls");

ExcelWorkbook

Suppresswarnings(deprecated): needs to be added to prevent warnings from deprecated WorkbookFactory.create(Object)

excelWorkbook.cloneCellStyle(cellStyle) ⇒ ExcelCellStyle

Clones the given ExcelCellStyle

ParamType

cellStyle

excelWorkbook.cloneFont(font) ⇒ ExcelFont

Clones the given font and returns a new ExcelFont

ParamType

font

excelWorkbook.cloneSheet(indexToClone) ⇒ ExcelSheet

Creates an ExcelSheet from an existing sheet in the Workbook

Returns: ExcelSheet - clone

ParamTypeDescription

indexToClone

Number

one based

excelWorkbook.close()

Closes this workbook

excelWorkbook.createCellStyle() ⇒ ExcelCellStyle

Creates an empty ExcelCellStyle

excelWorkbook.createFont() ⇒ ExcelFont

Creates a font

excelWorkbook.createSheet(sheetName) ⇒ ExcelSheet

Creates a sheet with the given name

This method makes sure that no illegal names are provided and might change the name if needed

ParamType

sheetName

String

excelWorkbook.getBytes() ⇒ [ 'Array' ].<byte>

Returns this workbook as a byte[] array

excelWorkbook.getNumberOfSheets() ⇒ Number

Returns the number of spreadsheets in the workbook

excelWorkbook.getSheet(sheetName) ⇒ ExcelSheet

Returns the sheet with the given name (case insensitive match)

ParamType

sheetName

String

excelWorkbook.getSheetAt(index) ⇒ ExcelSheet

Returns the ExcelSheet object at the given index

ParamType

index

Number

excelWorkbook.getSheetNameAt(index) ⇒ String

Returns the name of the sheet at the given index

Returns: String - sheetName

ParamType

index

Number

excelWorkbook.getSheetNames() ⇒ [ 'Array' ].<String>

Returns all sheet names

excelWorkbook.removeSheetAt(index)

Removes the sheet at the given index

ParamType

index

Number


excelWorkbook.setSheetNameAt(index, name)

Sets the sheet name

ParamType

index

Number

name

String


excelWorkbook.writeToFile(targetFile) ⇒ Boolean

Writes this workbook to the given targetFile

Returns: Boolean - success

ParamType

targetFile

plugins.file.JSFile | String


excelWorkbook.wb : Packages.org.apache.poi.ss.usermodel.Workbook

The internal workbook object


new ExcelWorkbook([templateOrFileType])

Creates an empty Excel workbook or reads the one provided

ParamTypeDescription

[templateOrFileType]

String | plugins.file.JSFile | Number | Array.<byte>

either a path, mediaUrl, JSFile or byte[] when reading an existing workbook or one of the FILE_FORMAT constants when creating empty workbooks

Example

// Create workbook and sheet
var workbook = new scopes.svyExcelUtils.Workbook(scopes.svyExcelUtils.FILE_FORMAT.XLSX);
var sheet = workbook.createSheet("Test");

// Create style for the header
var headerStyle = workbook.createCellStyle();
headerStyle
   .setFont("Arial,1,12")
   .setFillPattern(scopes.svyExcelUtils.FILL_PATTERN.SOLID_FOREGROUND)
   .setFillForegroundColor(scopes.svyExcelUtils.INDEXED_COLOR.LIGHT_ORANGE)
   .setAlignment(scopes.svyExcelUtils.ALIGNMENT.CENTER);

var rowNum = 1;

// Create header row and cells
var row = sheet.createRow(rowNum ++);
var cell = row.createCell(1);
cell.setCellValue("Test 1", headerStyle);

cell = row.createCell(2);
cell.setCellValue("Test 2", headerStyle);

// Create some data and write to the sheet
var data = [[10, 35], [15, 47], [9, 22], [10, 33]];
for (var i = 0; i < data.length; i++) {
   row = sheet.createRow(rowNum ++);
   row.createCell(1).setCellValue(data[i][0]);
   row.createCell(2).setCellValue(data[i][1]);
}

// Create a style for the sum
var sumStyle = workbook.createCellStyle();
// Clone the default font, so we won't be changing the default
var font = sumStyle.cloneFont();
font.underline = scopes.svyExcelUtils.FONT_UNDERLINE.DOUBLE_ACCOUNTING;
font.isBold = true;

// Create formula cells at the bottom
row = sheet.createRow(rowNum ++);