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

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

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

Introduction

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

Prototype

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.taobao.tddl.common.SQLPreParserTest.java

License:Open Source License

public static void main(String[] args) throws IOException {
    //String fileName = "D:/12_code/tddl/trunk/tddl/tddl-parser/sqlsummary-icsg-db0-db15-group-20100901100337-export.xlsx";
    //String fileName = "D:/12_code/tddl/trunk/tddl/tddl-parser/sqlsummary-tcsg-instance-group-20100901100641-export.xlsx";

    int count = 0;
    long time = 0;

    File home = new File(System.getProperty("user.dir") + "/appsqls");
    for (File f : home.listFiles()) {
        if (f.isDirectory() || !f.getName().endsWith(".xlsx")) {
            continue;
        }/*www. jav a2  s.c o  m*/
        log.info("---------------------- " + f.getAbsolutePath());
        faillog.info("---------------------- " + f.getAbsolutePath());
        Workbook wb = new XSSFWorkbook(new FileInputStream(f));
        Sheet sheet = wb.getSheetAt(0);
        for (Row row : sheet) {
            Cell cell = row.getCell(2);
            if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                String sql = cell.getStringCellValue();

                long t0 = System.currentTimeMillis();
                String tableName = SQLPreParser.findTableName(sql);
                time += System.currentTimeMillis() - t0;
                count++;

                log.info(tableName + " <-- " + sql);
                if (tableName == null) {
                    sql = sql.trim().toLowerCase();
                    if (isCRUD(sql)) {
                        System.out.println("failed:" + sql);
                        faillog.error("failed:" + sql);
                    }
                }
            }
        }
        wb = null;
    }
    faillog.fatal("------------------------------- finished --------------------------");
    faillog.fatal(count + " sql parsed, total time:" + time + ". average time use per sql:"
            + (double) time / count + "ms/sql");
}

From source file:com.tecacet.jflat.excel.PoiExcelReader.java

License:Apache License

private String getCellContentAsString(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.  java2  s .  c om
            double d = cell.getNumericCellValue();
            // TODO find a flexible enough format for all numeric types
            return numberFormat.format(d);
            // return Double.toString(d);
        }
    case Cell.CELL_TYPE_BOOLEAN:
        boolean b = cell.getBooleanCellValue();
        return Boolean.toString(b);
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        byte bt = cell.getErrorCellValue();
        return Byte.toString(bt);
    default:
        return cell.getStringCellValue();

    }
}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* @param rowIndex - Index of row 0,1,2 etc.
* @param colIndex - Index of col 0,1,2 etc.
* 
*///from  w ww  .j  a  v  a 2  s. c  om
private String readExcelCol(int sheetIndex, int rowIndex, int colIndex) {
    String cellContents = "";
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        cellContents = cell.getStringCellValue();

    } catch (IOException e) {
        // TODO Auto-generated catch block
        WmLog.printMessage("ERROR in reading =" + this.fileName + "Excel Sheet Index=" + sheetIndex
                + " Excel Row Index=" + rowIndex + "Excel Col Index=" + colIndex);
        e.printStackTrace();
        return null;
    }

    return (cellContents);

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* @param rowIndex - Index of row 0,1,2 etc.
* 
*//*from ww  w  .  j ava2s.  c o  m*/
private List<String> readExcelRow(int sheetIndex, int rowIndex) {
    String cellContents = "";
    ArrayList<String> rowVal = new ArrayList<String>();
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        Iterator<Cell> colIt = row.cellIterator();
        while (colIt.hasNext()) {
            Cell cell = colIt.next();
            cellContents = cell.getStringCellValue();
            rowVal.add(cellContents);
        }

    } catch (IOException e) {
        WmLog.printMessage("ERROR in reading Excel File=" + this.fileName + " Sheet Index=" + sheetIndex
                + " Excel Row Index=" + rowIndex + " " + e.getMessage());
        // TODO Auto-generated catch block
        e.printStackTrace();
        return null;
    }

    return (rowVal);

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* 
*//*from  w  ww .  j a v a2 s. c o m*/
private List<ArrayList> readExcel(int sheetIndex) {
    String cellContents = "";
    ArrayList<ArrayList> excel = new ArrayList<ArrayList>();
    ArrayList<String> rowVal = new ArrayList<String>();
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        Iterator<Row> rowIt = sheet.rowIterator();
        while (rowIt.hasNext()) {
            Row row = rowIt.next();
            Iterator<Cell> colIt = row.cellIterator();
            while (colIt.hasNext()) {
                Cell cell = colIt.next();
                cellContents = cell.getStringCellValue();
                rowVal.add(cellContents);
            }
            excel.add(rowVal);
        }

    } catch (IOException e) {
        WmLog.printMessage("ERROR in reading Excel Sheet Index=" + sheetIndex + " Excel File=" + this.fileName
                + " " + e.getMessage());
        // TODO Auto-generated catch block
        e.printStackTrace();
        return null;
    }

    return (excel);

}

From source file:com.tm.hiber.service.TMDataOperationServiceImpl.java

private String getCellData(Cell objCell) {
    mLogger.log(Level.INFO, "getCellData--Starts");
    String response;/*from ww  w.j av  a 2 s. c o m*/
    switch (objCell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        Double d = objCell.getNumericCellValue();
        response = String.valueOf(d.intValue());
        break;
    case Cell.CELL_TYPE_STRING:
        response = objCell.getStringCellValue();
        break;
    default:
        response = objCell.getStringCellValue();
        break;
    }
    mLogger.log(Level.INFO, "getCellData--Ends");
    return response;
}

From source file:com.ucuenca.dao.BaseExcelDao.java

/**
 * This method gets columns/*from   ww w  . ja v  a  2 s  . c o  m*/
 *
 * @param sheet
 * @Author pablo and adrian
 * @return list cols
 */
public List<Column> getColumn(Sheet sheet) {
    Iterator<Row> rowIterator = sheet.iterator();
    List<Column> columns = new ArrayList<Column>();
    while (rowIterator.hasNext()) {
        Column colum = new Column_Excel();
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                colum.setTitle(cell.getStringCellValue());
            } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                colum.setTitle(cell.getStringCellValue());
            }

        }
        columns.add(colum);
    }

    return columns;
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java

License:Open Source License

/**
 * Set's cell value for the newCell. It will be the same as shiftedCell
 * unless sequenceIncrement is not null, in that case the last digits are
 * replaced/* www. ja v a  2 s.  c  o m*/
 * 
 * @param shiftedCell
 *            Source cell
 * @param newCell
 *            Resulting new cell
 * @param sequenceIncrement
 *            not null to increase the number in source cell
 */
private void shiftString(Cell shiftedCell, Cell newCell, Double sequenceIncrement) {
    if (sequenceIncrement != null) {
        int dif;
        if (shiftedCell.getColumnIndex() != newCell.getColumnIndex()) {
            // shift column indexes
            dif = newCell.getColumnIndex() - shiftedCell.getColumnIndex();
        } else {
            dif = newCell.getRowIndex() - shiftedCell.getRowIndex();
        }

        Matcher matcher = stringSequencePattern.matcher(shiftedCell.getStringCellValue());
        if (matcher.find()) {
            String base = shiftedCell.getStringCellValue().substring(0, matcher.start());
            String currentValue = matcher.group();
            Double currVal = Double.parseDouble(currentValue);
            newCell.setCellValue(base + (int) Math.abs(currVal + sequenceIncrement * dif));
        } else {
            newCell.setCellValue(shiftedCell.getStringCellValue());
        }
    } else {
        newCell.setCellValue(shiftedCell.getStringCellValue());
    }
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java

License:Open Source License

/**
 * Returns an array with String values in column with columnIndex from row
 * r1 to row r2 in activeSheet until first non String cell or null value
 * Used by/*from  w w  w .  ja v  a 2s .  c  o  m*/
 * {@link CellSelectionShifter#getColumnSequenceIncrement(int, int, int)}
 * 
 * @param activeSheet
 *            Sheet where the cells are going to be taken from
 * @param columnIndex
 *            Defines the origin of the cell values to be returned, 1-based
 * @param r1
 *            First row of the column to be returned, 1-based
 * @param r2
 *            Last row of the column to be returned, 1-based
 * @return String array with values
 */
private String[] getColumnStringValues(Sheet activeSheet, int columnIndex, int r1, int r2) {
    String[] result = new String[r2 - r1 + 1];
    Cell cell;
    Row row;
    for (int i = r1; i <= r2; i++) {
        row = activeSheet.getRow(i - 1);
        if (row != null) {
            cell = row.getCell(columnIndex - 1);
            if (cell != null && cell.getCellType() == CELL_TYPE_STRING) {
                result[i - r1] = cell.getStringCellValue();
            } else {
                break;
            }
        } else {
            break;
        }
    }
    return result;
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java

License:Open Source License

/**
 * Returns an array with String values in row from column c1 to column c2
 * until first non String cell or null value. Used by
 * {@link CellSelectionShifter#getRowSequenceIncrement(int, int, int)}
 * /*from  w w  w  .ja  v a  2s. co m*/
 * @param row
 *            Row where the cells are going to be taken from
 * @param c1
 *            First column of the row to be returned, 1-based
 * @param c2
 *            Last column of the column to be returned, 1-based
 * @return String array with values
 */
private String[] getRowStringValues(Row row, int c1, int c2) {
    String[] result = new String[c2 - c1 + 1];
    Cell cell;
    for (int i = c1; i <= c2; i++) {
        cell = row.getCell(i - 1);
        if (cell != null && cell.getCellType() == CELL_TYPE_STRING) {
            result[i - c1] = cell.getStringCellValue();
        } else {
            break;
        }
    }
    return result;
}