Full API Reference

Classes

DataSetExcelWorkbook ⇐ ServoyExcelWorkbook

ExcelWorkbook

FoundSetExcelWorkbook ⇐ ServoyExcelWorkbook

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 ⇐ ServoyExcelWorkbook

  • inner

    • ~dataFilled : Boolean

  • instance

    • .cloneCellStyle(cellStyle) ⇒ ExcelCellStyle

    • .cloneFont(font) ⇒ ExcelFont

    • .cloneSheet(indexToClone) ⇒ ExcelSheet

    • .close()

    • .createCellStyle() ⇒ ExcelCellStyle

    • .createColumnStyle(columnIndex) ⇒ ExcelCellStyle

    • .createFont() ⇒ ExcelFont

    • .createHeaderStyle() ⇒ ExcelCellStyle

    • .createRowStyle() ⇒ ExcelCellStyle

    • .createSheet(sheetName) ⇒ ExcelSheet

    • .fillData()

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

    • .getDataSet() ⇒ JSDataSet

    • .getNumberOfSheets() ⇒ Number

    • .getSheet(sheetName) ⇒ ExcelSheet

    • .getSheetAt(index) ⇒ ExcelSheet

    • .getSheetNameAt(index) ⇒ String

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

    • .removeSheetAt(index)

    • .setFormatForColumn(columnIndex, format)

    • .setSheetNameAt(index, name)

    • .writeToFile(targetFile) ⇒ Boolean

    • .autoSizeColumns : Boolean

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

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

    • .dataset : JSDataSet

    • .defaultDateFormat : String

    • .defaultNumberFormat : String

    • .freezeFirstRow : Boolean

    • .headerStyle : ExcelCellStyle

    • .rowStyle : ExcelCellStyle

    • .setAutoFilter : Boolean

    • .sheet : ExcelSheet

    • .sheetName : String

    • .startColumn : Number

    • .startRow : Number

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

    • .workbook : ExcelWorkbook

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

DataSetExcelWorkbook~dataFilled : Boolean

dataSetExcelWorkbook.cloneCellStyle(cellStyle) ⇒ ExcelCellStyle

Clones the given ExcelCellStyle

Param	Type
cellStyle	ExcelCellStyle

dataSetExcelWorkbook.cloneFont(font) ⇒ ExcelFont

Clones the given font and returns a new ExcelFont

Param	Type
font	ExcelFont

dataSetExcelWorkbook.cloneSheet(indexToClone) ⇒ ExcelSheet

Creates an ExcelSheet from an existing sheet in the Workbook

Returns: ExcelSheet - clone

Param	Type	Description
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

Param	Type
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

Param	Type
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)

Param	Type
sheetName	String

dataSetExcelWorkbook.getSheetAt(index) ⇒ ExcelSheet

Returns the ExcelSheet object at the given index

Param	Type
index	Number

dataSetExcelWorkbook.getSheetNameAt(index) ⇒ String

Returns the name of the sheet at the given index

Returns: String - sheetName

Param	Type
index	Number

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

Returns all sheet names

dataSetExcelWorkbook.removeSheetAt(index)

Removes the sheet at the given index

Param	Type
index	Number

dataSetExcelWorkbook.setFormatForColumn(columnIndex, format)

Sets a date or number format used for the given column

Param	Type
columnIndex	Number
format	String

dataSetExcelWorkbook.setSheetNameAt(index, name)

Sets the sheet name

Param	Type
index	Number
name	String

dataSetExcelWorkbook.writeToFile(targetFile) ⇒ Boolean

Writes this workbook to the given file

Returns: Boolean - success

Param	Type
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

Param	Type	Description
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

  • .cloneFont(font) ⇒ ExcelFont

  • .cloneSheet(indexToClone) ⇒ ExcelSheet

  • .close()

  • .createCellStyle() ⇒ ExcelCellStyle

  • .createFont() ⇒ ExcelFont

  • .createSheet(sheetName) ⇒ ExcelSheet

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

  • .getNumberOfSheets() ⇒ Number

  • .getSheet(sheetName) ⇒ ExcelSheet

  • .getSheetAt(index) ⇒ ExcelSheet

  • .getSheetNameAt(index) ⇒ String

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

  • .removeSheetAt(index)

  • .setSheetNameAt(index, name)

  • .writeToFile(targetFile) ⇒ Boolean

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

  • new ExcelWorkbook([templateOrFileType])

excelWorkbook.cloneCellStyle(cellStyle) ⇒ ExcelCellStyle

Clones the given ExcelCellStyle

Param	Type
cellStyle	ExcelCellStyle

excelWorkbook.cloneFont(font) ⇒ ExcelFont

Clones the given font and returns a new ExcelFont

Param	Type
font	ExcelFont

excelWorkbook.cloneSheet(indexToClone) ⇒ ExcelSheet

Creates an ExcelSheet from an existing sheet in the Workbook

Returns: ExcelSheet - clone

Param	Type	Description
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

Param	Type
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)

Param	Type
sheetName	String

excelWorkbook.getSheetAt(index) ⇒ ExcelSheet

Returns the ExcelSheet object at the given index

Param	Type
index	Number

excelWorkbook.getSheetNameAt(index) ⇒ String

Returns the name of the sheet at the given index

Returns: String - sheetName

Param	Type
index	Number

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

Returns all sheet names

excelWorkbook.removeSheetAt(index)

Removes the sheet at the given index

Param	Type
index	Number

---

excelWorkbook.setSheetNameAt(index, name)

Sets the sheet name

Param	Type
index	Number
name	String

---

excelWorkbook.writeToFile(targetFile) ⇒ Boolean

Writes this workbook to the given targetFile

Returns: Boolean - success

Param	Type
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

Param	Type	Description
[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);