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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. 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.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

/**
 * ?Excel????//from  w w w.j av a  2 s .  c  o m
 * @param filename Excel???
 * @throws IOException Excel???????
 */
public ExcelUtils(String filename) throws IOException {
    this.filename = filename;
    InputStream is = new FileInputStream(filename);
    workbook = new HSSFWorkbook(is);
    inputDataSheet = workbook.getSheet(Constants.INPUT_DATA_SHEET_NAME);
    if (inputDataSheet == null) {
        throw new IOException(
                "Excel: " + filename + "??????");
    }
    outputDataSheet = workbook.getSheet(Constants.OUTPUT_DATA_SHEET_NAME);
    if (outputDataSheet == null) {
        throw new IOException(
                "Excel: " + filename + "??????");
    }
    testConditionSheet = workbook.getSheet(Constants.TEST_CONDITION_SHEET_NAME);
    if (testConditionSheet == null) {
        throw new IOException("Excel: " + filename
                + "???????");
    }

    HSSFCell tableNameCell = getCell(testConditionSheet, ConditionSheetItem.TABLE_NAME.getRow(),
            ConditionSheetItem.TABLE_NAME.getCol() + 1);
    tablename = tableNameCell.getStringCellValue();
    if (tablename == null || tablename.length() == 0) {
        throw new IOException(
                "Excel: " + filename + "?????????");
    }

    HSSFCell rowMatchingConditionCell = getCell(testConditionSheet,
            ConditionSheetItem.ROW_MATCHING_CONDITION.getRow(),
            ConditionSheetItem.ROW_MATCHING_CONDITION.getCol() + 1);
    String rowMatchingConditionStr = rowMatchingConditionCell.getStringCellValue();
    if (rowMatchingConditionStr == null || rowMatchingConditionStr.length() == 0) {
        throw new IOException("Excel: " + filename
                + "?????????");
    }
    rowMatchingCondition = RowMatchingCondition.getConditonByJapanseName(rowMatchingConditionStr);
    if (rowMatchingCondition == null) {
        throw new IOException("Excel: " + filename
                + "????????????");
    }
    columnInfos = createColumnInfos();
}

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

/**
 * ?????????()??//from  w w w . j  a va  2  s.co m
 * @param sheet ?
 * @param item ??
 * @param row ?
 * @return ??
 */
private String getStringCellValue(HSSFSheet sheet, ConditionSheetItem item, HSSFRow row) {
    HSSFCell cell = getCell(item, row);
    String ret;
    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        double dval = cell.getNumericCellValue();
        ret = Double.toString(dval);
        ret = ret.replaceAll("\\.0*$", "");
    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        ret = "";
    } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
        String fmt = "Excel???????????? file = %s, sheet = %s, row = %d, col = %d";
        int rownum = row.getRowNum() + 1;
        int col = item.getCol() + 1;
        String msg = String.format(fmt, filename, sheet.getSheetName(), rownum, col);
        throw new InvalidExcelBookException(msg);
    } else {
        ret = cell.getStringCellValue();
    }
    return ret;
}

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

private Double getDubleCellValue(HSSFSheet sheet, ConditionSheetItem item, HSSFRow row) {
    HSSFCell cell = getCell(item, row);
    Double ret;// ww  w  . j  a v  a 2  s .co  m
    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        String str = cell.getStringCellValue();
        if (str == null || str.length() == 0) {
            ret = null;
        } else {
            try {
                ret = Double.parseDouble(str);
            } catch (NumberFormatException e) {
                String fmt = "Excel???????????? file = %s, sheet = %s, row = %d, col = %d";
                int rownum = row.getRowNum() + 1;
                int col = item.getCol() + 1;
                String msg = String.format(fmt, filename, sheet.getSheetName(), rownum, col);
                throw new InvalidExcelBookException(msg);
            }
        }
    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        ret = null;
    } else if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
        String fmt = "Excel???????????? file = %s, sheet = %s, row = %d, col = %d";
        int rownum = row.getRowNum() + 1;
        int col = item.getCol() + 1;
        String msg = String.format(fmt, filename, sheet.getSheetName(), rownum, col);
        throw new InvalidExcelBookException(msg);
    } else {
        ret = cell.getNumericCellValue();
    }
    return ret;
}

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

/**
 * ?StringOption??//from  w  w  w . j  a  va2s  .  com
 * @param cell ?
 * @return ?
 */
private StringOption getStringOption(HSSFCell cell) {
    String str;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        str = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        if (cell.getBooleanCellValue()) {
            str = "1";
        } else {
            str = "0";
        }
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            double d = cell.getNumericCellValue();
            Date date = DateUtil.getJavaDate(d);
            str = dateTimeFormat.format(date);
        } else {
            double d = cell.getNumericCellValue();
            str = Double.toString(d);
            str = str.replaceAll("\\.0*$", "");
        }
        break;
    case Cell.CELL_TYPE_STRING:
        str = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_ERROR:
    case Cell.CELL_TYPE_FORMULA:
    default:
        String msg = createCellTypeMismatchExceptionMsg(cell, "");
        throw new CellTypeMismatchException(msg);
    }
    StringOption stringOption = new StringOption();
    stringOption.modify(str);
    return stringOption;
}

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

/**
 * ?DecimalOption??//from w w w .j  a  v  a  2s . co  m
 * @param cell ?
 * @return ?
 */
private DecimalOption getDecimalOption(HSSFCell cell) {
    BigDecimal bigDecimal;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        bigDecimal = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        if (cell.getBooleanCellValue()) {
            bigDecimal = new BigDecimal(1);
        } else {
            bigDecimal = new BigDecimal(0);
        }
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            String msg = createCellTypeMismatchExceptionMsg(cell, "DECIMAL");
            throw new CellTypeMismatchException(msg);
        } else {
            double d = cell.getNumericCellValue();
            if (d < EXCEL_MIN_LONG || EXCEL_MAX_LONG < d) {
                String msg = createExceptionMsg(cell, "????(" + d + ")");
                throw new NumberFormatException(msg);
            }
            long l = (long) d;
            if (l != d) {
                String msg = createExceptionMsg(cell,
                        "???DECIMAL???????");
                throw new NumberFormatException(msg);
            }
            String str = Double.toString(d);
            str = str.replaceAll("\\.0*$", "");
            bigDecimal = new BigDecimal(str);
        }
        break;
    case Cell.CELL_TYPE_STRING:
        String str = cell.getStringCellValue();
        try {
            bigDecimal = new BigDecimal(str);
        } catch (NumberFormatException e) {
            String msg = createExceptionMsg(cell, "DECIMAL???????");
            throw new NumberFormatException(msg);
        }
        break;
    case Cell.CELL_TYPE_ERROR:
    case Cell.CELL_TYPE_FORMULA:
    default:
        String msg = createCellTypeMismatchExceptionMsg(cell, "DECIMAL");
        throw new CellTypeMismatchException(msg);
    }
    DecimalOption decimalOption = new DecimalOption();
    decimalOption.modify(bigDecimal);
    return decimalOption;
}

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

/**
 * ?java.util.Data??/*w w w  .ja  v  a  2 s.  c o  m*/
 * @param cell ?
 * @return ?
 */
private Date getDate(HSSFCell cell) {
    Date date;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        date = null;
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            double d = cell.getNumericCellValue();
            date = DateUtil.getJavaDate(d);
        } else {
            String msg = createCellTypeMismatchExceptionMsg(cell, "");
            throw new CellTypeMismatchException(msg);
        }
        break;
    case Cell.CELL_TYPE_STRING:
        String str = cell.getStringCellValue();
        try {
            date = dateTimeFormat.parse(str);
        } catch (Exception e) {
            try {
                date = dateFormat.parse(str);
            } catch (Exception e2) {
                String msg = createCellTypeMismatchExceptionMsg(cell, "");
                throw new CellTypeMismatchException(msg);
            }
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
    case Cell.CELL_TYPE_ERROR:
    case Cell.CELL_TYPE_FORMULA:
    default:
        String msg = createCellTypeMismatchExceptionMsg(cell, "");
        throw new CellTypeMismatchException(msg);
    }
    return date;
}

From source file:com.asakusafw.testtools.excel.ExcelUtils.java

License:Apache License

/**
 * ?Long??/*from   w  ww . ja  va 2  s.com*/
 * @param cell ?
 * @return ?
 */
private Long getLong(HSSFCell cell) {
    Long l;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        l = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        if (cell.getBooleanCellValue()) {
            l = 1L;
        } else {
            l = 0L;
        }
        break;
    case Cell.CELL_TYPE_NUMERIC:
        double d = cell.getNumericCellValue();
        if (d < EXCEL_MIN_LONG || EXCEL_MAX_LONG < d) {
            String msg = createExceptionMsg(cell, "????(" + d + ")");
            throw new NumberFormatException(msg);
        }
        l = (long) d;
        if ((double) l != d) {
            String msg = createExceptionMsg(cell,
                    "??????????");
            throw new NumberFormatException(msg);
        }
        break;
    case Cell.CELL_TYPE_STRING:
        try {
            String str = cell.getStringCellValue();
            l = Long.parseLong(str);
        } catch (Exception e) {
            String msg = createCellTypeMismatchExceptionMsg(cell, "");
            throw new CellTypeMismatchException(msg);
        }
        break;
    case Cell.CELL_TYPE_ERROR:
    case Cell.CELL_TYPE_FORMULA:
    default:
        String msg = createCellTypeMismatchExceptionMsg(cell, "");
        throw new CellTypeMismatchException(msg);
    }
    return l;
}

From source file:com.b510.excel.client.HSSFReadWrite.java

License:Apache License

/**
 * Method main/*from  ww w.ja  v  a 2  s.c  o m*/
 *
 * Given 1 argument takes that as the filename, inputs it and dumps the cell
 * values/types out to sys.out.<br/>
 *
 * given 2 arguments where the second argument is the word "write" and the
 * first is the filename - writes out a sample (test) spreadsheet see
 * {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/>
 *
 * given 2 arguments where the first is an input filename and the second an
 * output filename (not write), attempts to fully read in the spreadsheet
 * and fully write it out.<br/>
 *
 * given 3 arguments where the first is an input filename and the second an
 * output filename (not write) and the third is "modify1", attempts to read
 * in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col
 * 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1".
 * If you take the output from the write test, you'll have a valid scenario.
 */
public static void main(String[] args) {
    String[] name = new String[2];
    name[0] = "HSSFReadWrite.xlsx";
    name[1] = "write";
    if (name.length < 1) {
        System.err.println("At least one argument expected");
        return;
    }
    String fileName = name[0];
    try {
        if (name.length < 2) {

            HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);

            System.out.println("Data dump:\n");

            for (int k = 0; k < wb.getNumberOfSheets(); k++) {
                HSSFSheet sheet = wb.getSheetAt(k);
                int rows = sheet.getPhysicalNumberOfRows();
                System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s).");
                for (int r = 0; r < rows; r++) {
                    HSSFRow row = sheet.getRow(r);
                    if (row == null) {
                        continue;
                    }

                    int cells = row.getPhysicalNumberOfCells();
                    System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s).");
                    for (int c = 0; c < cells; c++) {
                        HSSFCell cell = row.getCell(c);
                        String value = null;

                        switch (cell.getCellType()) {

                        case HSSFCell.CELL_TYPE_FORMULA:
                            value = "FORMULA value=" + cell.getCellFormula();
                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC:
                            value = "NUMERIC value=" + cell.getNumericCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_STRING:
                            value = "STRING value=" + cell.getStringCellValue();
                            break;

                        default:
                        }
                        System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                    }
                }
            }
        } else if (name.length == 2) {
            if (name[1].toLowerCase().equals("write")) {
                System.out.println("Write mode");
                long time = System.currentTimeMillis();
                HSSFReadWrite.testCreateSampleSheet(fileName);

                System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time");
            } else {
                System.out.println("readwrite test");
                HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
                FileOutputStream stream = new FileOutputStream(name[1]);

                wb.write(stream);
                stream.close();
            }
        } else if (name.length == 3 && name[2].toLowerCase().equals("modify1")) {
            // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to
            // string "MODIFIED CELL!!"

            HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
            FileOutputStream stream = new FileOutputStream(name[1]);
            HSSFSheet sheet = wb.getSheetAt(0);

            for (int k = 0; k < 25; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            for (int k = 74; k < 100; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            HSSFRow row = sheet.getRow(39);
            HSSFCell cell = row.getCell(3);
            cell.setCellValue("MODIFIED CELL!!!!!");

            wb.write(stream);
            stream.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.bayareasoftware.chartengine.ds.util.ExcelDump.java

License:Apache License

private static String cell2string(HSSFCell c) {
    if (c == null) {
        return "<i>NULL CELL</i>";
    }/*from w w w  .j  a v a  2 s .co  m*/
    int type = c.getCellType();
    String t = null, v = null;
    switch (type) {
    case HSSFCell.CELL_TYPE_BLANK:
        t = "BLANK";
        v = "";
        break;
    case HSSFCell.CELL_TYPE_STRING:
        t = "STRING";
        v = c.getStringCellValue();
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (ExcelInference.isCellDateFormatted(c)) {
            t = "DATE";
            v = c.getDateCellValue() + "";
        } else {
            t = "NUMERIC";
            v = c.getNumericCellValue() + "";
        }
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        t = "ERROR";
        v = "(errByte=" + c.getErrorCellValue() + "/toString=" + c + ")";
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        t = "FORMULA";
        v = c.getCellFormula();
        break;
    default:
        t = "(UNKNOWN TYPE: " + type + ")";
        v = c.toString();
        break;
    }
    short style = c.getCellStyle().getDataFormat();
    return v + "<br/>(" + t + ")<br/>dataformat=0x" + Integer.toHexString(style);
}

From source file:com.beginner.core.utils.ObjectExcelRead.java

License:Apache License

/**
 * @param filepath ///*from w ww.  j  a  v a 2s .c  om*/
 * @param filename //??
 * @param startrow //?
 * @param startcol //?
 * @param sheetnum //sheet
 * @return list
 */
@SuppressWarnings("deprecation")
public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol,
        int sheetnum) {
    List<Object> varList = new ArrayList<Object>();

    try {
        File target = new File(filepath, filename);
        FileInputStream fi = new FileInputStream(target);
        HSSFWorkbook wb = new HSSFWorkbook(fi);
        HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 0
        int rowNum = sheet.getLastRowNum() + 1; //???

        for (int i = startrow; i < rowNum; i++) { //

            PageData varpd = new PageData();
            HSSFRow row = sheet.getRow(i); //
            int cellNum = row.getLastCellNum(); //????

            for (int j = startcol; j < cellNum; j++) { //

                HSSFCell cell = row.getCell(Short.parseShort(j + ""));
                String cellValue = null;
                if (null != cell) {
                    switch (cell.getCellType()) { // excel?????
                    case 0:
                        cellValue = String.valueOf((int) cell.getNumericCellValue());
                        break;
                    case 1:
                        cellValue = cell.getStringCellValue();
                        break;
                    case 2:
                        cellValue = cell.getNumericCellValue() + "";
                        // cellValue = String.valueOf(cell.getDateCellValue());
                        break;
                    case 3:
                        cellValue = "";
                        break;
                    case 4:
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    case 5:
                        cellValue = String.valueOf(cell.getErrorCellValue());
                        break;
                    }
                } else {
                    cellValue = "";
                }
                varpd.put("var" + j, cellValue);
            }
            varList.add(varpd);
        }
    } catch (Exception e) {
        System.out.println(e);
    }
    return varList;
}