Package ortus.boxlang.spreadsheet

Class SpreadsheetUtil

java.lang.Object
ortus.boxlang.spreadsheet.SpreadsheetUtil
public class SpreadsheetUtil extends Object
This class provides spreadsheet utilities for the BoxLang runtime using Apache POI

  • Constructor Summary

    Constructors
    Constructor
    Description
    SpreadsheetUtil()
     

  • Method Summary

    Modifier and Type
    Method
    Description
    static void
    addImage(org.apache.poi.ss.usermodel.Sheet sheet, String filepath, int startRow, int startCol, int endRow, int endCol, org.apache.poi.ss.usermodel.Workbook workbook)
    Adds an image with custom anchor coordinates
    static void
    addImage(org.apache.poi.ss.usermodel.Sheet sheet, String filepath, int row, int col, org.apache.poi.ss.usermodel.Workbook workbook)
    Adds an image to a sheet at a specified cell location
    static void
    addPrintGridlines(org.apache.poi.ss.usermodel.Sheet sheet)
    Adds print gridlines to the sheet (gridlines will print when document is printed).
    static void
    autoSizeColumn(org.apache.poi.ss.usermodel.Sheet sheet, int column)
    Auto-sizes a specific column in the specified sheet
    static void
    autoSizeColumn(org.apache.poi.ss.usermodel.Workbook workbook, int column)
    Auto-sizes a specific column in the active sheet
    static void
    autoSizeColumns(org.apache.poi.ss.usermodel.Sheet sheet)
    Auto-sizes all columns in the specified sheet
    static void
    autoSizeColumns(org.apache.poi.ss.usermodel.Workbook workbook)
    Auto-sizes all columns in the active sheet
    static void
    clearCell(org.apache.poi.ss.usermodel.Sheet sheet, int row, int col)
    Clears the specified cell of all styles and values
    static void
    clearCellRange(org.apache.poi.ss.usermodel.Sheet sheet, int startRow, int startCol, int endRow, int endCol)
    Clears a range of cells of all styles and values
    static void
    createSplitPane(org.apache.poi.ss.usermodel.Sheet sheet, int xSplitPos, int ySplitPos, int leftmostColumn, int topRow)
    Creates a split pane in a sheet with default active pane
    static void
    createSplitPane(org.apache.poi.ss.usermodel.Sheet sheet, int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane)
    Creates a split pane in a sheet
    static org.apache.poi.ss.usermodel.Workbook
    createWorkbook(boolean xmlFormat, String sheetName)
    Creates a new workbook based on the specified format
    static org.apache.poi.ss.usermodel.Workbook
    createXlsWorkbook(String sheetName)
    Creates a new .xls workbook
    static org.apache.poi.ss.usermodel.Workbook
    createXlsxWorkbook(String sheetName)
    Creates a new .xlsx workbook
    static void
    deleteColumn(org.apache.poi.ss.usermodel.Sheet sheet, int column)
    Deletes a column by clearing all cells in that column Note: Apache POI doesn't support true column deletion, so we clear the column content
    static void
    deleteColumns(org.apache.poi.ss.usermodel.Sheet sheet, int startColumn, int endColumn)
    Deletes multiple columns by clearing all cells in those columns
    static void
    formatCell(org.apache.poi.ss.usermodel.Cell cell, ortus.boxlang.runtime.types.IStruct format)
    Applies formatting to a specific cell based on a format structure
    static void
    formatCellRange(org.apache.poi.ss.usermodel.Sheet sheet, ortus.boxlang.runtime.types.IStruct format, int startRow, int startCol, int endRow, int endCol, org.apache.poi.ss.usermodel.Workbook workbook)
    Formats a range of cells with a single format struct
    static void
    formatColumn(org.apache.poi.ss.usermodel.Sheet sheet, int column, ortus.boxlang.runtime.types.IStruct format)
    Formats all cells in a single column
    static void
    formatColumns(org.apache.poi.ss.usermodel.Sheet sheet, String columns, ortus.boxlang.runtime.types.IStruct format)
    Formats all cells in multiple columns specified by a comma-separated string or range Format: "1,3,5" or "1-5,7-10" or combinations like "1-3,5,7-10"
    static void
    formatRow(org.apache.poi.ss.usermodel.Sheet sheet, int row, ortus.boxlang.runtime.types.IStruct format)
    Formats all cells in a single row
    static void
    formatRows(org.apache.poi.ss.usermodel.Sheet sheet, String rows, ortus.boxlang.runtime.types.IStruct format)
    Formats all cells in multiple rows specified by a comma-separated string or range Format: "1,3,5" or "1-5,7-10" or combinations like "1-3,5,7-10"
    static ortus.boxlang.runtime.types.IStruct
    getActiveCell(org.apache.poi.ss.usermodel.Sheet sheet)
    Gets the currently active/selected cell in a sheet
    static org.apache.poi.ss.usermodel.Sheet
    getActiveSheet(org.apache.poi.ss.usermodel.Workbook workbook)
    Gets the active sheet from the workbook
    static ortus.boxlang.runtime.types.Array
    getAllCellComments(org.apache.poi.ss.usermodel.Sheet sheet)
    Get all cell comments from a sheet as an array of structs
    static boolean
    getAutoCalculate(org.apache.poi.ss.usermodel.Workbook workbook)
    Gets whether formulas automatically calculate
    static org.apache.poi.ss.usermodel.Cell
    getCell(org.apache.poi.ss.usermodel.Sheet sheet, int row, int col)
    Gets a cell at the specified row and column, returning null if it doesn't exist
    static String
    getCellAddress(org.apache.poi.ss.usermodel.Workbook workbook, int row, int column)
    Gets the cell address (e.g., "A1", "B5") for a given row and column
    static String
    getCellComment(org.apache.poi.ss.usermodel.Cell cell)
    Gets a comment from a cell
    static ortus.boxlang.runtime.types.IStruct
    getCellCommentAsStruct(org.apache.poi.ss.usermodel.Cell cell)
    Get cell comment as a struct with Author, Column, Comment, Row keys
    static ortus.boxlang.runtime.types.IStruct
    getCellFormat(org.apache.poi.ss.usermodel.Cell cell)
    Gets the formatting information of a cell
    static ortus.boxlang.runtime.types.IStruct
    getCellHyperlink(org.apache.poi.ss.usermodel.Sheet sheet, int row, int column)
    Gets the hyperlink from a cell
    static String
    getCellType(org.apache.poi.ss.usermodel.Cell cell)
    Gets the Excel cell type of a cell
    static Object
    getCellValue(org.apache.poi.ss.usermodel.Cell cell)
    Gets the cell value as an Object, handling different cell types
    static int
    getColumnCount(org.apache.poi.ss.usermodel.Sheet sheet)
    Gets the number of columns that contain data in the specified sheet
    static int
    getColumnCount(org.apache.poi.ss.usermodel.Workbook workbook)
    Gets the number of columns that contain data in the active sheet
    static double
    getColumnWidth(org.apache.poi.ss.usermodel.Sheet sheet, int column, boolean returnWidthInPixels)
    Gets the width of a column.
    static ortus.boxlang.runtime.types.IStruct
    getInfo(org.apache.poi.ss.usermodel.Workbook workbook)
    Gets information about the spreadsheet as a struct
    static int
    getLastRowNumber(org.apache.poi.ss.usermodel.Sheet sheet)
    Gets the last row number with data in the sheet.
    static org.apache.poi.ss.usermodel.Cell
    getOrCreateCell(org.apache.poi.ss.usermodel.Sheet sheet, int row, int col)
    Gets a cell at the specified row and column, creating it if it doesn't exist
    static String
    getPrintOrientation(org.apache.poi.ss.usermodel.Sheet sheet)
    Gets the print orientation of the sheet.
    static boolean
    getRecalculateFormulasOnNextOpen(org.apache.poi.ss.usermodel.Workbook workbook)
    Gets whether formulas will be recalculated when the file is opened
    static int
    getRowCount(org.apache.poi.ss.usermodel.Sheet sheet)
    Gets the number of rows that contain data in the specified sheet
    static int
    getRowCount(org.apache.poi.ss.usermodel.Workbook workbook)
    Gets the number of rows that contain data in the active sheet
    static ortus.boxlang.runtime.types.Array
    getSheetNames(org.apache.poi.ss.usermodel.Workbook workbook)
    Gets the names of all sheets in the workbook
    static ortus.boxlang.runtime.types.IStruct
    getSheetsVisibility(org.apache.poi.ss.usermodel.Workbook workbook)
    Gets the visibility status of all sheets in the workbook
    static void
    groupColumns(org.apache.poi.ss.usermodel.Sheet sheet, int startColumn, int endColumn, Boolean isCollapsed)
    Groups columns together in the active sheet
    static void
    groupRows(org.apache.poi.ss.usermodel.Sheet sheet, int startRow, int endRow, Boolean isCollapsed)
    Groups rows together in the active sheet
    static boolean
    hasSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName)
    Checks if a sheet with the given name exists in the workbook
    static void
    hideSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName)
    Hides a sheet from view
    static boolean
    isBinaryFormat(org.apache.poi.ss.usermodel.Workbook workbook)
    Determines if a workbook is in binary format (.xls)
    static boolean
    isColumnHidden(org.apache.poi.ss.usermodel.Sheet sheet, int column)
    Gets the hidden state of a column
    static boolean
    isRowHidden(org.apache.poi.ss.usermodel.Sheet sheet, int row)
    Gets the hidden state of a row
    static boolean
    isSpreadsheetFile(String filePath)
    Determines if a file is a valid spreadsheet file
    static boolean
    isXmlFormat(org.apache.poi.ss.usermodel.Workbook workbook)
    Determines if a workbook is in XML format (.xlsx)
    static void
    moveSheet(org.apache.poi.ss.usermodel.Workbook workbook, int fromIndex, int toIndex)
    Moves a sheet to a new position by sheet index
    static void
    moveSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName, int toIndex)
    Moves a sheet to a new position by sheet name
    static org.apache.poi.ss.usermodel.Workbook
    readWorkbook(String filePath)
    Reads a spreadsheet file from the given path
    static org.apache.poi.ss.usermodel.Workbook
    readWorkbook(String filePath, String password)
    Reads a spreadsheet file from the given path with optional password
    static void
    recalculateAllFormulas(org.apache.poi.ss.usermodel.Workbook workbook)
    Recalculates all formulas in the workbook
    static void
    removeColumnBreak(org.apache.poi.ss.usermodel.Sheet sheet, int zeroBasedCol)
    Removes a page (column) break on the specified 0-based column index on the sheet.
    static void
    removePrintGridlines(org.apache.poi.ss.usermodel.Sheet sheet)
    Removes print gridlines from the sheet (gridlines will not print when document is printed).
    static void
    removeRowBreak(org.apache.poi.ss.usermodel.Sheet sheet, int zeroBasedRow)
    Removes a page (row) break on the specified 0-based row index on the sheet.
    static void
    removeSheetByNumber(org.apache.poi.ss.usermodel.Workbook workbook, int sheetNumber)
    Removes a sheet by index (1-based)
    static void
    renameSheet(org.apache.poi.ss.usermodel.Workbook workbook, String oldName, String newName)
    Renames an existing sheet
    static void
    setActiveCell(org.apache.poi.ss.usermodel.Sheet sheet, int row, int col)
    Sets the active/selected cell in a sheet
    static void
    setActiveSheet(org.apache.poi.ss.usermodel.Workbook workbook, int sheetIndex)
    Sets the active sheet by index (0-based)
    static void
    setActiveSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName)
    Sets the active sheet by name
    static void
    setAutoCalculate(org.apache.poi.ss.usermodel.Workbook workbook, boolean autoCalculate)
    Sets whether formulas automatically calculate
    static void
    setCellComment(org.apache.poi.ss.usermodel.Cell cell, Object commentObj, String author)
    Sets a comment on a cell with optional formatting
    static void
    setCellHyperlink(org.apache.poi.ss.usermodel.Sheet sheet, int row, int column, String hyperlink, String label, org.apache.poi.ss.usermodel.Workbook workbook)
    Sets a hyperlink on a cell
    static void
    setCellRangeValue(org.apache.poi.ss.usermodel.Sheet sheet, Object value, int startRow, int startCol, int endRow, int endCol)
    Sets the same value to a range of cells
    static void
    setCellValue(org.apache.poi.ss.usermodel.Cell cell, Object value)
    Sets a cell value, automatically determining the appropriate cell type
    static void
    setColumnBreak(org.apache.poi.ss.usermodel.Sheet sheet, int zeroBasedCol)
    Sets a page (column) break on the specified 0-based column index on the sheet.
    static void
    setColumnHidden(org.apache.poi.ss.usermodel.Sheet sheet, int column, boolean hidden)
    Sets the hidden state of a column
    static void
    setDocumentProperties(org.apache.poi.ss.usermodel.Workbook workbook, ortus.boxlang.runtime.types.IStruct properties)
    Sets document properties/metadata for a workbook
    static void
    setFitToPage(org.apache.poi.ss.usermodel.Sheet sheet, boolean fitToPage, int pagesWide, int pagesHigh)
    Sets the fit-to-page print option for a sheet
    static void
    setFooter(org.apache.poi.ss.usermodel.Sheet sheet, ortus.boxlang.runtime.types.IStruct footer)
    Sets the footer text for the specified sheet.
    static void
    setFooterImage(org.apache.poi.ss.usermodel.Sheet sheet, String alignment, String imagePath, org.apache.poi.ss.usermodel.Workbook workbook)
    Sets an image in the footer of the specified sheet
    static void
    setHeader(org.apache.poi.ss.usermodel.Sheet sheet, ortus.boxlang.runtime.types.IStruct header)
    Sets the header text for the specified sheet.
    static void
    setHeaderImage(org.apache.poi.ss.usermodel.Sheet sheet, String alignment, String imagePath, org.apache.poi.ss.usermodel.Workbook workbook)
    Sets an image in the header of the specified sheet
    static void
    setRecalculateFormulasOnNextOpen(org.apache.poi.ss.usermodel.Workbook workbook, boolean recalculate)
    Sets whether formulas should be recalculated when the file is opened
    static void
    setRepeatingColumns(org.apache.poi.ss.usermodel.Workbook workbook, org.apache.poi.ss.usermodel.Sheet sheet, int startCol, int endCol)
    Sets columns to repeat on every printed page
    static void
    setRepeatingRows(org.apache.poi.ss.usermodel.Workbook workbook, org.apache.poi.ss.usermodel.Sheet sheet, int startRow, int endRow)
    Sets rows to repeat on every printed page
    static void
    setRowBreak(org.apache.poi.ss.usermodel.Sheet sheet, int zeroBasedRow)
    Sets a page (row) break on the specified 0-based row index on the sheet.
    static void
    setRowHidden(org.apache.poi.ss.usermodel.Sheet sheet, int row, boolean hidden)
    Sets the hidden state of a row
    static void
    setSheetPrintOrientation(org.apache.poi.ss.usermodel.Sheet sheet, String orientation)
    Sets the sheet print orientation
    static void
    shiftColumns(org.apache.poi.ss.usermodel.Sheet sheet, int start, int end, int n)
    Shifts columns left or right in a sheet Note: Apache POI doesn't provide a native column shift operation, so we implement it manually by copying cell data from the source columns to the destination columns.
    static void
    ungroupColumns(org.apache.poi.ss.usermodel.Sheet sheet, int startColumn, int endColumn)
    Ungroups columns in the active sheet
    static void
    ungroupRows(org.apache.poi.ss.usermodel.Sheet sheet, int startRow, int endRow)
    Ungroups rows in the active sheet
    static void
    unhideSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName)
    Unhides a sheet to make it visible
    static void
    writeWorkbook(org.apache.poi.ss.usermodel.Workbook workbook, String filePath, boolean overwrite)
    Writes a workbook to the specified file path
    static void
    writeWorkbook(org.apache.poi.ss.usermodel.Workbook workbook, String filePath, boolean overwrite, String password)
    Writes a workbook to the specified file path with optional password protection

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

  • Constructor Details

    • SpreadsheetUtil

      public SpreadsheetUtil()

  • Method Details

    • createWorkbook

      public static org.apache.poi.ss.usermodel.Workbook createWorkbook(boolean xmlFormat, String sheetName)
      Creates a new workbook based on the specified format
      Parameters:
      xmlFormat - True for .xlsx format, false for .xls format
      sheetName - The name of the initial sheet (defaults to "Sheet1")
      Returns:
      A new workbook instance

    • createXlsxWorkbook

      public static org.apache.poi.ss.usermodel.Workbook createXlsxWorkbook(String sheetName)
      Creates a new .xlsx workbook
      Parameters:
      sheetName - The name of the initial sheet
      Returns:
      A new XLSX workbook

    • createXlsWorkbook

      public static org.apache.poi.ss.usermodel.Workbook createXlsWorkbook(String sheetName)
      Creates a new .xls workbook
      Parameters:
      sheetName - The name of the initial sheet
      Returns:
      A new XLS workbook

    • readWorkbook

      public static org.apache.poi.ss.usermodel.Workbook readWorkbook(String filePath)
      Reads a spreadsheet file from the given path
      Parameters:
      filePath - The absolute path to the spreadsheet file
      Returns:
      A workbook object
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxIOException - If the file cannot be read or doesn't exist

    • readWorkbook

      public static org.apache.poi.ss.usermodel.Workbook readWorkbook(String filePath, String password)
      Reads a spreadsheet file from the given path with optional password
      Parameters:
      filePath - The absolute path to the spreadsheet file
      password - Optional password for encrypted files
      Returns:
      A workbook object
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxIOException - If the file cannot be read or doesn't exist

    • writeWorkbook

      public static void writeWorkbook(org.apache.poi.ss.usermodel.Workbook workbook, String filePath, boolean overwrite)
      Writes a workbook to the specified file path
      Parameters:
      workbook - The workbook to write
      filePath - The absolute path where to save the file
      overwrite - Whether to overwrite existing files
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxIOException - If the file cannot be written

    • writeWorkbook

      public static void writeWorkbook(org.apache.poi.ss.usermodel.Workbook workbook, String filePath, boolean overwrite, String password)
      Writes a workbook to the specified file path with optional password protection
      Parameters:
      workbook - The workbook to write
      filePath - The absolute path where to save the file
      overwrite - Whether to overwrite existing files
      password - Optional password to protect the file (only works with .xlsx format)
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxIOException - If the file cannot be written

    • getCellValue

      public static Object getCellValue(org.apache.poi.ss.usermodel.Cell cell)
      Gets the cell value as an Object, handling different cell types
      Parameters:
      cell - The cell to get the value from
      Returns:
      The cell value as an appropriate Java object

    • setCellValue

      public static void setCellValue(org.apache.poi.ss.usermodel.Cell cell, Object value)
      Sets a cell value, automatically determining the appropriate cell type
      Parameters:
      cell - The cell to set the value for
      value - The value to set

    • clearCell

      public static void clearCell(org.apache.poi.ss.usermodel.Sheet sheet, int row, int col)
      Clears the specified cell of all styles and values
      Parameters:
      sheet - The sheet containing the cell
      row - The 1-based row number
      col - The 1-based column number

    • clearCellRange

      public static void clearCellRange(org.apache.poi.ss.usermodel.Sheet sheet, int startRow, int startCol, int endRow, int endCol)
      Clears a range of cells of all styles and values
      Parameters:
      sheet - The sheet containing the cells
      startRow - The 1-based starting row (inclusive)
      startCol - The 1-based starting column (inclusive)
      endRow - The 1-based ending row (inclusive)
      endCol - The 1-based ending column (inclusive)

    • getCellType

      public static String getCellType(org.apache.poi.ss.usermodel.Cell cell)
      Gets the Excel cell type of a cell
      Parameters:
      cell - The cell to check
      Returns:
      Cell type as a string: "BLANK", "BOOLEAN", "ERROR", "FORMULA", "NUMERIC", or "STRING"

    • getCellFormat

      public static ortus.boxlang.runtime.types.IStruct getCellFormat(org.apache.poi.ss.usermodel.Cell cell)
      Gets the formatting information of a cell
      Parameters:
      cell - The cell to get formatting from
      Returns:
      A struct containing all formatting attributes

    • setCellRangeValue

      public static void setCellRangeValue(org.apache.poi.ss.usermodel.Sheet sheet, Object value, int startRow, int startCol, int endRow, int endCol)
      Sets the same value to a range of cells
      Parameters:
      sheet - The sheet containing the cells
      value - The value to set for all cells in the range
      startRow - The 1-based starting row (inclusive)
      startCol - The 1-based starting column (inclusive)
      endRow - The 1-based ending row (inclusive)
      endCol - The 1-based ending column (inclusive)

    • getActiveCell

      public static ortus.boxlang.runtime.types.IStruct getActiveCell(org.apache.poi.ss.usermodel.Sheet sheet)
      Gets the currently active/selected cell in a sheet
      Parameters:
      sheet - The sheet
      Returns:
      A struct with keys "row" and "column" (both 1-based), or with 1,1 as default

    • setActiveCell

      public static void setActiveCell(org.apache.poi.ss.usermodel.Sheet sheet, int row, int col)
      Sets the active/selected cell in a sheet
      Parameters:
      sheet - The sheet
      row - The 1-based row number
      col - The 1-based column number

    • setRowBreak

      public static void setRowBreak(org.apache.poi.ss.usermodel.Sheet sheet, int zeroBasedRow)
      Sets a page (row) break on the specified 0-based row index on the sheet.
      Parameters:
      sheet - the sheet
      zeroBasedRow - the 0-based row index

    • removeRowBreak

      public static void removeRowBreak(org.apache.poi.ss.usermodel.Sheet sheet, int zeroBasedRow)
      Removes a page (row) break on the specified 0-based row index on the sheet.
      Parameters:
      sheet - the sheet
      zeroBasedRow - the 0-based row index

    • setColumnBreak

      public static void setColumnBreak(org.apache.poi.ss.usermodel.Sheet sheet, int zeroBasedCol)
      Sets a page (column) break on the specified 0-based column index on the sheet.
      Parameters:
      sheet - the sheet
      zeroBasedCol - the 0-based column index

    • removeColumnBreak

      public static void removeColumnBreak(org.apache.poi.ss.usermodel.Sheet sheet, int zeroBasedCol)
      Removes a page (column) break on the specified 0-based column index on the sheet.
      Parameters:
      sheet - the sheet
      zeroBasedCol - the 0-based column index

    • setFitToPage

      public static void setFitToPage(org.apache.poi.ss.usermodel.Sheet sheet, boolean fitToPage, int pagesWide, int pagesHigh)
      Sets the fit-to-page print option for a sheet
      Parameters:
      sheet - The sheet to configure
      fitToPage - Whether to enable fit-to-page
      pagesWide - Number of pages to fit columns into (0 = no horizontal fit)
      pagesHigh - Number of pages to fit rows into (0 = no vertical fit)
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null

    • addPrintGridlines

      public static void addPrintGridlines(org.apache.poi.ss.usermodel.Sheet sheet)
      Adds print gridlines to the sheet (gridlines will print when document is printed).
      Parameters:
      sheet - The sheet to modify
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null

    • removePrintGridlines

      public static void removePrintGridlines(org.apache.poi.ss.usermodel.Sheet sheet)
      Removes print gridlines from the sheet (gridlines will not print when document is printed).
      Parameters:
      sheet - The sheet to modify
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null

    • getColumnWidth

      public static double getColumnWidth(org.apache.poi.ss.usermodel.Sheet sheet, int column, boolean returnWidthInPixels)
      Gets the width of a column.
      Parameters:
      sheet - The sheet containing the column
      column - The column number (0-based)
      returnWidthInPixels - If true, returns width in pixels; if false, returns width in points
      Returns:
      Column width as a double value
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null or column is invalid

    • getLastRowNumber

      public static int getLastRowNumber(org.apache.poi.ss.usermodel.Sheet sheet)
      Gets the last row number with data in the sheet.
      Parameters:
      sheet - The sheet to query
      Returns:
      The last row number (0-based), or -1 if sheet is empty
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null

    • getPrintOrientation

      public static String getPrintOrientation(org.apache.poi.ss.usermodel.Sheet sheet)
      Gets the print orientation of the sheet.
      Parameters:
      sheet - The sheet to query
      Returns:
      "portrait" or "landscape"
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null

    • setColumnHidden

      public static void setColumnHidden(org.apache.poi.ss.usermodel.Sheet sheet, int column, boolean hidden)
      Sets the hidden state of a column
      Parameters:
      sheet - The sheet containing the column
      column - The column number (1-based)
      hidden - True to hide, false to show
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null or column is invalid

    • isColumnHidden

      public static boolean isColumnHidden(org.apache.poi.ss.usermodel.Sheet sheet, int column)
      Gets the hidden state of a column
      Parameters:
      sheet - The sheet containing the column
      column - The column number (1-based)
      Returns:
      true if column is hidden, false otherwise
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null or column is invalid

    • setRowHidden

      public static void setRowHidden(org.apache.poi.ss.usermodel.Sheet sheet, int row, boolean hidden)
      Sets the hidden state of a row
      Parameters:
      sheet - The sheet containing the row
      row - The row number (1-based)
      hidden - True to hide, false to show
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null or row is invalid

    • isRowHidden

      public static boolean isRowHidden(org.apache.poi.ss.usermodel.Sheet sheet, int row)
      Gets the hidden state of a row
      Parameters:
      sheet - The sheet containing the row
      row - The row number (1-based)
      Returns:
      true if row is hidden, false otherwise
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - if sheet is null or row is invalid

    • getActiveSheet

      public static org.apache.poi.ss.usermodel.Sheet getActiveSheet(org.apache.poi.ss.usermodel.Workbook workbook)
      Gets the active sheet from the workbook
      Parameters:
      workbook - The workbook
      Returns:
      The active sheet

    • setActiveSheet

      public static void setActiveSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName)
      Sets the active sheet by name
      Parameters:
      workbook - The workbook
      sheetName - The name of the sheet to make active
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - If the sheet doesn't exist

    • setActiveSheet

      public static void setActiveSheet(org.apache.poi.ss.usermodel.Workbook workbook, int sheetIndex)
      Sets the active sheet by index (0-based)
      Parameters:
      workbook - The workbook
      sheetIndex - The index of the sheet to make active (0-based)
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - If the sheet index is invalid

    • renameSheet

      public static void renameSheet(org.apache.poi.ss.usermodel.Workbook workbook, String oldName, String newName)
      Renames an existing sheet
      Parameters:
      workbook - The workbook
      oldName - The current name of the sheet
      newName - The new name for the sheet
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - If the sheet doesn't exist or new name is invalid

    • removeSheetByNumber

      public static void removeSheetByNumber(org.apache.poi.ss.usermodel.Workbook workbook, int sheetNumber)
      Removes a sheet by index (1-based)
      Parameters:
      workbook - The workbook
      sheetNumber - The sheet index (1-based)
      Throws:
      ortus.boxlang.runtime.types.exceptions.BoxRuntimeException - If the sheet index is invalid

    • getOrCreateCell

      public static org.apache.poi.ss.usermodel.Cell getOrCreateCell(org.apache.poi.ss.usermodel.Sheet sheet, int row, int col)
      Gets a cell at the specified row and column, creating it if it doesn't exist
      Parameters:
      sheet - The sheet containing the cell
      row - The row number (1-based)
      col - The column number (1-based)
      Returns:
      The cell object

    • getCell

      public static org.apache.poi.ss.usermodel.Cell getCell(org.apache.poi.ss.usermodel.Sheet sheet, int row, int col)
      Gets a cell at the specified row and column, returning null if it doesn't exist
      Parameters:
      sheet - The sheet containing the cell
      row - The row number (1-based)
      col - The column number (1-based)
      Returns:
      The cell object or null if it doesn't exist

    • isSpreadsheetFile

      public static boolean isSpreadsheetFile(String filePath)
      Determines if a file is a valid spreadsheet file
      Parameters:
      filePath - The absolute path to the file
      Returns:
      True if the file is a valid spreadsheet, false otherwise

    • isXmlFormat

      public static boolean isXmlFormat(org.apache.poi.ss.usermodel.Workbook workbook)
      Determines if a workbook is in XML format (.xlsx)
      Parameters:
      workbook - The workbook to check
      Returns:
      True if the workbook is in XML format, false if binary format

    • isBinaryFormat

      public static boolean isBinaryFormat(org.apache.poi.ss.usermodel.Workbook workbook)
      Determines if a workbook is in binary format (.xls)
      Parameters:
      workbook - The workbook to check
      Returns:
      True if the workbook is in binary format, false if XML format

    • getRowCount

      public static int getRowCount(org.apache.poi.ss.usermodel.Workbook workbook)
      Gets the number of rows that contain data in the active sheet
      Parameters:
      workbook - The workbook
      Returns:
      The number of rows with data

    • getRowCount

      public static int getRowCount(org.apache.poi.ss.usermodel.Sheet sheet)
      Gets the number of rows that contain data in the specified sheet
      Parameters:
      sheet - The sheet
      Returns:
      The number of rows with data

    • getColumnCount

      public static int getColumnCount(org.apache.poi.ss.usermodel.Workbook workbook)
      Gets the number of columns that contain data in the active sheet
      Parameters:
      workbook - The workbook
      Returns:
      The number of columns with data

    • getColumnCount

      public static int getColumnCount(org.apache.poi.ss.usermodel.Sheet sheet)
      Gets the number of columns that contain data in the specified sheet
      Parameters:
      sheet - The sheet
      Returns:
      The number of columns with data

    • autoSizeColumns

      public static void autoSizeColumns(org.apache.poi.ss.usermodel.Workbook workbook)
      Auto-sizes all columns in the active sheet
      Parameters:
      workbook - The workbook

    • autoSizeColumns

      public static void autoSizeColumns(org.apache.poi.ss.usermodel.Sheet sheet)
      Auto-sizes all columns in the specified sheet
      Parameters:
      sheet - The sheet

    • autoSizeColumn

      public static void autoSizeColumn(org.apache.poi.ss.usermodel.Workbook workbook, int column)
      Auto-sizes a specific column in the active sheet
      Parameters:
      workbook - The workbook
      column - The column number (1-based)

    • autoSizeColumn

      public static void autoSizeColumn(org.apache.poi.ss.usermodel.Sheet sheet, int column)
      Auto-sizes a specific column in the specified sheet
      Parameters:
      sheet - The sheet
      column - The column number (1-based)

    • deleteColumn

      public static void deleteColumn(org.apache.poi.ss.usermodel.Sheet sheet, int column)
      Deletes a column by clearing all cells in that column Note: Apache POI doesn't support true column deletion, so we clear the column content
      Parameters:
      sheet - The sheet
      column - The column number to delete (1-based)

    • deleteColumns

      public static void deleteColumns(org.apache.poi.ss.usermodel.Sheet sheet, int startColumn, int endColumn)
      Deletes multiple columns by clearing all cells in those columns
      Parameters:
      sheet - The sheet
      startColumn - The starting column number (1-based, inclusive)
      endColumn - The ending column number (1-based, inclusive)

    • formatCell

      public static void formatCell(org.apache.poi.ss.usermodel.Cell cell, ortus.boxlang.runtime.types.IStruct format)
      Applies formatting to a specific cell based on a format structure
      Parameters:
      cell - The cell to format
      format - A structure containing formatting options

    • setHeader

      public static void setHeader(org.apache.poi.ss.usermodel.Sheet sheet, ortus.boxlang.runtime.types.IStruct header)
      Sets the header text for the specified sheet. Expects a struct with optional keys: left, center, right
      Parameters:
      sheet - The sheet to update
      header - The header struct containing left/center/right text

    • setFooter

      public static void setFooter(org.apache.poi.ss.usermodel.Sheet sheet, ortus.boxlang.runtime.types.IStruct footer)
      Sets the footer text for the specified sheet. Expects a struct with optional keys: left, center, right
      Parameters:
      sheet - The sheet to update
      footer - The footer struct containing left/center/right text

    • setHeaderImage

      public static void setHeaderImage(org.apache.poi.ss.usermodel.Sheet sheet, String alignment, String imagePath, org.apache.poi.ss.usermodel.Workbook workbook)
      Sets an image in the header of the specified sheet
      Parameters:
      sheet - The sheet to update
      alignment - The alignment: "left", "center", or "right"
      imagePath - The absolute path to the image file
      workbook - The workbook instance

    • setFooterImage

      public static void setFooterImage(org.apache.poi.ss.usermodel.Sheet sheet, String alignment, String imagePath, org.apache.poi.ss.usermodel.Workbook workbook)
      Sets an image in the footer of the specified sheet
      Parameters:
      sheet - The sheet to update
      alignment - The alignment: "left", "center", or "right"
      imagePath - The absolute path to the image file
      workbook - The workbook instance

    • setCellComment

      public static void setCellComment(org.apache.poi.ss.usermodel.Cell cell, Object commentObj, String author)
      Sets a comment on a cell with optional formatting
      Parameters:
      cell - The cell to add comment to
      commentObj - The comment text (String) or struct with comment details
      author - The comment author (optional, used if commentObj is a String)

    • getCellComment

      public static String getCellComment(org.apache.poi.ss.usermodel.Cell cell)
      Gets a comment from a cell
      Parameters:
      cell - The cell to get comment from
      Returns:
      The comment text, or null if no comment exists

    • getCellCommentAsStruct

      public static ortus.boxlang.runtime.types.IStruct getCellCommentAsStruct(org.apache.poi.ss.usermodel.Cell cell)
      Get cell comment as a struct with Author, Column, Comment, Row keys
      Parameters:
      cell - The cell to get the comment from
      Returns:
      A struct with {Author, Column, Comment, Row} or null if no comment exists

    • getAllCellComments

      public static ortus.boxlang.runtime.types.Array getAllCellComments(org.apache.poi.ss.usermodel.Sheet sheet)
      Get all cell comments from a sheet as an array of structs
      Parameters:
      sheet - The sheet to get comments from
      Returns:
      An array of structs with {Author, Column, Comment, Row} for each comment

    • setDocumentProperties

      public static void setDocumentProperties(org.apache.poi.ss.usermodel.Workbook workbook, ortus.boxlang.runtime.types.IStruct properties)
      Sets document properties/metadata for a workbook
      Parameters:
      workbook - The workbook to set properties on
      properties - A struct containing metadata properties (AUTHOR, TITLE, SUBJECT, COMMENTS, KEYWORDS, CATEGORY, MANAGER, COMPANY, LASTAUTHOR)

    • createSplitPane

      public static void createSplitPane(org.apache.poi.ss.usermodel.Sheet sheet, int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane)
      Creates a split pane in a sheet
      Parameters:
      sheet - The sheet to add split pane to
      xSplitPos - The horizontal (x-axis) position of the split in 1/20th of a pixel units
      ySplitPos - The vertical (y-axis) position of the split in 1/20th of a pixel units
      leftmostColumn - The leftmost column visible in the right pane (1-based, will be converted to 0-based for POI)
      topRow - The top row visible in the bottom pane (1-based, will be converted to 0-based for POI)
      activePane - The active pane indicator (0=lower-left, 1=upper-right, 2=lower-right, 3=upper-left)

    • createSplitPane

      public static void createSplitPane(org.apache.poi.ss.usermodel.Sheet sheet, int xSplitPos, int ySplitPos, int leftmostColumn, int topRow)
      Creates a split pane in a sheet with default active pane
      Parameters:
      sheet - The sheet to add split pane to
      xSplitPos - The horizontal position of the split
      ySplitPos - The vertical position of the split
      leftmostColumn - The leftmost column visible in the right pane
      topRow - The top row visible in the bottom pane

    • formatRow

      public static void formatRow(org.apache.poi.ss.usermodel.Sheet sheet, int row, ortus.boxlang.runtime.types.IStruct format)
      Formats all cells in a single row
      Parameters:
      sheet - The sheet containing the row
      row - The row number (1-based)
      format - A structure containing formatting options

    • formatRows

      public static void formatRows(org.apache.poi.ss.usermodel.Sheet sheet, String rows, ortus.boxlang.runtime.types.IStruct format)
      Formats all cells in multiple rows specified by a comma-separated string or range Format: "1,3,5" or "1-5,7-10" or combinations like "1-3,5,7-10"
      Parameters:
      sheet - The sheet containing the rows
      rows - A string specifying which rows to format
      format - A structure containing formatting options

    • formatColumn

      public static void formatColumn(org.apache.poi.ss.usermodel.Sheet sheet, int column, ortus.boxlang.runtime.types.IStruct format)
      Formats all cells in a single column
      Parameters:
      sheet - The sheet containing the column
      column - The column number (1-based)
      format - A structure containing formatting options

    • formatColumns

      public static void formatColumns(org.apache.poi.ss.usermodel.Sheet sheet, String columns, ortus.boxlang.runtime.types.IStruct format)
      Formats all cells in multiple columns specified by a comma-separated string or range Format: "1,3,5" or "1-5,7-10" or combinations like "1-3,5,7-10"
      Parameters:
      sheet - The sheet containing the columns
      columns - A string specifying which columns to format
      format - A structure containing formatting options

    • shiftColumns

      public static void shiftColumns(org.apache.poi.ss.usermodel.Sheet sheet, int start, int end, int n)
      Shifts columns left or right in a sheet Note: Apache POI doesn't provide a native column shift operation, so we implement it manually by copying cell data from the source columns to the destination columns.
      Parameters:
      sheet - The sheet containing the columns
      start - The starting column number (1-based)
      end - The ending column number (1-based)
      n - The number of columns to shift (positive for right, negative for left)

    • addImage

      public static void addImage(org.apache.poi.ss.usermodel.Sheet sheet, String filepath, int row, int col, org.apache.poi.ss.usermodel.Workbook workbook)
      Adds an image to a sheet at a specified cell location
      Parameters:
      sheet - The sheet to add the image to
      filepath - The absolute path to the image file
      row - The starting row index (1-based, converted to 0-based)
      col - The starting column index (1-based, converted to 0-based)
      workbook - The workbook instance (needed to get image bytes)

    • addImage

      public static void addImage(org.apache.poi.ss.usermodel.Sheet sheet, String filepath, int startRow, int startCol, int endRow, int endCol, org.apache.poi.ss.usermodel.Workbook workbook)
      Adds an image with custom anchor coordinates
      Parameters:
      sheet - The sheet to add the image to
      filepath - The absolute path to the image file
      startRow - The starting row (1-based)
      startCol - The starting column (1-based)
      endRow - The ending row (1-based)
      endCol - The ending column (1-based)
      workbook - The workbook instance

    • setCellHyperlink

      public static void setCellHyperlink(org.apache.poi.ss.usermodel.Sheet sheet, int row, int column, String hyperlink, String label, org.apache.poi.ss.usermodel.Workbook workbook)
      Sets a hyperlink on a cell
      Parameters:
      sheet - The sheet containing the cell
      row - The row number (1-based)
      column - The column number (1-based)
      hyperlink - The hyperlink URL
      label - The optional display label for the hyperlink
      workbook - The workbook instance

    • getCellHyperlink

      public static ortus.boxlang.runtime.types.IStruct getCellHyperlink(org.apache.poi.ss.usermodel.Sheet sheet, int row, int column)
      Gets the hyperlink from a cell
      Parameters:
      sheet - The sheet containing the cell
      row - The row number (1-based)
      column - The column number (1-based)
      Returns:
      A struct containing hyperlink information, or null if no hyperlink exists

    • formatCellRange

      public static void formatCellRange(org.apache.poi.ss.usermodel.Sheet sheet, ortus.boxlang.runtime.types.IStruct format, int startRow, int startCol, int endRow, int endCol, org.apache.poi.ss.usermodel.Workbook workbook)
      Formats a range of cells with a single format struct
      Parameters:
      sheet - The sheet containing the cells
      format - The format struct containing style properties
      startRow - The starting row (1-based)
      startCol - The starting column (1-based)
      endRow - The ending row (1-based)
      endCol - The ending column (1-based)
      workbook - The workbook instance

    • setSheetPrintOrientation

      public static void setSheetPrintOrientation(org.apache.poi.ss.usermodel.Sheet sheet, String orientation)
      Sets the sheet print orientation
      Parameters:
      sheet - The sheet to modify
      orientation - The orientation ("portrait" or "landscape")

    • setRepeatingColumns

      public static void setRepeatingColumns(org.apache.poi.ss.usermodel.Workbook workbook, org.apache.poi.ss.usermodel.Sheet sheet, int startCol, int endCol)
      Sets columns to repeat on every printed page
      Parameters:
      workbook - The workbook instance
      sheet - The sheet to modify
      startCol - The starting column (1-based)
      endCol - The ending column (1-based)

    • setRepeatingRows

      public static void setRepeatingRows(org.apache.poi.ss.usermodel.Workbook workbook, org.apache.poi.ss.usermodel.Sheet sheet, int startRow, int endRow)
      Sets rows to repeat on every printed page
      Parameters:
      workbook - The workbook instance
      sheet - The sheet to modify
      startRow - The starting row (1-based)
      endRow - The ending row (1-based)

    • groupRows

      public static void groupRows(org.apache.poi.ss.usermodel.Sheet sheet, int startRow, int endRow, Boolean isCollapsed)
      Groups rows together in the active sheet
      Parameters:
      sheet - The sheet to group rows in
      startRow - The starting row number (1-based)
      endRow - The ending row number (1-based)
      isCollapsed - Whether the group should be initially collapsed (optional)

    • ungroupRows

      public static void ungroupRows(org.apache.poi.ss.usermodel.Sheet sheet, int startRow, int endRow)
      Ungroups rows in the active sheet
      Parameters:
      sheet - The sheet to ungroup rows in
      startRow - The starting row number (1-based)
      endRow - The ending row number (1-based)

    • groupColumns

      public static void groupColumns(org.apache.poi.ss.usermodel.Sheet sheet, int startColumn, int endColumn, Boolean isCollapsed)
      Groups columns together in the active sheet
      Parameters:
      sheet - The sheet to group columns in
      startColumn - The starting column number (1-based)
      endColumn - The ending column number (1-based)
      isCollapsed - Whether the group should be initially collapsed (optional)

    • ungroupColumns

      public static void ungroupColumns(org.apache.poi.ss.usermodel.Sheet sheet, int startColumn, int endColumn)
      Ungroups columns in the active sheet
      Parameters:
      sheet - The sheet to ungroup columns in
      startColumn - The starting column number (1-based)
      endColumn - The ending column number (1-based)

    • getAutoCalculate

      public static boolean getAutoCalculate(org.apache.poi.ss.usermodel.Workbook workbook)
      Gets whether formulas automatically calculate
      Parameters:
      workbook - The workbook to check
      Returns:
      True if auto-calculation is enabled (XSSF only, always true for HSSF)

    • setAutoCalculate

      public static void setAutoCalculate(org.apache.poi.ss.usermodel.Workbook workbook, boolean autoCalculate)
      Sets whether formulas automatically calculate
      Parameters:
      workbook - The workbook to update
      autoCalculate - Whether to enable auto-calculation

    • getRecalculateFormulasOnNextOpen

      public static boolean getRecalculateFormulasOnNextOpen(org.apache.poi.ss.usermodel.Workbook workbook)
      Gets whether formulas will be recalculated when the file is opened
      Parameters:
      workbook - The workbook to check
      Returns:
      True if force recalculation is enabled, false otherwise

    • setRecalculateFormulasOnNextOpen

      public static void setRecalculateFormulasOnNextOpen(org.apache.poi.ss.usermodel.Workbook workbook, boolean recalculate)
      Sets whether formulas should be recalculated when the file is opened
      Parameters:
      workbook - The workbook to update
      recalculate - Whether to force recalculation on next open

    • hideSheet

      public static void hideSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName)
      Hides a sheet from view
      Parameters:
      workbook - The workbook containing the sheet
      sheetName - The name of the sheet to hide

    • unhideSheet

      public static void unhideSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName)
      Unhides a sheet to make it visible
      Parameters:
      workbook - The workbook containing the sheet
      sheetName - The name of the sheet to unhide

    • moveSheet

      public static void moveSheet(org.apache.poi.ss.usermodel.Workbook workbook, int fromIndex, int toIndex)
      Moves a sheet to a new position by sheet index
      Parameters:
      workbook - The workbook containing the sheet
      fromIndex - The current sheet index (0-based)
      toIndex - The target sheet index (0-based)

    • moveSheet

      public static void moveSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName, int toIndex)
      Moves a sheet to a new position by sheet name
      Parameters:
      workbook - The workbook containing the sheet
      sheetName - The name of the sheet to move
      toIndex - The target sheet index (0-based)

    • getCellAddress

      public static String getCellAddress(org.apache.poi.ss.usermodel.Workbook workbook, int row, int column)
      Gets the cell address (e.g., "A1", "B5") for a given row and column
      Parameters:
      workbook - The workbook containing the cell
      row - The row number (1-based)
      column - The column number (1-based)
      Returns:
      The cell address string (e.g., "A1")

    • getInfo

      public static ortus.boxlang.runtime.types.IStruct getInfo(org.apache.poi.ss.usermodel.Workbook workbook)
      Gets information about the spreadsheet as a struct
      Parameters:
      workbook - The workbook containing the sheet
      Returns:
      A struct containing sheet properties

    • getSheetsVisibility

      public static ortus.boxlang.runtime.types.IStruct getSheetsVisibility(org.apache.poi.ss.usermodel.Workbook workbook)
      Gets the visibility status of all sheets in the workbook
      Parameters:
      workbook - The workbook to get sheet visibility from
      Returns:
      A struct with sheet names as keys and visibility status ("visible", "hidden", "veryHidden") as values

    • hasSheet

      public static boolean hasSheet(org.apache.poi.ss.usermodel.Workbook workbook, String sheetName)
      Checks if a sheet with the given name exists in the workbook
      Parameters:
      workbook - The workbook to check
      sheetName - The name of the sheet to look for
      Returns:
      True if the sheet exists, false otherwise

    • getSheetNames

      public static ortus.boxlang.runtime.types.Array getSheetNames(org.apache.poi.ss.usermodel.Workbook workbook)
      Gets the names of all sheets in the workbook
      Parameters:
      workbook - The workbook to get sheet names from
      Returns:
      An array of sheet names

    • recalculateAllFormulas

      public static void recalculateAllFormulas(org.apache.poi.ss.usermodel.Workbook workbook)
      Recalculates all formulas in the workbook
      Parameters:
      workbook - The workbook containing formulas to recalculate