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

Introduction

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

Prototype

String getDataFormatString();

Source Link

Document

Get the format string

Usage

From source file:examples.toHTML.ToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();/*from   w w w.  j  a v  a  2s . c o  m*/

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();

        out.format("  <tr>%n");
        out.format("    <td class=\"%s\">%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            // &nbsp;
            String content = " ";
            String attrs = "";
            CellStyle style = null;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;

                    content = replaceUmlaut(content);

                    if (content.equals(""))
                        // &nbsp;
                        content = " ";
                }
            }
            out.format("    <td class=\"%s %s\">%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

---

From source file:itpreneurs.itp.report.archive.CellStyleDetails.java

License:Apache License

public static void main(String[] args) throws Exception {

    //     if(args.length == 0) {
    //        throw new IllegalArgumentException("Filename must be given");
    //     }/*  ww w . j  ava  2  s .  c om*/

    String filename = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/Workbook1.xlsx";

    Workbook wb = WorkbookFactory.create(new File(filename));
    DataFormatter formatter = new DataFormatter();

    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        Sheet sheet = wb.getSheetAt(sn);
        System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());

        for (Row row : sheet) {
            System.out.println("  Row " + row.getRowNum());

            for (Cell cell : row) {
                CellReference ref = new CellReference(cell);
                System.out.print("    " + ref.formatAsString());
                System.out.print(" (" + cell.getColumnIndex() + ") ");

                CellStyle style = cell.getCellStyle();
                System.out.print("Format=" + style.getDataFormatString() + " ");
                System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " ");
                System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " ");

                Font font = wb.getFontAt(style.getFontIndex());
                System.out.print("Font=" + font.getFontName() + " ");
                System.out.print("FontColor=");
                if (font instanceof HSSFFont) {
                    System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb)));
                }
                if (font instanceof XSSFFont) {
                    System.out.print(renderColor(((XSSFFont) font).getXSSFColor()));
                }

                System.out.println();
                System.out.println("        " + formatter.formatCellValue(cell));
            }
        }

        System.out.println();
    }
}

---

From source file:lucee.runtime.poi.Excel.java

License:Open Source License

public void setValue(int rowNumber, int columnNumber, String value) throws CasterException {
    if (value == null)
        value = "";
    Sheet sheet = workbook.getSheet(getExcelSheetName());

    // get Row/*  ww  w  .  j a  va 2 s .c om*/
    Row row = sheet.getRow(rowNumber);
    if (row == null)
        row = sheet.createRow(rowNumber);

    // get Cell
    Cell cell = row.getCell(columnNumber);
    CellStyle style = null;
    if (cell != null) {
        style = cell.getCellStyle();
        row.removeCell(cell);
    }
    cell = row.createCell(columnNumber);
    if (style != null)
        cell.setCellStyle(style);

    CreationHelper createHelper = workbook.getCreationHelper();
    boolean isFormula = style != null && style.getDataFormatString().equals("@");

    if (!isFormula && Decision.isNumeric(value)) {
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        double dbl = Caster.toDoubleValue(value);
        cell.setCellValue(dbl);
        _expandColumnWidth(sheet, Caster.toString(dbl), columnNumber);
    } else if (StringUtil.isEmpty("")) {
        cell.setCellType(Cell.CELL_TYPE_BLANK);
        cell.setCellValue(createHelper.createRichTextString(""));
    } else {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(createHelper.createRichTextString(value));
        _expandColumnWidth(sheet, value, columnNumber);
    }

}

---

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * DateUtil?Localize??(,,?)?????????/*from   ww  w  .j  a v  a 2  s  . c  o m*/
 * ?""????????
 * DateUtil???????? 
 * Bug 47071????
 * 
 * @param cell 
 */
public static boolean isCellDateFormatted(Cell cell) {
    if (cell == null) {
        return false;
    }
    boolean bDate = false;

    double d = cell.getNumericCellValue();
    if (DateUtil.isValidExcelDate(d)) {
        CellStyle style = cell.getCellStyle();
        if (style == null) {
            return false;
        }
        int i = style.getDataFormat();
        String fs = style.getDataFormatString();
        if (fs != null) {
            // And '"any"' into ''
            while (fs.contains("\"")) {
                int beginIdx = fs.indexOf("\"");
                if (beginIdx == -1) {
                    break;
                }
                int endIdx = fs.indexOf("\"", beginIdx + 1);
                if (endIdx == -1) {
                    break;
                }
                fs = fs.replaceFirst(Pattern.quote(fs.substring(beginIdx, endIdx + 1)), "");
            }
        }
        bDate = DateUtil.isADateFormat(i, fs);
    }
    return bDate;
}

---

From source file:org.dbunit.dataset.excel.MyXlsTable.java

License:Open Source License

public Object getValue(int row, String column) throws DataSetException {
    if (logger.isDebugEnabled())
        logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);

    assertValidRowIndex(row);//  ww  w.  j  a  v a  2 s  .c  om

    int columnIndex = getColumnIndex(column);
    Row rowObject = _sheet.getRow(row + 1);
    if (rowObject == null) {
        return null;
    }
    Cell cell = rowObject.getCell(columnIndex);
    if (cell == null) {
        return null;
    }

    int type = cell.getCellType();
    switch (type) {
    case Cell.CELL_TYPE_NUMERIC:
        CellStyle style = cell.getCellStyle();
        if (DateUtil.isCellDateFormatted(cell)) {
            return getDateValue(cell);
        } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) {
            // The special dbunit date format
            return getDateValueFromJavaNumber(cell);
        } else {
            return getNumericValue(cell);
        }

    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

    case Cell.CELL_TYPE_FORMULA:
        throw new DataTypeException("Formula not supported at row=" + row + ", column=" + column);

    case Cell.CELL_TYPE_BLANK:
        return null;

    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;

    case Cell.CELL_TYPE_ERROR:
        throw new DataTypeException("Error at row=" + row + ", column=" + column);

    default:
        throw new DataTypeException("Unsupported type at row=" + row + ", column=" + column);
    }
}

---

From source file:org.drugepi.table.CellStyleLookup.java

License:Mozilla Public License

public static String styleToString(CellStyle style) {
    StringBuffer sb = new StringBuffer();
    sb.append("getDataFormatString=" + style.getDataFormatString() + "\n");
    sb.append("getFontIndex=" + style.getFontIndex() + "\n");
    sb.append("getHidden=" + style.getHidden() + "\n");
    sb.append("getAlignment=" + style.getAlignment() + "\n");
    sb.append("getWrapText=" + style.getWrapText() + "\n");
    sb.append("getVerticalAlignment=" + style.getVerticalAlignment() + "\n");
    sb.append("getRotation=" + style.getRotation() + "\n");
    sb.append("getIndention=" + style.getIndention() + "\n");
    sb.append("getBorderLeft=" + style.getBorderLeft() + "\n");
    sb.append("getBorderRight=" + style.getBorderRight() + "\n");
    sb.append("getBorderTop=" + style.getBorderTop() + "\n");
    sb.append("getBorderBottom=" + style.getBorderBottom() + "\n");
    sb.append("getLeftBorderColor=" + style.getLeftBorderColor() + "\n");
    sb.append("getRightBorderColor=" + style.getRightBorderColor() + "\n");
    sb.append("getTopBorderColor=" + style.getTopBorderColor() + "\n");
    sb.append("getBottomBorderColor=" + style.getBottomBorderColor() + "\n");
    sb.append("getFillPattern=" + style.getFillPattern() + "\n");
    sb.append("getFillBackgroundColor=" + style.getFillBackgroundColor() + "\n");
    sb.append("getFillForegroundColor=" + style.getFillForegroundColor() + "\n");

    return sb.toString();
}

---

From source file:org.pentaho.reporting.ui.datasources.table.ImportFromFileTask.java

License:Open Source License

private void importFromFile(final File file, final boolean firstRowIsHeader) {
    final ByteArrayOutputStream bout = new ByteArrayOutputStream(Math.max(8192, (int) file.length()));
    try {/*w ww.jav a  2  s  .c  om*/
        final InputStream fin = new FileInputStream(file);
        try {
            IOUtils.getInstance().copyStreams(new BufferedInputStream(fin), bout);
        } finally {
            fin.close();
        }

        if (Thread.currentThread().isInterrupted()) {
            return;
        }

        final Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray()));
        int sheetIndex = 0;
        if (workbook.getNumberOfSheets() > 1) {
            final SheetSelectorDialog selectorDialog = new SheetSelectorDialog(workbook, parent);
            if (selectorDialog.performSelection()) {
                sheetIndex = selectorDialog.getSelectedIndex();
            } else {
                return;
            }
        }

        final TypedTableModel tableModel = new TypedTableModel();
        final Sheet sheet = workbook.getSheetAt(sheetIndex);
        final Iterator rowIterator = sheet.rowIterator();

        if (firstRowIsHeader) {
            if (rowIterator.hasNext()) {
                final Row headerRow = (Row) rowIterator.next();
                final short cellCount = headerRow.getLastCellNum();
                for (short colIdx = 0; colIdx < cellCount; colIdx++) {
                    final Cell cell = headerRow.getCell(colIdx);
                    if (cell != null) {
                        while (colIdx > tableModel.getColumnCount()) {
                            tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
                                    String.valueOf(tableModel.getColumnCount())), Object.class);
                        }

                        final RichTextString string = cell.getRichStringCellValue();
                        if (string != null) {
                            tableModel.addColumn(string.getString(), Object.class);
                        } else {
                            tableModel.addColumn(
                                    Messages.getString("TableDataSourceEditor.Column", String.valueOf(colIdx)),
                                    Object.class);
                        }
                    }
                }
            }
        }

        Object[] rowData = null;
        while (rowIterator.hasNext()) {
            final Row row = (Row) rowIterator.next();
            final short cellCount = row.getLastCellNum();
            if (cellCount == -1) {
                continue;
            }
            if (rowData == null || rowData.length != cellCount) {
                rowData = new Object[cellCount];
            }

            for (short colIdx = 0; colIdx < cellCount; colIdx++) {
                final Cell cell = row.getCell(colIdx);

                final Object value;
                if (cell != null) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        final RichTextString string = cell.getRichStringCellValue();
                        if (string != null) {
                            value = string.getString();
                        } else {
                            value = null;
                        }
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        final CellStyle hssfCellStyle = cell.getCellStyle();
                        final short dataFormat = hssfCellStyle.getDataFormat();
                        final String dataFormatString = hssfCellStyle.getDataFormatString();
                        if (isDateFormat(dataFormat, dataFormatString)) {
                            value = cell.getDateCellValue();
                        } else {
                            value = cell.getNumericCellValue();
                        }
                    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                        value = cell.getBooleanCellValue();
                    } else {
                        value = cell.getStringCellValue();
                    }
                } else {
                    value = null;
                }

                if (value != null && "".equals(value) == false) {
                    while (colIdx >= tableModel.getColumnCount()) {
                        tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
                                String.valueOf(tableModel.getColumnCount())), Object.class);
                    }
                }

                rowData[colIdx] = value;
            }

            if (Thread.currentThread().isInterrupted()) {
                return;
            }

            tableModel.addRow(rowData);
        }

        final int colCount = tableModel.getColumnCount();
        final int rowCount = tableModel.getRowCount();
        for (int col = 0; col < colCount; col++) {
            Class type = null;
            for (int row = 0; row < rowCount; row += 1) {
                final Object value = tableModel.getValueAt(row, col);
                if (value == null) {
                    continue;
                }
                if (type == null) {
                    type = value.getClass();
                } else if (type != Object.class) {
                    if (type.isInstance(value) == false) {
                        type = Object.class;
                    }
                }
            }

            if (Thread.currentThread().isInterrupted()) {
                return;
            }

            if (type != null) {
                tableModel.setColumnType(col, type);
            }
        }

        parent.importComplete(tableModel);
    } catch (Exception e) {
        parent.importFailed(e);
        logger.error("Failed to import spreadsheet", e); // NON-NLS
    }
}

---

From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java

License:MIT License

/**
 * Gets the input type from cell type./*from   ww  w.  j  a v  a  2 s  .  c  o  m*/
 *
 * @param cell
 *            the cell
 * @return the input type from cell type
 */
@SuppressWarnings("deprecation")
private static String getInputTypeFromCellType(final Cell cell) {

    String inputType = TieConstants.CELL_INPUT_TYPE_TEXT;
    if (cell.getCellTypeEnum() == CellType.NUMERIC) {
        inputType = TieConstants.CELL_INPUT_TYPE_DOUBLE;
    }
    CellStyle style = cell.getCellStyle();
    if (style != null) {
        int formatIndex = style.getDataFormat();
        String formatString = style.getDataFormatString();
        if (DateUtil.isADateFormat(formatIndex, formatString)) {
            inputType = TieConstants.CELL_INPUT_TYPE_DATE;
        } else {
            if (isAPercentageCell(formatString)) {
                inputType = TieConstants.CELL_INPUT_TYPE_PERCENTAGE;
            }
        }
    }
    return inputType;
}

---

From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java

License:MIT License

/**
 * get decimal places from format string e.g. 0.00 will return 2
 *
 * @param cell/* w  w  w  . ja v  a  2s  . com*/
 *            the cell
 * @return decimal places of the formatted string
 */
private static short getDecimalPlacesFromFormat(final Cell cell) {
    CellStyle style = cell.getCellStyle();
    if (style == null) {
        return 0;
    }
    String formatString = style.getDataFormatString();
    if (formatString == null) {
        return 0;
    }
    int ipos = formatString.indexOf('.');
    if (ipos < 0) {
        return 0;
    }
    short counter = 0;
    for (int i = ipos + 1; i < formatString.length(); i++) {
        if (formatString.charAt(i) == '0') {
            counter++;
        } else {
            break;
        }
    }
    return counter;
}

---

From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java

License:MIT License

/**
 * get symbol from format string e.g. [$CAD] #,##0.00 will return CAD. While
 * $#,##0.00 will return $//from w  ww . j a v a  2  s . c  o  m
 *
 * @param cell
 *            the cell
 * @return symbol of the formatted string
 */
private static String getSymbolFromFormat(final Cell cell) {
    CellStyle style = cell.getCellStyle();
    if (style == null) {
        return null;
    }
    String formatString = style.getDataFormatString();
    if (formatString == null) {
        return null;
    }
    if (formatString.indexOf(TieConstants.CELL_ADDR_PRE_FIX) < 0) {
        return null;
    }
    int ipos = formatString.indexOf("[$");
    if (ipos < 0) {
        // only $ found, then return default dollar symbol
        return "$";
    }
    // return specified dollar symbol
    return formatString.substring(ipos + 2, formatString.indexOf(']', ipos));
}

---

• «
• 1
• 2
• 3
• »