Examples with XSSFCell org.apache.poi.xssf.usermodel.XSSFCell used on opensource projects

Example 11 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project poi by apache.

the class TestXSSFCellFill method testColorFromTheme.

@Test
public void testColorFromTheme() throws IOException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("styles.xlsx");
    XSSFCell cellWithThemeColor = wb.getSheetAt(0).getRow(10).getCell(0);
    //color RGB will be extracted from theme
    XSSFColor foregroundColor = cellWithThemeColor.getCellStyle().getFillForegroundXSSFColor();
    byte[] rgb = foregroundColor.getRGB();
    byte[] rgbWithTint = foregroundColor.getRGBWithTint();
    // Dk2
    assertEquals(rgb[0], 31);
    assertEquals(rgb[1], 73);
    assertEquals(rgb[2], 125);
    // Dk2, lighter 40% (tint is about 0.39998)
    // 31 * (1.0 - 0.39998) + (255 - 255 * (1.0 - 0.39998)) = 120.59552 => 120 (byte)
    // 73 * (1.0 - 0.39998) + (255 - 255 * (1.0 - 0.39998)) = 145.79636 => -111 (byte)
    // 125 * (1.0 - 0.39998) + (255 - 255 * (1.0 - 0.39998)) = 176.99740 => -80 (byte)
    assertEquals(rgbWithTint[0], 120);
    assertEquals(rgbWithTint[1], -111);
    assertEquals(rgbWithTint[2], -80);
    wb.close();
}

Example 12 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project poi by apache.

the class XSSFRowShifter method updateRowFormulas.

/**
     * Update the formulas in specified row using the formula shifting policy specified by shifter
     *
     * @param row the row to update the formulas on
     * @param shifter the formula shifting policy
     */
@Internal
public void updateRowFormulas(Row row, FormulaShifter shifter) {
    XSSFSheet sheet = (XSSFSheet) row.getSheet();
    for (Cell c : row) {
        XSSFCell cell = (XSSFCell) c;
        CTCell ctCell = cell.getCTCell();
        if (ctCell.isSetF()) {
            CTCellFormula f = ctCell.getF();
            String formula = f.getStringValue();
            if (formula.length() > 0) {
                String shiftedFormula = shiftFormula(row, formula, shifter);
                if (shiftedFormula != null) {
                    f.setStringValue(shiftedFormula);
                    if (f.getT() == STCellFormulaType.SHARED) {
                        int si = (int) f.getSi();
                        CTCellFormula sf = sheet.getSharedFormula(si);
                        sf.setStringValue(shiftedFormula);
                        updateRefInCTCellFormula(row, shifter, sf);
                    }
                }
            }
            //Range of cells which the formula applies to.
            updateRefInCTCellFormula(row, shifter, f);
        }
    }
}

Example 13 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project poi by apache.

the class TestCalculationChain method test46535.

public void test46535() {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("46535.xlsx");
    CalculationChain chain = wb.getCalculationChain();
    //the bean holding the reference to the formula to be deleted
    CTCalcCell c = chain.getCTCalcChain().getCArray(0);
    int cnt = chain.getCTCalcChain().sizeOfCArray();
    assertEquals(10, c.getI());
    assertEquals("E1", c.getR());
    XSSFSheet sheet = wb.getSheet("Test");
    XSSFCell cell = sheet.getRow(0).getCell(4);
    assertEquals(CellType.FORMULA, cell.getCellTypeEnum());
    cell.setCellFormula(null);
    //the count of items is less by one
    c = chain.getCTCalcChain().getCArray(0);
    int cnt2 = chain.getCTCalcChain().sizeOfCArray();
    assertEquals(cnt - 1, cnt2);
    //the first item in the calculation chain is the former second one
    assertEquals(10, c.getI());
    assertEquals("C1", c.getR());
    assertEquals(CellType.STRING, cell.getCellTypeEnum());
    cell.setCellValue("ABC");
    assertEquals(CellType.STRING, cell.getCellTypeEnum());
}

Example 14 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project dhis2-core by dhis2.

the class ExcelNodeSerializer method startWriteRootNode.

@Override
protected void startWriteRootNode(RootNode rootNode) throws Exception {
    XSSFCreationHelper creationHelper = workbook.getCreationHelper();
    int rowIdx = 1;
    for (Node collectionNode : rootNode.getChildren()) {
        if (collectionNode.isCollection()) {
            for (Node complexNode : collectionNode.getChildren()) {
                XSSFRow row = sheet.createRow(rowIdx++);
                int cellIdx = 0;
                for (Node node : complexNode.getChildren()) {
                    if (node.isSimple()) {
                        XSSFCell cell = row.createCell(cellIdx++);
                        cell.setCellValue(getValue((SimpleNode) node));
                        if (node.haveProperty() && PropertyType.URL.equals(node.getProperty().getPropertyType())) {
                            XSSFHyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
                            hyperlink.setAddress(getValue((SimpleNode) node));
                            hyperlink.setLabel(getValue((SimpleNode) node));
                            cell.setHyperlink(hyperlink);
                        } else if (node.haveProperty() && PropertyType.EMAIL.equals(node.getProperty().getPropertyType())) {
                            XSSFHyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.EMAIL);
                            hyperlink.setAddress(getValue((SimpleNode) node));
                            hyperlink.setLabel(getValue((SimpleNode) node));
                            cell.setHyperlink(hyperlink);
                        }
                    }
                }
            }
        }
    }
}

Example 15 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project dhis2-core by dhis2.

the class ExcelNodeSerializer method startSerialize.

@Override
protected void startSerialize(RootNode rootNode, OutputStream outputStream) throws Exception {
    workbook = new XSSFWorkbook();
    sheet = workbook.createSheet("Sheet1");
    XSSFFont boldFont = workbook.createFont();
    boldFont.setBold(true);
    XSSFCellStyle boldCellStyle = workbook.createCellStyle();
    boldCellStyle.setFont(boldFont);
    // build schema
    for (Node child : rootNode.getChildren()) {
        if (child.isCollection()) {
            if (!child.getChildren().isEmpty()) {
                Node node = child.getChildren().get(0);
                XSSFRow row = sheet.createRow(0);
                int cellIdx = 0;
                for (Node property : node.getChildren()) {
                    if (property.isSimple()) {
                        XSSFCell cell = row.createCell(cellIdx++);
                        cell.setCellValue(property.getName());
                        cell.setCellStyle(boldCellStyle);
                    }
                }
            }
        }
    }
}