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

Introduction

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

Prototype

public String getStringCellValue() 

Source Link

Document

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

Usage

From source file:com.softtek.mdm.web.admin.IndexController.java

private void exportExcel(String sheetName, String[] headNames, List<OrganizationModel> lists,
        HttpServletResponse response) {//from w ww  .java 2  s .  c om

    OutputStream out = null;
    try {
        HSSFWorkbook workbook = new HSSFWorkbook(); //   
        HSSFSheet sheet = workbook.createSheet(sheetName); //   
        //   
        HSSFRow rowm = sheet.createRow(0);
        HSSFCell cellTiltle = rowm.createCell(0);
        //sheet??getColumnTopStyle()/getStyle()? - ?  - ?  
        HSSFCellStyle columnTopStyle = CommUtil.getColumnTopStyle(workbook);//??  
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headNames.length - 1)));
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(sheetName);
        //   
        int columnNum = headNames.length;
        HSSFRow rowRowName = sheet.createRow(2); // 2?()  
        // sheet?  
        for (int n = 0; n < columnNum; n++) {
            HSSFCell cellRowName = rowRowName.createCell(n); //?  
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //??  
            HSSFRichTextString text = new HSSFRichTextString(headNames[n]);
            cellRowName.setCellValue(text); //?  
            cellRowName.setCellStyle(columnTopStyle); //??  
        }
        //?sheet?  
        HSSFDataFormat format = workbook.createDataFormat();
        short formatDate = format.getFormat("yyyy-MM-dd hh:mm:ss");
        for (int i = 0; i < lists.size(); i++) {
            HSSFRow row = sheet.createRow(i + 3);//  
            OrganizationModel obj = lists.get(i);//???  
            /*row.createCell(0).setCellValue(obj.getOrgType());*/
            row.createCell(0).setCellValue(obj.getName());
            row.createCell(1).setCellValue(obj.getCreateName());
            row.createCell(2).setCellValue(obj.getTotalUsers() == null ? 0 : obj.getTotalUsers());
            row.createCell(3).setCellValue(obj.getTotalDevices() == null ? 0 : obj.getTotalDevices());
            row.createCell(4).setCellValue(obj.getLicenseCount() == null ? 0 : obj.getLicenseCount());
            row.createCell(5).setCellValue(obj.getUseUsers() == null ? 0 : obj.getUseUsers());
            HSSFCell cell = row.createCell(6);
            cell.setCellValue(obj.getCreateTime());
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(formatDate);
            cell.setCellStyle(cellStyle);
        }
        //??  
        for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                //?  
                currentRow = (sheet.getRow(rowNum) == null) ? sheet.createRow(rowNum) : sheet.getRow(rowNum);
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(colNum, (colNum == 0) ? (columnWidth * 256) : ((columnWidth + 10) * 256));
        }
        if (workbook != null) {
            try {
                String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13)
                        + ".xls";
                String headStr = "attachment; filename=\"" + fileName + "\"";
                response.setContentType("application/octet-stream");
                response.setHeader("Content-Disposition", headStr);
                out = response.getOutputStream();
                workbook.write(out);
            } catch (IOException e) {
                logger.error(e.getMessage());
            } finally {
                if (out != null) {
                    out.close();
                }
            }
        }
    } catch (Exception e) {
        logger.error(e.getMessage());
    }
}

---

From source file:com.templatemailer.handler.ExcelHandler.java

public static MailerData readFile(String fileName) throws Exception {

    FileInputStream file = new FileInputStream(new File(fileName));
    System.out.println("Read File");
    HSSFWorkbook workbook = new HSSFWorkbook(file);
    System.out.println("Parsed");
    HSSFSheet worksheet = workbook.getSheetAt(0);
    int numberOfEmails = 0, numberOfAttributes = 0;

    for (int i = 0;; i++) {

        HSSFRow row1 = worksheet.getRow(i);
        if (row1 == null)
            break;
        if (row1.getCell(0) == null)
            break;
        String s = row1.getCell(0).getStringCellValue();
        System.out.println("Reading s:" + s);
        if (s != null && s != "") {
            numberOfEmails++;/*  w w  w.ja va 2 s  . c  o  m*/
        } else {
            break;
        }

    }

    HSSFRow row1 = worksheet.getRow(0);
    for (int i = 0;; i++) {
        HSSFCell cell = row1.getCell(i);
        if (cell == null)
            break;
        if (cell.getStringCellValue() == null | cell.getStringCellValue() == "") {
            break;

        }

        numberOfAttributes++;
    }
    MailerData mailerData = new MailerData(numberOfAttributes, numberOfEmails);
    for (int i = 0; i < numberOfAttributes; i++) {
        HSSFCell cell = row1.getCell(i);
        if (cell == null)
            break;
        if (cell.getStringCellValue() == null | cell.getStringCellValue() == "") {
            break;

        }
        mailerData.attributes[i] = cell.getStringCellValue().trim();

    }

    for (int i = 0; i < numberOfEmails; i++) {
        HSSFRow row = worksheet.getRow(i);
        if (i != 0)
            mailerData.listOfAddresses[i - 1] = row.getCell(0).getStringCellValue();
        for (int j = 0; j < numberOfAttributes; j++) {
            HSSFCell cell = row.getCell(j);
            mailerData.attributeMatrix[i][j] = cell.getStringCellValue().trim();

        }
    }

    file.close();
    return mailerData;

}

---

From source file:com.verticon.treatment.poi.handlers.NamingImportHandler.java

License:Open Source License

static String getStringValue(HSSFRow row, int index) throws Exception {
    String result = null;/*  w  ww  .  j  a v a 2 s.  com*/

    if (index != -1) {

        HSSFCell cellContents = row.getCell(index);
        if (cellContents != null) {
            switch (cellContents.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                result = cellContents.getStringCellValue();
                break;

            default:
                throw new Exception(
                        "The string value in a critical spreadsheet cell has the wrong data type. Please make sure your spreadsheet column number "
                                + index + " is set to the string datatype. Row: " + row.getRowNum()
                                + " Column Index: " + index);

            }

        }

    }
    return result;
}

---

From source file:com.verticon.treatment.poi.handlers.PoiUtils.java

License:Open Source License

static String getStringValue(HSSFRow row, EStructuralFeature feature, int index)
        throws MissingCriticalDataException {
    String result = null;/*from w w w  .  j a  v  a 2 s.c om*/

    if (index != -1) {
        try {
            HSSFCell cellContents = row.getCell(index);
            if (cellContents != null) {
                switch (cellContents.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    result = cellContents.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    result = Double.toString(cellContents.getNumericCellValue());
                    result = result.replace(".0", "");
                    break;
                default:
                    throw new MissingCriticalDataException(
                            "The string value in a critical spreadsheet cell has the wrong data type (id: "
                                    + cellContents.getCellType()
                                    + "). Please make sure your spreadsheet column number " + index
                                    + " is set to the string datatype.",
                            index, feature, row.getRowNum());

                }

            }
        } catch (RuntimeException e) {
            // just fall through and return a null
        }
    }
    return result;
}

---

From source file:com.verticon.treatment.poi.handlers.PoiUtils.java

License:Open Source License

static String getValue(HSSFRow row, EStructuralFeature feature, int index) {
    String result = null;//from   www .  j a va 2  s.  c o  m

    if (index != -1) {
        try {
            HSSFCell cellContents = row.getCell(index);
            if (cellContents != null) {
                switch (cellContents.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    result = cellContents.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    double num = cellContents.getNumericCellValue();
                    long l = (long) num;
                    result = Long.toString(l);
                    break;
                default:
                    break;
                }

            }
        } catch (RuntimeException e) {
            //just fall through and return a null
        }
    }
    return result;
}

---

From source file:com.weibo.datasys.parser.office.extractor.ExcelParse.java

License:Open Source License

private FileData extractor(File filePath) {
    FileData fData = new FileData();
    fData.setName(filePath.getName());//from   ww  w.j av  a  2s . c  om
    StringBuffer sBuffer = new StringBuffer();
    HSSFWorkbook workbook = null;
    try {
        workbook = new HSSFWorkbook(new FileInputStream(filePath));
        for (int iSheets = 0; iSheets < workbook.getNumberOfSheets(); ++iSheets) {
            HSSFSheet sheet = workbook.getSheetAt(iSheets);
            for (int iRow = 0; iRow < sheet.getLastRowNum(); ++iRow) {
                HSSFRow row = sheet.getRow(iRow);
                for (int iCell = 0; iCell < row.getLastCellNum(); ++iCell) {
                    HSSFCell cell = row.getCell(iCell);
                    if (null != cell) {
                        if (0 == cell.getCellType()) {
                            sBuffer.append(String.valueOf(cell.getNumericCellValue()));
                            sBuffer.append(SEGMENT_CHAR);
                        } else if (1 == cell.getCellType()) {
                            sBuffer.append(cell.getStringCellValue().trim());
                            sBuffer.append(SEGMENT_CHAR);
                        }
                    }
                }
            }
        }
        fData.setContent(sBuffer.toString());
    } catch (Exception e) {
        LOG.error("", e);
    }
    return fData;
}

---

From source file:com.xhsoft.framework.common.file.ExcelHandle.java

License:Open Source License

/**
 * ????.xls?//from   ww  w.  j av  a  2 s .  c o m
 * @params {:,:}
 * @return String
 * @author lijiangwei
 * @since 2012-11-12
 */
private String getCellValue(HSSFCell xls_cell) {
    String value = "";

    switch (xls_cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        value = "";
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(xls_cell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        String.valueOf(xls_cell.getCellFormula());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        value = String.valueOf(xls_cell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_STRING:
        value = String.valueOf(xls_cell.getStringCellValue());
        break;
    default:
        break;
    }

    return value;
}

---

From source file:com.xx.platform.util.tools.ms.ExcelExtractor.java

License:Apache License

public String extractText(POIFSFileSystem poifs) throws Exception {
    StringBuffer resultText = new StringBuffer();
    HSSFWorkbook wb = new HSSFWorkbook(poifs, true);
    if (wb == null) {
        return "";
    }//  www.j a  va2  s .c  o  m

    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();

            for (int k = 0; k < cNum; k++) {
                if ((cell = row.getCell((short) k)) != null) {
                    /*if(HSSFDateUtil.isCellDateFormatted(cell) == true) {
                        resultText += cell.getDateCellValue().toString() + " ";
                      } else
                     */
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        resultText.append(cell.getStringCellValue());
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        Double d = new Double(cell.getNumericCellValue());
                        resultText.append(d.toString());
                    }
                    /* else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
                         resultText += cell.getCellFormula() + " ";
                       }
                     */
                }
            }
        }
    }
    return resultText.toString();
}

---

From source file:com.xx.platform.util.tools.ms.ExcelExtrator.java

License:Apache License

public String extractText(POIFSFileSystem poifs) throws Exception {
    List<Map<String, String>> list = new ArrayList<Map<String, String>>();
    String text = null;/*from w  w w .  ja v  a 2s. c o  m*/
    try {
        HSSFWorkbook wb = new HSSFWorkbook(poifs, true);
        if (wb == null) {
            return null;
        }

        HSSFSheet sheet;
        HSSFRow row;
        HSSFCell cell;
        int sNum = 0;
        int rNum = 0;
        int cNum = 0;

        sNum = wb.getNumberOfSheets();
        for (int i = 0; i < sNum; i++) {
            if ((sheet = wb.getSheetAt(i)) == null) {
                continue;
            }
            String[] key = null; //field
            boolean init = false; //key 
            rNum = sheet.getLastRowNum();
            for (int j = 0; j <= rNum; j++) {
                if ((row = sheet.getRow(j)) == null) {
                    continue;
                }
                Map<String, String> rowdata = new HashMap<String, String>();
                cNum = row.getLastCellNum();
                if (!init)
                    key = new String[cNum];
                String value = "";
                StringBuffer content = new StringBuffer();
                for (int k = 0; k < cNum; k++) {
                    if ((cell = row.getCell((short) k)) != null) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            value = cell.getStringCellValue();
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            value = d.toString();
                        } else
                            value = "";
                        if (init) {
                            content.append(value);
                        } else {
                            key[k] = value;
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        text = "";
    }
    return text;

}

---

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 *    ?cell/*w w  w .j a v a 2 s.co  m*/
 * @param cell
 * @return
 */
private static String getCellValue(HSSFCell cell) {
    if (cell == null) {
        return "";
    }
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC: {
        DecimalFormat df = new DecimalFormat("0");
        return df.format(cell.getNumericCellValue());
    }
    default:
        return String.valueOf(cell.getStringCellValue());
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• »