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

List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. 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:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void formatCell(Workbook workbook, Cell cell, ExcelCell excell,
        Map<IndexedColors, CellStyle> s_cellStyle, Font font, Font invisibleFont) {

    if (excell.getFormat() != null) {

        ExcelFormat format = excell.getFormat();

        CellStyle style = s_cellStyle.get(format.getBackgroundColor());

        if (format.isDate()) {
            // for date create a new style
            style = getDateStyle("date", cell.getSheet(), font);
            XSSFCreationHelper createHelper = (XSSFCreationHelper) cell.getSheet().getWorkbook()
                    .getCreationHelper();
            style.setDataFormat(createHelper.createDataFormat().getFormat("MMMM dd, yyyy"));
            font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            font.setBold(false);/*from  w  ww.  j  a v  a  2 s. c  o m*/
            font.setFontHeightInPoints((short) 12);
            style.setFont(font);
            cell.setCellValue(new Date());
        }

        if (style == null) {
            style = workbook.createCellStyle();
            s_cellStyle.put(format.getBackgroundColor(), style);
        }

        if (format.getAlignment() > 0) {
            style.setAlignment(format.getAlignment());
        }
        if (format.getBackgroundColor() != null && !IndexedColors.WHITE.equals(format.getBackgroundColor())) {
            style.setFillForegroundColor(format.getBackgroundColor().getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        if (format.getTextColor() != null) {
            font.setColor(format.getTextColor().getIndex());
            style.setFont(font);
        }
        if (format.isBold()) {
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        if (format.getFontHeight() > 0) {
            font.setFontHeightInPoints(format.getFontHeight());
        }
        if (format.isWrapText()) {
            style.setWrapText(true);
        }
        style.setFont(font);
        if (format.isHideText()) {
            invisibleFont.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(invisibleFont);
        }
        cell.setCellStyle(style);

    } else {
        // Let's set default formatting for free text cell
        IndexedColors defaultStyle = IndexedColors.AUTOMATIC; // we are using this index
        CellStyle style = s_cellStyle.get(defaultStyle);
        if (style == null) {
            style = workbook.createCellStyle();
            s_cellStyle.put(defaultStyle, style);
        }
        style.setWrapText(true);
        cell.setCellStyle(style);

    }

}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void setCellValueFeeFormat(Workbook wb, Cell cell, Object oneCellValue, String vendor) {
    CellStyle style = wb.createCellStyle();
    style.setDataFormat(wb.createDataFormat().getFormat("$#,#0.00"));
    cell.setCellStyle(style);//from  w  w w  .ja  va 2  s  .  co  m

    if (oneCellValue == null) {
        cell.setCellValue(Double.parseDouble("0.0"));
        return;
    }

    String feeStr = StringUtils.replace(oneCellValue.toString(), "$", StringUtils.EMPTY);
    feeStr = StringUtils.trimToEmpty(feeStr);
    feeStr = feeStr.replaceAll("\\p{javaSpaceChar}", StringUtils.EMPTY);
    if (StringUtils.isEmpty(feeStr)) {
        cell.setCellValue(Double.parseDouble("0.0"));
        return;
    }

    if (StringUtils.contains(vendor, TOLL_COMPANY_EZ_PASS_PA)
            || StringUtils.contains(vendor, TOLL_COMPANY_IPASS)
            || StringUtils.contains(vendor, TOLL_COMPANY_SUN_PASS)) {
        if (StringUtils.startsWith(feeStr, "-")) {
            feeStr = StringUtils.substring(feeStr, 1);
        } else {
            feeStr = "-" + feeStr;
        }
    }

    cell.setCellValue(Double.parseDouble(feeStr));
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void setCellValueDateFormat(Workbook wb, Cell cell, Object oneCellValue, String vendor)
        throws ParseException {
    System.out.println("Incoming vendor = " + vendor);
    String tollCompanyDateFormat = tollCompanyToDateFormatMapping.get(vendor);
    System.out.println("Value = " + tollCompanyDateFormat);

    int columnIndex = cell.getColumnIndex();

    if (oneCellValue instanceof Date) {
        System.out.println("Incoming date is a Date Object.");
        tollCompanyDateFormat = "EEE MMM dd HH:mm:ss z yyyy";
    }/*from w  w w .  j a  va  2 s  .c om*/

    String dateStr = StringUtils.trimToEmpty(oneCellValue.toString());

    if (StringUtils.isEmpty(dateStr)) {
        cell.setCellValue(StringUtils.EMPTY);
    } else {
        if (columnIndex == 7) { // Transaction time
            cell.setCellValue(convertToExpectedTimeFormatStr(dateStr, tollCompanyDateFormat));
            return;
        } else {
            cell.setCellValue(convertToExpectedDateFormat(dateStr, tollCompanyDateFormat));
        }
    }

    CellStyle style = wb.createCellStyle();
    style.setDataFormat(wb.createDataFormat().getFormat(expectedDateFormat.toPattern()));
    cell.setCellStyle(style);
}

From source file:com.quanticate.opensource.datalistdownload.DataListDownloadWebScript.java

License:Open Source License

@Override
protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties)
        throws IOException {
    NodeRef list = (NodeRef) resource;//w  ww  .  j av  a 2 s .c om
    List<NodeRef> items = getItems(list);

    // Our various formats
    DataFormat formatter = workbook.createDataFormat();

    CellStyle styleInt = workbook.createCellStyle();
    styleInt.setDataFormat(formatter.getFormat("0"));
    CellStyle styleDate = workbook.createCellStyle();
    styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd"));
    CellStyle styleDouble = workbook.createCellStyle();
    styleDouble.setDataFormat(formatter.getFormat("General"));
    CellStyle styleNewLines = workbook.createCellStyle();
    styleNewLines.setWrapText(true);

    // Export the items
    int rowNum = 1, colNum = 0;
    for (NodeRef item : items) {
        Row r = sheet.createRow(rowNum);

        colNum = 0;
        for (QName prop : properties) {
            Cell c = r.createCell(colNum);

            Pair<Object, String> valAndLink = identifyValueAndLink(item, prop);

            if (valAndLink == null) {
                // This property isn't set
                c.setCellType(Cell.CELL_TYPE_BLANK);
            } else {
                Object val = valAndLink.getFirst();

                // Multi-line property?
                if (val instanceof String[]) {
                    String[] lines = (String[]) val;
                    StringBuffer text = new StringBuffer();

                    for (String line : lines) {
                        if (text.length() > 0) {
                            text.append('\n');
                        }
                        text.append(line);
                    }

                    String v = text.toString();
                    c.setCellValue(v);
                    if (lines.length > 1) {
                        c.setCellStyle(styleNewLines);
                        r.setHeightInPoints(lines.length * sheet.getDefaultRowHeightInPoints());
                    }
                }

                // Regular properties
                else if (val instanceof String) {
                    c.setCellValue((String) val);
                } else if (val instanceof Date) {
                    c.setCellValue((Date) val);
                    c.setCellStyle(styleDate);
                } else if (val instanceof Integer || val instanceof Long) {
                    double v = 0.0;
                    if (val instanceof Long)
                        v = (double) (Long) val;
                    if (val instanceof Integer)
                        v = (double) (Integer) val;
                    c.setCellValue(v);
                    c.setCellStyle(styleInt);
                } else if (val instanceof Float || val instanceof Double) {
                    double v = 0.0;
                    if (val instanceof Float)
                        v = (double) (Float) val;
                    if (val instanceof Double)
                        v = (double) (Double) val;
                    c.setCellValue(v);
                    c.setCellStyle(styleDouble);
                } else {
                    // TODO
                    System.err
                            .println("TODO: Handle Excel output of " + val.getClass().getName() + " - " + val);
                }
            }

            colNum++;
        }

        rowNum++;
    }

    // Sensible column widths please!
    colNum = 0;
    for (QName prop : properties) {
        sheet.autoSizeColumn(colNum);
        colNum++;
    }
}

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  ww w .  ja  v  a2  s .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.sccl.attech.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?// ww  w. j a va2 s . c  o  m
 * @param row 
 * @param column ?
 * @param val 
 * @param align ??1?23??
 * @return ?
 */
public Cell addCellStyle(Row row, int column, Object val, int align, Class<?> fieldType) {
    Cell cell = row.createCell(column);
    CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    style.setWrapText(true);
    try {
        if (val == null) {
            cell.setCellValue("");
        } else if (val instanceof String) {
            cell.setCellValue((String) val);
        } else if (val instanceof Integer) {
            cell.setCellValue((Integer) val);
        } else if (val instanceof Long) {
            cell.setCellValue((Long) val);
        } else if (val instanceof Double) {
            cell.setCellValue((Double) val);
        } else if (val instanceof Float) {
            cell.setCellValue((Float) val);
        } else if (val instanceof Date) {
            DataFormat format = wb.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellValue((Date) val);
        } else {
            if (fieldType != Class.class) {
                cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
            } else {
                cell.setCellValue((String) Class
                        .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                "fieldtype." + val.getClass().getSimpleName() + "Type"))
                        .getMethod("setValue", Object.class).invoke(null, val));
            }
        }
    } catch (Exception ex) {
        log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
        cell.setCellValue(val.toString());
    }
    if (column == 8) {
        CellRangeAddressList regions = new CellRangeAddressList(25, 25, 8, 8);
        // ?  
        DVConstraint constraint = DVConstraint
                .createExplicitListConstraint(new String[] { "2", "3", "" });
        //   
        HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint);
        // sheet  
        data_validation.createErrorBox("Error", "Error");
        data_validation.createPromptBox("", null);
        sheet.addValidationData(data_validation);
    }
    cell.setCellStyle(style);
    return cell;
}

From source file:com.squid.core.poi.ExcelWriter.java

License:Open Source License

private CellStyle getStyle(String format) {
    if (!styles.containsKey(format)) {
        CellStyle style = wb.createCellStyle();
        style.setDataFormat(dataFormat.getFormat(format));
        styles.put(format, style);// ww w .ja va  2 s. co m
    }
    return styles.get(format);
}

From source file:com.toolsverse.etl.connector.excel.ExcelConnector.java

License:Open Source License

@SuppressWarnings("resource")
public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception {
    String fileName = null;/*from ww w  .  jav  a  2 s . c  om*/

    OutputStream out = null;

    if (params.getOutputStream() == null) {
        fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName(
                dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xls", true));

        params.setRealFileName(fileName);

        out = new FileOutputStream(fileName);

        if (params.getTransactionMonitor() != null)
            params.getTransactionMonitor().addFile(fileName);
    } else
        out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new HSSFWorkbook();

    params.setWorkbook(workbook);

    Sheet sheet = workbook
            .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
        if (!fieldDef.isVisible())
            continue;

        Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING);
        labelCell.setCellStyle(labelCellStyle);
        labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
}

From source file:com.toolsverse.etl.connector.excel.ExcelXlsxConnector.java

License:Open Source License

@SuppressWarnings("resource")
public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception {
    String fileName = null;/*from w  ww. ja v a2  s. co  m*/

    OutputStream out = null;

    if (params.getOutputStream() == null) {
        fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName(
                dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xlsx", true));

        params.setRealFileName(fileName);

        out = new FileOutputStream(fileName);

        if (params.getTransactionMonitor() != null)
            params.getTransactionMonitor().addFile(fileName);
    } else
        out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new SXSSFWorkbook(100);

    params.setWorkbook(workbook);

    Sheet sheet = workbook
            .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
        if (!fieldDef.isVisible())
            continue;

        Cell labelCell = excelRow.createCell(col++);
        labelCell.setCellStyle(labelCellStyle);
        labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
}

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

/**
 * Updates the cell value and type, causes a recalculation of all the values
 * in the cell./*from   w w w.  java  2 s .  com*/
 *
 * If there is a {@link CellValueHandler} defined, then it is used.
 *
 * Cells starting with "=" or "+" will be created/changed into FORMULA type.
 *
 * Cells that are existing and are NUMERIC type will be parsed according to
 * their existing format, or if that fails, as Double.
 *
 * Cells not containing any letters and containing at least one number will
 * be created/changed into NUMERIC type (formatting is not changed).
 *
 * Existing Boolean cells will be parsed as Boolean.
 *
 * For everything else and if any of the above fail, the cell will get the
 * STRING type and the value will just be a string, except empty values will
 * cause the cell type to be BLANK.
 *
 * @param col
 *            Column index of target cell, 1-based
 * @param row
 *            Row index of target cell, 1-based
 * @param value
 *            The new value to set to the target cell, formulas will start
 *            with an extra "=" or "+"
 */
public void onCellValueChange(int col, int row, String value) {
    Workbook workbook = spreadsheet.getWorkbook();
    // update cell value
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Row r = activeSheet.getRow(row - 1);
    if (r == null) {
        r = activeSheet.createRow(row - 1);
    }
    Cell cell = r.getCell(col - 1);
    String formattedCellValue = null;
    int oldCellType = -1;
    // capture cell value to history
    CellValueCommand command = new CellValueCommand(spreadsheet);
    command.captureCellValues(new CellReference(row - 1, col - 1));
    spreadsheet.getSpreadsheetHistoryManager().addCommand(command);
    boolean updateHyperlinks = false;

    if (getCustomCellValueHandler() == null || getCustomCellValueHandler().cellValueUpdated(cell, activeSheet,
            col - 1, row - 1, value, getFormulaEvaluator(), formatter)) {
        Exception exception = null;
        try {
            // handle new cell creation
            SpreadsheetStyleFactory styler = spreadsheet.getSpreadsheetStyleFactory();
            final Locale spreadsheetLocale = spreadsheet.getLocale();
            if (cell == null) {
                cell = r.createCell(col - 1);
            } else {
                // modify existing cell, possibly switch type
                formattedCellValue = getFormattedCellValue(cell);
                final String key = SpreadsheetUtil.toKey(col, row);
                oldCellType = cell.getCellType();
                if (!sentCells.remove(key)) {
                    sentFormulaCells.remove(key);
                }

                // Old value was hyperlink => needs refresh
                if (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                        && cell.getCellFormula().startsWith("HYPERLINK")) {
                    updateHyperlinks = true;
                }
            }
            if (formulaFormatter.isFormulaFormat(value)) {
                if (formulaFormatter.isValidFormulaFormat(value, spreadsheetLocale)) {
                    spreadsheet.removeInvalidFormulaMark(col, row);
                    getFormulaEvaluator().notifyUpdateCell(cell);
                    cell.setCellType(Cell.CELL_TYPE_FORMULA);
                    cell.setCellFormula(
                            formulaFormatter.unFormatFormulaValue(value.substring(1), spreadsheetLocale));
                    getFormulaEvaluator().notifySetFormula(cell);
                    if (value.startsWith("=HYPERLINK(")
                            && cell.getCellStyle().getIndex() != hyperlinkStyleIndex) {
                        // set the cell style to link cell
                        CellStyle hyperlinkCellStyle;
                        if (hyperlinkStyleIndex == -1) {
                            hyperlinkCellStyle = styler.createHyperlinkCellStyle();
                            hyperlinkStyleIndex = -1;
                        } else {
                            hyperlinkCellStyle = workbook.getCellStyleAt(hyperlinkStyleIndex);
                        }
                        cell.setCellStyle(hyperlinkCellStyle);
                        styler.cellStyleUpdated(cell, true);
                        updateHyperlinks = true;
                    }
                } else {
                    // it's formula but invalid
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(value);
                    spreadsheet.markInvalidFormula(col, row);
                }
            } else {
                spreadsheet.removeInvalidFormulaMark(col, row);
                Double percentage = SpreadsheetUtil.parsePercentage(value, spreadsheetLocale);
                Double numVal = SpreadsheetUtil.parseNumber(cell, value, spreadsheetLocale);
                if (value.isEmpty()) {
                    cell.setCellType(Cell.CELL_TYPE_BLANK);
                } else if (percentage != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    CellStyle cs = cell.getCellStyle();
                    if (cs == null) {
                        cs = workbook.createCellStyle();
                        cell.setCellStyle(cs);
                    }

                    if (cs.getDataFormatString() != null && !cs.getDataFormatString().contains("%")) {
                        cs.setDataFormat(workbook.createDataFormat()
                                .getFormat(spreadsheet.getDefaultPercentageFormat()));
                        styler.cellStyleUpdated(cell, true);
                    }
                    cell.setCellValue(percentage);
                } else if (numVal != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(numVal);
                } else if (oldCellType == Cell.CELL_TYPE_BOOLEAN) {
                    cell.setCellValue(Boolean.parseBoolean(value));
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(value);
                }
                getFormulaEvaluator().notifyUpdateCell(cell);
            }

        } catch (FormulaParseException fpe) {
            try {
                exception = fpe;

                // parses formula
                cell.setCellFormula(value.substring(1).replace(" ", ""));
            } catch (FormulaParseException fpe2) {
                exception = fpe2;
                /*
                 * We could force storing the formula even if it is invalid.
                 * Instead, just store it as the value. Clearing the formula
                 * makes sure the value is displayed as-is.
                 */
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(value);
                spreadsheet.markInvalidFormula(col, row);
            }
        } catch (NumberFormatException nfe) {
            exception = nfe;
            cell.setCellValue(value);
        } catch (Exception e) {
            exception = e;
            cell.setCellValue(value);
        }
        if (cell != null) {
            markCellForUpdate(cell);
            if (formattedCellValue == null || !formattedCellValue.equals(getFormattedCellValue(cell))
                    || oldCellType != cell.getCellType()) {
                fireCellValueChangeEvent(cell);
            }
        }
        if (exception != null) {
            LOGGER.log(Level.FINE, "Failed to parse cell value for cell at col " + col + " row " + row + " ("
                    + exception.getMessage() + ")", exception);
        }
    }

    spreadsheet.updateMarkedCells();

    if (updateHyperlinks) {
        spreadsheet.loadHyperLinks();
    }
}