Components

Component Spreadsheet

CFC wrapper for the Apache POI project's HSSF (xls) and XSSF (xlsx) classes

Functions

addColumn
Adds a column and inserts the data provided into the new column.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
data string true
startRow numeric false
column numeric false
insert boolean false If false, will overwrite data in an existing column if one exists
delimiter string true
addDelimitedRows
Appends rows to a sheet from a csv string

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
data string true
delimiter string false
addFreezePane
Adds a split ('freeze pane') to the sheet

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
splitColumn numeric true Horizontal position of split
splitRow numeric true Vertical position of split
leftmostColumn numeric false Left column visible in right pane
topRow numeric false Top row visible in bottom pane
addImage
Adds an image to the workbook. Valid argument combinations are filepath + anchor, or imageData + imageType + anchor

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
filepath string false
imageData any false
imageType string false
anchor string true
addInfo
Set standard properties on the workbook

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
props struct true Valid struct keys are author, category, lastauthor, comments, keywords, manager, company, subject, title
addInfoBinary
Set standard properties on the workbook

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
props struct true Valid struct keys are author, category, lastauthor, comments, keywords, manager, company, subject, title
addInfoOOXML
Set standard properties on the workbook

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
props struct true Valid struct keys are author, category, lastauthor, comments, keywords, manager, company, subject, title
addRow
Adds a new row and inserts the data provided in the new row.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
data string true Delimited list of data
startRow numeric false Target row number
startColumn numeric false Target column number
insert boolean false If true, data is inserted as a new row. Otherwise, any existing data is overwritten
delimiter string false Delimiter for the list of values
handleEmbeddedCommas boolean false When true, values enclosed in single quotes are treated as a single element like in ACF. Only applies when the delimiter is a comma.
addRows
Adds rows to a sheet from a query object

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
data query true
row numeric false
column numeric false
insert boolean false
formats struct false Column format properties [key: columnName, value: format structure]
autoSizeColumns boolean false
autoSizeColumn
Adjusts the width of the specified column to fit the contents. For performance reasons, this should normally be called only once per column.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
column numeric false
useMergedCells boolean false whether to use the contents of merged cells when calculating the width of the column
autoSizeColumnFix

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
columnIndex numeric true Base-0 column index
isDateColumn boolean false
dateMask string false
buildCellStyle
Builds an HSSFCellStyle with settings provided in a struct

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
format struct true
cellExists
Returns true if the requested cell exists

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
row numeric true Row index of cell to retrieve ( 1-based !)
column numeric true Col index of cell to retrieve ( 1-based !)
clearCell
Clears the specified cell of all styles and values

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
row numeric true
column numeric true
clearCellRange
Clears the specified cell range of all styles and values

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
startRow numeric true
startColumn numeric true
endRow numeric true
endColumn numeric true
cloneFont
Returns a new Font object with the same settings as the Font object passed in

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
fontToClone any true
createCell
Creates a new cell in a row and returns the cell

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
row any true
cellNum numeric false
overwrite boolean false
createRow
Creates a new row in the sheet and returns the row

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
rowNum numeric false
overwrite boolean false
createSheet
Adds a new Sheet to the current workbook and returns it. Throws an error if the Sheet name is invalid or already exists

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string false Name of the new sheet
nameConflict string false Action to take if the sheet name already exists: overwrite or error (default)
createSplitPane
Adds a split pane to a sheet, which differs from a freeze pane in that it has x and y positioning

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
xSplitPos numeric true
ySplitPos numeric true
leftmostColumn numeric true
topRow numeric true
activePane string false Valid values are LOWER_LEFT, LOWER_RIGHT, UPPER_LEFT, and UPPER_RIGHT
createWorkBook
This function creates and returns a new POI Workbook with one blank Sheet

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string false Name of the initial Sheet. Default name is 'Sheet1'
useXMLFormat boolean false If true, returns type XSSFWorkbook (xml). Otherwise, returns an HSSFWorkbook (binary)
deleteColumn
Deletes the data from a column. Does not physically remove the column.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
columnNum numeric true
deleteColumns
Deletes a range of columns

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
range string true
deleteRow
Deletes the data from a row. Does not physically delete the row.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
rowNum numeric true
deleteRows
Deletes a range of rows

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
range string true
deleteSheet
Removes the requested sheet. Throws an error if the sheet name or index is invalid -OR- if it is the last sheet in the workbook.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string false Name of the sheet to remove
sheetIndex numeric false Position of the sheet to remove
deleteSheetAt
(Internal use only) Removes the sheet at the specified index without any validation

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetIndex numeric false Index of the sheet to remove (1-based)
estimateColumnWidth
Estimates approximate column width based on cell value and default character width.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
value any true
extractRanges
Parses and validates a list of row/column numbers. Returns an array of structures with the keys: startAt, endAt

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
rangeList string true
formatCell
Sets various formatting values on a single cell

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
format struct true
row numeric true
column numeric true
cellStyle any false Existing cellStyle to reusue
formatCellRange
Applies formatting to a contigous range of cells

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
format struct true
startRow numeric true
startColumn numeric true
endRow numeric true
endColumn numeric true
formatColumn
Sets various formatting values on a column

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
format struct true
column numeric true
formatColumns
Sets various formatting values on multiple columns

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
format struct true
range string true
formatRow
Sets various formatting values on a row

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
format struct true
rowNum numeric true
formatRows
Sets various formatting values on multiple rows

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
format struct true
range string true
generateUniqueSheetName
Generates a unique sheet name (Sheet1, Sheet2, etecetera).
getActiveSheet
getAWTFontStyle
Transforms a POI Font

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
poiFont any true
getBinaryInfo
Returns a struct containing the standard properties for a binary workbook
getCellAt
Returns the cell at the given position. Throws exception if the cell does not exist.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
row numeric true Row index of cell to retrieve ( 1-based !)
column numeric true Column index of cell to retrieve ( 1-based !)
getCellComment
Returns a struct containing comment info (author, column, row, and comment) for a specific cell, or an array of structs containing the comments for the entire sheet

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
row numeric false
column numeric false
getCellFormula
Returns the formula for a cell or for the entire spreadsheet

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
row numeric false
column numeric false
getCellUtil
Returns stored cell utility object ie org.apache.poi.ss.util.CellUtil
getCellValue
Returns the value of a single cell

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
row numeric true
column numeric true
getCFMLEngine
getColorIndex
Returns the color index of a color string

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
colorName string true
getDateTimeValueFormat
Returns the default date mask for the given value: DATE (only), TIME (only) or TIMESTAMP

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
value any true
getDefaultCharWidth
Estimates the default character width using Excel's 'Normal' font
getEvaluator
Returns evaluator object ie org.apache.poi.ss.usermodel.FormulaEvaluator
getFirstRowNum
Returns the index of the first row in the active sheet (0-based). Returns -1 if the sheet is empty
getFormatter
Returns cell formatting utility object ie org.apache.poi.ss.usermodel.DataFormatter
getInfo
Returns a struct containing the standard properties for the workbook
getJavaColorRGB
Returns a struct containing RGB values from java.awt.Color for the color name passed in

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
colorName string true
getLastRowNum
Returns the last row number in the current sheet (base-0). Returns -1 if the sheet is empty
getNextEmptyRow
Returns the index of the next empty row in the active sheet (0-based)
getOOXMLInfo
Returns a struct containing the standard properties for an OOXML workbook
getQueryColumnFormats

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
query query true
formats struct false
getWorkbook
init
Creates or loads a workbook from disk. Returns a new Spreadsheet object.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string false Name of the initial Sheet -or- name of the Sheet to activate.
useXmlFormat boolean false If true, creates an .xlsx workbook (ie XSSFWorkbook). Otherwise, creates a binary .xls object (ie HSSFWorkbook)
src string false Path to an existing workbook on disk
sheet numeric false Activate the sheet at this position. Applies only when using 'src'
initializeCell
Returns the cell at the given position. Creates the row and cell if either does not already exist.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
row numeric true Row index of cell to retrieve ( 1-based !)
column numeric true Column index of cell to retrieve ( 1-based !)
isBinaryFormat
Returns true if this is a binary *.xls spreadsheet (ie instance of org.apache.poi.hssf.usermodel.HSSFWorkbook)
isLinux
loadFromFile
Initializes this component from a workbook file from disk.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
src string true The full file path to the spreadsheet
sheet numeric false Used to set the active sheet
sheetName string false Used to set the active sheet
loadPoi

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
javaclass string true I am the java class to be loaded
mergeCells
Merges two or more cells

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
startRow numeric true
endRow numeric true
startColumn numeric true
endColumn numeric true
moveSheet
Moves a Sheet Name to the given position

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string true Name of the sheet to move
sheet numeric true Move the sheet to this position
parseRowData
Converts a list of values to an array

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
line string true List of values to parse
delimiter string true List delimiter
handleEmbeddedCommas boolean false
read
Reads a spreadsheet from disk and returns a query, CSV, or HTML. **NOTE: To read a file into a spreadsheet object use init() instead.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
src string true The full file path to the spreadsheet
columns string false
columnnames string false
format string false
headerrow numeric false
query string false
rows string false
sheet numeric false
sheetname string false
excludeHeaderRow boolean false
readAllSheets boolean false
readBinary
Returns a binary representation of the file
renameSheet
Renames the work sheet at the given position. Throws an error if the SheetName or Position is not valid

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string true New Sheet Name
sheetIndex numeric true Position of the Sheet to rename (1-based)
setActiveSheet
Sets the active sheet within the workbook, either by name or by index

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string false
sheetIndex numeric false
setCellComment
Sets a cell comment

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
comment struct true
row numeric true
column numeric true
setCellFormula
Sets the formula for a cell

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
formula string true
row numeric true
column numeric true
setCellValue
Sets the value of a single cell

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
cellValue string true
row numeric true
column numeric true
setColumnWidth
Sets the width of a column

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
column numeric true
width numeric true
setFooter
Sets the footer values on the sheet

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
leftFooter string true
centerFooter string true
rightFooter string true
setHeader
Sets the header values on the sheet

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
leftHeader string true
centerHeader string true
rightHeader string true
setRowHeight
Sets the height of a row in points

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
row numeric true
height numeric true
setWorkbook

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
workbook any true
sheetExists
Returns true if the requested SheetName or Sheet (position) exists

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string false
sheetIndex numeric false Sheet position (1-based)
shiftColumns
Shifts columns left (negative integer) or right (positive integer)

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
startColumn numeric true
endColumn numeric false
offset numeric false
shiftRows
Shifts rows up (negative integer) or down (positive integer)

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
startRow numeric true
endRow numeric false
offset numeric false
update
Updates a workbook with a new sheet or overwrites an existing sheet with the same name

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
filepath string true
format string false
name string false
password string false
query query false
sheetname string false
nameConflict string false Action to take if the sheetname already exists: overwrite or error (default)
columnFormats struct false
autoSizeColumns boolean false
validateSheetIndex
Validates the given SheetIndex is valid for this workbook: a) an integer greater than 0 and b) does not exceed the number sheets in this workbook

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetIndex numeric true Sheet position (base-1)
validateSheetName
Validates the given SheetName exists within this workbook.

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string true Name of the sheet to validate
validateSheetNameOrIndexWasProvided
Validates either a SheetName OR SheetIndex was supplied (not both).

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
sheetName string false
sheetIndex numeric false
write
Writes a spreadsheet to disk

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
filepath string true
format string false
name string false
overwrite boolean false
password string false
query query false
sheetname string false
columnFormats struct false
autoSizeColumns boolean false
writeToFile
Writes the current spreadsheet file to disk

Arguments

The arguments for this function are set. You can not use other arguments except the following ones.
Name Type Required Description
filepath string true
overwrite boolean false
password string false