Example usage for org.apache.poi.ss.usermodel Cell 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:edu.ucsd.bioeng.coreplugin.tableImport.reader.ExcelAttributeSheetReader.java

License:Open Source License

/**
 * For a given Excell row, convert the cells into String.
 *
 * @param row//from  w  ww .ja va  2s .c  o  m
 * @return
 */
private String[] createElementStringArray(Row row) {
    String[] cells = new String[mapping.getColumnCount()];
    Cell cell = null;

    for (short i = 0; i < mapping.getColumnCount(); i++) {
        cell = row.getCell(i);

        if (cell == null) {
            cells[i] = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            cells[i] = cell.getRichStringCellValue().getString();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (mapping.getAttributeTypes()[i] == CyAttributes.TYPE_INTEGER) {
                Double dblValue = cell.getNumericCellValue();
                Integer intValue = dblValue.intValue();
                cells[i] = intValue.toString();
            } else {
                cells[i] = Double.toString(cell.getNumericCellValue());
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            cells[i] = Boolean.toString(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            cells[i] = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            cells[i] = null;
            logger.warn("Error found when reading a cell!");
        }
    }

    return cells;
}

---

From source file:edu.ucsd.bioeng.coreplugin.tableImport.reader.ExcelNetworkSheetReader.java

License:Open Source License

/**
 * For a given Excell row, convert the cells into String.
 *
 * @param row/*  w w  w.j  av  a 2s . c om*/
 * @return
 */
private String[] createElementStringArray(final Row row) {
    String[] cells = new String[nmp.getColumnCount()];
    Cell cell = null;

    for (short i = 0; i < nmp.getColumnCount(); i++) {
        cell = row.getCell(i);

        if (cell == null) {
            cells[i] = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            cells[i] = cell.getRichStringCellValue().getString();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (nmp.getAttributeTypes()[i] == CyAttributes.TYPE_INTEGER) {
                Double dblValue = cell.getNumericCellValue();
                Integer intValue = dblValue.intValue();
                cells[i] = intValue.toString();
            } else {
                cells[i] = Double.toString(cell.getNumericCellValue());
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            cells[i] = Boolean.toString(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            cells[i] = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            cells[i] = null;
            logger.warn("Error found when reading a cell!");
        }
    }

    return cells;
}

---

From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.PreviewTablePanel.java

License:Open Source License

private TableModel parseExcel(final URL sourceURL, int size, TableCellRenderer renderer, final Sheet sheet,
        int startLine) throws IOException {

    if (size == -1)
        size = Integer.MAX_VALUE;

    int maxCol = 0;
    final Vector<Object> data = new Vector<Object>();

    int rowCount = 0;
    Row row;/*from  w ww  . java 2  s  .  com*/

    while (((row = sheet.getRow(rowCount)) != null) && (rowCount < size)) {
        if (rowCount >= startLine) {
            Vector<Object> rowVector = new Vector<Object>();

            if (maxCol < row.getPhysicalNumberOfCells()) {
                maxCol = row.getPhysicalNumberOfCells();
            }

            for (short j = 0; j < maxCol; j++) {
                Cell cell = row.getCell(j);

                if (cell == null) {
                    rowVector.add(null);
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    rowVector.add(cell.getRichStringCellValue().getString());
                } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    final Double dblValue = cell.getNumericCellValue();
                    final Integer intValue = dblValue.intValue();

                    if (intValue.doubleValue() == dblValue) {
                        rowVector.add(intValue.toString());
                    } else {
                        rowVector.add(dblValue.toString());
                    }
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    rowVector.add(Boolean.toString(cell.getBooleanCellValue()));
                } else if ((cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        || (cell.getCellType() == Cell.CELL_TYPE_ERROR)) {
                    rowVector.add(null);
                } else {
                    rowVector.add(null);
                }
            }

            data.add(rowVector);
        }

        rowCount++;
    }

    return new DefaultTableModel(data, this.getDefaultColumnNames(maxCol, sourceURL));
}

---

From source file:edu.vt.cs.irwin.etdscraper.retriever.excel.ExcelEtdSource.java

License:Apache License

private String getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue().toString();
        } else {//from   w  w w. j  ava2 s  . com
            return ((Double) cell.getNumericCellValue()).toString();
        }
    case Cell.CELL_TYPE_BOOLEAN:
        return ((Boolean) cell.getBooleanCellValue()).toString();
    case Cell.CELL_TYPE_BLANK:
        return "";
    }
    throw new UnsupportedOperationException("Don't know how to work with type: " + cell.getCellType());
}

---

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

License:Apache License

/**
 * [Flow #-2]  ?  : ? ?  ? ?    ?//from   ww  w  . j av  a2 s .co  m
 */
@Test
public void testModifyCellContents() throws Exception {

    try {
        String content = "Use \n with word wrap on to create a new line";
        short rownum = 2;
        int cellnum = 2;

        LOGGER.debug("testModifyCellContents start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellContents.xls");

        if (!EgovFileUtil.isExistsFile(sb.toString())) {
            Workbook wbT = new HSSFWorkbook();
            wbT.createSheet();

            //  ? ?
            excelService.createWorkbook(wbT, sb.toString());
        }

        //  ? 
        Workbook wb = excelService.loadWorkbook(sb.toString());
        LOGGER.debug("testModifyCellContents after loadWorkbook....");

        Sheet sheet = wb.getSheetAt(0);
        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        //Word Wrap MUST be turned on
        cs.setWrapText(true);

        Row row = sheet.createRow(rownum);
        row.setHeight((short) 0x349);
        Cell cell = row.createCell(cellnum);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(new HSSFRichTextString(content));
        cell.setCellStyle(cs);

        sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20)));

        //excelService.writeWorkbook(wb);

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

        //  ? 
        Workbook wb1 = excelService.loadWorkbook(sb.toString());

        Sheet sheet1 = wb1.getSheetAt(0);
        Row row1 = sheet1.getRow(rownum);
        Cell cell1 = row1.getCell(cellnum);

        // ? ?  ?
        LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue());
        LOGGER.debug("cont ###{}###", content);

        assertNotSame("TEST", cell1.getRichStringCellValue().toString());
        assertEquals(content, cell1.getRichStringCellValue().toString());

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

---

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

License:Apache License

/**
 * [Flow #-5]    :  ?? ?  ? ?  /* w  w  w . j  a va  2  s.co  m*/
 */
@Test
public void testGetCellContents() throws Exception {

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

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testGetCellContents.xls");

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

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

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

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

        //  ? 
        Workbook wb = excelService.loadWorkbook(sb.toString());
        LOGGER.debug("testGetCellContents after loadWorkbook....");

        Sheet sheet = wb.createSheet("cell test sheet");

        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();
        cs.setWrapText(true);

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

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

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sb.toString());
        Sheet sheetT = wbT.getSheet("cell test sheet");

        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("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString());
            }
        }

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

---

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

License:Apache License

/**
 * [Flow #-6]  ?  :  ? ?(?, ? )? //from  w w w  . j a v  a 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.xls");

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

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

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

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

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

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

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

        cs.setFont(font);
        cs.setAlignment(CellStyle.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 HSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

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

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

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

        Font fontT = ((HSSFCellStyle) cs1).getFont(wbT);
        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(16, fontT.getFontHeight());
                assertEquals(3, fontT.getBoldweight());
                assertEquals(CellStyle.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.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-7]    :  ?  ?? //w  w w .j a v  a  2s  .c  o m
 */
@Test
public void testUseTemplate1() throws Exception {

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

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

    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 {

        Workbook wb = excelService.loadExcelTemplate(sb.toString());
        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 HSSFRichTextString((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());
        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(), Cell.CELL_TYPE_BLANK);
                } else if (cell.getCellType() == Cell.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.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-2]  ?  : ? ?  ? ?    ?/*from w  w w . j ava 2s  .  c  om*/
 */
@Test
public void testModifyCellContents() throws Exception {

    try {
        String content = "Use \n with word wrap on to create a new line";
        short rownum = 2;
        int cellnum = 2;

        LOGGER.debug("testModifyCellContents start....");

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

        if (!EgovFileUtil.isExistsFile(sb.toString())) {
            Workbook wbT = new XSSFWorkbook();
            wbT.createSheet();

            //  ? ?
            excelService.createWorkbook(wbT, sb.toString());
        }

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

        Sheet sheet = wb.getSheetAt(0);
        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        //Word Wrap MUST be turned on
        cs.setWrapText(true);

        Row row = sheet.createRow(rownum);
        row.setHeight((short) 0x349);
        Cell cellx = row.createCell(cellnum);
        cellx.setCellType(XSSFCell.CELL_TYPE_STRING);
        cellx.setCellValue(new XSSFRichTextString(content));
        cellx.setCellStyle(cs);

        sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20)));

        //excelService.writeWorkbook(wb);

        FileOutputStream outx = new FileOutputStream(sb.toString());
        wb.write(outx);
        outx.close();

        //  ? 
        Workbook wb1 = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());

        Sheet sheet1 = wb1.getSheetAt(0);
        Row row1 = sheet1.getRow(rownum);
        Cell cell1 = row1.getCell(cellnum);

        // ? ?  ?
        LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue());
        LOGGER.debug("cont ###{}###", content);

        assertNotSame("TEST", cell1.getRichStringCellValue().toString());
        assertEquals(content, cell1.getRichStringCellValue().toString());

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

---

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

License:Apache License

/**
 * [Flow #-5]    :  ?? ?  ? ?  /*from  w  ww  .  j  a v a  2s . com*/
 */
@Test
public void testGetCellContents() throws Exception {

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

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testGetCellContents.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());

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

        Sheet sheet = wb.createSheet("cell test sheet");

        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            Row row = sheet.createRow(i);
            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();

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
        Sheet sheetT = wbT.getSheet("cell test sheet");

        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("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString());
            }
        }

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

---

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