Search in sources :

Example 21 with CellReference

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

the class BaseTestXSSFPivotTable method testPivotTableSheetNamesAreCaseInsensitive.

@Test
public void testPivotTableSheetNamesAreCaseInsensitive() {
    wb.setSheetName(0, "original");
    wb.setSheetName(1, "offset");
    XSSFSheet original = wb.getSheet("OriginaL");
    XSSFSheet offset = wb.getSheet("OffseT");
    // assume sheets are accessible via case-insensitive name
    assertNotNull(original);
    assertNotNull(offset);
    AreaReference source = new AreaReference("ORIGinal!A1:C2", _testDataProvider.getSpreadsheetVersion());
    // create a pivot table on the same sheet, case insensitive
    original.createPivotTable(source, new CellReference("W1"));
    // create a pivot table on a different sheet, case insensitive
    offset.createPivotTable(source, new CellReference("W1"));
}
Also used : AreaReference(org.apache.poi.ss.util.AreaReference) CellReference(org.apache.poi.ss.util.CellReference) Test(org.junit.Test)

Example 22 with CellReference

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

the class TestSXSSFWorkbook method populateWorkbook.

private static void populateWorkbook(Workbook wb) {
    Sheet sh = wb.createSheet();
    for (int rownum = 0; rownum < 100; rownum++) {
        Row row = sh.createRow(rownum);
        for (int cellnum = 0; cellnum < 10; cellnum++) {
            Cell cell = row.createCell(cellnum);
            String address = new CellReference(cell).formatAsString();
            cell.setCellValue(address);
        }
    }
}
Also used : Row(org.apache.poi.ss.usermodel.Row) CellReference(org.apache.poi.ss.util.CellReference) Sheet(org.apache.poi.ss.usermodel.Sheet) Cell(org.apache.poi.ss.usermodel.Cell)

Example 23 with CellReference

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

the class TestSXSSFWorkbook method assertWorkbookDispose.

protected static void assertWorkbookDispose(SXSSFWorkbook wb) {
    int rowNum = 1000;
    int sheetNum = 5;
    for (int i = 0; i < sheetNum; i++) {
        Sheet sh = wb.createSheet("sheet" + i);
        for (int j = 0; j < rowNum; j++) {
            Row row = sh.createRow(j);
            Cell cell1 = row.createCell(0);
            cell1.setCellValue(new CellReference(cell1).formatAsString());
            Cell cell2 = row.createCell(1);
            cell2.setCellValue(i);
            Cell cell3 = row.createCell(2);
            cell3.setCellValue(j);
        }
    }
    for (Sheet sheet : wb) {
        SXSSFSheet sxSheet = (SXSSFSheet) sheet;
        assertTrue(sxSheet.getSheetDataWriter().getTempFile().exists());
    }
    assertTrue(wb.dispose());
    for (Sheet sheet : wb) {
        SXSSFSheet sxSheet = (SXSSFSheet) sheet;
        assertFalse(sxSheet.getSheetDataWriter().getTempFile().exists());
    }
}
Also used : Row(org.apache.poi.ss.usermodel.Row) CellReference(org.apache.poi.ss.util.CellReference) Sheet(org.apache.poi.ss.usermodel.Sheet) Cell(org.apache.poi.ss.usermodel.Cell)

Example 24 with CellReference

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

the class TestXSSFDataFormat method testConditionalFormattingEvaluation.

@Test
public void testConditionalFormattingEvaluation() throws IOException {
    final Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61060-conditional-number-formatting.xlsx");
    final DataFormatter formatter = new DataFormatter();
    final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    final ConditionalFormattingEvaluator cfEvaluator = new ConditionalFormattingEvaluator(wb, (WorkbookEvaluatorProvider) evaluator);
    CellReference ref = new CellReference("A1");
    Cell cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("0.10", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("0.1", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A3");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("-2.00E+03", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("-2000", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A4");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("100", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    ref = new CellReference("A5");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("$1,000", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("1000", formatter.formatCellValue(cell, evaluator));
    wb.close();
}
Also used : ConditionalFormattingEvaluator(org.apache.poi.ss.formula.ConditionalFormattingEvaluator) CellReference(org.apache.poi.ss.util.CellReference) Cell(org.apache.poi.ss.usermodel.Cell) Workbook(org.apache.poi.ss.usermodel.Workbook) DataFormatter(org.apache.poi.ss.usermodel.DataFormatter) FormulaEvaluator(org.apache.poi.ss.usermodel.FormulaEvaluator) Test(org.junit.Test)

Example 25 with CellReference

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

the class TestXSSFComment method getSetCol.

@Test
public void getSetCol() {
    CommentsTable sheetComments = new CommentsTable();
    XSSFVMLDrawing vml = new XSSFVMLDrawing();
    CTComment ctComment = sheetComments.newComment(CellAddress.A1);
    CTShape vmlShape = vml.newCommentShape();
    XSSFComment comment = new XSSFComment(sheetComments, ctComment, vmlShape);
    comment.setColumn(1);
    assertEquals(1, comment.getColumn());
    assertEquals(1, new CellReference(ctComment.getRef()).getCol());
    assertEquals(1, vmlShape.getClientDataArray(0).getColumnArray(0).intValue());
    comment.setColumn(5);
    assertEquals(5, comment.getColumn());
    assertEquals(5, new CellReference(ctComment.getRef()).getCol());
    assertEquals(5, vmlShape.getClientDataArray(0).getColumnArray(0).intValue());
}
Also used : CTComment(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment) CTShape(com.microsoft.schemas.vml.CTShape) CellReference(org.apache.poi.ss.util.CellReference) CommentsTable(org.apache.poi.xssf.model.CommentsTable) Test(org.junit.Test)

Aggregations

CellReference (org.apache.poi.ss.util.CellReference)116 Test (org.junit.Test)52 Cell (org.apache.poi.ss.usermodel.Cell)23 Row (org.apache.poi.ss.usermodel.Row)22 AreaReference (org.apache.poi.ss.util.AreaReference)19 Sheet (org.apache.poi.ss.usermodel.Sheet)13 CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)12 Workbook (org.apache.poi.ss.usermodel.Workbook)11 SXSSFWorkbook (org.apache.poi.xssf.streaming.SXSSFWorkbook)11 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)10 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)8 FormulaEvaluator (org.apache.poi.ss.usermodel.FormulaEvaluator)7 XSSFSheet (org.apache.poi.xssf.usermodel.XSSFSheet)6 FileOutputStream (java.io.FileOutputStream)4 ArrayList (java.util.ArrayList)4 CellStyle (org.apache.poi.ss.usermodel.CellStyle)4 XSSFRow (org.apache.poi.xssf.usermodel.XSSFRow)4 File (java.io.File)3 OutputStream (java.io.OutputStream)3 LinkedHashMap (java.util.LinkedHashMap)3