Skip to content

API svyExcelUtils

Jump to bottom
Paolo edited this page Dec 29, 2020 · 1 revision

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

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

<pre>
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");
</pre>

ExcelWorkbook

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

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

<pre>
// 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");
</pre>

FoundSetExcelWorkbook ⇐ ServoyExcelWorkbook

Extends: ServoyExcelWorkbook

FoundSetExcelWorkbook~dataFilled : Boolean

foundSetExcelWorkbook.cloneCellStyle(cellStyle) ⇒ ExcelCellStyle

Clones the given ExcelCellStyle

Param Type
cellStyle ExcelCellStyle

foundSetExcelWorkbook.cloneFont(font) ⇒ ExcelFont

Clones the given font and returns a new ExcelFont

Param Type
font ExcelFont

foundSetExcelWorkbook.cloneSheet(indexToClone) ⇒ ExcelSheet

Creates an ExcelSheet from an existing sheet in the Workbook

Returns: ExcelSheet - clone

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

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

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

Param Type
sheetName String

foundSetExcelWorkbook.getSheetAt(index) ⇒ ExcelSheet

Returns the ExcelSheet object at the given index

Param Type
index Number

foundSetExcelWorkbook.getSheetNameAt(index) ⇒ String

Returns the name of the sheet at the given index

Returns: String - sheetName

Param Type
index Number

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

Returns all sheet names

foundSetExcelWorkbook.removeSheetAt(index)

Removes the sheet at the given index

Param Type
index Number

foundSetExcelWorkbook.setFormatForColumn(columnIndex, format)

Sets a date or number format used for the given column

Param Type
columnIndex Number
format String

foundSetExcelWorkbook.setSheetNameAt(index, name)

Sets the sheet name

Param Type
index Number
name String

foundSetExcelWorkbook.writeToFile(targetFile) ⇒ Boolean

Writes this workbook to the given file

Returns: Boolean - success

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

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

Name Type Default
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

Name Type Default
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

Name Default
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

Name Default Description
XLS 1 XLS format
XLSX 2 XLSX format, requires additional libraries @see https://github.com/Servoy/svyUtils/wiki/ExcelUtils
SXLSX 4 Streaming version of the XLSX format to avoid out of memory errors

FILL_PATTERN : enum

Fill patterns used in ExcelCellStyle

Properties

Name Type Default
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

Name Default
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

Name Type Default
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

Name Default
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

Name Type Default
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

Name Type Default
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

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

<pre>
// 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");
</pre>

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

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

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

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

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

Param Type
cellReference String

getWorkbook(original) ⇒ ExcelWorkbook

Returns an ExcelWorkbook from the given file or media URL

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

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

Home

Smart Doc Editor Utils

Custom Dialogs

Excel Utils, Full API Reference

Crypto Utils

Clone this wiki locally