Example usage for org.apache.poi.hssf.util CellReference CellReference

Introduction

In this page you can find the example usage for org.apache.poi.hssf.util CellReference CellReference.

Prototype

public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) 

Source Link

Usage

From source file:uk.ac.liverpool.spreadsheet.FormulaRenderer.java

License:Apache License

/**
 * Static method to convert an array of {@link Ptg}s in RPN order
 * to a human readable string format in infix mode.
 * @param book  used for defined names and 3D references
 * @param ptgs  must not be <code>null</code>
 * @return a human readable String// ww w . j a  v  a2  s  .co m
 */
public static String toFormulaString(FormulaRenderingWorkbook book, Ptg[] ptgs) {
    if (ptgs == null || ptgs.length == 0) {
        throw new IllegalArgumentException("ptgs must not be null");
    }
    Stack<String> stack = new Stack<String>();

    for (int i = 0; i < ptgs.length; i++) {
        Ptg ptg = ptgs[i];
        // TODO - what about MemNoMemPtg?
        if (ptg instanceof MemAreaPtg || ptg instanceof MemFuncPtg || ptg instanceof MemErrPtg) {
            // marks the start of a list of area expressions which will be naturally combined
            // by their trailing operators (e.g. UnionPtg)
            // TODO - put comment and throw exception in toFormulaString() of these classes
            continue;
        }
        if (ptg instanceof ParenthesisPtg) {
            String contents = stack.pop();
            stack.push("(" + contents + ")");
            continue;
        }
        if (ptg instanceof AttrPtg) {
            AttrPtg attrPtg = ((AttrPtg) ptg);
            if (attrPtg.isOptimizedIf() || attrPtg.isOptimizedChoose() || attrPtg.isSkip()) {
                continue;
            }
            if (attrPtg.isSpace()) {
                // POI currently doesn't render spaces in formulas
                continue;
                // but if it ever did, care must be taken:
                // tAttrSpace comes *before* the operand it applies to, which may be consistent
                // with how the formula text appears but is against the RPN ordering assumed here
            }
            if (attrPtg.isSemiVolatile()) {
                // similar to tAttrSpace - RPN is violated
                continue;
            }
            if (attrPtg.isSum()) {
                String[] operands = getOperands(stack, attrPtg.getNumberOfOperands());
                stack.push(attrPtg.toFormulaString(operands));
                continue;
            }
            throw new RuntimeException("Unexpected tAttr: " + attrPtg.toString());
        }

        if (ptg instanceof WorkbookDependentFormula) {
            WorkbookDependentFormula optg = (WorkbookDependentFormula) ptg;
            stack.push(optg.toFormulaString(book));
            continue;
        }
        if (!(ptg instanceof OperationPtg)) {
            String s = "";
            if (ptg instanceof AreaPtg) {
                AreaPtg a = (AreaPtg) ptg;
                s = formatReferenceAsString(a);
            } else if (ptg instanceof RefPtg) {
                RefPtg a = (RefPtg) ptg;
                CellReference cr = new CellReference(a.getRow(), a.getColumn(), !a.isRowRelative(),
                        !a.isColRelative());
                s = "[." + cr.formatAsString() + "]";
            }

            else
                s = ptg.toFormulaString();
            stack.push(s);
            continue;
        }

        OperationPtg o = (OperationPtg) ptg;

        String[] operands = getOperands(stack, o.getNumberOfOperands());
        if (o instanceof AbstractFunctionPtg) {
            AbstractFunctionPtg a = (AbstractFunctionPtg) o;
            stack.push(toFormulaString(a, operands));
        } else
            stack.push(o.toFormulaString(operands));
    }
    if (stack.isEmpty()) {
        // inspection of the code above reveals that every stack.pop() is followed by a
        // stack.push(). So this is either an internal error or impossible.
        throw new IllegalStateException("Stack underflow");
    }
    String result = stack.pop();
    if (!stack.isEmpty()) {
        // Might be caused by some tokens like AttrPtg and Mem*Ptg, which really shouldn't
        // put anything on the stack
        throw new IllegalStateException("too much stuff left on the stack");
    }
    return result;
}

---

From source file:uk.ac.liverpool.spreadsheet.FormulaRenderer.java

License:Apache License

protected static String formatReferenceAsString(AreaPtg a) {
    CellReference topLeft = new CellReference(a.getFirstRow(), a.getFirstColumn(), !a.isFirstRowRelative(),
            !a.isFirstColRelative());//from   w ww  .j ava2s. c o m
    CellReference botRight = new CellReference(a.getLastRow(), a.getLastColumn(), !a.isLastRowRelative(),
            !a.isLastColRelative());

    if (AreaReference.isWholeColumnReference(topLeft, botRight)) {
        return (new AreaReference(topLeft, botRight)).formatAsString();
    }
    return "[." + topLeft.formatAsString() + ":" + "." + botRight.formatAsString() + "]";
}

---

From source file:uk.co.danielrendall.fractdim.app.workers.ExcelExportWorker.java

License:Open Source License

public void visit(AngleGridCollection collection) {
    intermediateAngleColumn = 3;//  www  . j a  v a2 s .co m
    resultsRow = 1;
    for (Double angle : collection.getAvailableAngles()) {
        currentAngle = angle;

        ResolutionGridCollection rgc = collection.collectionForAngle(angle);
        rgc.accept(this);

        final Row angleRow = resultsSheet.createRow(resultsRow);
        angleRow.createCell(0).setCellValue(angle);

        CellReference topXRange = new CellReference(2, 2, false, false);
        CellReference bottomXRange = new CellReference(intermediateResolutionRow - 1, 2, false, false);

        CellReference topYRange = new CellReference(2, intermediateAngleColumn + 1, false, false);
        CellReference bottomYRange = new CellReference(intermediateResolutionRow - 1,
                intermediateAngleColumn + 1, false, false);

        angleRow.createCell(1)
                .setCellFormula(String.format("SLOPE('Intermediate'!%s:%s,'Intermediate'!%s:%s)",
                        topYRange.formatAsString(), bottomYRange.formatAsString(), topXRange.formatAsString(),
                        bottomXRange.formatAsString()));
        intermediateAngleColumn += 3;
        resultsRow += 1;
    }

    Row grandAverageRow = resultsSheet.createRow(resultsRow + 1);
    grandAverageRow.createCell(0).setCellValue(ch.createRichTextString("Average:"));
    grandAverageRow.createCell(1).setCellFormula(String.format("AVERAGE(B2:B%d)", resultsRow));

}

---

From source file:uk.co.danielrendall.fractdim.app.workers.ExcelExportWorker.java

License:Open Source License

public void visit(ResolutionGridCollection collection) {
    intermediateResolutionRow = 2;/*  ww w. ja  v  a 2 s  .co  m*/
    for (Double resolution : collection.getAvailableResolutions()) {
        currentResolution = resolution;
        int initialRawDataRow = currentDataRow + 1; // rows are 0 based, but formulas are 1-based
        DisplacementGridCollection dgc = collection.collectionForResolution(resolution);

        dgc.accept(this);

        int finalRawDataRow = currentDataRow; // actually currentdataRow - 1 + 1
        final Row intermediateAverageRow = intermediateSheet.getRow(intermediateResolutionRow);
        Cell displacementSum = intermediateAverageRow.createCell(intermediateAngleColumn);
        Cell reciprocal = intermediateAverageRow.createCell(intermediateAngleColumn + 1);

        displacementSum
                .setCellFormula(String.format("MIN('Data'!E%d:E%d)", initialRawDataRow, finalRawDataRow));
        reciprocal.setCellFormula(String.format("LOG(%s)",
                new CellReference(intermediateResolutionRow, intermediateAngleColumn, false, false)
                        .formatAsString()));

        intermediateResolutionRow++;
    }
}

---