ServoyCloudSupportSamplesOpen SourceCommunity

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~dataFilled : Boolean

dataSetExcelWorkbook.cloneCellStyle(cellStyle) ⇒ ExcelCellStyle

Clones the given ExcelCellStyle

dataSetExcelWorkbook.cloneFont(font) ⇒ ExcelFont

Clones the given font and returns a new ExcelFont

dataSetExcelWorkbook.cloneSheet(indexToClone) ⇒ ExcelSheet

Creates an ExcelSheet from an existing sheet in the Workbook

Returns: ExcelSheet - clone

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

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

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)

dataSetExcelWorkbook.getSheetAt(index) ⇒ ExcelSheet

Returns the ExcelSheet object at the given index

dataSetExcelWorkbook.getSheetNameAt(index) ⇒ String

Returns the name of the sheet at the given index

Returns: String - sheetName

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

Returns all sheet names

dataSetExcelWorkbook.removeSheetAt(index)

Removes the sheet at the given index

dataSetExcelWorkbook.setFormatForColumn(columnIndex, format)

Sets a date or number format used for the given column

dataSetExcelWorkbook.setSheetNameAt(index, name)

Sets the sheet name

dataSetExcelWorkbook.writeToFile(targetFile) ⇒ Boolean

Writes this workbook to the given file

Returns: Boolean - success

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

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

excelWorkbook.cloneFont(font) ⇒ ExcelFont

Clones the given font and returns a new ExcelFont

excelWorkbook.cloneSheet(indexToClone) ⇒ ExcelSheet

Creates an ExcelSheet from an existing sheet in the Workbook

Returns: ExcelSheet - clone

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

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)

excelWorkbook.getSheetAt(index) ⇒ ExcelSheet

Returns the ExcelSheet object at the given index

excelWorkbook.getSheetNameAt(index) ⇒ String

Returns the name of the sheet at the given index

Returns: String - sheetName

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

Returns all sheet names

excelWorkbook.removeSheetAt(index)

Removes the sheet at the given index

excelWorkbook.setSheetNameAt(index, name)

Sets the sheet name

excelWorkbook.writeToFile(targetFile) ⇒ Boolean

Writes this workbook to the given targetFile

Returns: Boolean - success

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

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 ++);
cell = row.createCell(1);
cell.setCellStyle(sumStyle);
cell.setCellFormula("SUM(" + scopes.svyExcelUtils.getCellReferenceFromRange(2, 1 + data.length, 1, 1) + ")");

cell = row.createCell(2);
cell.setCellStyle(sumStyle);
cell.setCellFormula("SUM(" + scopes.svyExcelUtils.getCellReferenceFromRange(2, 1 + data.length, 2, 2) + ")");

// Write to file
var success = workbook.writeToFile("d:\\test.xls");

FoundSetExcelWorkbook ⇐ ServoyExcelWorkbook

Extends: ServoyExcelWorkbook

FoundSetExcelWorkbook~dataFilled : Boolean

foundSetExcelWorkbook.cloneCellStyle(cellStyle) ⇒ ExcelCellStyle

Clones the given ExcelCellStyle

foundSetExcelWorkbook.cloneFont(font) ⇒ ExcelFont

Clones the given font and returns a new ExcelFont

foundSetExcelWorkbook.cloneSheet(indexToClone) ⇒ ExcelSheet

Creates an ExcelSheet from an existing sheet in the Workbook

Returns: ExcelSheet - clone

foundSetExcelWorkbook.close()

Closes this workbook

foundSetExcelWorkbook.createCellStyle() ⇒ ExcelCellStyle

Creates an empty ExcelCellStyle

foundSetExcelWorkbook.createColumnStyle(columnIndex) ⇒ ExcelCellStyle

Creates and returns an ExcelCellStyle used for a specific column

foundSetExcelWorkbook.createFont() ⇒ ExcelFont

Creates a font

foundSetExcelWorkbook.createHeaderStyle() ⇒ ExcelCellStyle

Creates and returns an ExcelCellStyle used for the header row

foundSetExcelWorkbook.createRowStyle() ⇒ ExcelCellStyle

Creates and returns an ExcelCellStyle used for a data row

foundSetExcelWorkbook.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

foundSetExcelWorkbook.fillData()

Fills the sheet with the data of the foundset

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

Overrides: fillData

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

Returns the data of this workbook as a byte[]

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

foundSetExcelWorkbook.getFoundSet() ⇒ JSFoundSet

Returns the foundset used to create this workbook

foundSetExcelWorkbook.getNumberOfSheets() ⇒ Number

Returns the number of spreadsheets in the workbook

foundSetExcelWorkbook.getSheet(sheetName) ⇒ ExcelSheet

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

foundSetExcelWorkbook.getSheetAt(index) ⇒ ExcelSheet

Returns the ExcelSheet object at the given index

foundSetExcelWorkbook.getSheetNameAt(index) ⇒ String

Returns the name of the sheet at the given index

Returns: String - sheetName

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

Returns all sheet names

foundSetExcelWorkbook.removeSheetAt(index)

Removes the sheet at the given index

foundSetExcelWorkbook.setFormatForColumn(columnIndex, format)

Sets a date or number format used for the given column

foundSetExcelWorkbook.setSheetNameAt(index, name)

Sets the sheet name

foundSetExcelWorkbook.writeToFile(targetFile) ⇒ Boolean

Writes this workbook to the given file

Returns: Boolean - success

foundSetExcelWorkbook.autoSizeColumns : Boolean

Whether or not all data columns should be auto sized

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

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

foundSetExcelWorkbook.defaultDateFormat : String

The default format used to format date values

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

foundSetExcelWorkbook.defaultNumberFormat : String

The default format used to format number values

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

foundSetExcelWorkbook.foundset : JSFoundSet

The foundset used to create this workbook

foundSetExcelWorkbook.freezeFirstRow : Boolean

Whether the header row is frozen or not

Overrides: freezeFirstRow

foundSetExcelWorkbook.headerStyle : ExcelCellStyle

The style used for the header of the data

Overrides: headerStyle

foundSetExcelWorkbook.rowStyle : ExcelCellStyle

The style used for a data cell

Overrides: rowStyle

foundSetExcelWorkbook.setAutoFilter : Boolean

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

Overrides: setAutoFilter

foundSetExcelWorkbook.sheet : ExcelSheet

The ExcelSheet used or created

foundSetExcelWorkbook.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

foundSetExcelWorkbook.startColumn : Number

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

foundSetExcelWorkbook.startRow : Number

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

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

The internal workbook object

foundSetExcelWorkbook.workbook : ExcelWorkbook

The ExcelWorkbook created

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

A FoundSet based Excel workbook

ALIGNMENT : enum

Horizontal alignments used in ExcelCellStyle

Properties

BORDER : enum

Borders used in ExcelCellStyle

Properties

CELL_TYPE

Possible cell types

Properties

FILE_FORMAT

Possible file formats used instead of templates when creating empty workbooks

Properties

FILL_PATTERN : enum

Fill patterns used in ExcelCellStyle

Properties

FONT_UNDERLINE

Underline patterns used in ExcelFont

Properties

INDEXED_COLOR : enum

Colors from the Excel color palette

Properties

PAPER_SIZE

Possible paper sizes for a PrintSetup

Properties

SHEET_PANE : enum

Panes of a sheet used in split panes

Properties

VERTICAL_ALIGNMENT : enum

Vertical alignments used in ExcelCellStyle

Properties

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

Example

// Create workbook and sheet
var workbook = scopes.svyExcelUtils.createWorkbook(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 ++);
cell = row.createCell(1);
cell.setCellStyle(sumStyle);
cell.setCellFormula("SUM(" + scopes.svyExcelUtils.getCellReferenceFromRange(2, 1 + data.length, 1, 1) + ")");

cell = row.createCell(2);
cell.setCellStyle(sumStyle);
cell.setCellFormula("SUM(" + scopes.svyExcelUtils.getCellReferenceFromRange(2, 1 + data.length, 2, 2) + ")");

// Write to file
var success = workbook.writeToFile("d:\\test.xls");

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

Returns: String - cellReference

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

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

PreviousExcel UtilsNextCrypto Utils

Last updated