Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.efficio.fieldbook.web.nursery.service.impl.ImportGermplasmFileServiceImpl.java

License:Open Source License

/**
 * Gets the cell string value.//from   w  ww .j a v  a 2  s .  c om
 *
 * @param sheetNumber the sheet number
 * @param rowNumber the row number
 * @param columnNumber the column number
 * @param followThisPosition the follow this position
 * @return the cell string value
 */
private String getCellStringValue(Integer sheetNumber, Integer rowNumber, Integer columnNumber,
        Boolean followThisPosition) {
    if (followThisPosition) {
        currentSheet = sheetNumber;
        currentRow = rowNumber;
        currentColumn = columnNumber;
    }

    try {
        Sheet sheet = wb.getSheetAt(sheetNumber);
        Row row = sheet.getRow(rowNumber);
        Cell cell = row.getCell(columnNumber);
        return cell.getStringCellValue();
    } catch (IllegalStateException e) {
        Sheet sheet = wb.getSheetAt(sheetNumber);
        Row row = sheet.getRow(rowNumber);
        Cell cell = row.getCell(columnNumber);
        return String.valueOf(Integer.valueOf((int) cell.getNumericCellValue()));
    } catch (NullPointerException e) {
        return "";
    }
}

From source file:com.elecnor.ecosystem.serviceimpl.BulkUploadServiceImpl.java

@SuppressWarnings({ "unchecked", "rawtypes" })
public HashMap<String, Object> saveFile(Workbook workBook, String className, UserDetail userDetail)
        throws JsonSyntaxException, NoSuchMethodException, SecurityException, IllegalAccessException,
        IllegalArgumentException, InvocationTargetException, ClassNotFoundException {
    Sheet sheet = workBook.getSheetAt(ConstantUtil.SHEETNUM);
    Row headerRow = sheet.getRow(0);
    ArrayList<String> header = new ArrayList<String>();
    int j = 0;// w  w w.  ja  v  a2  s  .c  o  m
    boolean isErrorOccured = false;
    ArrayList list = new ArrayList();
    ArrayList<ExcelErrorDetails> exceptionlist = new ArrayList<ExcelErrorDetails>();
    while (headerRow.getCell(j) != null) {
        header.add(headerRow.getCell(j).getStringCellValue());
        j++;
    }

    int loopCounter;
    String jsonString = "";
    //Since the first row contains hidden bean property values and second row contains column names in the excel sheet
    //The data starts from 3rd row i.e loopCounter=2
    for (loopCounter = 2; loopCounter <= sheet.getLastRowNum(); loopCounter++) {
        Row row = sheet.getRow(loopCounter);
        if (row == null) {
            continue;
        }

        HashMap<String, Object> rowContent = new HashMap<String, Object>();

        for (int k = 0; k < header.size(); k++) {
            String rowContents = "";
            if (row.getCell(k) != null) {
                rowContents = "" + row.getCell(k);
                rowContent.put(header.get(k), rowContents);
            } else
                rowContent.put(header.get(k), rowContents);
        }
        jsonString = util.getJsonResultWithoutExposeString(rowContent);
        Gson gson = new Gson();
        if (className.equalsIgnoreCase("SLicenseDirectory")
                || className.equalsIgnoreCase("LLicenseDirectory")) {
            className = className.substring(1, className.length());
        }
        String beanName = ConstantUtil.PACKAGE_NAME + className;
        Class projectType = Class.forName(beanName);

        if (!validateData(gson, jsonString, projectType, exceptionlist, isErrorOccured, loopCounter)) {
            list.add(setCommonMethods(gson.fromJson(jsonString, projectType), userDetail));

        }
        jsonString = "";
    }
    if (exceptionlist.isEmpty())
        exceptionlist = null;
    bulkUploadDAO.saveBulkUpdate(list);
    System.out.println("the list is as follows");
    System.out.println(util.responseBuilder(exceptionlist));
    return util.responseBuilder(exceptionlist);
}

From source file:com.elecnor.ecosystem.serviceimpl.LicenseDirectoryServiceImpl.java

License:Open Source License

private Integer getSchemaValidation(Workbook workBook) throws Exception {
    ArrayList<String> excelHeader = new ArrayList<String>();

    int j = 0;//  w w w  . j a va2s.  c  o  m
    // Strings for reading from property file
    String sheetNumberString = "STATE_LICENSE_SHEETNUM";
    int sheetNumber = readFromHeader(null, sheetNumberString);
    // Values obtained from property file
    String headerRowStringForProp = "STATE_LICENSE_HEADER_ROWNUM";
    Sheet sheet = workBook.getSheetAt(sheetNumber);

    int headerRowNum = readFromHeader(null, headerRowStringForProp);
    Row rowHeader = sheet.getRow(headerRowNum);
    while (rowHeader.getCell(j) != null) {
        String headerVal = rowHeader.getCell(j).getStringCellValue();
        excelHeader.add(headerVal);
        j++;
    }
    return readFromHeader(excelHeader, null);
}

From source file:com.elecnor.ecosystem.serviceimpl.LicenseDirectoryServiceImpl.java

License:Open Source License

private Integer getLocalSchemaValidation(Workbook workBook) throws Exception {
    ArrayList<String> excelHeader = new ArrayList<String>();

    int j = 0;//from   w w  w.ja v  a 2 s.  c o m
    // Strings for reading from property file
    String sheetNumberString = "LOCAL_LICENSE_SHEETNUM";
    int sheetNumber = readFromLocalHeader(null, sheetNumberString);
    // Values obtained from property file
    String headerRowStringForProp = "LOCAL_LICENSE_HEADER_ROWNUM";
    Sheet sheet = workBook.getSheetAt(sheetNumber);

    int headerRowNum = readFromLocalHeader(null, headerRowStringForProp);
    Row rowHeader = sheet.getRow(headerRowNum);
    while (rowHeader.getCell(j) != null) {
        String headerVal = rowHeader.getCell(j).getStringCellValue();
        excelHeader.add(headerVal);
        j++;
    }
    return readFromLocalHeader(excelHeader, null);
}

From source file:com.eleven0eight.xls2json.App.java

License:Open Source License

public String convertXlsToJson(FileInputStream fis) throws Exception {

    Workbook workbook = WorkbookFactory.create(fis);
    Sheet sheet = workbook.getSheetAt(0);
    JSONObject json = new JSONObject();
    JSONArray items = new JSONArray();
    ArrayList cols = new ArrayList();

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        JSONObject item = new JSONObject();

        for (short colIndex = row.getFirstCellNum(); colIndex <= row.getLastCellNum(); colIndex++) {
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                continue;
            }/*from  w  w w .j av  a  2  s .c  o  m*/
            if (i == 0) { // header
                cols.add(colIndex, cell.getStringCellValue());
            } else {
                item.put((String) cols.get(colIndex), cell.getStringCellValue());
            }
        }
        if (item.length() > 0) {
            items.put(item);
        }
    }
    json.put("items", items);
    return json.toString();

}

From source file:com.envisioncn.it.super_sonic.showcase.evaluation.biz.EvaluationService.java

License:Open Source License

private List<ImportEvaPageBean> readMDExcel(Workbook workbook) throws IOException {

    ImportEvaPageBean eva = null;/*  w  w  w  .  jav  a 2  s  . c  om*/
    List<ImportEvaPageBean> list = new LinkedList<ImportEvaPageBean>();

    // Sheet
    for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
        Sheet sheet = workbook.getSheetAt(numSheet);
        if (sheet == null) {
            continue;
        }
        // Row
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            eva = new ImportEvaPageBean();
            // ?Cell
            Cell periodId = row.getCell(1);
            if (periodId == null) {
                continue;
            }
            eva.setPeriodId(ExcelUtils.getValue(periodId));

            Cell managerId = row.getCell(2);
            if (managerId == null) {
                continue;
            }
            eva.setManagerId(ExcelUtils.getValue(managerId));

            Cell userId = row.getCell(3);
            if (userId == null) {
                continue;
            }
            eva.setUserId(ExcelUtils.getValue(userId));

            Cell priseWill = row.getCell(4);
            if (priseWill == null) {
                continue;
            }
            eva.setPriseWill(Integer.parseInt(ExcelUtils.getValue(priseWill)));

            Cell priseWisdom = row.getCell(5);
            if (priseWisdom == null) {
                continue;
            }
            eva.setPriseWisdom(Integer.parseInt(ExcelUtils.getValue(priseWisdom)));

            Cell priseLove = row.getCell(6);
            if (priseLove == null) {
                continue;
            }
            eva.setPriseLove(Integer.parseInt(ExcelUtils.getValue(priseLove)));

            Cell prosWill = row.getCell(7);
            if (prosWill == null) {
                continue;
            }
            eva.setProsWill(ExcelUtils.getValue(prosWill));

            Cell prosWisdom = row.getCell(8);
            if (prosWisdom == null) {
                continue;
            }
            eva.setProsWisdom(ExcelUtils.getValue(prosWisdom));

            Cell prosLove = row.getCell(9);
            if (prosLove == null) {
                continue;
            }
            eva.setProsLove(ExcelUtils.getValue(prosLove));

            Cell remark = row.getCell(10);
            if (remark == null) {
                continue;
            }
            eva.setRemark(ExcelUtils.getValue(remark));

            list.add(eva);
        }
    }
    return list;
}

From source file:com.envisioncn.it.super_sonic.showcase.evaluation.biz.EvaluationService.java

License:Open Source License

private List<ImportAssPageBean> readTDExcel(Workbook workbook) throws IOException {
    ImportAssPageBean ass = null;//from w w w.  jav a2 s . co m
    List<ImportAssPageBean> list = new LinkedList<ImportAssPageBean>();

    // Sheet
    for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
        Sheet sheet = workbook.getSheetAt(numSheet);
        if (sheet == null) {
            continue;
        }
        // Row
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            ass = new ImportAssPageBean();
            // ?Cell
            Cell cycleId = row.getCell(1);
            if (cycleId == null) {
                continue;
            }
            ass.setCycleId(ExcelUtils.getValue(cycleId));

            Cell criticId = row.getCell(2);
            if (criticId == null) {
                continue;
            }
            ass.setCriticId(ExcelUtils.getValue(criticId));

            Cell userId = row.getCell(3);
            if (userId == null) {
                continue;
            }
            ass.setUserId(ExcelUtils.getValue(userId));

            Cell priseWill = row.getCell(4);
            if (priseWill == null) {
                continue;
            }
            ass.setPriseWill(Integer.parseInt(ExcelUtils.getValue(priseWill)));

            Cell priseWisdom = row.getCell(5);
            if (priseWisdom == null) {
                continue;
            }
            ass.setPriseWisdom(Integer.parseInt(ExcelUtils.getValue(priseWisdom)));

            Cell priseLove = row.getCell(6);
            if (priseLove == null) {
                continue;
            }
            ass.setPriseLove(Integer.parseInt(ExcelUtils.getValue(priseLove)));

            Cell prosWill = row.getCell(7);
            if (prosWill == null) {
                continue;
            }
            ass.setProsWill(ExcelUtils.getValue(prosWill));

            Cell prosWisdom = row.getCell(8);
            if (prosWisdom == null) {
                continue;
            }
            ass.setProsWisdom(ExcelUtils.getValue(prosWisdom));

            Cell prosLove = row.getCell(9);
            if (prosLove == null) {
                continue;
            }
            ass.setProsLove(ExcelUtils.getValue(prosLove));

            list.add(ass);
        }
    }
    return list;
}

From source file:com.exilant.exility.core.XLSReader.java

License:Open Source License

/**
 * Purpose of this method to read rows from given Excel Sheet.
 * //from  w  w  w .j  a  v a  2 s .co  m
 * @param sheet
 *            an Instance of .ss.usermodel.Sheet class from POI apache.
 * @return -1 if fail to read sheet else number of columns read successfully
 *         from the sheet.
 * @throws ExilityException
 */

public int readASheet(Sheet sheet) throws ExilityException {
    int nonEmptyFirstRowIdx = 0;
    int lastRowIdx = 0;

    int nbrPhysicalRows = sheet.getPhysicalNumberOfRows();
    String sheetName = sheet.getSheetName();

    if (nbrPhysicalRows < 2) {
        Spit.out(sheetName + XLSReader.INSUFFICIENT_DATA_ROWS);
        return -1;
    }

    try {
        nonEmptyFirstRowIdx = sheet.getFirstRowNum();
        lastRowIdx = sheet.getLastRowNum();

        /*
         * For checking to valid header.First row must be header.
         */

        Row headerRow = sheet.getRow(nonEmptyFirstRowIdx);
        int nbrCol = headerRow.getPhysicalNumberOfCells();

        for (int colIdx = 0; colIdx < nbrCol; colIdx++) {
            Cell hCell = headerRow.getCell(colIdx);

            if (hCell == null || hCell.getCellType() == Cell.CELL_TYPE_BLANK) {
                Spit.out("Error--->Found blank column " + (colIdx + 1) + " in Sheet " + sheetName
                        + XLSReader.INVALID_HEADER);
                this.columnsData.clear();
                return -1;
            }

            String columnName = hCell.getStringCellValue();
            this.setDataType(columnName, colIdx);
        }

    } catch (Exception e) {
        Spit.out(sheetName + XLSReader.INVALID_HEADER);
        Spit.out(e);
        return -1;
    }

    int nbrColumnsInARow = this.columnsData.size();

    /*
     * Loop starts with second data row that is first row(header as column
     * name) excluded.
     */
    Spit.out(sheetName + ":\n");
    for (int rowIdx = (nonEmptyFirstRowIdx + 1); rowIdx <= lastRowIdx; rowIdx++) {
        Row row = sheet.getRow(rowIdx);
        if (row == null) {
            Spit.out(XLSReader.SKIP_BLANK_ROW + rowIdx);
            continue;
        }
        /**
         * readARow() will throws ExilityException if something goes wrong.
         */
        this.readARow(row, nbrColumnsInARow);
    }

    return this.columnsData.size();

}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * get values in a row as per a normalized list with fixed number of columns
 * in each row/*  w w  w . ja v  a 2s.c  o m*/
 * 
 * @param row
 *            We expect that all data rows have the same set of columns,
 *            extra cells are ignored while blank is assumed for missing
 *            cells
 * @param startingCell
 *            - as determined from header row.
 * @param nbrCells
 * @return
 */
private String[] getTextValues(Row row, int startingCell, int nbrCells) {
    if (row == null) {
        return null;
    }

    String[] values = new String[nbrCells];
    int lastCell = startingCell + nbrCells;
    boolean cellFound = false;
    for (int i = startingCell; i < lastCell; i++) {
        String textValue = this.getTextValue(row.getCell(i));
        if (cellFound == false && textValue.length() > 0) {
            cellFound = true;
        }
        values[i] = textValue;
    }
    if (cellFound) {
        return values;
    }
    return null;
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void addMissingRows(Sheet sheet, String[][] rows) {
    /**//w w  w .j  av a  2  s  .  c o  m
     * create a set of existing labels
     */
    Set<String> existingEntries = new HashSet<String>();
    int lastRow = sheet.getLastRowNum();
    for (int i = 0; i <= lastRow; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        Cell cell = row.getCell(0);
        if (cell == null) {
            continue;
        }
        existingEntries.add(cell.getStringCellValue());
    }
    /**
     * now, add rows, only if they are not there already
     */
    for (String[] row : rows) {
        if (existingEntries.contains(row[0])) {
            continue;
        }
        lastRow++;
        Row xlRow = sheet.createRow(lastRow);
        int colIdx = 0;
        for (String columnValue : row) {
            xlRow.createCell(colIdx).setCellValue(columnValue);
            colIdx++;
        }
    }
}