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

Example 86 with CellReference

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

the class TestXSSFPivotTableRef method setUp.

@Override
@Before
public void setUp() {
    wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    Row row1 = sheet.createRow(0);
    // Create a cell and put a value in it.
    Cell cell = row1.createCell(0);
    cell.setCellValue("Names");
    Cell cell2 = row1.createCell(1);
    cell2.setCellValue("#");
    Cell cell7 = row1.createCell(2);
    cell7.setCellValue("Data");
    Cell cell10 = row1.createCell(3);
    cell10.setCellValue("Value");
    Row row2 = sheet.createRow(1);
    Cell cell3 = row2.createCell(0);
    cell3.setCellValue("Jan");
    Cell cell4 = row2.createCell(1);
    cell4.setCellValue(10);
    Cell cell8 = row2.createCell(2);
    cell8.setCellValue("Apa");
    Cell cell11 = row1.createCell(3);
    cell11.setCellValue(11.11);
    Row row3 = sheet.createRow(2);
    Cell cell5 = row3.createCell(0);
    cell5.setCellValue("Ben");
    Cell cell6 = row3.createCell(1);
    cell6.setCellValue(9);
    Cell cell9 = row3.createCell(2);
    cell9.setCellValue("Bepa");
    Cell cell12 = row1.createCell(3);
    cell12.setCellValue(12.12);
    AreaReference source = new AreaReference("A1:C2", SpreadsheetVersion.EXCEL2007);
    pivotTable = sheet.createPivotTable(source, new CellReference("H5"));
    XSSFSheet offsetSheet = wb.createSheet();
    Row tableRow_1 = offsetSheet.createRow(1);
    offsetOuterCell = tableRow_1.createCell(1);
    offsetOuterCell.setCellValue(-1);
    Cell tableCell_1_1 = tableRow_1.createCell(2);
    tableCell_1_1.setCellValue("Row #");
    Cell tableCell_1_2 = tableRow_1.createCell(3);
    tableCell_1_2.setCellValue("Exponent");
    Cell tableCell_1_3 = tableRow_1.createCell(4);
    tableCell_1_3.setCellValue("10^Exponent");
    Row tableRow_2 = offsetSheet.createRow(2);
    Cell tableCell_2_1 = tableRow_2.createCell(2);
    tableCell_2_1.setCellValue(0);
    Cell tableCell_2_2 = tableRow_2.createCell(3);
    tableCell_2_2.setCellValue(0);
    Cell tableCell_2_3 = tableRow_2.createCell(4);
    tableCell_2_3.setCellValue(1);
    Row tableRow_3 = offsetSheet.createRow(3);
    Cell tableCell_3_1 = tableRow_3.createCell(2);
    tableCell_3_1.setCellValue(1);
    Cell tableCell_3_2 = tableRow_3.createCell(3);
    tableCell_3_2.setCellValue(1);
    Cell tableCell_3_3 = tableRow_3.createCell(4);
    tableCell_3_3.setCellValue(10);
    Row tableRow_4 = offsetSheet.createRow(4);
    Cell tableCell_4_1 = tableRow_4.createCell(2);
    tableCell_4_1.setCellValue(2);
    Cell tableCell_4_2 = tableRow_4.createCell(3);
    tableCell_4_2.setCellValue(2);
    Cell tableCell_4_3 = tableRow_4.createCell(4);
    tableCell_4_3.setCellValue(100);
    AreaReference offsetSource = new AreaReference(new CellReference("C2"), new CellReference("E4"));
    offsetPivotTable = offsetSheet.createPivotTable(offsetSource, new CellReference("C6"));
}

Example 87 with CellReference

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

the class TestFormulaShifter method testShiftSheet.

@Test
public void testShiftSheet() {
    // 4 sheets, move a sheet from pos 2 to pos 0, i.e. current 0 becomes 1, current 1 becomes pos 2 
    FormulaShifter shifter = FormulaShifter.createForSheetShift(2, 0);
    Ptg[] ptgs = new Ptg[] { new Ref3DPtg(new CellReference("first", 0, 0, true, true), 0), new Ref3DPtg(new CellReference("second", 0, 0, true, true), 1), new Ref3DPtg(new CellReference("third", 0, 0, true, true), 2), new Ref3DPtg(new CellReference("fourth", 0, 0, true, true), 3) };
    shifter.adjustFormula(ptgs, -1);
    assertEquals("formula previously pointing to sheet 0 should now point to sheet 1", 1, ((Ref3DPtg) ptgs[0]).getExternSheetIndex());
    assertEquals("formula previously pointing to sheet 1 should now point to sheet 2", 2, ((Ref3DPtg) ptgs[1]).getExternSheetIndex());
    assertEquals("formula previously pointing to sheet 2 should now point to sheet 0", 0, ((Ref3DPtg) ptgs[2]).getExternSheetIndex());
    assertEquals("formula previously pointing to sheet 3 should be unchanged", 3, ((Ref3DPtg) ptgs[3]).getExternSheetIndex());
}

Example 88 with CellReference

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

the class TestCellReference method testToString.

@Test
public void testToString() {
    CellReference ref = new CellReference("'Sheet 1'!A5");
    assertEquals("org.apache.poi.ss.util.CellReference ['Sheet 1'!A5]", ref.toString());
}

Example 89 with CellReference

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

the class TestCellReference method testGetCellRefParts.

@Test
public void testGetCellRefParts() {
    CellReference cellReference;
    String[] parts;
    String cellRef = "A1";
    cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("1", parts[1]);
    assertEquals("A", parts[2]);
    cellRef = "AA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("1", parts[1]);
    assertEquals("AA", parts[2]);
    cellRef = "AA100";
    cellReference = new CellReference(cellRef);
    assertEquals(26, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("100", parts[1]);
    assertEquals("AA", parts[2]);
    cellRef = "AAA300";
    cellReference = new CellReference(cellRef);
    assertEquals(702, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("300", parts[1]);
    assertEquals("AAA", parts[2]);
    cellRef = "ZZ100521";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 + 25, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("100521", parts[1]);
    assertEquals("ZZ", parts[2]);
    cellRef = "ZYX987";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 * 26 + 25 * 26 + 24 - 1, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("987", parts[1]);
    assertEquals("ZYX", parts[2]);
    cellRef = "AABC10065";
    cellReference = new CellReference(cellRef);
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("10065", parts[1]);
    assertEquals("AABC", parts[2]);
}

Example 90 with CellReference

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

the class TestCellReference method testConstructors.

@Test
public void testConstructors() {
    CellReference cellReference;
    final String sheet = "Sheet1";
    final String cellRef = "A1";
    final int row = 0;
    final int col = 0;
    final boolean absRow = true;
    final boolean absCol = false;
    cellReference = new CellReference(row, col);
    assertEquals("A1", cellReference.formatAsString());
    cellReference = new CellReference(row, col, absRow, absCol);
    assertEquals("A$1", cellReference.formatAsString());
    cellReference = new CellReference(row, (short) col);
    assertEquals("A1", cellReference.formatAsString());
    cellReference = new CellReference(cellRef);
    assertEquals("A1", cellReference.formatAsString());
    cellReference = new CellReference(sheet, row, col, absRow, absCol);
    assertEquals("Sheet1!A$1", cellReference.formatAsString());
}