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.dituiba.excel.BaseExcelService.java

License:Apache License

/**
 * ?// www.j  a va  2s .com
 * @param sheet
 * @param row
 * @return
 */
public static Row getRow(Sheet sheet, int row) {
    return sheet.getRow(row);
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ??/*  w w  w .  j  ava 2  s.  c  o  m*/
 * @param config
 * @param sheet
 * @param columnIndex
 * @param valueSet
 */
protected void createDicCodeSheet(DicValidateConfig config, Sheet sheet, int columnIndex,
        Set<String> valueSet) {
    Workbook workbook = sheet.getWorkbook();
    Sheet codeSheet = workbook.getSheet(DICCODE_SHEET_NAME);
    if (codeSheet == null) {
        log.debug("?Sheet?Sheet");
        codeSheet = workbook.createSheet(DICCODE_SHEET_NAME);
    }
    int codeIndex = config.columnName() - 'A';
    log.debug("codeIndex{}", codeIndex);
    if (codeSheet.getRow(0) == null || codeSheet.getRow(0).getCell(codeIndex) == null) {
        log.debug("????");
        int i = 0;
        for (String dic : valueSet) {
            Row row = codeSheet.getRow(i);
            if (row == null)
                row = codeSheet.createRow(i);
            Cell cell = row.createCell(codeIndex);
            cell.setCellValue(dic);
            i++;
        }
    } else {
        log.debug("????");
    }
    Name name = workbook.getName(config.columnName() + "");
    if (name == null || name.isDeleted()) {
        log.debug("?Name?Name");
        name = workbook.createName();
        name.setNameName(config.columnName() + "");
    }
    name.setRefersToFormula(DICCODE_SHEET_NAME + "!$" + config.columnName() + "$1:$" + config.columnName() + "$"
            + valueSet.size());
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(name.getNameName());
    CellRangeAddressList addressList = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
            columnIndex, columnIndex);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    workbook.setSheetHidden(workbook.getSheetIndex(DICCODE_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN);
    setValidationTip(validation, config);
    sheet.addValidationData(validation);
    log.debug("??");
}

From source file:com.dituiba.excel.ExcelUtility.java

License:Apache License

public static void copyRows(Sheet st, int startRow, int endRow, int pPosition) {
    int pStartRow = startRow;
    int pEndRow = endRow;
    int targetRowFrom;
    int targetRowTo;
    int columnCount;
    CellRangeAddress region = null;/*from w  ww. j  a v  a2 s .  c  om*/
    int i;
    int j;
    for (i = 0; i < st.getNumMergedRegions(); i++) {
        region = st.getMergedRegion(i);
        if ((region.getFirstRow() >= pStartRow) && (region.getLastRow() <= pEndRow)) {
            targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
            targetRowTo = region.getLastRow() - pStartRow + pPosition;

            CellRangeAddress newRegion = region.copy();

            newRegion.setFirstRow(targetRowFrom);
            newRegion.setFirstColumn(region.getFirstColumn());
            newRegion.setLastRow(targetRowTo);
            newRegion.setLastColumn(region.getLastColumn());
            st.addMergedRegion(newRegion);
        }
    }
    //set the column height and value
    for (i = pStartRow; i <= pEndRow; i++) {
        Row sourceRow = st.getRow(i);
        columnCount = sourceRow.getLastCellNum();
        if (sourceRow != null) {
            Row newRow = st.createRow(pPosition + i);
            newRow.setHeight(sourceRow.getHeight());
            for (j = 0; j < columnCount; j++) {
                Cell templateCell = sourceRow.getCell(j);
                if (templateCell != null) {
                    Cell newCell = newRow.createCell(j);
                    copyCell(templateCell, newCell);
                }
            }
        }
    }
}

From source file:com.diversityarrays.kdxplore.importdata.excelio.KdxploreWorksheet.java

License:Open Source License

protected <T> DataError processWorksheet(Sheet sheet, Class<T> tClass, EntityProcessor<T> entityProcessor,
        WorkbookReadResult wrr) {//from   ww w  .jav  a 2s  .c  o  m
    HeadingRow headingRow = null;

    int nRows = ExcelUtil.getRowCount(sheet);
    for (int rowIndex = 0; rowIndex < nRows; ++rowIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            continue;
        }

        List<String> cellValues = getCellValuesIfAnyNonBlank(row);
        if (cellValues == null) {
            continue;
        }

        if (headingRow == null) {
            Either<List<Pair<Integer, String>>, HeadingRow> either = worksheetInfo.scanHeadingRow(cellValues);
            if (either.isLeft()) {
                String errmsg = either.left().stream().map(pair -> pair.first + ":" + pair.second)
                        .collect(Collectors.joining(","));

                return new DataError(rowIndex, errmsg);
            }
            headingRow = either.right();
            if (headingRow.importFieldByColumnIndex.isEmpty()) {
                return new DataError(rowIndex,
                        "No Column Headings found in worksheet '" + sheet.getSheetName() + "'");
            }
        } else {
            RowData rowData = worksheetInfo.collectImportFields(headingRow.importFieldByColumnIndex,
                    cellValues);

            Either<DataError, T> pEither = entityProcessor.createEntity(rowIndex);

            if (pEither.isLeft()) {
                return pEither.left();
            }
            T entity = pEither.right();

            for (Pair<ImportField, String> pair : rowData.importFieldsAndCellValues) {
                ImportField importField = pair.first;
                String cellValue = pair.second;

                Either<String, Object> either = KDSmartDbUtil.convertValueOrError(tClass, importField.field,
                        cellValue);

                if (either.isLeft()) {
                    return new DataError(rowIndex, either.left());
                }

                try {
                    importField.field.set(entity, either.right());
                } catch (IllegalArgumentException | IllegalAccessException e) {
                    String msg = e.getClass().getSimpleName() + ": " + e.getMessage();
                    return new DataError(rowIndex, msg);
                }
            }

            DataError error = entityProcessor.handleRemainingColumns(rowIndex, entity, headingRow, rowData,
                    wrr);
            if (error != null) {
                return error;
            }
        }
    }

    return null;
}

From source file:com.diversityarrays.kdxplore.importdata.excelio.TraitsWorksheet.java

License:Open Source License

@Override
public DataError processWorksheet(Sheet sheet, WorkbookReadResult wrr) {

    int nRows = ExcelUtil.getRowCount(sheet);

    HeadingRow headingRow = null;//from   w w  w. jav a  2s .c  o m
    int lastColumnIndex = -1;

    for (int rowIndex = 0; rowIndex < nRows; ++rowIndex) {
        Row rrow = sheet.getRow(rowIndex);
        if (rrow == null) {
            continue;
        }

        List<String> cellValues = getCellValuesIfAnyNonBlank(rrow);
        if (cellValues == null) {
            continue;
        }

        if (headingRow == null) {
            Either<List<Pair<Integer, String>>, HeadingRow> either = worksheetInfo.scanHeadingRow(cellValues);
            if (either.isLeft()) {
                String errmsg = either.left().stream().map(pair -> pair.first + ":" + pair.second)
                        .collect(Collectors.joining(","));

                return new DataError(rowIndex, errmsg);
            }
            headingRow = either.right();
            if (headingRow.importFieldByColumnIndex.isEmpty()) {
                return new DataError(rowIndex,
                        "No Column Headings found in worksheet '" + sheet.getSheetName() + "'");
            }
            List<Integer> columnIndices = new ArrayList<>(headingRow.importFieldByColumnIndex.keySet());
            Collections.sort(columnIndices, Collections.reverseOrder());

            ImportField lastImportField = headingRow.importFieldByColumnIndex.get(lastColumnIndex);
            if (!"traitValRule".equals(lastImportField.fieldName)) {
                return new DataError(rowIndex, "Last Column Heading must be '" + HDG_TRAIT_VALIDATION + "'");
            }
        } else {
            Either<DataError, Trait> either = getTraitFromCellValues(worksheetInfo, headingRow, rowIndex,
                    cellValues, lastColumnIndex);
            if (either.isLeft()) {
                return either.left();
            }
            String errmsg = wrr.addTrait(either.right());
            if (!Check.isEmpty(errmsg)) {
                return new DataError(rowIndex, errmsg);
            }
        }

    }
    return null;
}

From source file:com.diversityarrays.kdxplore.importdata.excelio.TrialWorksheet.java

License:Open Source License

@Override
public DataError processWorksheet(Sheet sheet, WorkbookReadResult wrr) {

    int nRows = ExcelUtil.getRowCount(sheet);
    for (int rowIndex = 0; rowIndex < nRows; ++rowIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            continue;
        }//from   w w  w.  jav a  2 s.  c  o m

        int nCells = Math.min(2, ExcelUtil.getCellCount(row));
        String name = "";
        String value = "";
        for (int cellIndex = 0; cellIndex < nCells; ++cellIndex) {
            Cell cell = row.getCell(cellIndex);
            if (cell != null) {
                String s = ExcelUtil.getCellStringValue(cell, "");
                if (cellIndex == 0) {
                    name = s;
                } else if (cellIndex == 1) {
                    value = s;
                }
            }
        }

        ImportField importField = worksheetInfo.getFieldForHeading(name);
        if (importField == null) {
            TrialAttribute ta = new TrialAttribute();
            ta.setTrialAttributeName(name);
            ta.setTrialAttributeValue(value);
            wrr.trialAttributes.add(ta);
        } else {
            Either<String, Object> either = KDSmartDbUtil.convertValueOrError(Trial.class, importField.field,
                    value);
            if (either.isLeft()) {
                return new DataError(rowIndex, either.left());
            }

            try {
                importField.field.set(wrr.trial, either.right());
            } catch (IllegalArgumentException | IllegalAccessException e) {
                String msg = e.getClass().getSimpleName() + ": " + e.getMessage();
                return new DataError(rowIndex, msg);
            }
        }
    }
    return null;
}

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

License:Open Source License

/**
 * Gets the cell string value./*from   w w  w . j  a  v  a 2 s.  c o m*/
 *
 * @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;//from w  w w . j  a va  2  s.c om
    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;/*from   ww w .jav a 2  s .  c o m*/

    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

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

    int j = 0;/* w  w  w  .  jav  a 2s .  com*/
    // 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);
}