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

Example 71 with Workbook

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

use of org.apache.poi.ss.usermodel.Workbook 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();
}

Example 73 with Workbook

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

the class TestPropertyTemplate method applyBorders.

@Test
public void applyBorders() throws IOException {
    CellRangeAddress a1c3 = new CellRangeAddress(0, 2, 0, 2);
    CellRangeAddress b2 = new CellRangeAddress(1, 1, 1, 1);
    PropertyTemplate pt = new PropertyTemplate();
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    pt.drawBorders(a1c3, BorderStyle.THIN, IndexedColors.RED.getIndex(), BorderExtent.ALL);
    pt.applyBorders(sheet);
    for (Row row : sheet) {
        for (Cell cell : row) {
            CellStyle cs = cell.getCellStyle();
            assertEquals(BorderStyle.THIN, cs.getBorderTopEnum());
            assertEquals(IndexedColors.RED.getIndex(), cs.getTopBorderColor());
            assertEquals(BorderStyle.THIN, cs.getBorderBottomEnum());
            assertEquals(IndexedColors.RED.getIndex(), cs.getBottomBorderColor());
            assertEquals(BorderStyle.THIN, cs.getBorderLeftEnum());
            assertEquals(IndexedColors.RED.getIndex(), cs.getLeftBorderColor());
            assertEquals(BorderStyle.THIN, cs.getBorderRightEnum());
            assertEquals(IndexedColors.RED.getIndex(), cs.getRightBorderColor());
        }
    }
    pt.drawBorders(b2, BorderStyle.NONE, BorderExtent.ALL);
    pt.applyBorders(sheet);
    for (Row row : sheet) {
        for (Cell cell : row) {
            CellStyle cs = cell.getCellStyle();
            if (cell.getColumnIndex() != 1 || row.getRowNum() == 0) {
                assertEquals(BorderStyle.THIN, cs.getBorderTopEnum());
                assertEquals(IndexedColors.RED.getIndex(), cs.getTopBorderColor());
            } else {
                assertEquals(BorderStyle.NONE, cs.getBorderTopEnum());
            }
            if (cell.getColumnIndex() != 1 || row.getRowNum() == 2) {
                assertEquals(BorderStyle.THIN, cs.getBorderBottomEnum());
                assertEquals(IndexedColors.RED.getIndex(), cs.getBottomBorderColor());
            } else {
                assertEquals(BorderStyle.NONE, cs.getBorderBottomEnum());
            }
            if (cell.getColumnIndex() == 0 || row.getRowNum() != 1) {
                assertEquals(BorderStyle.THIN, cs.getBorderLeftEnum());
                assertEquals(IndexedColors.RED.getIndex(), cs.getLeftBorderColor());
            } else {
                assertEquals(BorderStyle.NONE, cs.getBorderLeftEnum());
            }
            if (cell.getColumnIndex() == 2 || row.getRowNum() != 1) {
                assertEquals(BorderStyle.THIN, cs.getBorderRightEnum());
                assertEquals(IndexedColors.RED.getIndex(), cs.getRightBorderColor());
            } else {
                assertEquals(BorderStyle.NONE, cs.getBorderRightEnum());
            }
        }
    }
    wb.close();
}

Example 74 with Workbook

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

the class TestSheetBuilder method testNotCreateEmptyCells.

public void testNotCreateEmptyCells() {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = new SheetBuilder(wb, testData).build();
    assertEquals(sheet.getPhysicalNumberOfRows(), 3);
    Row firstRow = sheet.getRow(0);
    Cell firstCell = firstRow.getCell(0);
    assertEquals(firstCell.getCellTypeEnum(), CellType.NUMERIC);
    assertEquals(1.0, firstCell.getNumericCellValue(), 0.00001);
    Row secondRow = sheet.getRow(1);
    assertNotNull(secondRow.getCell(0));
    assertNull(secondRow.getCell(2));
    Row thirdRow = sheet.getRow(2);
    assertEquals(CellType.STRING, thirdRow.getCell(0).getCellTypeEnum());
    String cellValue = thirdRow.getCell(0).getStringCellValue();
    assertEquals(testData[2][0].toString(), cellValue);
    assertEquals(CellType.FORMULA, thirdRow.getCell(2).getCellTypeEnum());
    assertEquals("A1+B2", thirdRow.getCell(2).getCellFormula());
}

Example 75 with Workbook

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

the class TestSheetUtil method testGetCellWidthNumber.

public void testGetCellWidthNumber() throws IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("sheet");
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue(88.234);
    assertTrue(SheetUtil.getCellWidth(cell, 1, null, true) > 0);
    wb.close();
}