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 |
Components
Component Spreadsheet
CFC wrapper for the Apache POI project's HSSF (xls) and XSSF (xlsx) classesFunctions
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.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
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 |