Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

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

License:Open Source License

public HashMap<String, Object> uploadLocalLicFile(MultipartFile fileUploaded, HttpSession session,
        int confirmLocalLicUploadId) throws Exception {

    HashMap<String, Object> resultMap = new HashMap<String, Object>();
    Utility util = new Utility();
    ArrayList<ExcelErrorDetails> errorList = new ArrayList<ExcelErrorDetails>();
    ArrayList<LicenseDirectory> localLicenseList = new ArrayList<LicenseDirectory>();
    UploadFileUtility upUltil = new UploadFileUtility();
    LocalLicenseHelper LocalLicenseDirectoryHelper = new LocalLicenseHelper();

    boolean hasErrorOccured = false;
    boolean isValidSchema;
    int i;/*from   w  ww.j a v a 2 s  .  c  om*/

    UserDetail userDetail = (UserDetail) session.getAttribute("selectedUser");

    if (!fileUploaded.isEmpty()) {

        Workbook workBook = upUltil.readExcelFileFromMultipart(fileUploaded);
        if (workBook == null) {
            return upUltil.getErrorMessage(ConstantUtil.ERROR_FILE_READING_ERROR);
        }

        if (confirmLocalLicUploadId != 1) {
            isValidSchema = upUltil.isSchemaValid(workBook, ConstantUtil.LOCAL_LICENSE_SHEETNUM,
                    ConstantUtil.LOCAL_LICENSE_HEADER_ROWNUM, ConstantUtil.LOCAL_LICENSE_EXCEL_FORMAT);
            if (!isValidSchema) {
                return upUltil.getErrorMessage(ConstantUtil.ERROR_HEADER_VALIDATION_ERROR);
            }
        }
        Sheet sheet = workBook.getSheetAt(ConstantUtil.LOCAL_LICENSE_SHEETNUM);
        for (i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }

            Map<String, Object> rowValidationResult = new HashMap<String, Object>();
            //if (confirmUploadId != 1) {
            rowValidationResult = LocalLicenseDirectoryHelper.validateRowDataAndFetchBean(row, userDetail);
            if (rowValidationResult.get("licenseDirectoryBean") == null) {
                errorList
                        .addAll((Collection<? extends ExcelErrorDetails>) rowValidationResult.get("errorList"));
                hasErrorOccured = true;
            } else {
                localLicenseList.add((LicenseDirectory) rowValidationResult.get("licenseDirectoryBean"));
            }
            //}
        }
        if ((confirmLocalLicUploadId == 1) || (confirmLocalLicUploadId != 1 && !hasErrorOccured)) {
            errorList = saveLocalLicenseList(localLicenseList);
            if (errorList.isEmpty()) {
                errorList = null;
            }
            return util.responseBuilder(errorList);

        } else {

            return util.responseBuilder(errorList);
        }

    }
    //         Workbook workBook = null;
    //         try {
    //            workBook = new XSSFWorkbook(fileUploaded.getInputStream());
    //         } catch (Exception e) {
    //            try {
    //               workBook = new HSSFWorkbook(fileUploaded.getInputStream());
    //            } catch (IOException e1) {
    //            }
    //         }
    //         if (workBook == null) {
    //            localLicErrorList.add(getExcelErrorDetails(0, 0, "Wrong File Type"));
    //            resultMap.put("ajaxResult", "error");
    //            resultMap.put("reason", localLicErrorList);
    //            return resultMap;
    //         }
    //         // Schema Validation - Checks Whether Row header name is same as we
    //         // specified.
    //         isValidSchema = getLocalSchemaValidation(workBook);
    //         if (isValidSchema != 1) {
    //            hasErrorOccured = true;
    //            localLicErrorList.add(getExcelErrorDetails(0, 0, "Header Validation Failed"));
    //            resultMap.put("ajaxResult", "error");
    //            resultMap.put("reason", localLicErrorList);
    //            return resultMap;
    //         }
    //         // Schema Validation Ends
    //
    //         // Data Validation Starts
    //         sheetStart = readFromLocalHeader(null, stringSheetStart);
    //         Sheet sheet = workBook.getSheetAt(sheetStart);
    //         for (i = 1; i <= sheet.getLastRowNum(); i++) {
    //            Row row = sheet.getRow(i);
    //            if (row == null) {
    //               continue;
    //            }
    //            if (confirmLocalLicUploadId != 1) {
    //               ArrayList<ExcelErrorDetails> rowErrorDetailList = new ArrayList<ExcelErrorDetails>();
    //               rowErrorDetailList = validateLocalRowData(row);
    //               if (rowErrorDetailList != null) {
    //                  localLicErrorList.addAll(rowErrorDetailList);
    //                  hasErrorOccured = true;
    //               }
    //            }
    //            if ((confirmLocalLicUploadId != 1 && !hasErrorOccured) || confirmLocalLicUploadId == 1) {
    //               localLicenseList.add(getLocalLicenseDetails(row, domainDetail, userDetail));
    //            }
    //         }
    //         if ((confirmLocalLicUploadId == 1) || (confirmLocalLicUploadId != 1 && !hasErrorOccured)) {
    //            localLicErrorList = saveLocalLicenseList(localLicenseList);
    //            if (localLicErrorList.isEmpty()) {
    //               resultMap.put("ajaxResult", "success");
    //               resultMap.put("reason", null);
    //            } else {
    //               resultMap.put("ajaxResult", "error");
    //               resultMap.put("reason", localLicErrorList);
    //            }
    //         } else {
    //            resultMap.put("ajaxResult", "error");
    //            resultMap.put("reason", localLicErrorList);
    //
    //         }
    //
    //      } else {
    //         resultMap.put("ajaxResult", "error");
    //         resultMap
    //               .put("reason",
    //                     "Cannot Find the excel file. Please refresh the page and try again. If this problem persists report it to Dev. Team.");
    //
    //      }
    return resultMap;
}

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;//  ww w.j  ava2s.  co 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   ww w . jav  a  2  s . co 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 .j av  a  2  s. co m*/
    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  . j  a va2 s.  c o  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.eurodyn.qlack2.fuse.lexicon.impl.LanguageServiceImpl.java

License:EUPL

@Override
@Transactional(TxType.REQUIRED)/*from ww  w  . ja va  2  s.c  om*/
public void uploadLanguage(String languageID, byte[] lgXL) {
    Map<String, String> translations = new HashMap<>();
    try {
        Workbook wb = WorkbookFactory.create(new BufferedInputStream(new ByteArrayInputStream(lgXL)));
        for (int si = 0; si < wb.getNumberOfSheets(); si++) {
            Sheet sheet = wb.getSheetAt(si);
            String groupName = sheet.getSheetName();
            String groupID = null;
            if (StringUtils.isNotBlank(groupName)) {
                groupID = Group.findByName(groupName, em).getId();
            }
            // Skip first row (the header of the Excel file) and start
            // parsing translations.
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                String keyName = sheet.getRow(i).getCell(0).getStringCellValue();
                String keyValue = sheet.getRow(i).getCell(1).getStringCellValue();
                translations.put(keyName, keyValue);
            }
            keyService.updateTranslationsForLanguageByKeyName(languageID, groupID, translations);
        }
    } catch (IOException | InvalidFormatException ex) {
        // Convert to a runtime exception in order to roll back transaction
        LOGGER.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        throw new QLanguageProcessingException("Error reading Excel file for language " + languageID);
    }

}

From source file:com.evidon.areweprivateyet.Aggregator.java

License:Open Source License

private void createContent(Workbook wb, Sheet s, String map) {
    Map<String, String> out = new HashMap<String, String>();

    int rownum = 2;
    int cellnum = 0;

    // create a merged list of domains.
    domains.clear();/*  w  w w  .  j a v  a  2 s.  co m*/
    for (String database : results.keySet()) {
        if (database.equals("baseline")) {
            Analyzer ra = results.get(database);
            Map<String, Integer> mapToUse = this.getMap(map, ra);

            for (String domain : mapToUse.keySet()) {
                if ((!domains.contains(domain)) && !exclusions.contains(domain)) {
                    domains.add(domain);
                    out.put(domain, "");
                }
            }
        }
    }

    CellStyle numberStyle = wb.createCellStyle();
    numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number"));
    s.setColumnWidth(0, 5000);

    for (String domain : domains) {
        cellnum = 0;

        Row r = s.createRow(rownum);
        Cell c = r.createCell(cellnum);
        c.setCellValue(domain);
        cellnum++;

        for (String database : results.keySet()) {
            Analyzer ra = results.get(database);

            Map<String, Integer> mapToUse = this.getMap(map, ra);

            c = r.createCell(cellnum);
            try {
                if (mapToUse.containsKey(domain)) {
                    c.setCellValue(mapToUse.get(domain));
                } else {
                    c.setCellValue(0);
                }
            } catch (Exception e) {
                c.setCellValue(0);
            }

            c.setCellStyle(numberStyle);

            cellnum++;
        }
        rownum++;
    }

    // Totals.
    rownum++;
    cellnum = 1;
    Row r = s.createRow(rownum);

    Cell c = r.createCell(0);
    c.setCellValue("Totals:");

    for (int i = 0; i < results.keySet().size(); i++) {
        c = r.createCell(cellnum);
        c.setCellType(Cell.CELL_TYPE_FORMULA);
        c.setCellFormula("SUM(" + getCellLetter(i) + "3:" + getCellLetter(i) + (domains.size() + 2) + ")");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateFormulaCell(c);

        if (!totals.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) {
            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        } else {
            Map<String, String> contents = totals.get(s.getRow(1).getCell(i + 1).getStringCellValue());
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        }

        cellnum++;
    }

    // Delta/Reduction
    rownum++;
    cellnum = 1;
    r = s.createRow(rownum);

    c = r.createCell(0);
    c.setCellValue("Tracking Decrease:");

    for (int i = 0; i < results.keySet().size(); i++) {
        c = r.createCell(cellnum);
        c.setCellType(Cell.CELL_TYPE_FORMULA);
        c.setCellFormula("ROUND((100-(" + getCellLetter(i) + (rownum) + "*100/B" + (rownum) + ")),0)");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateFormulaCell(c);

        if (!decrease.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) {
            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        } else {
            Map<String, String> contents = decrease.get(s.getRow(1).getCell(i + 1).getStringCellValue());
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        }

        cellnum++;
    }
}

From source file:com.example.poi.ToCSV.java

License:Apache License

/**
 * Called to convert the contents of the currently opened workbook into
 * a CSV file.//  w  ww  . j a  va2  s. c  o m
 */
private void convertToCSV() {
    Sheet sheet = null;
    Row row = null;
    int lastRowNum = 0;
    this.csvData = new ArrayList<ArrayList>();

    System.out.println("Converting files contents to CSV format.");

    // Discover how many sheets there are in the workbook....
    int numSheets = this.workbook.getNumberOfSheets();

    // and then iterate through them.
    for (int i = 0; i < numSheets; i++) {

        // Get a reference to a sheet and check to see if it contains
        // any rows.
        sheet = this.workbook.getSheetAt(i);
        if (sheet.getPhysicalNumberOfRows() > 0) {

            // Note down the index number of the bottom-most row and
            // then iterate through all of the rows on the sheet starting
            // from the very first row - number 1 - even if it is missing.
            // Recover a reference to the row and then call another method
            // which will strip the data from the cells and build lines
            // for inclusion in the resylting CSV file.
            lastRowNum = sheet.getLastRowNum();
            for (int j = 0; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row);
            }
        }
    }
}

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  ww.  j  a  v  a  2 s  . com
 * @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 contents of a sheet into text rows and columns
 * /*  ww  w  .  j  a  v a 2  s. co m*/
 * @param sheet
 * @return
 */
private String[][] getRawData(Sheet sheet, boolean expectValueInFirstColumn) {

    // let us get a normalized rows/columns out of this sheet.
    int firstRowIdx = sheet.getFirstRowNum();
    Row firstRow = sheet.getRow(firstRowIdx);
    int firstCellIdx = firstRow.getFirstCellNum();
    int lastCellAt = firstRow.getLastCellNum();
    int nbrCells = lastCellAt - firstCellIdx;

    int lastRow = sheet.getLastRowNum();

    List<String[]> rawData = new ArrayList<String[]>();
    for (int rowNbr = firstRowIdx; rowNbr <= lastRow; rowNbr++) {
        Row row = sheet.getRow(rowNbr);
        if (row == null || row.getPhysicalNumberOfCells() == 0) {
            Spit.out(
                    "row at " + rowNbr + "is empty. while this is not an error, we certianly discourage this.");
            continue;
        }

        String[] rowData = this.getTextValues(row, firstCellIdx, nbrCells);
        if (rowData == null) {
            continue;
        }
        if (expectValueInFirstColumn) {
            String firstData = rowData[0];
            if (firstData == null || firstData.length() == 0) {
                Spit.out("row at" + rowNbr + " has its first column empty, and hence the row is ignored");
                continue;
            }
        }
        rawData.add(rowData);
    }

    if (rawData.size() > 0) {
        return rawData.toArray(new String[0][0]);
    }
    return null;
}