Search in sources :

Example 1 with SpreadsheetVersion

use of org.apache.poi.ss.SpreadsheetVersion in project poi by apache.

the class SXSSFRow method checkBounds.

/**
     * @throws RuntimeException if the bounds are exceeded.
     */
private static void checkBounds(int cellIndex) {
    SpreadsheetVersion v = SpreadsheetVersion.EXCEL2007;
    int maxcol = SpreadsheetVersion.EXCEL2007.getLastColumnIndex();
    if (cellIndex < 0 || cellIndex > maxcol) {
        throw new IllegalArgumentException("Invalid column index (" + cellIndex + ").  Allowable column range for " + v.name() + " is (0.." + maxcol + ") or ('A'..'" + v.getLastColumnName() + "')");
    }
}
Also used : SpreadsheetVersion(org.apache.poi.ss.SpreadsheetVersion)

Example 2 with SpreadsheetVersion

use of org.apache.poi.ss.SpreadsheetVersion in project poi by apache.

the class OperationEvaluationContext method getDynamicReference.

/**
     * Resolves a cell or area reference dynamically.
     * @param workbookName the name of the workbook containing the reference.  If <code>null</code>
     * the current workbook is assumed.  Note - to evaluate formulas which use multiple workbooks,
     * a {@link CollaboratingWorkbooksEnvironment} must be set up.
     * @param sheetName the name of the sheet containing the reference.  May be <code>null</code>
     * (when <tt>workbookName</tt> is also null) in which case the current workbook and sheet is
     * assumed.
     * @param refStrPart1 the single cell reference or first part of the area reference.  Must not
     * be <code>null</code>.
     * @param refStrPart2 the second part of the area reference. For single cell references this
     * parameter must be <code>null</code>
     * @param isA1Style specifies the format for <tt>refStrPart1</tt> and <tt>refStrPart2</tt>.
     * Pass <code>true</code> for 'A1' style and <code>false</code> for 'R1C1' style.
     * TODO - currently POI only supports 'A1' reference style
     * @return a {@link RefEval} or {@link AreaEval}
     */
public ValueEval getDynamicReference(String workbookName, String sheetName, String refStrPart1, String refStrPart2, boolean isA1Style) {
    if (!isA1Style) {
        throw new RuntimeException("R1C1 style not supported yet");
    }
    SheetRefEvaluator se = createExternSheetRefEvaluator(workbookName, sheetName);
    if (se == null) {
        return ErrorEval.REF_INVALID;
    }
    SheetRangeEvaluator sre = new SheetRangeEvaluator(_sheetIndex, se);
    // ugly typecast - TODO - make spreadsheet version more easily accessible
    SpreadsheetVersion ssVersion = ((FormulaParsingWorkbook) _workbook).getSpreadsheetVersion();
    NameType part1refType = classifyCellReference(refStrPart1, ssVersion);
    switch(part1refType) {
        case BAD_CELL_OR_NAMED_RANGE:
            return ErrorEval.REF_INVALID;
        case NAMED_RANGE:
            EvaluationName nm = ((FormulaParsingWorkbook) _workbook).getName(refStrPart1, _sheetIndex);
            if (!nm.isRange()) {
                throw new RuntimeException("Specified name '" + refStrPart1 + "' is not a range as expected.");
            }
            return _bookEvaluator.evaluateNameFormula(nm.getNameDefinition(), this);
    }
    if (refStrPart2 == null) {
        // no ':'
        switch(part1refType) {
            case COLUMN:
            case ROW:
                return ErrorEval.REF_INVALID;
            case CELL:
                CellReference cr = new CellReference(refStrPart1);
                return new LazyRefEval(cr.getRow(), cr.getCol(), sre);
        }
        throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'.");
    }
    NameType part2refType = classifyCellReference(refStrPart1, ssVersion);
    switch(part2refType) {
        case BAD_CELL_OR_NAMED_RANGE:
            return ErrorEval.REF_INVALID;
        case NAMED_RANGE:
            throw new RuntimeException("Cannot evaluate '" + refStrPart1 + "'. Indirect evaluation of defined names not supported yet");
    }
    if (part2refType != part1refType) {
        // LHS and RHS of ':' must be compatible
        return ErrorEval.REF_INVALID;
    }
    int firstRow, firstCol, lastRow, lastCol;
    switch(part1refType) {
        case COLUMN:
            firstRow = 0;
            if (part2refType.equals(NameType.COLUMN)) {
                lastRow = ssVersion.getLastRowIndex();
                firstCol = parseRowRef(refStrPart1);
                lastCol = parseRowRef(refStrPart2);
            } else {
                lastRow = ssVersion.getLastRowIndex();
                firstCol = parseColRef(refStrPart1);
                lastCol = parseColRef(refStrPart2);
            }
            break;
        case ROW:
            // support of cell range in the form of integer:integer
            firstCol = 0;
            if (part2refType.equals(NameType.ROW)) {
                firstRow = parseColRef(refStrPart1);
                lastRow = parseColRef(refStrPart2);
                lastCol = ssVersion.getLastColumnIndex();
            } else {
                lastCol = ssVersion.getLastColumnIndex();
                firstRow = parseRowRef(refStrPart1);
                lastRow = parseRowRef(refStrPart2);
            }
            break;
        case CELL:
            CellReference cr;
            cr = new CellReference(refStrPart1);
            firstRow = cr.getRow();
            firstCol = cr.getCol();
            cr = new CellReference(refStrPart2);
            lastRow = cr.getRow();
            lastCol = cr.getCol();
            break;
        default:
            throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'.");
    }
    return new LazyAreaEval(firstRow, firstCol, lastRow, lastCol, sre);
}
Also used : NameType(org.apache.poi.ss.util.CellReference.NameType) SpreadsheetVersion(org.apache.poi.ss.SpreadsheetVersion) CellReference(org.apache.poi.ss.util.CellReference)

Example 3 with SpreadsheetVersion

use of org.apache.poi.ss.SpreadsheetVersion in project poi by apache.

the class TestCellReference method isRowWithinRange.

@Test
public void isRowWithinRange() {
    SpreadsheetVersion ss = SpreadsheetVersion.EXCEL2007;
    assertFalse("1 before first row", CellReference.isRowWithinRange("0", ss));
    assertTrue("first row", CellReference.isRowWithinRange("1", ss));
    assertTrue("last row", CellReference.isRowWithinRange("1048576", ss));
    assertFalse("1 beyond last row", CellReference.isRowWithinRange("1048577", ss));
    // int versions of above, using 0-based indices
    assertFalse("1 before first row", CellReference.isRowWithinRange(-1, ss));
    assertTrue("first row", CellReference.isRowWithinRange(0, ss));
    assertTrue("last row", CellReference.isRowWithinRange(1048575, ss));
    assertFalse("1 beyond last row", CellReference.isRowWithinRange(1048576, ss));
}
Also used : SpreadsheetVersion(org.apache.poi.ss.SpreadsheetVersion) Test(org.junit.Test)

Example 4 with SpreadsheetVersion

use of org.apache.poi.ss.SpreadsheetVersion in project poi by apache.

the class TestCellReference method testBadRowNumber.

@Test
public void testBadRowNumber() {
    SpreadsheetVersion v97 = SpreadsheetVersion.EXCEL97;
    SpreadsheetVersion v2007 = SpreadsheetVersion.EXCEL2007;
    confirmCrInRange(true, "A", "1", v97);
    confirmCrInRange(true, "IV", "65536", v97);
    confirmCrInRange(false, "IV", "65537", v97);
    confirmCrInRange(false, "IW", "65536", v97);
    confirmCrInRange(true, "A", "1", v2007);
    confirmCrInRange(true, "XFD", "1048576", v2007);
    confirmCrInRange(false, "XFD", "1048577", v2007);
    confirmCrInRange(false, "XFE", "1048576", v2007);
    assertFalse("Identified bug 47312a", CellReference.cellReferenceIsWithinRange("B", "0", v97));
    confirmCrInRange(false, "A", "0", v97);
    confirmCrInRange(false, "A", "0", v2007);
}
Also used : SpreadsheetVersion(org.apache.poi.ss.SpreadsheetVersion) Test(org.junit.Test)

Example 5 with SpreadsheetVersion

use of org.apache.poi.ss.SpreadsheetVersion in project poi by apache.

the class BaseTestSheet method addMerged.

/**
     * Test adding merged regions. If the region's bounds are outside of the allowed range
     * then an IllegalArgumentException should be thrown
     *
     */
@Test
public void addMerged() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet();
    assertEquals(0, sheet.getNumMergedRegions());
    SpreadsheetVersion ssVersion = _testDataProvider.getSpreadsheetVersion();
    CellRangeAddress region = new CellRangeAddress(0, 1, 0, 1);
    sheet.addMergedRegion(region);
    assertEquals(1, sheet.getNumMergedRegions());
    try {
        region = new CellRangeAddress(-1, -1, -1, -1);
        sheet.addMergedRegion(region);
        fail("Expected exception");
    } catch (IllegalArgumentException e) {
    // TODO: assertEquals("Minimum row number is 0.", e.getMessage());
    }
    try {
        region = new CellRangeAddress(0, 0, 0, ssVersion.getLastColumnIndex() + 1);
        sheet.addMergedRegion(region);
        fail("Expected exception");
    } catch (IllegalArgumentException e) {
        assertEquals("Maximum column number is " + ssVersion.getLastColumnIndex(), e.getMessage());
    }
    try {
        region = new CellRangeAddress(0, ssVersion.getLastRowIndex() + 1, 0, 1);
        sheet.addMergedRegion(region);
        fail("Expected exception");
    } catch (IllegalArgumentException e) {
        assertEquals("Maximum row number is " + ssVersion.getLastRowIndex(), e.getMessage());
    }
    assertEquals(1, sheet.getNumMergedRegions());
    wb.close();
}
Also used : CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) SpreadsheetVersion(org.apache.poi.ss.SpreadsheetVersion) Test(org.junit.Test)

Aggregations

SpreadsheetVersion (org.apache.poi.ss.SpreadsheetVersion)10 Test (org.junit.Test)6 AttributedString (java.text.AttributedString)1 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)1 CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)1 CellReference (org.apache.poi.ss.util.CellReference)1 NameType (org.apache.poi.ss.util.CellReference.NameType)1