Examples with CellReference org.apache.poi.ss.util.CellReference used on opensource projects

Example 91 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestCellReference method testGetRowNumFromRef.

@Test
public void testGetRowNumFromRef() {
    String cellRef = "A1";
    CellReference cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getRow());
    cellRef = "A12";
    cellReference = new CellReference(cellRef);
    assertEquals(11, cellReference.getRow());
    cellRef = "AS121";
    cellReference = new CellReference(cellRef);
    assertEquals(120, cellReference.getRow());
}

Example 92 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class HSSFSheet method removeArrayFormula.

@Override
public CellRange<HSSFCell> removeArrayFormula(Cell cell) {
    if (cell.getSheet() != this) {
        throw new IllegalArgumentException("Specified cell does not belong to this sheet.");
    }
    CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
    if (!(rec instanceof FormulaRecordAggregate)) {
        String ref = new CellReference(cell).formatAsString();
        throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
    }
    FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
    CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());
    CellRange<HSSFCell> result = getCellRange(range);
    // clear all cells in the range
    for (Cell c : result) {
        c.setCellType(CellType.BLANK);
    }
    return result;
}

Example 93 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestXSLFChart method testFillChartTemplate.

/**
     * a modified version from POI-examples
     */
@Test
public void testFillChartTemplate() throws IOException {
    // first line is chart title
    String chartTitle = "Apache POI";
    XMLSlideShow pptx = XSLFTestDataSamples.openSampleDocument("pie-chart.pptx");
    XSLFSlide slide = pptx.getSlides().get(0);
    // find chart in the slide
    XSLFChart chart = null;
    for (POIXMLDocumentPart part : slide.getRelations()) {
        if (part instanceof XSLFChart) {
            chart = (XSLFChart) part;
            break;
        }
    }
    if (chart == null)
        throw new IllegalStateException("chart not found in the template");
    // embedded Excel workbook that holds the chart data
    POIXMLDocumentPart xlsPart = chart.getRelations().get(0);
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    CTChart ctChart = chart.getCTChart();
    CTPlotArea plotArea = ctChart.getPlotArea();
    CTPieChart pieChart = plotArea.getPieChartArray(0);
    //Pie Chart Series
    CTPieSer ser = pieChart.getSerArray(0);
    // Series Text
    CTSerTx tx = ser.getTx();
    tx.getStrRef().getStrCache().getPtArray(0).setV(chartTitle);
    sheet.createRow(0).createCell(1).setCellValue(chartTitle);
    String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString();
    tx.getStrRef().setF(titleRef);
    // Category Axis Data
    CTAxDataSource cat = ser.getCat();
    CTStrData strData = cat.getStrRef().getStrCache();
    // Values
    CTNumDataSource valSrc = ser.getVal();
    CTNumData numData = valSrc.getNumRef().getNumCache();
    // unset old axis text
    strData.setPtArray(null);
    // unset old values
    numData.setPtArray(null);
    Map<String, Double> pieModel = new LinkedHashMap<String, Double>();
    pieModel.put("First", 1.0);
    pieModel.put("Second", 3.0);
    pieModel.put("Third", 4.0);
    // set model
    int idx = 0;
    int rownum = 1;
    for (String key : pieModel.keySet()) {
        double val = pieModel.get(key);
        CTNumVal numVal = numData.addNewPt();
        numVal.setIdx(idx);
        numVal.setV("" + val);
        CTStrVal sVal = strData.addNewPt();
        sVal.setIdx(idx);
        sVal.setV(key);
        idx++;
        XSSFRow row = sheet.createRow(rownum++);
        row.createCell(0).setCellValue(key);
        row.createCell(1).setCellValue(val);
    }
    numData.getPtCount().setVal(idx);
    strData.getPtCount().setVal(idx);
    String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
    valSrc.getNumRef().setF(numDataRange);
    String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true);
    cat.getStrRef().setF(axisDataRange);
    // updated the embedded workbook with the data
    OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream();
    wb.write(xlsOut);
    xlsOut.close();
    wb.close();
}

Example 94 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestStructuredReferences method testTableFormulas.

@Test
public void testTableFormulas() throws Exception {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
    try {
        final FormulaEvaluator eval = new XSSFFormulaEvaluator(wb);
        final XSSFSheet tableSheet = wb.getSheet("Table");
        final XSSFSheet formulaSheet = wb.getSheet("Formulas");
        confirm(eval, tableSheet.getRow(5).getCell(0), 49);
        confirm(eval, formulaSheet.getRow(0).getCell(0), 209);
        confirm(eval, formulaSheet.getRow(1).getCell(0), "one");
        // test changing a table value, to see if the caches are properly cleared
        // Issue 59814
        // this test passes before the fix for 59814
        tableSheet.getRow(1).getCell(1).setCellValue("ONEA");
        confirm(eval, formulaSheet.getRow(1).getCell(0), "ONEA");
        // test adding a row to a table, issue 59814
        Row newRow = tableSheet.getRow(7);
        if (newRow == null)
            newRow = tableSheet.createRow(7);
        newRow.createCell(0, CellType.FORMULA).setCellFormula("\\_Prime.1[[#This Row],[@Number]]*\\_Prime.1[[#This Row],[@Number]]");
        newRow.createCell(1, CellType.STRING).setCellValue("thirteen");
        newRow.createCell(2, CellType.NUMERIC).setCellValue(13);
        // update Table
        final XSSFTable table = wb.getTable("\\_Prime.1");
        final AreaReference newArea = new AreaReference(table.getStartCellReference(), new CellReference(table.getEndRowIndex() + 1, table.getEndColIndex()));
        String newAreaStr = newArea.formatAsString();
        table.getCTTable().setRef(newAreaStr);
        table.getCTTable().getAutoFilter().setRef(newAreaStr);
        table.updateHeaders();
        table.updateReferences();
        // these fail before the fix for 59814
        confirm(eval, tableSheet.getRow(7).getCell(0), 13 * 13);
        confirm(eval, formulaSheet.getRow(0).getCell(0), 209 + 13 * 13);
    } finally {
        wb.close();
    }
}

Example 95 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestXSSFSheet method testCopyOneRow.

protected void testCopyOneRow(String copyRowsTestWorkbook) throws IOException {
    final double FLOAT_PRECISION = 1e-9;
    final XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(copyRowsTestWorkbook);
    final XSSFSheet sheet = wb.getSheetAt(0);
    final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy();
    sheet.copyRows(1, 1, 6, defaultCopyPolicy);
    final Row srcRow = sheet.getRow(1);
    final Row destRow = sheet.getRow(6);
    int col = 0;
    Cell cell;
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("Source row ->", cell.getStringCellValue());
    // Style
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Style] B7 cell value", "Red", cell.getStringCellValue());
    assertEquals("[Style] B7 cell style", CellUtil.getCell(srcRow, 1).getCellStyle(), cell.getCellStyle());
    // Blank
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Blank] C7 cell type", CellType.BLANK, cell.getCellTypeEnum());
    // Error
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Error] D7 cell type", CellType.ERROR, cell.getCellTypeEnum());
    final FormulaError error = FormulaError.forInt(cell.getErrorCellValue());
    //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here
    assertEquals("[Error] D7 cell value", FormulaError.NA, error);
    // Date
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Date] E7 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    final Date date = LocaleUtil.getLocaleCalendar(2000, Calendar.JANUARY, 1).getTime();
    assertEquals("[Date] E7 cell value", date, cell.getDateCellValue());
    // Boolean
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Boolean] F7 cell type", CellType.BOOLEAN, cell.getCellTypeEnum());
    assertEquals("[Boolean] F7 cell value", true, cell.getBooleanCellValue());
    // String
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[String] G7 cell type", CellType.STRING, cell.getCellTypeEnum());
    assertEquals("[String] G7 cell value", "Hello", cell.getStringCellValue());
    // Int
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Int] H7 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    assertEquals("[Int] H7 cell value", 15, (int) cell.getNumericCellValue());
    // Float
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Float] I7 cell type", CellType.NUMERIC, cell.getCellTypeEnum());
    assertEquals("[Float] I7 cell value", 12.5, cell.getNumericCellValue(), FLOAT_PRECISION);
    // Cell Formula
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("J7", new CellReference(cell).formatAsString());
    assertEquals("[Cell Formula] J7 cell type", CellType.FORMULA, cell.getCellTypeEnum());
    assertEquals("[Cell Formula] J7 cell formula", "5+2", cell.getCellFormula());
    System.out.println("Cell formula evaluation currently unsupported");
    // Cell Formula with Reference
    // Formula row references should be adjusted by destRowNum-srcRowNum
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("K7", new CellReference(cell).formatAsString());
    assertEquals("[Cell Formula with Reference] K7 cell type", CellType.FORMULA, cell.getCellTypeEnum());
    assertEquals("[Cell Formula with Reference] K7 cell formula", "J7+H$2", cell.getCellFormula());
    // Cell Formula with Reference spanning multiple rows
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell type", CellType.FORMULA, cell.getCellTypeEnum());
    assertEquals("[Cell Formula with Reference spanning multiple rows] L7 cell formula", "G7&\" \"&G8", cell.getCellFormula());
    // Cell Formula with Reference spanning multiple rows
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Cell Formula with Area Reference] M7 cell type", CellType.FORMULA, cell.getCellTypeEnum());
    assertEquals("[Cell Formula with Area Reference] M7 cell formula", "SUM(H7:I8)", cell.getCellFormula());
    // Array Formula
    cell = CellUtil.getCell(destRow, col++);
    System.out.println("Array formulas currently unsupported");
    // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula()
    /*
        assertEquals("[Array Formula] N7 cell type", CellType.FORMULA, cell.getCellTypeEnum());
        assertEquals("[Array Formula] N7 cell formula", "{SUM(H7:J7*{1,2,3})}", cell.getCellFormula());
        */
    // Data Format
    cell = CellUtil.getCell(destRow, col++);
    assertEquals("[Data Format] O7 cell type;", CellType.NUMERIC, cell.getCellTypeEnum());
    assertEquals("[Data Format] O7 cell value", 100.20, cell.getNumericCellValue(), FLOAT_PRECISION);
    //FIXME: currently fails
    final String moneyFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)";
    assertEquals("[Data Format] O7 data format", moneyFormat, cell.getCellStyle().getDataFormatString());
    // Merged
    cell = CellUtil.getCell(destRow, col);
    assertEquals("[Merged] P7:Q7 cell value", "Merged cells", cell.getStringCellValue());
    assertTrue("[Merged] P7:Q7 merged region", sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P7:Q7")));
    // Merged across multiple rows
    // Microsoft Excel 2013 does not copy a merged region unless all rows of
    // the source merged region are selected
    // POI's behavior should match this behavior
    col += 2;
    cell = CellUtil.getCell(destRow, col);
    // Note: this behavior deviates from Microsoft Excel,
    // which will not overwrite a cell in destination row if merged region extends beyond the copied row.
    // The Excel way would require:
    //assertEquals("[Merged across multiple rows] R7:S8 merged region", "Should NOT be overwritten", cell.getStringCellValue());
    //assertFalse("[Merged across multiple rows] R7:S8 merged region", 
    //        sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8")));
    // As currently implemented, cell value is copied but merged region is not copied
    assertEquals("[Merged across multiple rows] R7:S8 cell value", "Merged cells across multiple rows", cell.getStringCellValue());
    assertFalse("[Merged across multiple rows] R7:S7 merged region (one row)", //shouldn't do 1-row merge
    sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S7")));
    assertFalse("[Merged across multiple rows] R7:S8 merged region", //shouldn't do 2-row merge
    sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8")));
    // Make sure other rows are blank (off-by-one errors)
    assertNull(sheet.getRow(5));
    assertNull(sheet.getRow(7));
    wb.close();
}