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

Example 56 with CellReference

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

the class XSSFWorkbook method getReferencePrintArea.

private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) {
    //windows excel example: Sheet1!$C$3:$E$4
    CellReference colRef = new CellReference(sheetName, startR, startC, true, true);
    CellReference colRef2 = new CellReference(sheetName, endR, endC, true, true);
    return "$" + colRef.getCellRefParts()[2] + "$" + colRef.getCellRefParts()[1] + ":$" + colRef2.getCellRefParts()[2] + "$" + colRef2.getCellRefParts()[1];
}

Example 57 with CellReference

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

the class XSSFPivotTable method createSourceReferences.

/**
     * Creates cacheSource and workSheetSource for pivot table and sets the source reference as well assets the location of the pivot table
     * @param position Position for pivot table in sheet
     * @param sourceSheet Sheet where the source will be collected from
     * @param refConfig  an configurator that knows how to configure pivot table references
     */
@Beta
protected void createSourceReferences(CellReference position, Sheet sourceSheet, PivotTableReferenceConfigurator refConfig) {
    //Get cell one to the right and one down from position, add both to AreaReference and set pivot table location.
    AreaReference destination = new AreaReference(position, new CellReference(position.getRow() + 1, position.getCol() + 1));
    CTLocation location;
    if (pivotTableDefinition.getLocation() == null) {
        location = pivotTableDefinition.addNewLocation();
        location.setFirstDataCol(1);
        location.setFirstDataRow(1);
        location.setFirstHeaderRow(1);
    } else {
        location = pivotTableDefinition.getLocation();
    }
    location.setRef(destination.formatAsString());
    pivotTableDefinition.setLocation(location);
    //Set source for the pivot table
    CTPivotCacheDefinition cacheDef = getPivotCacheDefinition().getCTPivotCacheDefinition();
    CTCacheSource cacheSource = cacheDef.addNewCacheSource();
    cacheSource.setType(STSourceType.WORKSHEET);
    CTWorksheetSource worksheetSource = cacheSource.addNewWorksheetSource();
    worksheetSource.setSheet(sourceSheet.getSheetName());
    setDataSheet(sourceSheet);
    refConfig.configureReference(worksheetSource);
    if (worksheetSource.getName() == null && worksheetSource.getRef() == null)
        throw new IllegalArgumentException("Pivot table source area reference or name must be specified.");
}

Example 58 with CellReference

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

the class TestXSSFCell method testCopyCellFrom_CellCopyPolicy_mergeHyperlink.

@Test
public final void testCopyCellFrom_CellCopyPolicy_mergeHyperlink() throws IOException {
    setUp_testCopyCellFrom_CellCopyPolicy();
    final Workbook wb = srcCell.getSheet().getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    srcCell.setCellValue("URL LINK");
    Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    destCell.setHyperlink(link);
    // Set link cell style (optional)
    CellStyle hlinkStyle = wb.createCellStyle();
    Font hlinkFont = wb.createFont();
    hlinkFont.setUnderline(Font.U_SINGLE);
    hlinkFont.setColor(IndexedColors.BLUE.getIndex());
    hlinkStyle.setFont(hlinkFont);
    destCell.setCellStyle(hlinkStyle);
    // Pre-condition assumptions. This test is broken if either of these fail.
    assertSame("unit test assumes srcCell and destCell are on the same sheet", srcCell.getSheet(), destCell.getSheet());
    assertNull(srcCell.getHyperlink());
    // Merge hyperlink - since srcCell doesn't have a hyperlink, destCell's hyperlink is not overwritten (cleared).
    final CellCopyPolicy policy = new CellCopyPolicy.Builder().mergeHyperlink(true).copyHyperlink(false).build();
    destCell.copyCellFrom(srcCell, policy);
    assertNull(srcCell.getHyperlink());
    assertNotNull(destCell.getHyperlink());
    assertSame(link, destCell.getHyperlink());
    List<XSSFHyperlink> links;
    links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 1, links.size());
    assertEquals("source hyperlink", new CellReference(destCell).formatAsString(), links.get(0).getCellRef());
    // Merge destCell's hyperlink to srcCell. Since destCell does have a hyperlink, this should copy destCell's hyperlink to srcCell.
    srcCell.copyCellFrom(destCell, policy);
    assertNotNull(srcCell.getHyperlink());
    assertNotNull(destCell.getHyperlink());
    links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 2, links.size());
    assertEquals("dest hyperlink", new CellReference(destCell).formatAsString(), links.get(0).getCellRef());
    assertEquals("source hyperlink", new CellReference(srcCell).formatAsString(), links.get(1).getCellRef());
    wb.close();
}

Example 59 with CellReference

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

the class TestXSSFBugs method bug51710.

/**
     * Bugzilla 51710: problems reading shared formuals from .xlsx
     */
@Test
public void bug51710() throws IOException {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("51710.xlsx");
    final String[] columns = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N" };
    // bug triggers on row index 59
    final int rowMax = 500;
    Sheet sheet = wb.getSheetAt(0);
    // go through all formula cells
    for (int rInd = 2; rInd <= rowMax; rInd++) {
        Row row = sheet.getRow(rInd);
        for (int cInd = 1; cInd <= 12; cInd++) {
            Cell cell = row.getCell(cInd);
            String formula = cell.getCellFormula();
            CellReference ref = new CellReference(cell);
            //simulate correct answer
            String correct = "$A" + (rInd + 1) + "*" + columns[cInd] + "$2";
            assertEquals("Incorrect formula in " + ref.formatAsString(), correct, formula);
        }
    }
    wb.close();
}

Example 60 with CellReference

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

the class TestXSSFCell method test56170.

@Test
public void test56170() throws IOException {
    final Workbook wb1 = XSSFTestDataSamples.openSampleWorkbook("56170.xlsx");
    final XSSFSheet sheet = (XSSFSheet) wb1.getSheetAt(0);
    Workbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1);
    Cell cell;
    // add some contents to table so that the table will need expansion
    Row row = sheet.getRow(0);
    Workbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2);
    cell = row.createCell(0);
    Workbook wb4 = XSSFTestDataSamples.writeOutAndReadBack(wb3);
    cell.setCellValue("demo1");
    Workbook wb5 = XSSFTestDataSamples.writeOutAndReadBack(wb4);
    cell = row.createCell(1);
    Workbook wb6 = XSSFTestDataSamples.writeOutAndReadBack(wb5);
    cell.setCellValue("demo2");
    Workbook wb7 = XSSFTestDataSamples.writeOutAndReadBack(wb6);
    cell = row.createCell(2);
    Workbook wb8 = XSSFTestDataSamples.writeOutAndReadBack(wb7);
    cell.setCellValue("demo3");
    Workbook wb9 = XSSFTestDataSamples.writeOutAndReadBack(wb8);
    row = sheet.getRow(1);
    cell = row.createCell(0);
    cell.setCellValue("demo1");
    cell = row.createCell(1);
    cell.setCellValue("demo2");
    cell = row.createCell(2);
    cell.setCellValue("demo3");
    Workbook wb10 = XSSFTestDataSamples.writeOutAndReadBack(wb9);
    // expand table
    XSSFTable table = sheet.getTables().get(0);
    final CellReference startRef = table.getStartCellReference();
    final CellReference endRef = table.getEndCellReference();
    table.getCTTable().setRef(new CellRangeAddress(startRef.getRow(), 1, startRef.getCol(), endRef.getCol()).formatAsString());
    Workbook wb11 = XSSFTestDataSamples.writeOutAndReadBack(wb10);
    assertNotNull(wb11);
    wb11.close();
    wb10.close();
    wb9.close();
    wb8.close();
    wb7.close();
    wb6.close();
    wb5.close();
    wb4.close();
    wb3.close();
    wb2.close();
    wb1.close();
}