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

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

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

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java

License:Apache License

private PoiProxySheet makeSheet(Workbook wb, FormulaParsingWorkbook ewb) {
    Sheet wbSheet = wb.getSheetAt(0);
    PoiProxySheet sheet = new PoiProxySheet(wbSheet.getSheetName());

    for (int r = 0; r <= wbSheet.getLastRowNum(); r++) {
        Row wbSheetRow = wbSheet.getRow(r);
        if (wbSheetRow == null) {
            continue;
        }/*from w  w w .ja v  a  2s . c om*/

        for (int c = 0; c <= wbSheetRow.getLastCellNum(); c++) {
            Cell wbSheetRowCell = wbSheetRow.getCell(c);
            if (wbSheetRowCell == null) {
                continue;
            }

            final Ptg[] tokens = CELL_TYPE_FORMULA == wbSheetRowCell.getCellType()
                    ? FormulaParser.parse(wbSheetRowCell.getCellFormula(), ewb, FormulaType.CELL, 0)
                    : null;
            sheet.setCell(PoiProxyCell.makeCell(sheet, wbSheetRowCell, tokens));
        }
    }

    return sheet;
}

From source file:com.delpac.bean.CargarSellosBean.java

public List<Sellos> importData(Workbook workbook, int tabNumber) throws IOException {
    List<Sellos> lista = new ArrayList<>();
    String[][] data;//from   w w w . j av a 2 s .  c  o  m
    Sheet sheet = workbook.getSheetAt(tabNumber);
    data = new String[sheet.getLastRowNum() + 1][];
    Row[] row = new Row[sheet.getLastRowNum() + 1];
    Cell[][] cell = new Cell[row.length][];

    for (int i = 1; i < row.length; i++) {
        row[i] = sheet.getRow(i);
        cell[i] = new Cell[row[i].getLastCellNum()];
        data[i] = new String[row[i].getLastCellNum()];
        Sellos sellos = new Sellos();
        for (int j = 0; j < cell[i].length; j++) {
            cell[i][j] = row[i].getCell(j);
            if (cell[i][j] != null) {
                switch (j) {
                case 0:
                    sellos.setInv_seguridad(getDataFromCell(cell, i, j));
                    break;
                case 1:
                    sellos.setInv_sello(getDataFromCell(cell, i, j));
                    break;
                }
            } else {
                switch (j) {
                case 0:
                    sellos.setInv_seguridad("");
                    break;
                case 1:
                    sellos.setInv_sello("");
                    break;
                }
            }
        }
        if (!"".equals(sellos.getInv_sello())) {
            lista.add(sellos);
        }
    }

    return lista;
}

From source file:com.delpac.bean.PreDescargaBean.java

public List<PreDescarga> importData(Workbook workbook, int tabNumber) throws IOException {
    List<PreDescarga> lista = new ArrayList<>();
    String[][] data;//ww  w . ja va  2s.c o m
    DataFormatter df = new DataFormatter();
    Sheet sheet = workbook.getSheetAt(tabNumber);
    data = new String[sheet.getLastRowNum() + 1][];
    Row[] row = new Row[sheet.getLastRowNum() + 1];
    Cell[][] cell = new Cell[row.length][];

    for (int i = 1; i < row.length; i++) {
        row[i] = sheet.getRow(i);
        cell[i] = new Cell[row[i].getLastCellNum()];
        data[i] = new String[row[i].getLastCellNum()];
        PreDescarga preDescarga = new PreDescarga();
        for (int j = 0; j < cell[i].length; j++) {
            cell[i][j] = row[i].getCell(j);
            if (cell[i][j] != null) {
                switch (j) {
                case 0:
                    preDescarga.setBl(getDataFromCell(cell, i, j));
                    break;
                case 1:
                    preDescarga.setConsignatario(getDataFromCell(cell, i, j));
                    break;
                case 2:
                    preDescarga.setPto_origen(getDataFromCell(cell, i, j));
                    break;
                case 3:
                    preDescarga.setPto_destino(getDataFromCell(cell, i, j));
                    break;
                case 4:
                    preDescarga.setFec_embarque(getDataFromCell(cell, i, j));
                    break;
                case 5:
                    preDescarga.setPeso(Double.parseDouble(getDataFromCell(cell, i, j)));
                    break;
                case 6:
                    preDescarga.setBulto(Double.parseDouble(getDataFromCell(cell, i, j)));
                    break;
                case 7:
                    preDescarga.setEmbalaje(getDataFromCell(cell, i, j));
                    break;
                case 8:
                    preDescarga.setCarga(getDataFromCell(cell, i, j));
                    break;
                case 9:
                    preDescarga.setCon_codigo(getDataFromCell(cell, i, j));
                    break;
                case 10:
                    preDescarga.setTemperatura(getDataFromCell(cell, i, j));
                    break;
                case 11:
                    preDescarga.setVentilacion(getDataFromCell(cell, i, j));
                    break;
                case 12:
                    preDescarga.setSello(getDataFromCell(cell, i, j));
                    break;
                case 13:
                    preDescarga.setSown(getDataFromCell(cell, i, j));
                    break;
                case 14:
                    preDescarga.setClassimo(getDataFromCell(cell, i, j));
                    break;
                case 15:
                    preDescarga.setUnnro(getDataFromCell(cell, i, j));
                    break;
                case 16:
                    preDescarga.setTip_cont(getDataFromCell(cell, i, j));
                    break;
                case 17:
                    preDescarga.setCondicion(getDataFromCell(cell, i, j));
                    break;
                case 18:
                    preDescarga.setAlm_codigo(getDataFromCell(cell, i, j));
                    break;
                }
            } else {
                switch (j) {
                case 1:
                    preDescarga.setBl("");
                    break;
                case 2:
                    preDescarga.setConsignatario("");
                    break;
                case 3:
                    preDescarga.setPto_origen("");
                    break;
                case 4:
                    preDescarga.setPto_destino("");
                    break;
                case 5:
                    preDescarga.setFec_embarque("");
                    break;
                case 6:
                    preDescarga.setPeso(0);
                    break;
                case 7:
                    preDescarga.setBulto(0);
                    break;
                case 8:
                    preDescarga.setEmbalaje("");
                    break;
                case 9:
                    preDescarga.setCarga("");
                    break;
                case 10:
                    preDescarga.setCon_codigo("");
                    break;
                case 11:
                    preDescarga.setTemperatura("");
                    break;
                case 12:
                    preDescarga.setVentilacion("");
                    break;
                case 13:
                    preDescarga.setSello("");
                    break;
                case 14:
                    preDescarga.setSown("");
                    break;
                case 15:
                    preDescarga.setClassimo("");
                    break;
                case 16:
                    preDescarga.setUnnro("");
                    break;
                case 17:
                    preDescarga.setTip_cont("");
                    break;
                case 18:
                    preDescarga.setCondicion("");
                    break;
                case 19:
                    preDescarga.setAlm_codigo("");
                    break;
                }
            }
        }
        lista.add(preDescarga);
    }
    return lista;
}

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);/*from  w w w.  j  a va  2  s.c om*/
    ArrayList<String> header = new ArrayList<String>();
    int j = 0;
    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

@SuppressWarnings("unchecked")
public HashMap<String, Object> uploadStateLicFile(MultipartFile fileUploaded, HttpSession session,
        int confirmStateLicUploadId) throws Exception {

    HashMap<String, Object> resultMap = new HashMap<String, Object>();
    ArrayList<ExcelErrorDetails> stateLicErrorList = new ArrayList<ExcelErrorDetails>();
    ArrayList<LicenseDirectory> stateLicenseList = new ArrayList<LicenseDirectory>();
    UploadFileUtility upUltil = new UploadFileUtility();
    StateLicenseHelper stateLicenseHelper = new StateLicenseHelper();
    boolean hasErrorOccured = false;
    boolean isValidSchema;
    int i;/* ww  w .  j  a  v a2s.c  om*/

    Utility util = new Utility();
    UserDetail userDetail = (UserDetail) session.getAttribute("selectedUser");

    try {
        if (!fileUploaded.isEmpty()) {

            Workbook workBook = upUltil.readExcelFileFromMultipart(fileUploaded);
            if (workBook == null) {
                return upUltil.getErrorMessage(ConstantUtil.ERROR_FILE_READING_ERROR);
            }
            if (confirmStateLicUploadId != 1) {
                isValidSchema = upUltil.isSchemaValid(workBook, ConstantUtil.STATE_LICENSE_SHEETNUM,
                        ConstantUtil.STATE_LICENSE_HEADER_ROWNUM, ConstantUtil.STATE_LICENSE_EXCEL_FORMAT);
                if (!isValidSchema) {
                    return upUltil.getErrorMessage(ConstantUtil.ERROR_HEADER_VALIDATION_ERROR);
                }
            }
            Sheet sheet = null;
            try {
                sheet = workBook.getSheetAt(ConstantUtil.STATE_LICENSE_SHEETNUM);
            } catch (Exception e) {
                e.printStackTrace();
            }
            for (i = 1; i <= sheet.getLastRowNum(); i++) {
                System.out.println(i);
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Map<String, Object> rowValidationResult = new HashMap<String, Object>();
                //if (confirmUploadId != 1) {
                rowValidationResult = stateLicenseHelper.validateRowDataAndFetchBean(row, userDetail);
                if (rowValidationResult.get("licenseDirectoryBean") == null) {
                    stateLicErrorList.addAll(
                            (Collection<? extends ExcelErrorDetails>) rowValidationResult.get("errorList"));
                    hasErrorOccured = true;
                } else {
                    stateLicenseList.add((LicenseDirectory) rowValidationResult.get("licenseDirectoryBean"));
                }
            }

            if ((confirmStateLicUploadId == 1) || (confirmStateLicUploadId != 1 && !hasErrorOccured)) {
                stateLicErrorList = saveStateLicenseList(stateLicenseList);
                if (stateLicErrorList.isEmpty()) {
                    stateLicErrorList = null;
                }
                return util.responseBuilder(stateLicErrorList);

            } else {

                return util.responseBuilder(stateLicErrorList);
            }
        }
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        throw e;
    }
    return resultMap;
    //         try {
    //            workBook = new XSSFWorkbook(fileUploaded.getInputStream());
    //         } catch (Exception e) {
    //            try {
    //               workBook = new HSSFWorkbook(fileUploaded.getInputStream());
    //            } catch (IOException e1) {
    //            }
    //         }
    //         if (workBook == null) {
    //            stateLicErrorList.add(getExcelErrorDetails(0, 0, "Wrong File Type"));
    //            resultMap.put("ajaxResult", "error");
    //            resultMap.put("reason", stateLicErrorList);
    //            return resultMap;
    //         }
    //         // Schema Validation - Checks Whether Row header name is same as we
    //         // specified.
    //         isValidSchema = getSchemaValidation(workBook);
    //         if (isValidSchema != 1) {
    //            hasErrorOccured = true;
    //            stateLicErrorList.add(getExcelErrorDetails(0, 0, "Header Validation Failed"));
    //            resultMap.put("ajaxResult", "error");
    //            resultMap.put("reason", stateLicErrorList);
    //            return resultMap;
    //         }
    //
    //         // Schema Validation Ends
    //
    //         // Data Validation Starts
    //         sheetStart = readFromHeader(null, stringSheetStart);
    //         Sheet sheet = workBook.getSheetAt(sheetStart);
    //         for (i = 1; i <= sheet.getLastRowNum(); i++) {
    //            Row row = sheet.getRow(i);
    //            if (row == null) {
    //               continue;
    //            }
    //            if (confirmStateLicUploadId != 1) {
    //               ArrayList<ExcelErrorDetails> rowErrorDetailList = new ArrayList<ExcelErrorDetails>();
    //               rowErrorDetailList = validateRowData(row);
    //               if (rowErrorDetailList != null) {
    //                  stateLicErrorList.addAll(rowErrorDetailList);
    //                  hasErrorOccured = true;
    //               }
    //            }
    //            if ((confirmStateLicUploadId != 1 && !hasErrorOccured) || confirmStateLicUploadId == 1) {
    //               stateLicenseList.add(getStateLicenseDetails(row, domainDetail, userDetail));
    //            }
    //         }
    //         if ((confirmStateLicUploadId == 1) || (confirmStateLicUploadId != 1 && !hasErrorOccured)) {
    //            stateLicErrorList = saveStateLicenseList(stateLicenseList);
    //            if (stateLicErrorList.isEmpty()) {
    //               resultMap.put("ajaxResult", "success");
    //               resultMap.put("reason", null);
    //            } else {
    //               resultMap.put("ajaxResult", "error");
    //               resultMap.put("reason", stateLicErrorList);
    //            }
    //         } else {
    //            resultMap.put("ajaxResult", "error");
    //            resultMap.put("reason", stateLicErrorList);
    //
    //         }
    //
    //      } 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

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;//  w  w w .  ja  v  a 2 s.  c o  m

    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.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);//from www  .ja v a  2s .  c o m
        JSONObject item = new JSONObject();

        for (short colIndex = row.getFirstCellNum(); colIndex <= row.getLastCellNum(); colIndex++) {
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                continue;
            }
            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;//from www  . ja  v a  2 s .  c o  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.  ja v  a  2s  .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)/*  w ww . ja va  2  s.c  o  m*/
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);
    }

}