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

Example 11 with DataFormat

use of org.apache.poi.ss.usermodel.DataFormat in project hale by halestudio.

the class XLSLookupTableWriter method execute.

/**
 * @see eu.esdihumboldt.hale.common.core.io.impl.AbstractIOProvider#execute(eu.esdihumboldt.hale.common.core.io.ProgressIndicator,
 *      eu.esdihumboldt.hale.common.core.io.report.IOReporter)
 */
@Override
protected IOReport execute(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException {
    Workbook workbook;
    // write xls file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) {
        workbook = new HSSFWorkbook();
    } else // write xlsx file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) {
        workbook = new XSSFWorkbook();
    } else {
        reporter.error(new IOMessageImpl("Content type is invalid!", null));
        reporter.setSuccess(false);
        return reporter;
    }
    Sheet sheet = workbook.createSheet();
    workbook.setSheetName(0, "Lookup table");
    Row row = null;
    Cell cell = null;
    DataFormat df = workbook.createDataFormat();
    // create cell style of the header
    CellStyle headerStyle = workbook.createCellStyle();
    Font headerFont = workbook.createFont();
    // use bold font
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    // set a medium border
    headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    // set cell data format to text
    headerStyle.setDataFormat(df.getFormat("@"));
    // create cell style
    CellStyle rowStyle = workbook.createCellStyle();
    // set thin border around the cell
    rowStyle.setBorderBottom(CellStyle.BORDER_THIN);
    rowStyle.setBorderLeft(CellStyle.BORDER_THIN);
    rowStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    rowStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    rowStyle.setWrapText(true);
    Map<Value, Value> table = getLookupTable().getTable().asMap();
    int rownum = 0;
    // write header
    row = sheet.createRow(rownum++);
    cell = row.createCell(0);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_SOURCE_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);
    cell = row.createCell(1);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_TARGET_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);
    for (Value key : table.keySet()) {
        // create a row
        row = sheet.createRow(rownum);
        cell = row.createCell(0);
        cell.setCellValue(key.as(String.class));
        cell.setCellStyle(rowStyle);
        Value entry = table.get(key);
        cell = row.createCell(1);
        cell.setCellValue(entry.as(String.class));
        cell.setCellStyle(rowStyle);
        rownum++;
    }
    // write file
    FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath());
    workbook.write(out);
    out.close();
    reporter.setSuccess(true);
    return reporter;
}

Example 12 with DataFormat

use of org.apache.poi.ss.usermodel.DataFormat in project hale by halestudio.

the class XLSCellStyles method getHighlightedStyle.

/**
 * @param workbook the workbook of the cell
 * @param strikeOut true, if cell should be striked out
 * @return the highlighted cell style
 */
public static CellStyle getHighlightedStyle(Workbook workbook, boolean strikeOut) {
    // create highlight style for type cells
    CellStyle highlightStyle = workbook.createCellStyle();
    DataFormat df = workbook.createDataFormat();
    // set thin border around the cell
    highlightStyle.setBorderBottom(CellStyle.BORDER_THIN);
    highlightStyle.setBorderLeft(CellStyle.BORDER_THIN);
    highlightStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    highlightStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    highlightStyle.setWrapText(true);
    highlightStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    highlightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    if (strikeOut) {
        Font disabledTypeFont = workbook.createFont();
        disabledTypeFont.setStrikeout(true);
        disabledTypeFont.setColor(IndexedColors.BLACK.getIndex());
        highlightStyle.setFont(disabledTypeFont);
    }
    return highlightStyle;
}

Example 13 with DataFormat

use of org.apache.poi.ss.usermodel.DataFormat in project hale by halestudio.

the class XLSCellStyles method getNormalStyle.

/**
 * @param workbook the workbook of the cell
 * @param strikeOut true, if cell should be striked out
 * @return the normal cell style
 */
public static CellStyle getNormalStyle(Workbook workbook, boolean strikeOut) {
    // create cell style
    CellStyle cellStyle = workbook.createCellStyle();
    DataFormat df = workbook.createDataFormat();
    // set thin border around the cell
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    cellStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    cellStyle.setWrapText(true);
    if (strikeOut) {
        // strike out font
        Font disabledFont = workbook.createFont();
        disabledFont.setStrikeout(true);
        disabledFont.setColor(IndexedColors.GREY_40_PERCENT.getIndex());
        cellStyle.setFont(disabledFont);
    }
    return cellStyle;
}

Example 14 with DataFormat

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

the class TestXSSFDataFormat method test49928.

/**
     * [Bug 49928] formatCellValue returns incorrect value for £ formatted cells
     */
@Override
@Test
public void test49928() throws IOException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("49928.xlsx");
    doTest49928Core(wb);
    DataFormat dataFormat = wb.createDataFormat();
    // As of 2015-12-27, there is no way to override a built-in number format with POI XSSFWorkbook
    // 49928.xlsx has been saved with a poundFmt that overrides the default value (dollar)
    short poundFmtIdx = wb.getSheetAt(0).getRow(0).getCell(0).getCellStyle().getDataFormat();
    assertEquals(poundFmtIdx, dataFormat.getFormat(poundFmt));
    // now create a custom format with Pound (£)
    String customFmt = "£##.00[Yellow]";
    assertNotBuiltInFormat(customFmt);
    short customFmtIdx = dataFormat.getFormat(customFmt);
    assertTrue(customFmtIdx >= BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX);
    assertEquals(customFmt, dataFormat.getFormat(customFmtIdx));
    wb.close();
}

Example 15 with DataFormat

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

the class TestHSSFDataFormat method test49928.

/**
     * [Bug 49928] formatCellValue returns incorrect value for £ formatted cells
     */
@Override
@Test
public void test49928() throws IOException {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("49928.xls");
    doTest49928Core(wb);
    // an attempt to register an existing format returns its index
    int poundFmtIdx = wb.getSheetAt(0).getRow(0).getCell(0).getCellStyle().getDataFormat();
    assertEquals(poundFmtIdx, wb.createDataFormat().getFormat(poundFmt));
    // now create a custom format with Pound (£)
    DataFormat dataFormat = wb.createDataFormat();
    short customFmtIdx = dataFormat.getFormat("£##.00[Yellow]");
    assertTrue(customFmtIdx >= BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX);
    assertEquals("£##.00[Yellow]", dataFormat.getFormat(customFmtIdx));
    wb.close();
}