Example usage for org.apache.poi.ss.usermodel CreationHelper createDataFormat

List of usage examples for org.apache.poi.ss.usermodel CreationHelper createDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CreationHelper
  6. createDataFormat

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel CreationHelper createDataFormat.

Prototype

DataFormat createDataFormat();

Source Link

Document

Creates a new DataFormat instance

Usage

From source file:com.helger.poi.excel.style.ExcelStyle.java

License:Apache License

public void fillCellStyle(@Nonnull final Workbook aWB, @Nonnull final CellStyle aCS,
        @Nonnull final CreationHelper aCreationHelper) {
    if (m_eAlign != null)
        aCS.setAlignment(m_eAlign.getValue());
    if (m_eVAlign != null)
        aCS.setVerticalAlignment(m_eVAlign.getValue());
    aCS.setWrapText(m_bWrapText);//from www  .ja va 2 s . c om
    if (m_sDataFormat != null)
        aCS.setDataFormat(aCreationHelper.createDataFormat().getFormat(m_sDataFormat));
    if (m_eFillBackgroundColor != null)
        aCS.setFillBackgroundColor(m_eFillBackgroundColor.getIndex());
    if (m_eFillForegroundColor != null)
        aCS.setFillForegroundColor(m_eFillForegroundColor.getIndex());
    if (m_eFillPattern != null)
        aCS.setFillPattern(m_eFillPattern.getValue());
    if (m_eBorderTop != null)
        aCS.setBorderTop(m_eBorderTop.getValue());
    if (m_eBorderRight != null)
        aCS.setBorderRight(m_eBorderRight.getValue());
    if (m_eBorderBottom != null)
        aCS.setBorderBottom(m_eBorderBottom.getValue());
    if (m_eBorderLeft != null)
        aCS.setBorderLeft(m_eBorderLeft.getValue());
    if (m_nFontIndex >= 0)
        aCS.setFont(aWB.getFontAt(m_nFontIndex));
}

From source file:com.ipcglobal.fredimport.xls.BaseXls.java

License:Apache License

/**
 * Instantiates a new base xls./* w  ww . j  av a  2  s  .  c  o  m*/
 */
public BaseXls() {
    this.wb = new XSSFWorkbook();
    CreationHelper creationHelper = wb.getCreationHelper();
    this.formatGeneral = creationHelper.createDataFormat().getFormat("General");
    this.formatNumeric = creationHelper.createDataFormat().getFormat("#,##0");
    this.formatNumericDec2 = creationHelper.createDataFormat().getFormat("#,##0.00");
    this.formatMmDdYyyy = creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss");
    this.formatAccounting = creationHelper.createDataFormat()
            .getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)");
    this.formatPercent = creationHelper.createDataFormat().getFormat("0.00%");

}

From source file:com.netxforge.netxstudio.server.logic.reporting.ResourceReportingEngine.java

License:Open Source License

public void writeRange(List<Marker> markers, Sheet sheet, Row valueRow, List<Value> range) {

    CreationHelper createHelper = this.getWorkBook().getCreationHelper();
    CellStyle cellStyle = this.getWorkBook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m-d-yy h:mm"));

    // Styles for markers.
    // CellStyle markerStyleRed = this.getWorkBook().createCellStyle();
    // markerStyleRed.setFillForegroundColor(IndexedColors.RED.getIndex());
    // markerStyleRed.setFillPattern(CellStyle.SOLID_FOREGROUND);

    CellStyle markerStyleRed = createRedBorderStyle();
    CellStyle markerStyleAmber = createAmberBorderStyle();

    // CellStyle markerStyleAmber = this.getWorkBook().createCellStyle();
    // markerStyleAmber.setFillPattern(CellStyle.SOLID_FOREGROUND);
    // markerStyleAmber
    // .setFillForegroundColor(IndexedColors.ORANGE.getIndex());

    // Write the values.
    for (Value v : range) {

        // lookup the value.
        int valueIndex = tsColumnForValue(v);
        if (valueIndex == -1) {
            continue;
        }//from   w  ww  .  j av  a 2s.co m

        Cell valueCell = valueRow.createCell(valueIndex);
        valueCell.setCellValue(v.getValue());

        // Adapt the width of the column for this value.
        sheet.setColumnWidth(valueIndex, 14 * 256);

        // Set the markers.
        if (markers != null) {
            Marker m;
            if ((m = StudioUtils.markerForValue(markers, v)) != null) {
                if (m instanceof ToleranceMarker) {
                    switch (((ToleranceMarker) m).getLevel().getValue()) {
                    case LevelKind.RED_VALUE: {
                        valueCell.setCellStyle(markerStyleRed);
                    }
                        break;
                    case LevelKind.AMBER_VALUE: {
                        valueCell.setCellStyle(markerStyleAmber);
                    }
                        break;
                    }
                }
            }
        }
    }
}

From source file:com.netxforge.netxstudio.server.logic.reporting.ResourceReportingEngine.java

License:Open Source License

/**
 * Has a side effect of populating the columnTSMap with the index and date.
 * //from   w ww  . j  a v a  2  s.co m
 */
public int writeTS(Map<Integer, Date> columnTSMap, Sheet sheet, Row tsRow, List<Date> range, int weekNumber,
        int columnIndex) {

    CreationHelper createHelper = this.getWorkBook().getCreationHelper();
    CellStyle dateStyle = this.getWorkBook().createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("m-d-yy h:mm"));

    // Write the values.
    int valueIndex = columnIndex;
    for (Date d : range) {
        Cell tsCell = tsRow.createCell(valueIndex);
        // sheet.setColumnWidth(valueIndex, 14 * 256);
        tsCell.setCellValue(d);
        tsCell.setCellStyle(dateStyle);
        valueIndex++;
        columnTS.put(valueIndex, d);
    }
    return valueIndex;
}

From source file:com.rapidminer.operator.io.ExcelExampleSetWriter.java

License:Open Source License

/**
 * Writes the provided {@link ExampleSet} to a XLSX formatted data sheet.
 *
 * @param wb//from  www. j av a 2s.c  o m
 *            the workbook to use
 * @param sheet
 *            the excel sheet to write to.
 * @param dateFormat
 *            a string which describes the format used for dates.
 * @param numberFormat
 *            a string which describes the format used for numbers.
 * @param exampleSet
 *            the exampleSet to write
 * @param op
 *            needed for checkForStop
 * @throws ProcessStoppedException
 *             if the process was stopped by the user.
 * @throws WriteException
 */
private static void writeXLSXDataSheet(SXSSFWorkbook wb, Sheet sheet, String dateFormat, String numberFormat,
        ExampleSet exampleSet, Operator op) throws WriteException, ProcessStoppedException {

    Font headerFont = wb.createFont();
    headerFont.setBold(true);

    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFont(headerFont);

    // create the header
    Iterator<Attribute> a = exampleSet.getAttributes().allAttributes();
    int columnCounter = 0;
    int rowCounter = 0;
    Row headerRow = sheet.createRow(rowCounter);
    while (a.hasNext()) {
        Attribute attribute = a.next();
        Cell headerCell = headerRow.createCell(columnCounter);
        headerCell.setCellValue(attribute.getName());
        headerCell.setCellStyle(headerStyle);
        columnCounter++;
    }
    rowCounter++;

    // body font
    Font bodyFont = wb.createFont();
    bodyFont.setBold(false);

    CreationHelper createHelper = wb.getCreationHelper();

    // number format
    CellStyle numericalStyle = wb.createCellStyle();
    numericalStyle.setDataFormat(createHelper.createDataFormat().getFormat(numberFormat));
    numericalStyle.setFont(bodyFont);

    // date format
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat(dateFormat));
    dateStyle.setFont(bodyFont);

    // create nominal cell style
    CellStyle nominalStyle = wb.createCellStyle();
    nominalStyle.setFont(bodyFont);

    // fill body
    for (Example example : exampleSet) {

        // create new row
        Row bodyRow = sheet.createRow(rowCounter);

        // iterate over attributes and save examples
        a = exampleSet.getAttributes().allAttributes();
        columnCounter = 0;
        while (a.hasNext()) {
            Attribute attribute = a.next();
            Cell currentCell = bodyRow.createCell(columnCounter);
            if (!Double.isNaN(example.getValue(attribute))) {
                if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.DATE_TIME)) {
                    Date dateValue = example.getDateValue(attribute);
                    currentCell.setCellValue(dateValue);
                    currentCell.setCellStyle(dateStyle);
                } else if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.NUMERICAL)) {
                    double numericalValue = example.getNumericalValue(attribute);
                    currentCell.setCellValue(numericalValue);
                    currentCell.setCellStyle(numericalStyle);
                } else {
                    currentCell.setCellValue(
                            stripIfNecessary(replaceForbiddenChars(example.getValueAsString(attribute))));
                    currentCell.setCellStyle(nominalStyle);
                }
            }
            columnCounter++;
        }
        rowCounter++;

        // checkForStop every 100 examples
        if (op != null && rowCounter % 100 == 0) {
            op.checkForStop();
        }
    }
}

From source file:com.rdg.export.util.ExportXLS.java

License:Apache License

/**
 * create cell style for header names/* w  w w .ja  va 2  s .com*/
 *
 * @return
 */
private static XSSFCellStyle getCellStyleHeader() {
    XSSFCellStyle cellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(DateUtil.sDateFormat_1));
    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyle.setFont(headerFont);
    return cellStyle;
}

From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadsheetCell.java

/**
 * Get cell format<p>//w  w w  .  ja v a  2 s . c  o m
 * 
 * Thanks to http://stackoverflow.com/questions/15248284/using-poi-how-to-set-the-cell-type-as-number
 * @param wb
 * @return
 */
public HSSFCellStyle getCellStyle(HSSFWorkbook wb) {

    //EX1
    if (sheet.containsStyleId(styleId)) {
        return sheet.getStyle(styleId);
    }

    HSSFCellStyle style = wb.createCellStyle();
    Integer clazzX = clazz != null ? clazz : (headerCell != null ? headerCell.clazz : null);

    switch (clazzX != null ? clazzX : CLASS_STRING) {
    case CLASS_DATE:
        if (!isHeader()) {
            CreationHelper createHelper = wb.getCreationHelper();
            style = wb.createCellStyle();
            style.setDataFormat(createHelper.createDataFormat()
                    .getFormat(sheet.getDateFormat() != null ? sheet.getDateFormat() : "m/d/yy"));
        }
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_PERCENTAGE:
        style = wb.createCellStyle();
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_INTEGER:
    case CLASS_LONG:
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        break;

    case CLASS_DOUBLE:
    case CLASS_UKURS:
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        break;

    case CLASS_STRING:
    case CLASS_INTEGER_LEFT:
    case CLASS_BOOLEAN:
    case CLASS_CHARACTER:
    default:
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    }

    sheet.setCellStyle(wb, style, this, styleId);
    return style;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.BitacoraReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();/*w  w  w  .j  a va2 s .  c  o  m*/
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Row row = getRow(sheet, initialRow);
    Cell cell = row.createCell(initialColumn);
    cell.setCellValue("Bitacora");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);

    for (int i = 1; i < 4; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }

    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 3 //last column  (0-based)
    ));

    CreationHelper createHelper = wb.getCreationHelper();
    for (EventoRC x : datos.getBitacora()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        for (int i = 0; i < atributos.length; i++) {
            cell = row.createCell(initialColumn + i);
            cellStyle = wb.createCellStyle();
            try {
                if (atributos[i].equals("fecha")) {
                    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yy/m/d h:mm:ss"));
                    cell.setCellValue((Date) PropertyUtils.getProperty(x, atributos[i]));
                } else {
                    cell.setCellValue(PropertyUtils.getProperty(x, atributos[i]).toString());
                }
            } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException ex) {
                cell.setCellValue("");
            }
            addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
            cell.setCellStyle(cellStyle);
        }
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 3);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, r);
    return r;
}

From source file:com.webbfontaine.valuewebb.report.utils.cellStyles.CellStyleUtils.java

License:Open Source License

public static CellStyle cellStyle(Workbook workBook, CellType cellType) {
    CellStyle cellStyle = workBook.createCellStyle();

    if (!Color.NO_COLOR.equals(cellType.getColor())) {
        Font font = workBook.createFont();
        font.setFontName("Arial");
        font.setColor(cellType.getColor().index());
        cellStyle.setFont(font);/*  ww w  .  j  ava2 s.c o  m*/
    }

    if (!Alignment.NO_ALIGNMENT.equals(cellType.getHorizontalAlignment())) {
        cellStyle.setAlignment(cellType.getHorizontalAlignment().index());
    }

    if (!Alignment.NO_ALIGNMENT.equals(cellType.getVerticalAlignment())) {
        cellStyle.setVerticalAlignment(cellType.getVerticalAlignment().index());
    }

    if (!Format.NO_FORMAT.equals(cellType.getFormat())) {
        CreationHelper creationHelper = workBook.getCreationHelper();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(cellType.getFormat().text()));
    }

    setCellBorders(cellStyle);
    return cellStyle;
}

From source file:csv.impl.DefaultExcelFormatter.java

License:Open Source License

/**
 * Returns the ID of the format or creates a new one if required.
 * @param writer writer that provides the workbook
 * @param format format to be used/*from   w w  w .  j av  a 2s. c o m*/
 * @return ID of format
 */
protected Short getDateFormat(ExcelWriter writer, String format) {
    Short rc = dateFormat.get(format);
    if (rc == null) {
        CreationHelper createHelper = writer.getWorkbook().getCreationHelper();
        rc = createHelper.createDataFormat().getFormat(format);
        dateFormat.put(format, rc);
    }
    return rc;
}