Example usage for org.apache.poi.hssf.usermodel HSSFCell getRichStringCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getRichStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getRichStringCellValue

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFCell getRichStringCellValue.

Prototype

public HSSFRichTextString getRichStringCellValue()

Source Link

Document

get the value of the cell as a string - for numeric cells we throw an exception.

Usage

From source file:org.seasar.fisshplate.core.element.ElTest.java

License:Apache License

() throws Exception{
    HSSFWorkbook template = getTemplate("/ElTest_multi.xls");
    WorkbookWrapper workbook = new WorkbookWrapper(template);
    Map<String, Object> data = new HashMap<String, Object>();
    data.put("embeded1", new Integer(123));
    data.put("embeded2", new Integer(456));
    FPContext context = new FPContext(template.getSheetAt(0),data);

    CellWrapper cell = workbook.getSheetAt(0).getRow(0).getCell(0);//????${embeded1}?${embeded2}??

    el = new El(new GenericCell(cell));

    el.merge(context);//  www .ja v  a  2  s.c o m
    HSSFCell actual = template.getSheetAt(0).getRow(0).getCell(0);
    assertEquals("????123?456??", actual.getRichStringCellValue().getString());

    cell = workbook.getSheetAt(0).getRow(1).getCell(0);//${embeded1}?${embeded2}
    context.nextRow();

    el = new El(new GenericCell(cell));

    el.merge(context);
    actual = template.getSheetAt(0).getRow(1).getCell(0);
    assertEquals("123?456", actual.getRichStringCellValue().getString());

    cell = workbook.getSheetAt(0).getRow(2).getCell(0);//${embeded1}${embeded2}
    context.nextRow();

    el = new El(new GenericCell(cell));

    el.merge(context);
    actual = template.getSheetAt(0).getRow(2).getCell(0);
    assertEquals("123456", actual.getRichStringCellValue().getString());



}

From source file:org.springframework.ui.jasperreports.JasperReportsUtilsTests.java

License:Apache License

private void assertXlsOutputCorrect(byte[] output) throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(output));
    HSSFSheet sheet = workbook.getSheetAt(0);
    assertNotNull("Sheet should not be null", sheet);
    HSSFRow row = sheet.getRow(3);//from ww  w .j a  v a2  s  . c  om
    HSSFCell cell = row.getCell((short) 1);
    assertNotNull("Cell should not be null", cell);
    assertEquals("Cell content should be Dear Lord!", "Dear Lord!", cell.getRichStringCellValue().getString());
}

From source file:org.wso2.ws.dataservice.DBUtils.java

License:Apache License

private static OMElement getExcelResult(HSSFWorkbook wb, OMElement queryElement, AxisService axisService)
        throws AxisFault {
    OMElement resultElement = null;/*  ww w  .j a v a  2 s  .co  m*/

    //OMElement excelElement = queryElement.getFirstChildWithName(new QName("excel"));
    ExcelQuery excelQuery = new ExcelQuery(axisService, queryElement);

    OMElement result = queryElement.getFirstChildWithName(new QName("result"));
    String wrapperElementName = result.getAttributeValue(new QName("element"));
    String rowElementName = result.getAttributeValue(new QName("rowName"));
    String columnDefalut = result.getAttributeValue(new QName("columnDefault"));

    OMFactory fac = OMAbstractFactory.getOMFactory();
    OMNamespace omNs = fac.createOMNamespace(axisService.getTargetNamespace(), "data");
    resultElement = fac.createOMElement(wrapperElementName, omNs);

    if (!axisService.isElementFormDefault()) {
        omNs = fac.createOMNamespace("", "data");
    }

    //Retrieve the sheet name, if user has set it in configuration file
    String sheetName = excelQuery.getWorkBookName();

    //this is used to skip header columns, the spread sheet
    int startReadingFromRow = excelQuery.getStartingRow();
    if (startReadingFromRow >= 0) {
        //rows start from 0
        startReadingFromRow = startReadingFromRow - 1;
    }
    int maxRowCount = excelQuery.getMaxRowCount();

    HSSFSheet sheet = wb.getSheet(sheetName);
    int rowCount = sheet.getPhysicalNumberOfRows();

    //If hasHeaders is set to true, we need first row object in later stage.
    HSSFRow firstRow = null;
    if (excelQuery.hasHeaders()) {
        //assumption : first row is the header row
        firstRow = sheet.getRow(0);
    }

    int processedRowCount = 0;
    for (int r = 0; r < rowCount; r++) {
        if (r >= startReadingFromRow) {
            if (processedRowCount == maxRowCount) {
                break;
            }
            HSSFRow hssfRow = sheet.getRow(r);
            OMElement row = fac.createOMElement(rowElementName, omNs);
            if (rowElementName == null) {
                row = resultElement;
            }

            Iterator elements = result.getChildElements();
            while (elements.hasNext()) {
                OMElement element = (OMElement) elements.next();
                if (element.getLocalName().equals("element")) {
                    String displayTagName = element.getAttributeValue(new QName("name"));
                    String columnValue = element.getAttributeValue(new QName("column"));

                    short a = 1;
                    short columnNumber;
                    if (excelQuery.hasHeaders()) {
                        //if hasHeaders is set to true, column Names should be specified
                        //get the column number using specified name
                        columnNumber = getExcelColumnNumber(columnValue, firstRow);
                    } else {
                        try {
                            columnNumber = (short) (Short.valueOf(columnValue).shortValue() - a);
                        } catch (NumberFormatException e) {
                            log.error("Column value for element : " + displayTagName + " should be a number.",
                                    e);
                            throw new AxisFault(
                                    "Column value for element : " + displayTagName + " should be a number.");
                        }

                    }

                    HSSFCell hssfCell = hssfRow.getCell(columnNumber);
                    String elementValue = "";
                    if (hssfCell != null) {
                        if (HSSFCell.CELL_TYPE_STRING == hssfCell.getCellType()) {
                            elementValue = hssfCell.getRichStringCellValue().getString();
                        } else if (HSSFCell.CELL_TYPE_BLANK == hssfCell.getCellType()) {
                            //do nothing
                        } else if (HSSFCell.CELL_TYPE_BOOLEAN == hssfCell.getCellType()) {
                            elementValue = String.valueOf(hssfCell.getBooleanCellValue());
                        } else if (HSSFCell.CELL_TYPE_FORMULA == hssfCell.getCellType()) {
                            elementValue = "{formula}";
                        } else if (HSSFCell.CELL_TYPE_NUMERIC == hssfCell.getCellType()) {
                            elementValue = String.valueOf(hssfCell.getNumericCellValue());
                        }
                    }

                    if (columnDefalut == null || columnDefalut.equals("element")) {
                        OMElement rowElement = fac.createOMElement(displayTagName, omNs);
                        rowElement.addChild(fac.createOMText(rowElement, elementValue));
                        row.addChild(rowElement);
                    } else if (columnDefalut.equals("attribute")) {
                        row.addAttribute(displayTagName, elementValue, omNs);
                    }
                }
            }
            if (rowElementName != null) {
                resultElement.addChild(row);
                processedRowCount++;
            }
        } //end of if( k >= startReadingFromRow)
    } //for (int r = 0; r < rowCount; r++)
    return resultElement;
}

From source file:org.wso2.ws.dataservice.DBUtils.java

License:Apache License

private static short getExcelColumnNumber(String columnName, HSSFRow headerRow) throws AxisFault {
    int noOfCells = headerRow.getPhysicalNumberOfCells();
    short columnNo = -1;
    for (int a = 0; a < noOfCells; a++) {
        HSSFCell cell = headerRow.getCell((short) a);
        if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
            if (columnName.equals(cell.getRichStringCellValue().getString())) {
                columnNo = (short) a;
                break;
            }/* w  w  w .  j  a  v  a 2 s.  c  o m*/
        } else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
            try {
                double columnNameInDouble = Double.valueOf(columnName).doubleValue();
                if (columnNameInDouble == cell.getNumericCellValue()) {
                    columnNo = (short) a;
                    break;
                }
            } catch (NumberFormatException e) {
                log.error("Numeric value expected for Column Name : " + columnName, e);
                throw new AxisFault("Numeric value expected for Column Name : " + columnName, e);
            }
        }
    }
    return columnNo;
}

From source file:org.yccheok.jstock.file.Statements.java

License:Open Source License

/**
 * Construct Statements based on given Excel File.
 *
 * @param file Given Excel File/*ww w .j  a v a 2  s. co  m*/
 * @return the List of constructed Statements. Empty list if fail.
 */
public static List<Statements> newInstanceFromExcelFile(File file) {
    FileInputStream fileInputStream = null;
    final List<Statements> statementsList = new ArrayList<Statements>();
    try {
        fileInputStream = new FileInputStream(file);
        final POIFSFileSystem fs = new POIFSFileSystem(fileInputStream);
        final HSSFWorkbook wb = new HSSFWorkbook(fs);
        final int numberOfSheets = wb.getNumberOfSheets();
        for (int k = 0; k < numberOfSheets; k++) {
            final HSSFSheet sheet = wb.getSheetAt(k);
            final int startRow = sheet.getFirstRowNum();
            final int endRow = sheet.getLastRowNum();
            // If there are 3 rows, endRow will be 2.
            // We must have at least 2 rows. (endRow = 1)
            if (startRow != 0 || endRow <= startRow) {
                continue;
            }

            final HSSFRow row = sheet.getRow(startRow);
            if (row == null) {
                continue;
            }

            final int startCell = row.getFirstCellNum();
            final int endCell = row.getLastCellNum();
            // If there are 2 cols, endCell will be 2.
            // We must have at least 1 col. (endCell = 1)
            if (startCell != 0 || endCell <= startCell) {
                continue;
            }

            final List<String> types = new ArrayList<String>();
            for (int i = startCell; i < endCell; i++) {
                final HSSFCell cell = row.getCell(i);
                if (cell == null) {
                    continue;
                }

                // Exception may be thrown here, as cell may be numerical value.
                final String type = cell.getRichStringCellValue().getString();
                if (type != null) {
                    types.add(type);
                }
            }

            if (types.isEmpty()) {
                continue;
            }

            if (types.size() != (endCell - startCell)) {
                continue;
            }

            final Statement.What what = Statement.what(types);
            Statements s = new Statements(what.type, what.guiBundleWrapper);
            for (int i = startRow + 1; i <= endRow; i++) {
                final HSSFRow r = sheet.getRow(i);
                if (r == null) {
                    continue;
                }
                final List<Atom> atoms = new ArrayList<Atom>();
                for (int j = startCell; j < endCell; j++) {
                    final HSSFCell cell = r.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    Object value = null;
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        final HSSFRichTextString richString = cell.getRichStringCellValue();
                        if (richString != null) {
                            value = richString.getString();
                        } else {
                            value = "";
                        }
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        try {
                            value = new Double(cell.getNumericCellValue());
                        } catch (NumberFormatException ex) {
                            log.error(null, ex);
                            value = new Double(0.0);
                        }
                    } else {
                    }

                    if (null == value) {
                        continue;
                    }
                    atoms.add(new Atom(value, types.get(j - startCell)));
                }
                final Statement statement = new Statement(atoms);

                if (s.getType() != statement.getType()) {
                    // Give up.
                    s = null;
                    break;
                }
                s.statements.add(statement);
            } // for (int i = startRow + 1; i <= endRow; i++)

            if (s != null) {
                statementsList.add(s);
            }

        } /* for(int k = 0; k < numberOfSheets; k++) */
    } catch (Exception ex) {
        log.error(null, ex);
    } finally {
        org.yccheok.jstock.gui.Utils.close(fileInputStream);
    }
    return statementsList;
}

From source file:poi.hssf.view.SVTableCellEditor.java

License:Apache License

/**
 *  Gets the tableCellEditorComponent attribute of the SVTableCellEditor object
 *
 * @return             The tableCellEditorComponent value
 *//*from w  ww . j  a  v  a  2 s  . c om*/
public Component getTableCellEditorComponent(JTable table, Object value, boolean isSelected, int row,
        int column) {
    System.out.println("GetTableCellEditorComponent");
    HSSFCell cell = (HSSFCell) value;
    if (cell != null) {
        HSSFCellStyle style = cell.getCellStyle();
        HSSFFont f = wb.getFontAt(style.getFontIndex());
        boolean isbold = f.getBoldweight() > HSSFFont.BOLDWEIGHT_NORMAL;
        boolean isitalics = f.getItalic();

        int fontstyle = Font.PLAIN;

        if (isbold)
            fontstyle = Font.BOLD;
        if (isitalics)
            fontstyle = fontstyle | Font.ITALIC;

        int fontheight = f.getFontHeightInPoints();
        if (fontheight == 9)
            fontheight = 10; //fix for stupid ol Windows

        Font font = new Font(f.getFontName(), fontstyle, fontheight);
        editor.setFont(font);

        if (style.getFillPattern() == HSSFCellStyle.SOLID_FOREGROUND) {
            editor.setBackground(getAWTColor(style.getFillForegroundColor(), white));
        } else
            editor.setBackground(white);

        editor.setForeground(getAWTColor(f.getColor(), black));

        //Set the value that is rendered for the cell
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_BLANK:
            editor.setText("");
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            if (cell.getBooleanCellValue()) {
                editor.setText("true");
            } else {
                editor.setText("false");
            }
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            editor.setText(Double.toString(cell.getNumericCellValue()));
            break;
        case HSSFCell.CELL_TYPE_STRING:
            editor.setText(cell.getRichStringCellValue().getString());
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
        default:
            editor.setText("?");
        }
        switch (style.getAlignment()) {
        case HSSFCellStyle.ALIGN_LEFT:
        case HSSFCellStyle.ALIGN_JUSTIFY:
        case HSSFCellStyle.ALIGN_FILL:
            editor.setHorizontalAlignment(SwingConstants.LEFT);
            break;
        case HSSFCellStyle.ALIGN_CENTER:
        case HSSFCellStyle.ALIGN_CENTER_SELECTION:
            editor.setHorizontalAlignment(SwingConstants.CENTER);
            break;
        case HSSFCellStyle.ALIGN_GENERAL:
        case HSSFCellStyle.ALIGN_RIGHT:
            editor.setHorizontalAlignment(SwingConstants.RIGHT);
            break;
        default:
            editor.setHorizontalAlignment(SwingConstants.LEFT);
            break;
        }

    }
    return editor;
}

From source file:poi.hssf.view.SVTableCellRenderer.java

License:Apache License

public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus,
        int row, int column) {
    boolean isBorderSet = false;

    //If the JTables default cell renderer has been setup correctly the
    //value will be the HSSFCell that we are trying to render
    HSSFCell c = (HSSFCell) value;

    if (c != null) {
        HSSFCellStyle s = c.getCellStyle();
        HSSFFont f = wb.getFontAt(s.getFontIndex());
        setFont(SVTableUtils.makeFont(f));

        if (s.getFillPattern() == HSSFCellStyle.SOLID_FOREGROUND) {
            setBackground(SVTableUtils.getAWTColor(s.getFillForegroundColor(), SVTableUtils.white));
        } else//from   w  w  w  .  java2  s  .  co m
            setBackground(SVTableUtils.white);

        setForeground(SVTableUtils.getAWTColor(f.getColor(), SVTableUtils.black));

        cellBorder.setBorder(SVTableUtils.getAWTColor(s.getTopBorderColor(), SVTableUtils.black),
                SVTableUtils.getAWTColor(s.getRightBorderColor(), SVTableUtils.black),
                SVTableUtils.getAWTColor(s.getBottomBorderColor(), SVTableUtils.black),
                SVTableUtils.getAWTColor(s.getLeftBorderColor(), SVTableUtils.black), s.getBorderTop(),
                s.getBorderRight(), s.getBorderBottom(), s.getBorderLeft(), hasFocus);
        setBorder(cellBorder);
        isBorderSet = true;

        //Set the value that is rendered for the cell
        switch (c.getCellType()) {
        case HSSFCell.CELL_TYPE_BLANK:
            setValue("");
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            if (c.getBooleanCellValue()) {
                setValue("true");
            } else {
                setValue("false");
            }
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            short format = s.getDataFormat();
            double numericValue = c.getNumericCellValue();
            if (cellFormatter.useRedColor(format, numericValue))
                setForeground(Color.red);
            else
                setForeground(null);
            setValue(cellFormatter.format(format, c.getNumericCellValue()));
            break;
        case HSSFCell.CELL_TYPE_STRING:
            setValue(c.getRichStringCellValue().getString());
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
        default:
            setValue("?");
        }
        //Set the text alignment of the cell
        switch (s.getAlignment()) {
        case HSSFCellStyle.ALIGN_LEFT:
        case HSSFCellStyle.ALIGN_JUSTIFY:
        case HSSFCellStyle.ALIGN_FILL:
            setHorizontalAlignment(SwingConstants.LEFT);
            break;
        case HSSFCellStyle.ALIGN_CENTER:
        case HSSFCellStyle.ALIGN_CENTER_SELECTION:
            setHorizontalAlignment(SwingConstants.CENTER);
            break;
        case HSSFCellStyle.ALIGN_GENERAL:
        case HSSFCellStyle.ALIGN_RIGHT:
            setHorizontalAlignment(SwingConstants.RIGHT);
            break;
        default:
            setHorizontalAlignment(SwingConstants.LEFT);
            break;
        }
    } else {
        setValue("");
        setBackground(SVTableUtils.white);
    }

    if (hasFocus) {
        if (!isBorderSet) {
            //This is the border to paint when there is no border
            //and the cell has focus
            cellBorder.setBorder(SVTableUtils.black, SVTableUtils.black, SVTableUtils.black, SVTableUtils.black,
                    HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE,
                    HSSFCellStyle.BORDER_NONE, isSelected);
            setBorder(cellBorder);
        }
        if (table.isCellEditable(row, column)) {
            setForeground(UIManager.getColor("Table.focusCellForeground"));
            setBackground(UIManager.getColor("Table.focusCellBackground"));
        }
    } else if (!isBorderSet) {
        setBorder(noFocusBorder);
    }

    // ---- begin optimization to avoid painting background ----
    Color back = getBackground();
    boolean colorMatch = (back != null) && (back.equals(table.getBackground())) && table.isOpaque();
    setOpaque(!colorMatch);
    // ---- end optimization to aviod painting background ----
    return this;
}

From source file:POI.Sheet.java

/**
 * ??//from ww w. java 2  s  . co  m
 *
 * @param index ?
 * @return ?
 * @see HSSFRow
 * @see HSSFCell
 */
public ArrayList<String> getRowAt(int index) {
    HSSFRow row = sheet.getRow(index);
    ArrayList<String> cells = new ArrayList<String>();
    int i = row.getFirstCellNum();
    while (i < this.getColumnSize()) {
        HSSFCell cell = row.getCell(i++);
        if (cell == null) {
            cells.add("");
        } else {
            Object val = null;
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                val = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                val = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                val = cell.getNumericCellValue();
                break;
            case Cell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
            default:
                val = cell.getRichStringCellValue();
            }

            cells.add(String.valueOf(val));
        }
    }
    return cells;
}

From source file:senselogic.excelbundle.ExcelImporter.java

License:Apache License

/**
 * Returns the trimmed string value of the cell of null if none can be 
 * created. Null will also be returned for empty strings or if the null is
 * passed as a value.//from w  w  w  .jav a2 s. c  o m
 */
private String getString(HSSFCell c) {
    if (c == null)
        return null;
    if (c.getCellType() != HSSFCell.CELL_TYPE_STRING)
        return null;

    String str = c.getRichStringCellValue().getString();
    if (str.length() == 0)
        return null;

    return str;
}

From source file:test.ExcelUtil.java

License:Apache License

/**
 * , /*w  w w  .j  a  v a2  s. c  o  m*/
 * 
 * @param cell
 * @return
 */
public static String cell2string(HSSFCell cell, HSSFFormulaEvaluator evaluator) {
    if (cell == null) {
        return null;
    }
    String str = null;
    final int cellType = cell.getCellType();

    switch (cellType) {
    case HSSFCell.CELL_TYPE_STRING:
        str = "" + cell.getRichStringCellValue().getString().trim();
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            str = "" + dateFormat.format(cell.getDateCellValue());
        } else {
            str = String.valueOf(cell.getNumericCellValue());

            //modify by cyyan 2008-09-23 19:17:28 
            //excelE, ;
            //E, 15, (15, )
            // 15, 0, 0
            //            str = "" + new BigDecimal(numberStr).setScale(15, BigDecimal.ROUND_HALF_UP);
            //            
            //            //modify yanchangyou 2008-09-26 18:01:43 
            //            // .0000* , 0, 
            //            if (str.indexOf('.') != -1) {
            //               str = str.replaceAll("(\\.)?0*$", "");
            //            }            

            /*
             * , 
             */
            //            if (str.indexOf('.') != -1) { //
            //               int index = str.length();
            //               for (int i = str.length()-1; i > -1; i--) {
            //                  if (str.charAt(i) == '0') {
            //                     index = i;
            //                  } else if (str.charAt(i) == '.'){
            //                     index = i;
            //                     break;
            //                  } else {
            //                     break;
            //                  }
            //               }
            //               str = str.substring(0, index);
            //            }
        }
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        str = "";
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        str = "" + cell.getBooleanCellValue();
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        str = "" + cell.getErrorCellValue();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        if (evaluator == null) {
            str = "" + cell.getRichStringCellValue().getString();
        } else {
            str = "" + evaluator.evaluate(cell).getNumberValue();
        }

        break;
    }

    return (str == null || str.trim().equals("")) ? null : str.trim();
}