Examples with CellStyle org.apache.poi.ss.usermodel.CellStyle used on opensource projects

Example 21 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class TestLoadSaveXSSF method testLoadStyles.

// TODO filename string hard coded in XSSFWorkbook constructor in order to make ant test-ooxml target be successful.
public void testLoadStyles() throws Exception {
    XSSFWorkbook workbook = new XSSFWorkbook(_ssSampels.openResourceAsStream("styles.xlsx"));
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    Cell cell = row.getCell((short) 0);
    CellStyle style = cell.getCellStyle();
// assertNotNull(style);
}

Example 22 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle 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 23 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class TestXSSFWorkbook method bug49702.

/**
     * Problems with the count of the number of styles
     *  coming out wrong
     */
@Test
public void bug49702() throws IOException {
    // First try with a new file
    XSSFWorkbook wb1 = new XSSFWorkbook();
    // Should have one style
    assertEquals(1, wb1.getNumCellStyles());
    wb1.getCellStyleAt((short) 0);
    assertNull("Shouldn't be able to get style at 0 that doesn't exist", wb1.getCellStyleAt((short) 1));
    // Add another one
    CellStyle cs = wb1.createCellStyle();
    cs.setDataFormat((short) 11);
    // Re-check
    assertEquals(2, wb1.getNumCellStyles());
    wb1.getCellStyleAt((short) 0);
    wb1.getCellStyleAt((short) 1);
    assertNull("Shouldn't be able to get style at 2 that doesn't exist", wb1.getCellStyleAt((short) 2));
    // Save and reload
    XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb1);
    assertEquals(2, nwb.getNumCellStyles());
    nwb.getCellStyleAt((short) 0);
    nwb.getCellStyleAt((short) 1);
    assertNull("Shouldn't be able to get style at 2 that doesn't exist", nwb.getCellStyleAt((short) 2));
    // Now with an existing file
    XSSFWorkbook wb2 = XSSFTestDataSamples.openSampleWorkbook("sample.xlsx");
    assertEquals(3, wb2.getNumCellStyles());
    wb2.getCellStyleAt((short) 0);
    wb2.getCellStyleAt((short) 1);
    wb2.getCellStyleAt((short) 2);
    assertNull("Shouldn't be able to get style at 3 that doesn't exist", wb2.getCellStyleAt((short) 3));
    wb2.close();
    wb1.close();
    nwb.close();
}

Example 24 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class TestDateFormatConverter method outputLocaleDataFormats.

private void outputLocaleDataFormats(Date date, boolean dates, boolean times, int style, String styleName) throws Exception {
    Workbook workbook = new HSSFWorkbook();
    try {
        String sheetName;
        if (dates) {
            if (times) {
                sheetName = "DateTimes";
            } else {
                sheetName = "Dates";
            }
        } else {
            sheetName = "Times";
        }
        Sheet sheet = workbook.createSheet(sheetName);
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("locale");
        header.createCell(1).setCellValue("DisplayName");
        header.createCell(2).setCellValue("Excel " + styleName);
        header.createCell(3).setCellValue("java.text.DateFormat");
        header.createCell(4).setCellValue("Equals");
        header.createCell(5).setCellValue("Java pattern");
        header.createCell(6).setCellValue("Excel pattern");
        int rowNum = 1;
        for (Locale locale : DateFormat.getAvailableLocales()) {
            try {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(locale.toString());
                row.createCell(1).setCellValue(locale.getDisplayName(Locale.ROOT));
                DateFormat dateFormat;
                if (dates) {
                    if (times) {
                        dateFormat = DateFormat.getDateTimeInstance(style, style, locale);
                    } else {
                        dateFormat = DateFormat.getDateInstance(style, locale);
                    }
                } else {
                    dateFormat = DateFormat.getTimeInstance(style, locale);
                }
                Cell cell = row.createCell(2);
                cell.setCellValue(date);
                CellStyle cellStyle = row.getSheet().getWorkbook().createCellStyle();
                String javaDateFormatPattern = ((SimpleDateFormat) dateFormat).toPattern();
                String excelFormatPattern = DateFormatConverter.convert(locale, javaDateFormatPattern);
                DataFormat poiFormat = row.getSheet().getWorkbook().createDataFormat();
                cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
                row.createCell(3).setCellValue(dateFormat.format(date));
                cell.setCellStyle(cellStyle);
                // the formula returns TRUE is the formatted date in column C equals to the string in column D
                row.createCell(4).setCellFormula("TEXT(C" + rowNum + ",G" + rowNum + ")=D" + rowNum);
                row.createCell(5).setCellValue(javaDateFormatPattern);
                row.createCell(6).setCellValue(excelFormatPattern);
            } catch (Exception e) {
                throw new RuntimeException("Failed for locale: " + locale + ", having locales: " + Arrays.toString(DateFormat.getAvailableLocales()), e);
            }
        }
        File outputFile = TempFile.createTempFile("Locale" + sheetName + styleName, ".xlsx");
        FileOutputStream outputStream = new FileOutputStream(outputFile);
        try {
            workbook.write(outputStream);
        } finally {
            outputStream.close();
        }
        System.out.println("Open " + outputFile.getAbsolutePath() + " in Excel");
    } finally {
        workbook.close();
    }
}

Example 25 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class TestPropertyTemplate method clonePropertyTemplate.

@Test
public void clonePropertyTemplate() throws IOException {
    CellRangeAddress a1c3 = new CellRangeAddress(0, 2, 0, 2);
    PropertyTemplate pt = new PropertyTemplate();
    pt.drawBorders(a1c3, BorderStyle.MEDIUM, IndexedColors.RED.getIndex(), BorderExtent.ALL);
    PropertyTemplate pt2 = new PropertyTemplate(pt);
    assertNotSame(pt2, pt);
    for (int i = 0; i <= 2; i++) {
        for (int j = 0; j <= 2; j++) {
            assertEquals(4, pt2.getNumBorderColors(i, j));
            assertEquals(4, pt2.getNumBorderColors(i, j));
        }
    }
    CellRangeAddress b2 = new CellRangeAddress(1, 1, 1, 1);
    pt2.drawBorders(b2, BorderStyle.THIN, BorderExtent.ALL);
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    pt.applyBorders(sheet);
    for (Row row : sheet) {
        for (Cell cell : row) {
            CellStyle cs = cell.getCellStyle();
            assertEquals(BorderStyle.MEDIUM, cs.getBorderTopEnum());
            assertEquals(BorderStyle.MEDIUM, cs.getBorderBottomEnum());
            assertEquals(BorderStyle.MEDIUM, cs.getBorderLeftEnum());
            assertEquals(BorderStyle.MEDIUM, cs.getBorderRightEnum());
            assertEquals(IndexedColors.RED.getIndex(), cs.getTopBorderColor());
            assertEquals(IndexedColors.RED.getIndex(), cs.getBottomBorderColor());
            assertEquals(IndexedColors.RED.getIndex(), cs.getLeftBorderColor());
            assertEquals(IndexedColors.RED.getIndex(), cs.getRightBorderColor());
        }
    }
    wb.close();
}