Example usage for org.apache.poi.ss.usermodel CellStyle setDataFormat

Introduction

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

Prototype

void setDataFormat(short fmt);

Source Link

Document

set the data format (must be a valid format).

Usage

From source file:org.kopsox.spreadsheet.data.excel.ExcelSheet.java

License:Open Source License

@Override
public void setValueAt(int row, int column, Time value, String format) {
    HSSFRow excelRow = sheet.getRow(row);
    if (excelRow == null) {
        excelRow = sheet.createRow(row);
    }//from   ww  w. j  a v a2  s.  c om

    HSSFCell excelCell = excelRow.getCell(column);
    if (excelCell == null) {
        excelCell = excelRow.createCell(column);
    }

    CreationHelper createHelper = this.sheet.getWorkbook().getCreationHelper();
    CellStyle cellStyle = this.sheet.getWorkbook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
    excelCell.setCellStyle(cellStyle);

    excelCell.setCellValue(value);
    excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
}

---

From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java

License:Open Source License

@Override
public void setValueAt(int row, int column, Date value, String format) {
    XSSFRow excelRow = sheet.getRow(row);
    if (excelRow == null) {
        excelRow = sheet.createRow(row);
    }//w w  w  .  j  a v  a2s  . c  o m

    XSSFCell excelCell = excelRow.getCell(column);
    if (excelCell == null) {
        excelCell = excelRow.createCell(column);
    }

    CreationHelper createHelper = this.sheet.getWorkbook().getCreationHelper();
    CellStyle cellStyle = this.sheet.getWorkbook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
    excelCell.setCellStyle(cellStyle);

    excelCell.setCellValue(value);
    excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
}

---

From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java

License:Open Source License

@Override
public void setValueAt(int row, int column, Time value, String format) {
    XSSFRow excelRow = sheet.getRow(row);
    if (excelRow == null) {
        excelRow = sheet.createRow(row);
    }/* ww  w  . j  a v  a2  s .c o  m*/

    XSSFCell excelCell = excelRow.getCell(column);
    if (excelCell == null) {
        excelCell = excelRow.createCell(column);
    }

    CreationHelper createHelper = this.sheet.getWorkbook().getCreationHelper();
    CellStyle cellStyle = this.sheet.getWorkbook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
    excelCell.setCellStyle(cellStyle);

    excelCell.setCellValue(value);
    excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
}

---

From source file:org.nuclos.server.report.export.ExcelExport.java

License:Open Source License

private NuclosFile export(Workbook wb, String sheetname, ResultVO result, List<ReportFieldDefinition> fields,
        String name) throws NuclosReportException {
    sheetname = sheetname != null ? sheetname
            : SpringLocaleDelegate.getInstance().getMessage("XLSExport.2", "Daten aus Nucleus");
    Sheet s = wb.getSheet(sheetname);//from w  w w  .java  2 s  .c o  m
    if (s == null) {
        s = wb.createSheet(sheetname);
    }

    int iRowNum = 0;
    int iColumnNum = 0;
    CreationHelper createHelper = wb.getCreationHelper();

    Row row = getRow(s, 0);

    Map<Integer, CellStyle> styles = new HashMap<Integer, CellStyle>();

    for (Iterator<ResultColumnVO> i = result.getColumns().iterator(); i.hasNext(); iColumnNum++) {
        i.next();
        Cell cell = getCell(row, iColumnNum);
        cell.setCellValue(fields.get(iColumnNum).getLabel());

        CellStyle style = wb.createCellStyle();
        String f = getFormat(fields.get(iColumnNum));
        if (f != null) {
            style.setDataFormat(createHelper.createDataFormat().getFormat(f));
        }
        styles.put(iColumnNum, style);
    }
    iRowNum++;

    // export data
    for (int i = 0; i < result.getRows().size(); i++, iRowNum++) {
        iColumnNum = 0;
        Object[] dataRow = result.getRows().get(i);
        row = getRow(s, iRowNum);
        for (int j = 0; j < result.getColumns().size(); j++, iColumnNum++) {
            Object value = dataRow[j];
            Cell c = getCell(row, iColumnNum);
            ReportFieldDefinition def = fields.get(j);

            if (value != null) {
                if (value instanceof List) {
                    final StringBuilder sb = new StringBuilder();
                    for (Iterator<?> it = ((List<?>) value).iterator(); it.hasNext();) {
                        final Object v = it.next();
                        sb.append(CollectableFieldFormat.getInstance(def.getJavaClass())
                                .format(def.getOutputformat(), v));
                        if (it.hasNext()) {
                            sb.append(", ");
                        }
                    }
                    c.setCellValue(sb.toString());
                } else {
                    if (Date.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Date) value);
                    } else if (Integer.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Integer) value);
                    } else if (Double.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Double) value);
                    } else {
                        c.setCellValue(String.valueOf(value));
                    }
                }
            } else {
                c.setCellValue("");
            }
        }
    }

    try {
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = wb.getSheetAt(sheetNum);
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
        }
    } catch (Exception e) {
    } // ignore any Exception

    ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
    try {
        wb.write(baos);
        return new NuclosFile(name + format.getExtension(), baos.toByteArray());
    } catch (IOException e) {
        throw new NuclosReportException(e);
    } finally {
        try {
            baos.close();
        } catch (IOException e) {
        }
    }
}

---

From source file:org.openelis.bean.DataViewReportBean.java

License:Open Source License

/**
 * Returns the CellStyle for a cell showing date and/or time in the format
 * specified by "pattern"; returns the style from "dtStyles" if it contains
 * that style; otherwise, first creates the style using "wb", puts it in
 * "dtStyles" and then returns it//from   w ww .  j a  v a  2s  .  com
 * 
 * @param wb
 *        the workbook that the cell showing date and/or time belongs to
 * @param dtStyles
 *        a map containing CellStyles for various date-time patterns
 * @param pattern
 *        the date-time pattern for a cell
 * @return the CellStyle pertaining to "pattern"
 */
private CellStyle getDatetimeStyle(XSSFWorkbook wb, HashMap<String, CellStyle> dtStyles, String pattern) {
    CellStyle style;

    style = dtStyles.get(pattern);
    if (style == null) {
        style = wb.createCellStyle();
        style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat(pattern));
        dtStyles.put(pattern, style);
    }

    return style;
}

---

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

private void createStyles(HSSFWorkbook wb) {
    CellStyle dateTimeEditStyle, dateTimeNoEditStyle, headerStyle, rowEditStyle, rowNoEditStyle;
    CreationHelper helper;//from   w w  w.j a v a2  s  .c o  m
    Font font;

    helper = wb.getCreationHelper();
    styles = new HashMap<String, CellStyle>();

    font = wb.createFont();
    font.setColor(IndexedColors.WHITE.getIndex());
    headerStyle = wb.createCellStyle();
    headerStyle.setAlignment(CellStyle.ALIGN_LEFT);
    headerStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
    headerStyle.setFont(font);
    headerStyle.setLocked(true);
    styles.put("header", headerStyle);

    rowEditStyle = wb.createCellStyle();
    rowEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    rowEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    rowEditStyle.setLocked(false);
    styles.put("row_edit", rowEditStyle);

    rowNoEditStyle = wb.createCellStyle();
    rowNoEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    rowNoEditStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    rowNoEditStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    rowNoEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    rowNoEditStyle.setLocked(true);
    styles.put("row_no_edit", rowNoEditStyle);

    dateTimeEditStyle = wb.createCellStyle();
    dateTimeEditStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd hh:mm"));
    dateTimeEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    dateTimeEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    dateTimeEditStyle.setLocked(false);
    styles.put("datetime_edit", dateTimeEditStyle);

    dateTimeNoEditStyle = wb.createCellStyle();
    dateTimeNoEditStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd hh:mm"));
    dateTimeNoEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    dateTimeNoEditStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    dateTimeNoEditStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    dateTimeNoEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    dateTimeNoEditStyle.setLocked(true);
    styles.put("datetime_no_edit", dateTimeNoEditStyle);
}

---

From source file:org.opentestsystem.delivery.testreg.rest.ExcelBasedTemplateCreator.java

License:Open Source License

private CellStyle getTextCellSytle(Workbook workbook) {
    DataFormat dataFormat = workbook.createDataFormat();
    CellStyle textStyle = workbook.createCellStyle();
    textStyle.setDataFormat(dataFormat.getFormat("@")); //@ is equivalent to Text
    textStyle.setHidden(false);/*ww w. j  av  a  2s.  c o m*/
    return textStyle;
}

---

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java

License:Apache License

/**
 * Set specified cell format/*  www.  j a v  a 2 s  . c  om*/
 *
 * @param excelFieldFormat the specified format
 * @param cell             the cell to set up format
 */
private void setDataFormat(String excelFieldFormat, Cell cell) {
    if (log.isDebug()) {
        logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.SetDataFormat", excelFieldFormat,
                CellReference.convertNumToColString(cell.getColumnIndex()), cell.getRowIndex()));
    }

    DataFormat format = data.wb.createDataFormat();
    short formatIndex = format.getFormat(excelFieldFormat);
    CellStyle style = data.wb.createCellStyle();
    style.cloneStyleFrom(cell.getCellStyle());
    style.setDataFormat(formatIndex);
    cell.setCellStyle(style);
}

---

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_StyleFormatTest.java

License:Apache License

/**
 * Setup the data necessary for Excel Writer step
 *
 * @param fileType//from ww w .  ja va  2  s.c  o m
 * @throws KettleException
 */
private void createStepData(String fileType) throws KettleException {
    stepData = new ExcelWriterStepData();
    stepData.inputRowMeta = step.getInputRowMeta().clone();
    stepData.outputRowMeta = step.getInputRowMeta().clone();

    // we don't run transformation so ExcelWriterStep.processRow() doesn't get executed
    // we populate the ExcelWriterStepData with bare minimum required values
    CellReference cellRef = new CellReference(stepMeta.getStartingCell());
    stepData.startingRow = cellRef.getRow();
    stepData.startingCol = cellRef.getCol();
    stepData.posX = stepData.startingCol;
    stepData.posY = stepData.startingRow;

    int numOfFields = stepData.inputRowMeta.size();
    stepData.fieldnrs = new int[numOfFields];
    stepData.linkfieldnrs = new int[numOfFields];
    stepData.commentfieldnrs = new int[numOfFields];
    for (int i = 0; i < numOfFields; i++) {
        stepData.fieldnrs[i] = i;
        stepData.linkfieldnrs[i] = -1;
        stepData.commentfieldnrs[i] = -1;
    }

    // we avoid reading/writing Excel files, so ExcelWriterStep.prepareNextOutputFile() doesn't get executed
    // create Excel workbook object
    stepData.wb = stepMeta.getExtension().equalsIgnoreCase("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook();
    stepData.sheet = stepData.wb.createSheet();
    stepData.file = null;
    stepData.clearStyleCache(numOfFields);

    // we avoid reading template file from disk
    // so set beforehand cells with custom style and formatting
    DataFormat format = stepData.wb.createDataFormat();
    Row xlsRow = stepData.sheet.createRow(0);

    // Cell F1 has custom style applied, used as template
    Cell cell = xlsRow.createCell(5);
    CellStyle cellStyle = stepData.wb.createCellStyle();
    cellStyle.setBorderRight(BorderStyle.THICK);
    cellStyle.setFillPattern(FillPatternType.FINE_DOTS);
    cell.setCellStyle(cellStyle);

    // Cell G1 has same style, but also a custom data format
    cellStyle = stepData.wb.createCellStyle();
    cellStyle.cloneStyleFrom(cell.getCellStyle());
    cell = xlsRow.createCell(6);
    cellStyle.setDataFormat(format.getFormat("##0,000.0"));
    cell.setCellStyle(cellStyle);
}

---

From source file:org.projectforge.excel.ExportCell.java

License:Open Source License

/**
 * Sets the data format of the poi cell. Use this method only if you modify existing cells of an existing workbook (loaded from file).
 * @param dataFormat//from  w ww. j av a 2 s .  c o  m
 * @return this for chaining.
 */
public ExportCell setDataFormat(final String dataFormat) {
    final CellStyle cellStyle = ensureAndGetCellStyle();
    final short df = styleProvider.getWorkbook().getDataFormat(dataFormat);
    cellStyle.setDataFormat(df);
    return this;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• »