Example usage for org.apache.poi.ss.usermodel Cell getRichStringCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getRichStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getRichStringCellValue

Introduction

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

Prototype

RichTextString getRichStringCellValue();

Source Link

Document

Get the value of the cell as a XSSFRichTextString

For numeric cells we throw an exception.

Usage

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-6]  ?  :  ? ?(?, ? )? //  w w w  .ja  va 2s.c o m
 */
@Test
public void testModifyCellAttribute() throws Exception {

    try {
        LOGGER.debug("testModifyCellAttribute start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellAttribute.xlsx");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wbTmp = new XSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        XSSFWorkbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
        LOGGER.debug("testModifyCellAttribute after loadWorkbook....");

        Sheet sheet = wb.createSheet("cell test sheet2");
        sheet.setColumnWidth((short) 3, (short) 200); // column Width

        CellStyle cs = wb.createCellStyle();
        XSSFFont font = wb.createFont();
        font.setFontHeight(16);
        font.setBoldweight((short) 3);
        font.setFontName("fixedsys");

        cs.setFont(font);
        cs.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // cell 
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            Row row = sheet.createRow(i);
            row.setHeight((short) 300); // row? height 

            for (int j = 0; j < 5; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        XSSFWorkbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
        Sheet sheetT = wbT.getSheet("cell test sheet2");
        LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles());

        XSSFCellStyle cs1 = (XSSFCellStyle) wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1));

        XSSFFont fontT = cs1.getFont();
        LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight());
        LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight());
        LOGGER.debug("font getFontName : {}", fontT.getFontName());
        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        LOGGER.debug("getWrapText : {}", cs1.getWrapText());

        for (int i = 0; i < 100; i++) {
            Row row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell1 = row1.getCell(j);
                LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue());
                assertEquals(320, fontT.getFontHeight());
                assertEquals(400, fontT.getBoldweight());
                LOGGER.debug("fontT.getBoldweight()? ? 400? ?");

                assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());
                assertTrue(cs1.getWrapText());
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testModifyCellAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-7]    :  ?  ?? //  w w  w .ja  v a2s .c  om
 */
@Test
public void testUseTemplate1() throws Exception {

    StringBuffer sb = new StringBuffer();
    StringBuffer sbResult = new StringBuffer();

    sb.append(fileLocation).append("/template/").append("template.xlsx");
    sbResult.append(fileLocation).append("/").append("testUseTemplate1.xlsx");

    Object[][] sample_data = { { "Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 },
            { "Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0 }, };

    try {

        XSSFWorkbook wb = null;
        wb = excelService.loadExcelTemplate(sb.toString(), wb);
        Sheet sheet = wb.getSheetAt(0);

        // set data
        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheet.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                if (sample_data[i][j] == null)
                    continue;

                Cell cell = row.getCell(j);

                if (sample_data[i][j] instanceof String) {
                    cell.setCellValue(new XSSFRichTextString((String) sample_data[i][j]));
                } else {
                    cell.setCellValue((Double) sample_data[i][j]);
                }
            }
        }

        // ? 
        sheet.setForceFormulaRecalculation(true);

        excelService.createWorkbook(wb, sbResult.toString());

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sbResult.toString(), new XSSFWorkbook());
        Sheet sheetT = wbT.getSheetAt(0);

        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheetT.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                Cell cell = row.getCell(j);

                LOGGER.debug("sample_data[i][j] : {}", sample_data[i][j]);

                if (sample_data[i][j] == null) {
                    assertEquals(cell.getCellType(), XSSFCell.CELL_TYPE_BLANK);
                } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    assertEquals((Double) sample_data[i][j], Double.valueOf(cell.getNumericCellValue()));
                } else {
                    assertEquals((String) sample_data[i][j], cell.getRichStringCellValue().getString());
                }
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testUseTemplate end....");
    }
}

From source file:egovframework.rte.fdl.excel.util.EgovExcelUtil.java

License:Apache License

/**
 * ? ? String   ./*from ww  w.  j a va2  s . c om*/
 * 
 * @param cell <code>Cell</code>
 * @return  
 */
public static String getValue(Cell cell) {

    String result = "";

    if (null == cell || cell.equals(null)) {
        return "";
    }

    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        LOGGER.debug("### Cell.CELL_TYPE_BOOLEAN : {}", Cell.CELL_TYPE_BOOLEAN);
        result = String.valueOf(cell.getBooleanCellValue());

    } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
        LOGGER.debug("### Cell.CELL_TYPE_ERROR : {}", Cell.CELL_TYPE_ERROR);
        // byte errorValue =
        // cell.getErrorCellValue();

    } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        LOGGER.debug("### Cell.CELL_TYPE_FORMULA : {}", Cell.CELL_TYPE_FORMULA);

        String stringValue = null;
        String longValue = null;

        try {
            stringValue = cell.getRichStringCellValue().getString();
            longValue = doubleToString(cell.getNumericCellValue());
        } catch (Exception e) {
            LOGGER.debug("{}", e);
        }

        if (stringValue != null) {
            result = stringValue;
        } else if (longValue != null) {
            result = longValue;
        } else {
            result = cell.getCellFormula();
        }

    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        LOGGER.debug("### Cell.CELL_TYPE_NUMERIC : {}", Cell.CELL_TYPE_NUMERIC);

        result = DateUtil.isCellDateFormatted(cell)
                ? EgovDateUtil.toString(cell.getDateCellValue(), "yyyy/MM/dd", null)
                : doubleToString(cell.getNumericCellValue());

    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        LOGGER.debug("### Cell.CELL_TYPE_STRING : {}", Cell.CELL_TYPE_STRING);
        result = cell.getRichStringCellValue().getString();

    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        LOGGER.debug("### Cell.CELL_TYPE_BLANK : {}", Cell.CELL_TYPE_BLANK);
    }

    return result;
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java

License:Mozilla Public License

/**
 * Method goes through 4 rows and search the best fit of XML Schema. The deault row is 4.
 *
 * @param schemaSheet Schema sheet name.
 * @return schema URL.//w ww . j a  v a2 s .c  o  m
 */
private String findSchemaFromSheet(Sheet schemaSheet) {
    Row schemaRow = null;
    Cell schemaCell = null;

    for (int i = 3; i > -1; i--) {
        if (schemaSheet.getLastRowNum() < i) {
            continue;
        }
        schemaRow = schemaSheet.getRow(i);
        if (schemaRow == null) {
            continue;
        }
        if (schemaRow.getLastCellNum() < 0) {
            continue;
        }
        schemaCell = schemaRow.getCell(0);
        String val = schemaCell.getRichStringCellValue().toString();

        if (val.startsWith("http://") && val.toLowerCase().indexOf("/getschema") > 0 && Utils.isURL(val)) {
            return val;
        }
    }
    return null;
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java

License:Mozilla Public License

/**
 * Method goes through rows after XML Schema and finds schemas for Excel sheets (DataDict tables). cell(0) =sheet name;
 * cell(1)=XML schema//w  ww.j  a v a2 s  .c  o  m
 *
 * @param schemaSheet sheet name
 * @return Map
 */
private Map<String, String> findSheetSchemas(Sheet schemaSheet) {

    Row schemaRow = null;
    Cell schemaCell = null;
    Cell sheetCell = null;

    Map<String, String> result = new LinkedHashMap<String, String>();
    if (schemaSheet.getLastRowNum() < 1) {
        return null;
    }

    for (int i = 0; i <= schemaSheet.getLastRowNum(); i++) {
        schemaRow = schemaSheet.getRow(i);
        if (schemaRow == null) {
            continue;
        }
        if (schemaRow.getLastCellNum() < 1) {
            continue;
        }
        schemaCell = schemaRow.getCell(1);
        if (schemaCell == null) {
            continue;
        }
        String schemaValue = schemaCell.getRichStringCellValue().toString();

        if (schemaValue.startsWith("http://") && schemaValue.toLowerCase().indexOf("/getschema") > 0
                && Utils.isURL(schemaValue)) {

            sheetCell = schemaRow.getCell(0);
            String sheetValue = sheetCell.getRichStringCellValue().toString();
            if (sheetValue == null) {
                continue;
            }
            if (sheetValue != null && sheetValue.length() > 31) {
                sheetValue = sheetValue.substring(0, 31);
            }
            Sheet sheet = getSheet(sheetValue);
            if (sheet != null && !result.containsKey(sheetValue)) {
                result.put(sheetValue, schemaValue);
            }
        }
    }
    return result;
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java

License:Mozilla Public License

/**
 * Reads cell value and formats it according to element type defined in XML Schema. If the cell contains formula,
 * then calculated value is returned./*  w  w w  .j ava2s.c om*/
 *
 * @param cell       Spreadsheet Cell object.
 * @param schemaType XML Schema data type for given cell.
 * @return string value of the cell.
 */
protected String cellValueToString(Cell cell, String schemaType) {
    String value = "";

    if (cell != null) {
        switch (evaluator.evaluateInCell(cell).getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell) && !isYearValue(cell.getNumericCellValue())) {
                Date dateValue = cell.getDateCellValue();
                value = Utils.getFormat(dateValue, DEFAULT_DATE_FORMAT);
            } else if (HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue()) && schemaType != null
                    && schemaType.equals("xs:date") && !isYearValue(cell.getNumericCellValue())) {
                Date dateValue = cell.getDateCellValue();
                value = Utils.getFormat(dateValue, DEFAULT_DATE_FORMAT);
            } else {
                value = formatter.formatCellValue(cell);
            }
            break;
        case HSSFCell.CELL_TYPE_STRING:
            RichTextString richText = cell.getRichStringCellValue();
            value = richText.toString();
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            value = Boolean.toString(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            break;
        default:
            break;
        }
    }
    return StringUtils.strip(value.trim(), String.valueOf(NON_BREAKING_SPACE)).trim();
}

From source file:eu.learnpad.ontology.kpi.data.ExcelParser.java

public String getSPARQLQuery() throws IOException, InvalidFormatException {
    Boolean foundSparqlQuery = false;

    Workbook wb = WorkbookFactory.create(excelFile);

    for (Sheet sheet : wb) {
        if (sheet.getSheetName().equals(SHEETNAME)) {
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        continue;
                    }/*  w ww .  ja v  a  2 s  .c  o m*/
                    if (!foundSparqlQuery && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        if (cell.getRichStringCellValue().getString().equals(QUERYCELLNAME)) {
                            foundSparqlQuery = true;
                            continue;
                        }
                    }
                    if (foundSparqlQuery && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        return cell.getRichStringCellValue().getString();
                    }
                }
            }
        }
    }
    return null;
}

From source file:eu.learnpad.ontology.kpi.data.ExcelParser.java

public List<List<String>> getDataTable() throws IOException, InvalidFormatException {
    List<List<String>> dataTable = new ArrayList<>();
    Integer rowNumber = -2;//from w w  w  . j av a2s  .co  m

    Workbook wb = WorkbookFactory.create(excelFile);

    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (Sheet sheet : wb) {
        if (sheet.getSheetName().equals(SHEETNAME)) {
            for (Row row : sheet) {
                //stop with the first empty row
                if (row.getCell(0) == null) {
                    break;
                }
                if (rowNumber >= -1) {
                    rowNumber++;
                    dataTable.add(new ArrayList<String>());
                }
                for (Cell cell : row) {
                    String sheetName = sheet.getSheetName();
                    String cellRow = "Row:" + cell.getRowIndex();
                    String cellColumn = "Column:" + cell.getColumnIndex();
                    Object[] o = new Object[] { sheetName, cellRow, cellColumn };
                    LOGGER.log(Level.INFO, "Processing: Sheet={0} celladress={1}", o);
                    if (rowNumber <= -1 && cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        continue;
                    }
                    if (rowNumber == -2 && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        if (cell.getRichStringCellValue().getString().equals(DATACELLNAME)) {
                            rowNumber = -1;
                            continue;
                        }
                    }
                    //Attributes (column headers)
                    if (rowNumber == 0) {
                        dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString());
                    }

                    if (rowNumber >= 1) {

                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                dataTable.get(rowNumber).add(cell.getDateCellValue().toString());
                            } else {
                                dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue()));
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue()));
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            switch (cell.getCachedFormulaResultType()) {
                            case Cell.CELL_TYPE_STRING:
                                dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString());
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    dataTable.get(rowNumber).add(cell.getDateCellValue().toString());
                                } else {
                                    dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue()));
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue()));
                                break;
                            default:
                                dataTable.get(rowNumber).add("");
                            }
                            break;
                        default:
                            dataTable.get(rowNumber).add("");
                        }
                    }
                }
            }
        }
    }

    return dataTable;
}

From source file:excel.Reader.java

public void print() {
    System.out.println("START PRINT");
    SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
    int columnWidth = 15;
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    Sheet sheet = wb.getSheetAt(0);//from w  w w . ja va 2 s .c  o  m
    for (Row row : sheet) {
        //System.out.print("r");
        for (Cell cell : row) {
            //CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
            //System.out.print(cellRef.formatAsString());
            //System.out.print(" - ");
            // System.out.print("c");
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                //System.out.print("s");
                System.out.printf("%-" + columnWidth + "s", cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                //System.out.print("d");
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.printf("%-" + columnWidth + "s", df.format(cell.getDateCellValue()));
                } else {
                    if ((cell.getNumericCellValue() % 1.0) != 0.0)
                        System.out.printf("%-" + columnWidth + ".2f", cell.getNumericCellValue());
                    else
                        System.out.printf("%-" + columnWidth + ".0f", cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //System.out.print("b");
                System.out.printf("%-" + columnWidth + "s", cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                CellValue val = evaluator.evaluate(cell);
                //System.out.print("f");
                switch (val.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.printf("%-" + columnWidth + "s", val.getStringValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.printf("%-" + columnWidth + ".2f", val.getNumberValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.printf("%-" + columnWidth + "s", val.getBooleanValue());
                    break;
                default:
                    System.out.printf("%-" + columnWidth + "s", "");
                }
                break;
            default:
                System.out.print("");
            }
        }
        System.out.println();
    }
}

From source file:gda.hrpd.data.ExcelReader.java

License:Open Source License

/**
 * load data from spreadsheet to the map, or initialise the multimap
 *///from w ww. jav a  2  s  .  c o  m
public void readData() {
    mvm.clear();

    int i = 0;
    for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
        Row row = rit.next();
        for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) {
            Cell cell = cit.next();
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                mvm.put(i, cell.getRichStringCellValue().toString());
            } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                mvm.put(i, String.valueOf(cell.getNumericCellValue()));
            } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                mvm.put(i, "");
            }
        }
        i++;
    }
    logger.debug("Read row {}", i);
}