Package adams.parser.spreadsheetformula

Class ParserHelper

java.lang.Object

adams.core.logging.LoggingObject

adams.parser.ParserHelper

adams.parser.spreadsheetformula.ParserHelper

All Implemented Interfaces:

LoggingSupporter, SizeOfHandler, Serializable

public class ParserHelper
extends ParserHelper

Helper class for spreadsheet formulas.

Author:

fracpete (fracpete at waikato dot ac dot nz)

See Also:

Serialized Form

Field Summary

Fields

Modifier and Type	Field	Description
protected SpreadSheet	m_Sheet	the underlying spreadsheet.

Fields inherited from class adams.parser.ParserHelper

m_BusinessDays, m_Calendar, m_Functions, m_Procedures, m_Symbols

Fields inherited from class adams.core.logging.LoggingObject

m_Logger, m_LoggingIsEnabled, m_LoggingLevel

Constructor Summary

Constructors

Constructor	Description
ParserHelper()

Method Summary

Modifier and Type	Method	Description
Double	average(String fromCell, String toCell)	Calculates the average for a range of cells.
long	countblank(String fromCell, String toCell)	Counts how often an empty string or missing value occurs in the range of cells.
Double	countif(String fromCell, String toCell, Boolean value)	Counts how often a boolean occurs in the range of cells.
Double	countif(String fromCell, String toCell, Double value)	Counts how often a number occurs in the range of cells.
Double	countif(String fromCell, String toCell, Object value)	Counts how often a boolean occurs in the range of cells.
Double	countif(String fromCell, String toCell, String value)	Counts how often a string occurs or expression evalutes to true in the range of cells.
Cell	getCell(String loc)	Returns the cell object of the specified cell.
Double	getDateFieldFromCell(String loc, int field)	Returns the specified date from the cell.
SpreadSheet	getSheet()	Returns the current spreadsheet in use.
boolean	hasCell(String loc)	Checks whether the specified cell is available.
protected void	initialize()	Initializes the members.
Double	intercept(String fromCellY, String toCellY, String fromCellX, String toCellX)	Calculates the intercept of linear regression for a range of cells.
Double	linearRegression(boolean intercept, String fromCellY, String toCellY, String fromCellX, String toCellX)	Calculates the intercept or slope of linear regression for a range of cells.
Double	max(String fromCell, String toCell)	Calculates the maximum for a range of cells.
Double	median(String fromCell, String toCell)	Calculates the median for a range of cells.
Double	min(String fromCell, String toCell)	Calculates the minimum for a range of cells.
Double[]	rangeToDoubleArray(String fromCell, String toCell)	Turns a range of cells into a Double array.
List<Cell>	rangeToList(String fromCell, String toCell, boolean onlyNumeric)	Turns the cell range into a list of located cells.
void	setSheet(SpreadSheet value)	Sets the spreadsheet to use.
Double	slope(String fromCellY, String toCellY, String fromCellX, String toCellX)	Calculates the slope of linear regression for a range of cells.
Double	stdev(String fromCell, String toCell)	Calculates the standard deviation (sample) for a range of cells.
Double	stdevp(String fromCell, String toCell)	Calculates the standard deviation (population) for a range of cells.
Double	sum(String fromCell, String toCell)	Calculates the sum for a range of cells.
Double	sumif(String fromCell, String toCell, Double value)	Sums up the numbers of a range of cells if they match the value.
Double	sumif(String fromCell, String toCell, Double value, String fromSum, String toSum)	Sums up the numbers of a corresponding range of cells if the values in the range of cells match the value.
Double	sumif(String fromCell, String toCell, Object value)	Sums up the numbers of a range of cells if they match the value or the expression evaluates to true.
Double	sumif(String fromCell, String toCell, Object value, String fromSum, String toSum)	Sums up the numbers of a corresponding range of cells if the values in the range of cells match the value or the expression evaluates to true.
Double	sumif(String fromCell, String toCell, String value)	Sums up the numbers of a range of cells if they match the value or the expression evaluates to true.
Double	sumif(String fromCell, String toCell, String value, String fromSum, String toSum)	Sums up the numbers of a corresponding range of cells if the values in the range of cells match the value or the expression evaluates to true.
Double	toDouble(String loc)	Returns the double value of the specified cell.
Object	toNative(String loc)	Returns the native value of the specified cell.

Methods inherited from class adams.parser.ParserHelper

add, adjustAmount, callFunction, callProcedure, compare, getBusinessDays, getCalendar, getCalendar, getDateFieldFromString, getFunction, getFunctionOverview, getProcedure, getProcedureOverview, getSymbols, initFunctions, initProcedures, isBusinessDay, left, mid, repeat, replace, reportToSymbols, right, setBusinessDays, setSymbols, substitute, substitute, toBoolean, toDate, toDouble, toInteger, toNumber, toString

Methods inherited from class adams.core.logging.LoggingObject

configureLogger, getLogger, getLoggingLevel, initializeLogging, isLoggingEnabled, sizeOf

Methods inherited from class java.lang.Object

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

Field Detail

m_Sheet

protected SpreadSheet m_Sheet

the underlying spreadsheet.

Constructor Detail

ParserHelper

public ParserHelper()

Method Detail

initialize

protected void initialize()

Initializes the members.

Overrides:

initialize in class ParserHelper

setSheet

public void setSheet(SpreadSheet value)

Sets the spreadsheet to use.

Parameters:

value - the spreadsheet

getSheet

public SpreadSheet getSheet()

Returns the current spreadsheet in use.

Returns:

the spreadsheet

toDouble

public Double toDouble(String loc)
                throws Exception

Returns the double value of the specified cell.

Parameters:

loc - the cell location

Returns:

the numeric value, NaN if not found or missing

Throws:

Exception - if cell location cannot be parsed

toNative

public Object toNative(String loc)
                throws Exception

Returns the native value of the specified cell.

Parameters:

loc - the cell location

Returns:

the value, NaN if not found or missing

Throws:

Exception - if cell location cannot be parsed

getDateFieldFromCell

public Double getDateFieldFromCell(String loc,
                                   int field)
                            throws Exception

Returns the specified date from the cell.

Parameters:

loc - the cell location

field - the date field (see constants of Calendar class)

Returns:

the value or NaN if failed to convert or not present

Throws:

Exception - if cell location cannot be parsed

hasCell

public boolean hasCell(String loc)
                throws Exception

Checks whether the specified cell is available.

Parameters:

loc - the cell location

Returns:

true if available

Throws:

Exception - if cell location cannot be parsed

getCell

public Cell getCell(String loc)
             throws Exception

Returns the cell object of the specified cell.

Parameters:

loc - the cell location

Returns:

the cell object, null if not available

Throws:

Exception - if cell location cannot be parsed

rangeToList

public List<Cell> rangeToList(String fromCell,
                              String toCell,
                              boolean onlyNumeric)
                       throws Exception

Turns the cell range into a list of located cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

onlyNumeric - whether to locate only numeric cells

Returns:

the list of cells that aren't flagged as missing

Throws:

Exception - if cell location cannot be parsed

rangeToDoubleArray

public Double[] rangeToDoubleArray(String fromCell,
                                   String toCell)
                            throws Exception

Turns a range of cells into a Double array.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

Returns:

the double array

Throws:

Exception - if cell location cannot be parsed

sum

public Double sum(String fromCell,
                  String toCell)
           throws Exception

Calculates the sum for a range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

Returns:

the sum

Throws:

Exception - if cell location cannot be parsed

min

public Double min(String fromCell,
                  String toCell)
           throws Exception

Calculates the minimum for a range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

Returns:

the minimum

Throws:

Exception - if cell location cannot be parsed

max

public Double max(String fromCell,
                  String toCell)
           throws Exception

Calculates the maximum for a range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

Returns:

the maximum

Throws:

Exception - if cell location cannot be parsed

average

public Double average(String fromCell,
                      String toCell)
               throws Exception

Calculates the average for a range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

Returns:

the average

Throws:

Exception - if cell location cannot be parsed

median

public Double median(String fromCell,
                     String toCell)
              throws Exception

Calculates the median for a range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

Returns:

the median

Throws:

Exception - if cell location cannot be parsed

stdev

public Double stdev(String fromCell,
                    String toCell)
             throws Exception

Calculates the standard deviation (sample) for a range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

Returns:

the standard deviation

Throws:

Exception - if cell location cannot be parsed

stdevp

public Double stdevp(String fromCell,
                     String toCell)
              throws Exception

Calculates the standard deviation (population) for a range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

Returns:

the standard deviation

Throws:

Exception - if cell location cannot be parsed

countif

public Double countif(String fromCell,
                      String toCell,
                      Double value)
               throws Exception

Counts how often a number occurs in the range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

value - the numeric value to look for

Returns:

the count

Throws:

Exception - if cell location cannot be parsed

countif

public Double countif(String fromCell,
                      String toCell,
                      String value)
               throws Exception

Counts how often a string occurs or expression evalutes to true in the range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

value - the expression to evaluate or string to find

Returns:

the count

Throws:

Exception - if cell location cannot be parsed

countif

public Double countif(String fromCell,
                      String toCell,
                      Boolean value)
               throws Exception

Counts how often a boolean occurs in the range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

value - the boolean value to look for

Returns:

the count

Throws:

Exception - if cell location cannot be parsed

countif

public Double countif(String fromCell,
                      String toCell,
                      Object value)
               throws Exception

Counts how often a boolean occurs in the range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

value - the expression to evaluate or value to look for

Returns:

the count

Throws:

Exception - if cell location cannot be parsed

sumif

public Double sumif(String fromCell,
                    String toCell,
                    Double value)
             throws Exception

Sums up the numbers of a range of cells if they match the value.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

value - the numeric value to look for

Returns:

the sum

Throws:

Exception - if cell location cannot be parsed

sumif

public Double sumif(String fromCell,
                    String toCell,
                    String value)
             throws Exception

Sums up the numbers of a range of cells if they match the value or the expression evaluates to true.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

value - the expression to evaluate or string to find

Returns:

the sum

Throws:

Exception - if cell location cannot be parsed

sumif

public Double sumif(String fromCell,
                    String toCell,
                    Object value)
             throws Exception

Sums up the numbers of a range of cells if they match the value or the expression evaluates to true.

Parameters:

fromCell - the top-left cell to sum up

toCell - the bottom-right cell to sum up

value - the expression to evaluate or value to look for

Returns:

the sum

Throws:

Exception - if cell location cannot be parsed

sumif

public Double sumif(String fromCell,
                    String toCell,
                    Double value,
                    String fromSum,
                    String toSum)
             throws Exception

Sums up the numbers of a corresponding range of cells if the values in the range of cells match the value.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

value - the numeric value to look for

fromSum - the top-left cell to sum up

toSum - the bottom-right cell to sum up

Returns:

the sum

Throws:

Exception - if cell location cannot be parsed

IllegalArgumentException - if search and sum cell range differ in size

sumif

public Double sumif(String fromCell,
                    String toCell,
                    String value,
                    String fromSum,
                    String toSum)
             throws Exception

Sums up the numbers of a corresponding range of cells if the values in the range of cells match the value or the expression evaluates to true.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

value - the expression to evaluate or string to find

fromSum - the top-left cell to sum up

toSum - the bottom-right cell to sum up

Returns:

the sum

Throws:

Exception - if cell location cannot be parsed

IllegalArgumentException - if search and sum cell range differ in size

sumif

public Double sumif(String fromCell,
                    String toCell,
                    Object value,
                    String fromSum,
                    String toSum)
             throws Exception

Sums up the numbers of a corresponding range of cells if the values in the range of cells match the value or the expression evaluates to true.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

value - the expression to evaluate or value to look for

fromSum - the top-left cell to sum up

toSum - the bottom-right cell to sum up

Returns:

the sum

Throws:

Exception - if cell location cannot be parsed

IllegalArgumentException - if search and sum cell range differ in size

countblank

public long countblank(String fromCell,
                       String toCell)
                throws Exception

Counts how often an empty string or missing value occurs in the range of cells.

Parameters:

fromCell - the top-left cell

toCell - the bottom-right cell

Returns:

the count

Throws:

Exception - if cell location cannot be parsed

linearRegression

public Double linearRegression(boolean intercept,
                               String fromCellY,
                               String toCellY,
                               String fromCellX,
                               String toCellX)
                        throws Exception

Calculates the intercept or slope of linear regression for a range of cells.

Parameters:

fromCellY - the top-left cell of Y

toCellY - the bottom-right cell of Y

fromCellX - the top-left cell of X

toCellX - the bottom-right cell of X

Returns:

the standard deviation

Throws:

Exception - if cell location cannot be parsed

intercept

public Double intercept(String fromCellY,
                        String toCellY,
                        String fromCellX,
                        String toCellX)
                 throws Exception

Calculates the intercept of linear regression for a range of cells.

Parameters:

fromCellY - the top-left cell of Y

toCellY - the bottom-right cell of Y

fromCellX - the top-left cell of X

toCellX - the bottom-right cell of X

Returns:

the standard deviation

Throws:

Exception - if cell location cannot be parsed

slope

public Double slope(String fromCellY,
                    String toCellY,
                    String fromCellX,
                    String toCellX)
             throws Exception

Calculates the slope of linear regression for a range of cells.

Parameters:

fromCellY - the top-left cell of Y

toCellY - the bottom-right cell of Y

fromCellX - the top-left cell of X

toCellX - the bottom-right cell of X

Returns:

the standard deviation

Throws:

Exception - if cell location cannot be parsed