Package adams.data.spreadsheet

Class SpreadSheetUtils

java.lang.Object

adams.data.spreadsheet.SpreadSheetUtils

Direct Known Subclasses:

SpreadSheetHelper

public class SpreadSheetUtils
extends Object

Helper class for spreadsheet related functionality.

Author:

fracpete (fracpete at waikato dot ac dot nz)

Field Summary

Fields

Modifier and Type	Field	Description
static String	PREFIX_COL

Constructor Summary

Constructors

Constructor	Description
SpreadSheetUtils()

Method Summary

Modifier and Type	Method	Description
static List<String>	createHeader(int numCols, String customCols)	Creates list of column header names.
static int[]	getCellLocation(String position)	Returns row/column index based on the provided position string (e.g., A12).
static String	getCellPosition(int row, int col)	Returns the position of the cell.
static String[]	getColumn(SpreadSheet sheet, int col, boolean unique, boolean sort)	Returns the content of a column as array.
static String[]	getColumn(SpreadSheet sheet, int col, boolean unique, boolean sort, String missing)	Returns the content of a column as array.
static String	getColumnPosition(int col)	Returns the position letter(s) of the column.
static double[]	getMinMax(SpreadSheet sheet, int[] columns, int[] rows)	Determines min/max values in the spreadsheet.
static double[]	getMinMax(SpreadSheetTable table, int[] columns, int[] rows)	Determines min/max values in the table.
static double[]	getNumericColumn(SpreadSheet sheet, int col)	Returns the content of a numeric column as double array.
static double[]	getNumericRow(SpreadSheet sheet, int rowIndex)	Returns the content of a numeric row as double array.
static double[]	getNumericRow(SpreadSheet sheet, int rowIndex, int[] columns)	Returns the content of a numeric row as double array.
static String[]	split(String s, char delimiter)	Attempts to split a string, using the specified delimiter character.
static String[]	split(String s, char delimiter, boolean unquote)	Attempts to split a string, using the specified delimiter character.
static String[]	split(String s, char delimiter, boolean unquote, char quoteChar, boolean escaped)	Attempts to split a string, using the specified delimiter character.
static List<String>	uniqueValues(SpreadSheet sheet)	Returns a sorted list of all unique values in the spreadsheet (excludes missing values).

Methods inherited from class java.lang.Object

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

Field Detail

PREFIX_COL

public static final String PREFIX_COL

See Also:

Constant Field Values

Constructor Detail

SpreadSheetUtils

public SpreadSheetUtils()

Method Detail

split

public static String[] split(String s,
                             char delimiter)

Attempts to split a string, using the specified delimiter character. A delimiter gets ignored if inside double quotes.

Parameters:

s - the string to split

delimiter - the delimiting character

Returns:

the parts (single array element if no range)

split

public static String[] split(String s,
                             char delimiter,
                             boolean unquote)

Attempts to split a string, using the specified delimiter character. A delimiter gets ignored if inside double quotes.

Parameters:

s - the string to split

delimiter - the delimiting character

unquote - whether to remove double quotes

Returns:

the parts (single array element if no range)

split

public static String[] split(String s,
                             char delimiter,
                             boolean unquote,
                             char quoteChar,
                             boolean escaped)

Attempts to split a string, using the specified delimiter character. A delimiter gets ignored if inside double quotes.

Parameters:

s - the string to split

delimiter - the delimiting character

unquote - whether to remove single/double quotes

quoteChar - the quote character to use

escaped - if true then quotes preceded by backslash ('escaped') get ignored

Returns:

the parts (single array element if no range)

createHeader

public static List<String> createHeader(int numCols,
                                        String customCols)

Creates list of column header names. Either using the comma-separated list or, if that is empty, a made up list using "Col-" plus the index.

Parameters:

numCols - the number of column headers to generate

customCols - the comma-separated list of custom headers, can be empty

Returns:

the generated list of headers

getColumnPosition

public static String getColumnPosition(int col)

Returns the position letter(s) of the column.

Parameters:

col - the column index of the cell (0-based)

Returns:

the position string

getCellPosition

public static String getCellPosition(int row,
                                     int col)

Returns the position of the cell. A position is a combination of a number of letters (for the column) and number (for the row).

Note: add "1" to the row indices, since the header row does not count towards the row count.

Parameters:

row - the row index of the cell (0-based)

col - the column index of the cell (0-based)

Returns:

the position string or null if not found

getCellLocation

public static int[] getCellLocation(String position)
                             throws Exception

Returns row/column index based on the provided position string (e.g., A12).

Parameters:

position - the position string to parse

Returns:

the array with row and column index (0-based indices)

Throws:

Exception - in case of an invalid position string

getNumericColumn

public static double[] getNumericColumn(SpreadSheet sheet,
                                        int col)

Returns the content of a numeric column as double array.

Parameters:

sheet - the sheet to use

col - the index of the numeric column

Returns:

the numeric data, elements are NaN if missing or not numeric

getNumericRow

public static double[] getNumericRow(SpreadSheet sheet,
                                     int rowIndex)

Returns the content of a numeric row as double array.

Parameters:

sheet - the sheet to use

rowIndex - the index of the numeric row

Returns:

the numeric data, elements are NaN if missing or not numeric

getNumericRow

public static double[] getNumericRow(SpreadSheet sheet,
                                     int rowIndex,
                                     int[] columns)

Returns the content of a numeric row as double array.

Parameters:

sheet - the sheet to use

rowIndex - the index of the numeric row

columns - the columns to return, null for all

Returns:

the numeric data, elements are NaN if missing or not numeric

getColumn

public static String[] getColumn(SpreadSheet sheet,
                                 int col,
                                 boolean unique,
                                 boolean sort)

Returns the content of a column as array.

Parameters:

sheet - the sheet to use

col - the index of the column

unique - whether to return only unique values

sort - whether to sort the values

Returns:

the data, elements are NaN if missing (if not looking for unique values)

getColumn

public static String[] getColumn(SpreadSheet sheet,
                                 int col,
                                 boolean unique,
                                 boolean sort,
                                 String missing)

Returns the content of a column as array.

Parameters:

sheet - the sheet to use

col - the index of the column

unique - whether to return only unique values

sort - whether to sort the values

missing - the string to use for missing values

Returns:

the data, elements are NaN if missing (if not looking for unique values)

getMinMax

public static double[] getMinMax(SpreadSheetTable table,
                                 int[] columns,
                                 int[] rows)

Determines min/max values in the table.

Parameters:

table - the table to analyze

columns - the 0-based column indices in the spreadsheet to get the min/max for, ignored if null

rows - the 0-based row indices in the spreadsheet to get the min/max for, ignored if null

Returns:

the min and max

getMinMax

public static double[] getMinMax(SpreadSheet sheet,
                                 int[] columns,
                                 int[] rows)

Determines min/max values in the spreadsheet.

Parameters:

sheet - the sheet to analyze

columns - the 0-based column indices in the spreadsheet to get the min/max for, ignored if null

rows - the 0-based row indices in the spreadsheet to get the min/max for, ignored if null

Returns:

the min and max

uniqueValues

public static List<String> uniqueValues(SpreadSheet sheet)

Returns a sorted list of all unique values in the spreadsheet (excludes missing values).

Parameters:

sheet - the sheet to get the values for

Returns:

the unique values