Class SpreadsheetUtil

Constructor Detail

• SpreadsheetUtil

  public SpreadsheetUtil()

Method Detail

• toKey

  public static final String toKey​(int col,
                                   int row)

  Translates cell coordinates to a cell key used to identify cells in the server-client communication.

  Parameters:

  col - Column index, 1-based

  row - Row index 1-based

  Returns:

  Cell key

• toKey

  public static final String toKey​(org.apache.poi.ss.usermodel.Cell cell)

  Translates cell coordinates from the given Cell object to a cell key used to identify cells in the server-client communication.

  Parameters:

  cell - Cell to fetch the coordinates from

  Returns:

  Cell key

• cellContainsDate

  public static boolean cellContainsDate​(org.apache.poi.ss.usermodel.Cell cell)

  Determines whether the given cell contains a date or not.

  Parameters:

  cell - Cell to examine

  Returns:

  true if the cell contains a date

• relativeToAbsolute

  public static org.apache.poi.ss.util.CellReference relativeToAbsolute​(Spreadsheet sheet,
                                                                        org.apache.poi.ss.util.CellReference cell)

• getColHeader

  public static String getColHeader​(int columnIndex)

  Generates the column header for column with the given index

  Parameters:

  columnIndex - Index of column, 1-based

  Returns:

  Generated column header

• getColHeaderIndex

  public static int getColHeaderIndex​(String header)

  Returns the column index for the column with the given header.

  Parameters:

  header - Column header

  Returns:

  Index of column, 1-based

• isCellInRange

  public static boolean isCellInRange​(org.apache.poi.ss.util.CellReference cellReference,
                                      org.apache.poi.ss.util.CellRangeAddress cellRange)

  Determines whether the given cell is within the given range.

  Parameters:

  cellReference - Target cell reference

  cellRange - Cell range to check

  Returns:

  true if the cell is in the range

• getFirstVisibleSheetPOIIndex

  public static int getFirstVisibleSheetPOIIndex​(org.apache.poi.ss.usermodel.Workbook workbook)

  Returns the POI index of the first visible sheet (not hidden or very hidden). If no sheets are visible, returns 0. This is not be possible at least in Excel, but unfortunately POI allows it.

  Parameters:

  workbook - Workbook to get the sheets from

  Returns:

  Index of the first visible sheet, 0-based

• getNumberOfVisibleSheets

  public static int getNumberOfVisibleSheets​(org.apache.poi.ss.usermodel.Workbook workbook)

  Returns the number of visible sheets (not hidden or very hidden) in the given Workbook.

  Parameters:

  workbook - Workbook to get the sheets from

  Returns:

  Number of visible sheets

• getColumnIndexFromKey

  public static int getColumnIndexFromKey​(String key)

  Returns the column index for the given Cell key.

  Parameters:

  key - Cell key

  Returns:

  Column index of cell, 1-based

• getRowFromKey

  public static int getRowFromKey​(String key)

  Returns the row index for the given Cell key.

  Parameters:

  key - Cell key

  Returns:

  Row index of cell, 1-based

• parsePercentage

  public static Double parsePercentage​(String cellContent,
                                       Locale locale)

  Tries to parse the given String to a percentage. Specifically, checks if the String ends with the '%' character, and the rest can be parsed to a number.

  Parameters:

  cellContent - The string to be parsed

  locale - The current locale, used for number parsing.

  Returns:

  the number as a decimal if it can be parsed; e.g. 42% returns 0.42 and 0.42% returns 0.0042. Returns null if the number can't be parsed as a decimal.

• parseNumber

  public static Double parseNumber​(org.apache.poi.ss.usermodel.Cell cell,
                                   String value,
                                   Locale locale)

• parseNumber

  public static Double parseNumber​(String cellContent,
                                   Locale locale)

• needsLeadingQuote

  public static boolean needsLeadingQuote​(org.apache.poi.ss.usermodel.Cell cell)

  Determine if the given cell content should be displayed with a leading quote in both cell editor and formula bar

  Parameters:

  cell - The cell to be checked

  Returns:

  true if the cell contains a string with the "quotePrefix" style set. Note that for Excel 97 file format, returns true for every string.

• getRangeForReference

  public static org.apache.poi.ss.util.CellRangeAddress getRangeForReference​(String formula,
                                                                             Spreadsheet spreadsheet,
                                                                             boolean includeHiddenCells)

  evaluate the formula (which may just be a single cell or range string) and find the bounding rectangle for the referenced cells.

  Parameters:

  formula -

  spreadsheet -

  includeHiddenCells -

  Returns:

  CellRangeAddress bounding the evaluated result

• getAllReferencedCells

  public static List<org.apache.poi.ss.util.CellReference> getAllReferencedCells​(String formula,
                                                                                 Spreadsheet spreadsheet,
                                                                                 boolean includeHiddenCells)

  This function returns all the cells that the given formula references. You can optionally filter out all the hidden rows from the list honoring filtering of charts based on SpreadsheetTable filter settings.

  Parameters:

  formula - The formula to find referenced cells for

  spreadsheet - Spreadsheet to operate on

  includeHiddenCells - true to include cells residing in hidden rows or columns, false to omit them