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 ++);
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

ParamType

cellStyle

foundSetExcelWorkbook.cloneFont(font) ⇒ ExcelFont

Clones the given font and returns a new ExcelFont

ParamType

font

foundSetExcelWorkbook.cloneSheet(indexToClone) ⇒ ExcelSheet

Creates an ExcelSheet from an existing sheet in the Workbook

Returns: ExcelSheet - clone

ParamTypeDescription

indexToClone

Number

one based

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

ParamType

columnIndex

Number

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

ParamType

sheetName

String

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)

ParamType

sheetName

String

foundSetExcelWorkbook.getSheetAt(index) ⇒ ExcelSheet

Returns the ExcelSheet object at the given index

ParamType

index

Number

foundSetExcelWorkbook.getSheetNameAt(index) ⇒ String

Returns the name of the sheet at the given index

Returns: String - sheetName

ParamType

index

Number

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

Returns all sheet names

foundSetExcelWorkbook.removeSheetAt(index)

Removes the sheet at the given index

ParamType

index

Number

foundSetExcelWorkbook.setFormatForColumn(columnIndex, format)

Sets a date or number format used for the given column

ParamType

columnIndex

Number

format

String

foundSetExcelWorkbook.setSheetNameAt(index, name)

Sets the sheet name

ParamType

index

Number

name

String

foundSetExcelWorkbook.writeToFile(targetFile) ⇒ Boolean

Writes this workbook to the given file

Returns: Boolean - success

ParamType

targetFile

String | plugins.file.JSFile

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

ParamTypeDescription

foundset

JSFoundSet

the foundset

dataproviders

[ 'Array' ].<String>

the dataproviders to be used for the excel 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


ALIGNMENT : enum

Horizontal alignments used in ExcelCellStyle

Properties

NameTypeDefault

CENTER

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER

CENTER_SELECTION

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER_SELECTION

FILL

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment.FILL

GENERAL

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment.GENERAL

JUSTIFY

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment.JUSTIFY

LEFT

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment.LEFT

RIGHT

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment.RIGHT

DISTRIBUTED

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment

Packages.org.apache.poi.ss.usermodel.HorizontalAlignment.DISTRIBUTED


BORDER : enum

Borders used in ExcelCellStyle

Properties

NameTypeDefault

DASH_DOT

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.DASH_DOT

DASH_DOT_DOT

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.DASH_DOT_DOT

DASHED

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.DASHED

DOTTED

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.DOTTED

DOUBLE

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.DOUBLE

HAIR

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.HAIR

MEDIUM

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.MEDIUM

MEDIUM_DASH_DOT

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.MEDIUM_DASH_DOT

MEDIUM_DASH_DOT_DOT

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.MEDIUM_DASH_DOT_DOT

MEDIUM_DASHED

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.MEDIUM_DASHED

NONE

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.NONE

SLANTED_DASH_DOT

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.SLANTED_DASH_DOT

THICK

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.THICK

THIN

Packages.org.apache.poi.ss.usermodel.BorderStyle

Packages.org.apache.poi.ss.usermodel.BorderStyle.THIN


CELL_TYPE

Possible cell types

Properties

NameDefault

BLANK

Packages.org.apache.poi.ss.usermodel.CellType.BLANK

BOOLEAN

Packages.org.apache.poi.ss.usermodel.CellType.BOOLEAN

ERROR

Packages.org.apache.poi.ss.usermodel.CellType.ERROR

FORMULA

Packages.org.apache.poi.ss.usermodel.CellType.FORMULA

NUMERIC

Packages.org.apache.poi.ss.usermodel.CellType.NUMERIC

STRING

Packages.org.apache.poi.ss.usermodel.CellType.STRING


FILE_FORMAT

Possible file formats used instead of templates when creating empty workbooks

Properties

NameDefaultDescription

XLS

1

XLS format

XLSX

2

XLSX format, requires additional libraries @see Excel Utils

SXLSX

4

Streaming version of the XLSX format to avoid out of memory errors


FILL_PATTERN : enum

Fill patterns used in ExcelCellStyle

Properties

NameTypeDefault

NO_FILL

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.NO_FILL

SOLID_FOREGROUND

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.SOLID_FOREGROUND

FINE_DOTS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.FINE_DOTS

ALT_BARS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.ALT_BARS

SPARSE_DOTS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.SPARSE_DOTS

THICK_HORZ_BANDS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.THICK_HORZ_BANDS

THICK_VERT_BANDS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.THICK_VERT_BANDS

THICK_BACKWARD_DIAG

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.THICK_BACKWARD_DIAG

THICK_FORWARD_DIAG

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.THICK_FORWARD_DIAG

BIG_SPOTS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.BIG_SPOTS

BRICKS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.BRICKS

THIN_HORZ_BANDS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.THICK_HORZ_BANDS

THIN_VERT_BANDS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.THICK_VERT_BANDS

THIN_BACKWARD_DIAG

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.THICK_FORWARD_DIAG

THIN_FORWARD_DIAG

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.THICK_FORWARD_DIAG

SQUARES

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.SQUARES

DIAMONDS

Packages.org.apache.poi.ss.usermodel.FillPatternType

Packages.org.apache.poi.ss.usermodel.FillPatternType.DIAMONDS


FONT_UNDERLINE

Underline patterns used in ExcelFont

Properties

NameDefault

DOUBLE

Packages.org.apache.poi.hssf.usermodel.HSSFFont.U_DOUBLE

DOUBLE_ACCOUNTING

Packages.org.apache.poi.hssf.usermodel.HSSFFont.U_DOUBLE_ACCOUNTING

NONE

Packages.org.apache.poi.hssf.usermodel.HSSFFont.U_NONE

SINGLE

Packages.org.apache.poi.hssf.usermodel.HSSFFont.U_SINGLE

SINGLE_ACCOUNTING

Packages.org.apache.poi.hssf.usermodel.HSSFFont.U_SINGLE_ACCOUNTING


INDEXED_COLOR : enum

Colors from the Excel color palette

Properties

NameTypeDefault

AQUA

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.AQUA

BLACK

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.BLACK

BLUE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.BLUE

BLUE_GREY

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.BLUE_GREY

BRIGHT_GREEN

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.BRIGHT_GREEN

BROWN

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.BROWN

CORAL

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.CORAL

CORNFLOWER_BLUE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.CORNFLOWER_BLUE

DARK_BLUE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.DARK_BLUE

DARK_GREEN

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.DARK_GREEN

DARK_RED

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.DARK_RED

DARK_TEAL

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.DARK_TEAL

DARK_YELLOW

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.DARK_YELLOW

GOLD

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.GOLD

GREEN

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.GREEN

GREY_25_PERCENT

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.GREY_25_PERCENT

GREY_40_PERCENT

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.GREY_40_PERCENT

GREY_50_PERCENT

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.GREY_50_PERCENT

GREY_80_PERCENT

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.GREY_80_PERCENT

INDIGO

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.INDIGO

LAVENDER

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.LAVENDER

LEMON_CHIFFON

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.LEMON_CHIFFON

LIGHT_BLUE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.LIGHT_BLUE

LIGHT_CORNFLOWER_BLUE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.LIGHT_CORNFLOWER_BLUE

LIGHT_GREEN

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.LIGHT_GREEN

LIGHT_ORANGE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.LIGHT_ORANGE

LIGHT_TURQUOISE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.LIGHT_TURQUOISE

LIGHT_YELLOW

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.LIGHT_YELLOW

LIME

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.LIME

MAROON

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.MAROON

OLIVE_GREEN

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.OLIVE_GREEN

ORANGE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.ORANGE

ORCHID

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.ORCHID

PALE_BLUE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.PALE_BLUE

PINK

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.PINK

PLUM

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.PLUM

RED

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.RED

ROSE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.ROSE

ROYAL_BLUE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.ROYAL_BLUE

SEA_GREEN

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.SEA_GREEN

SKY_BLUE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.SKY_BLUE

TAN

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.TAN

TEAL

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.TEAL

TURQUOISE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.TURQUOISE

VIOLET

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.VIOLET

WHITE

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.WHITE

YELLOW

Packages.org.apache.poi.ss.usermodel.IndexedColors

Packages.org.apache.poi.ss.usermodel.IndexedColors.YELLOW


PAPER_SIZE

Possible paper sizes for a PrintSetup

Properties

NameDefault

A3_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.A3_PAPERSIZE

A4_EXTRA_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.A4_EXTRA_PAPERSIZE

A4_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.A4_PAPERSIZE

A4_PLUS_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.A4_PLUS_PAPERSIZE

A4_ROTATED_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.A4_ROTATED_PAPERSIZE

A4_SMALL_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.A4_SMALL_PAPERSIZE

A4_TRANSVERSE_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.A4_TRANSVERSE_PAPERSIZE

A5_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.A5_PAPERSIZE

B4_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.B4_PAPERSIZE

B5_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.B5_PAPERSIZE

ELEVEN_BY_SEVENTEEN_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ELEVEN_BY_SEVENTEEN_PAPERSIZE

ENVELOPE_10_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ENVELOPE_10_PAPERSIZE

ENVELOPE_9_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ENVELOPE_9_PAPERSIZE

ENVELOPE_C3_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ENVELOPE_C3_PAPERSIZE

ENVELOPE_C4_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ENVELOPE_C4_PAPERSIZE

ENVELOPE_C5_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ENVELOPE_C5_PAPERSIZE

ENVELOPE_C6_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ENVELOPE_C6_PAPERSIZE

ENVELOPE_CS_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ENVELOPE_CS_PAPERSIZE

ENVELOPE_DL_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ENVELOPE_DL_PAPERSIZE

ENVELOPE_MONARCH_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.ENVELOPE_MONARCH_PAPERSIZE

EXECUTIVE_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.EXECUTIVE_PAPERSIZE

FOLIO8_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.FOLIO8_PAPERSIZE

LEDGER_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.LEDGER_PAPERSIZE

LEGAL_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.LEGAL_PAPERSIZE

LETTER_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.LETTER_PAPERSIZE

LETTER_ROTATED_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.LETTER_ROTATED_PAPERSIZE

LETTER_SMALL_PAGESIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.LETTER_SMALL_PAGESIZE

NOTE8_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.NOTE8_PAPERSIZE

PRINTER_DEFAULT_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.PRINTER_DEFAULT_PAPERSIZE

QUARTO_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.QUARTO_PAPERSIZE

STATEMENT_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.STATEMENT_PAPERSIZE

TABLOID_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.TABLOID_PAPERSIZE

TEN_BY_FOURTEEN_PAPERSIZE

Packages.org.apache.poi.ss.usermodel.PrintSetup.TEN_BY_FOURTEEN_PAPERSIZE


SHEET_PANE : enum

Panes of a sheet used in split panes

Properties

NameTypeDefault

LOWER_RIGHT

byte

Packages.org.apache.poi.ss.usermodel.Sheet.PANE_LOWER_RIGHT

LOWER_LEFT

byte

Packages.org.apache.poi.ss.usermodel.Sheet.PANE_LOWER_LEFT

UPPER_LEFT

byte

Packages.org.apache.poi.ss.usermodel.Sheet.PANE_UPPER_LEFT

UPPER_RIGHT

byte

Packages.org.apache.poi.ss.usermodel.Sheet.PANE_UPPER_RIGHT


VERTICAL_ALIGNMENT : enum

Vertical alignments used in ExcelCellStyle

Properties

NameTypeDefault

BOTTOM

Packages.org.apache.poi.ss.usermodel.VerticalAlignment

Packages.org.apache.poi.ss.usermodel.VerticalAlignment.BOTTOM

CENTER

Packages.org.apache.poi.ss.usermodel.VerticalAlignment

Packages.org.apache.poi.ss.usermodel.VerticalAlignment.CENTER

JUSTIFY

Packages.org.apache.poi.ss.usermodel.VerticalAlignment

Packages.org.apache.poi.ss.usermodel.VerticalAlignment.JUSTIFY

TOP

Packages.org.apache.poi.ss.usermodel.VerticalAlignment

Packages.org.apache.poi.ss.usermodel.VerticalAlignment.TOP

DISTRIBUTED

Packages.org.apache.poi.ss.usermodel.VerticalAlignment

Packages.org.apache.poi.ss.usermodel.VerticalAlignment.DISTRIBUTED

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

ParamTypeDescription

[templateOrFileType]

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

either an existing Excel file as template or one of the FILE_FORMAT constants when creating empty workbooks

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

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

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

ParamTypeDescription

foundset

JSFoundSet

the foundset

dataproviders

[ 'Array' ].<String>

the dataproviders to be used for the excel 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 templateOrFileType 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

getCellReferenceFromRange(firstRow, lastRow, firstColumn, lastColumn) ⇒ String

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

Returns: String - cellReference

ParamType

firstRow

Number

lastRow

Number

firstColumn

Number

lastColumn

Number

getRangeFromCellReference(cellReference) ⇒ Object

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

ParamType

cellReference

String

getWorkbook(original) ⇒ ExcelWorkbook

Returns an ExcelWorkbook from the given file or media URL

ParamTypeDescription

original

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

path to the file, 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

ParamType

setup

PrintSetup

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


Last updated