Class ParserHelper

    • 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