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:cn.afterturn.easypoi.excel.imports.ExcelImportService.java

License:Apache License

/**
 * ??Excel??//from   w  w w .jav a 2 s. co m
 */
private void readSingleCell(ExcelImportResult result, Sheet sheet, ImportParams params) {
    if (result.getMap() == null) {
        result.setMap(new HashMap<String, Object>());
    }
    for (int i = 0; i < params.getTitleRows() + params.getHeadRows() + params.getStartRows(); i++) {
        getSingleCellValueForRow(result, sheet.getRow(i), params);
    }

    for (int i = sheet.getLastRowNum() - params.getLastOfInvalidRow(); i < sheet.getLastRowNum(); i++) {
        getSingleCellValueForRow(result, sheet.getRow(i), params);

    }
}

From source file:cn.afterturn.easypoi.util.PoiSheetUtil.java

License:Apache License

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * /*from  ww w  .j  av  a 2  s.  com*/
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param columnToDelete
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null) {
            continue;
        }

        // if the row doesn't have this many columns then we are good; next!
        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn) {
            maxColumn = lastColumn;
        }

        if (lastColumn < columnToDelete) {
            continue;
        }

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            Cell oldCell = row.getCell(x - 1);
            if (oldCell != null) {
                row.removeCell(oldCell);
            }

            Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

    // Adjust the column widths
    for (int c = columnToDelete; c < maxColumn; c++) {
        sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
    }
}

From source file:cn.bzvs.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 *  ? Cells/*from w ww  .  ja  va  2 s .co  m*/
 *
 * @param patriarch
 * @param index
 * @param t
 * @param excelParams
 * @param sheet
 * @param workbook
 * @param rowHeight
 * @return
 * @throws Exception
 */
public int createCells(Drawing patriarch, int index, Object t, List<ExcelExportEntity> excelParams, Sheet sheet,
        Workbook workbook, short rowHeight) throws Exception {
    ExcelExportEntity entity;
    Row row = sheet.createRow(index);
    row.setHeight(rowHeight);
    int maxHeight = 1, cellNum = 0;
    int indexKey = createIndexCell(row, index, excelParams.get(0));
    cellNum += indexKey;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            Collection<?> list = getListCellValue(entity, t);
            int listC = 0;
            if (list != null && list.size() > 0) {
                for (Object obj : list) {
                    createListCells(patriarch, index + listC, cellNum, obj, entity.getList(), sheet, workbook);
                    listC++;
                }
            }
            cellNum += entity.getList().size();
            if (list != null && list.size() > maxHeight) {
                maxHeight = list.size();
            }
        } else {
            Object value = getCellValue(entity, t);
            if (entity.getType() == BaseEntityTypeConstants.StringType) {
                createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                        index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
                if (entity.isHyperlink()) {
                    row.getCell(cellNum - 1).setHyperlink(dataHanlder.getHyperlink(
                            row.getSheet().getWorkbook().getCreationHelper(), t, entity.getName(), value));
                }
            } else if (entity.getType() == BaseEntityTypeConstants.DoubleType) {
                createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                        index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
                if (entity.isHyperlink()) {
                    row.getCell(cellNum - 1).setHyperlink(dataHanlder.getHyperlink(
                            row.getSheet().getWorkbook().getCreationHelper(), t, entity.getName(), value));
                }
            } else {
                createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), t);
            }
        }
    }
    // ????
    cellNum = 0;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            cellNum += entity.getList().size();
        } else if (entity.isNeedMerge()) {
            for (int i = index + 1; i < index + maxHeight; i++) {
                sheet.getRow(i).createCell(cellNum);
                sheet.getRow(i).getCell(cellNum).setCellStyle(getStyles(false, entity));
            }
            sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight - 1, cellNum, cellNum));
            cellNum++;
        }
    }
    return maxHeight;

}

From source file:cn.bzvs.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 * List??Cells/*from   w  w w.j  a va  2  s . c o  m*/
 *
 * @param patriarch
 * @param index
 * @param cellNum
 * @param obj
 * @param excelParams
 * @param sheet
 * @param workbook
 * @throws Exception
 */
public void createListCells(Drawing patriarch, int index, int cellNum, Object obj,
        List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook) throws Exception {
    ExcelExportEntity entity;
    Row row;
    if (sheet.getRow(index) == null) {
        row = sheet.createRow(index);
        row.setHeight(getRowHeight(excelParams));
    } else {
        row = sheet.getRow(index);
    }
    for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        Object value = getCellValue(entity, obj);
        if (entity.getType() == BaseEntityTypeConstants.StringType) {
            createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                    row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
            if (entity.isHyperlink()) {
                row.getCell(cellNum - 1).setHyperlink(dataHanlder.getHyperlink(
                        row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value));
            }
        } else if (entity.getType() == BaseEntityTypeConstants.DoubleType) {
            createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                    index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
            if (entity.isHyperlink()) {
                row.getCell(cellNum - 1).setHyperlink(dataHanlder.getHyperlink(
                        row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value));
            }
        } else {
            createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), obj);
        }
    }
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

private void parseTemplate(Sheet sheet, Map<String, Object> map, boolean colForeach) throws Exception {
    deleteCell(sheet, map);//from   ww  w  .java 2s. c  om
    mergedRegionHelper = new MergedRegionHelper(sheet);
    templateSumHanlder = new TemplateSumHanlder(sheet);
    if (colForeach) {
        colForeach(sheet, map);
    }
    Row row = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            if (row.getCell(i) != null
                    && !tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) {
                setValueForCellByMap(row.getCell(i), map);
            }
        }
    }

    //??
    hanlderSumCell(sheet);
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

private void hanlderSumCell(Sheet sheet) {
    for (TemplateSumHanlder.TemplateSumEntity sumEntity : templateSumHanlder.getDataList()) {
        Cell cell = sheet.getRow(sumEntity.getRow()).getCell(sumEntity.getCol());
        cell.setCellValue(cell.getStringCellValue().replace("sum:(" + sumEntity.getSumKey() + ")",
                sumEntity.getValue() + ""));
    }/*from   w w  w  .j  a v  a2  s  .com*/
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ,??//  ww w.j  a va 2 s  . c  o m
 * @param sheet
 * @param map
 */
private void colForeach(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String text = cell.getStringCellValue();
                if (text.contains(FOREACH_COL) || text.contains(FOREACH_COL_VALUE)) {
                    foreachCol(cell, map, text);
                }
            }
        }
    }
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ,??// w  w  w.ja  v a 2  s.  c om
 * @param sheet
 * @param map
 * @throws Exception 
 */
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String text = cell.getStringCellValue();
                if (text.contains(IF_DELETE)) {
                    if (Boolean.valueOf(
                            eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map)
                                    .toString())) {
                        PoiSheetUtility.deleteColumn(sheet, i);
                    }
                    cell.setCellValue("");
                }
            }
        }
    }
}

From source file:cn.lhfei.fu.poi.HomeworkDataFactory.java

License:Apache License

@Test
public void read() throws IOException, InvalidFormatException {
    InputStream inp = new FileInputStream(
            "E:\\Webapp_workspace\\webapps_agent\\forestry-university-app\\src\\test\\resource\\excel\\2014-2015.xlsx");
    //InputStream inp = new FileInputStream("workbook.xlsx");

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);

    int totalRows = sheet.getPhysicalNumberOfRows();
    Cell teacherCell = null;//from   ww  w. j  a  v a  2 s  .  c o m
    Cell courseCell = null;
    Cell academicYearCell = null;
    Cell semesterCell = null;
    Cell classCell = null;

    String teacherName = null;
    String courseName = null;
    String academicYear = null;
    String semester = null;
    String classNames = null;

    HomeworkBaseModel model = null;
    Date currentDate = new Date();
    for (int i = 0; i < totalRows; i++) {
        Row row = sheet.getRow(i);

        teacherCell = row.getCell(2); // 
        courseCell = row.getCell(3); // 
        academicYearCell = row.getCell(4); // 
        semesterCell = row.getCell(5); // 
        classCell = row.getCell(10); // ?

        if (teacherCell != null) {
            teacherName = teacherCell.getStringCellValue().trim();
        }
        if (courseCell != null) {
            courseName = courseCell.getStringCellValue().trim();
        }
        if (academicYearCell != null) {
            academicYear = academicYearCell.getStringCellValue().trim();
        }
        if (semesterCell != null) {
            semester = semesterCell.getStringCellValue().trim();
        }
        if (classCell != null) {
            classNames = classCell.getStringCellValue().trim();

            String[] names = classNames.split(",");
            if (names != null && names.length > 0) {
                for (String className : names) {
                    model = new HomeworkBaseModel();
                    model.setAcademicYear(academicYear);
                    model.setSemester(semester);
                    model.setActionType(OperationTypeEnum.SC.getCode().toString());
                    model.setClassName(className);
                    model.setCourseName(courseName);
                    model.setCreateTime(currentDate);
                    model.setTeacherName(teacherName);
                }
            }

        }

        log.info("teacher={}, course={}, xn={}, xq={}, homework={}", teacherName, courseName, academicYear,
                semester, classNames);
    }

}

From source file:cn.lhfei.fu.service.impl.ExcelServiceImpl.java

License:Apache License

/**
 * <tt> ?   ??   ?   </tt>/*from ww w.  j a  v a 2  s.  c om*/
 * <tt>       ?   ??   </tt>
 * @param file
 * @param degreeEnum
 * @return
 * @throws IOException 
 * @throws InvalidFormatException 
 */
public void importStudent(InputStream file, DegreeEnum degreeEnum) throws InvalidFormatException, IOException {

    Date currentDate = new Date();
    List<StudentBaseModel> studentList = new ArrayList<StudentBaseModel>();
    List<UserModel> userList = new ArrayList<UserModel>();

    Workbook wb = WorkbookFactory.create(file);
    Sheet sheet = wb.getSheetAt(0);

    int totalRows = sheet.getPhysicalNumberOfRows();

    Cell studentIdCell = null; // ?
    Cell studentNameCell = null; // ??
    Cell classCell = null; // ?
    Cell genderCell = null; // 

    Integer degree = degreeEnum.getCode();

    int size = 0;

    for (int i = 1; i < totalRows; i++) {
        String studentId = "";
        String studentName = "";
        int gender = 0; // (0)|(1)
        String genderStr = "";
        String className = "";
        String grade = "";
        String major = "";
        String desc = "?";

        Row row = sheet.getRow(i);

        StudentBaseModel model = new StudentBaseModel();
        UserModel userModel = new UserModel();

        if (degreeEnum.equals(DegreeEnum.BK)) { // ?

            studentIdCell = row.getCell(0);
            studentNameCell = row.getCell(1);
            classCell = row.getCell(2);
            genderCell = row.getCell(3);

            className = classCell.getStringCellValue();

        } else if (degreeEnum.equals(DegreeEnum.SS)) {
            Cell gradeCell = null; // 
            Cell majorCell = null; // 

            gradeCell = row.getCell(0);
            majorCell = row.getCell(1);
            studentIdCell = row.getCell(2);
            studentNameCell = row.getCell(3);
            genderCell = row.getCell(4);

            grade = gradeCell.getStringCellValue();
            major = majorCell.getStringCellValue();

        }

        studentIdCell.setCellType(Cell.CELL_TYPE_STRING);

        studentId = studentIdCell.getStringCellValue();
        studentName = studentNameCell.getStringCellValue();
        genderStr = genderCell.getStringCellValue();

        if ("".equals(genderStr)) {
            gender = 1;
        } else {
            gender = 0;
        }

        model.setClassName(className);
        model.setGrade(grade);
        model.setMajorName(major);
        model.setMajorCode(major);

        model.setStudentId(studentId);
        model.setName(studentName);
        model.setDegree(degree);
        model.setGender(gender);

        model.setCreateTime(currentDate);
        model.setModifyTime(currentDate);
        model.setBirthday(currentDate);

        model.setUserType(UserTypeEnum.STUDENT.getCode());
        model.setAliasName(studentName);
        model.setEmail(studentId + "@fu.com");
        model.setExtend(desc);
        model.setExtend1(desc);

        log.info("Total Rows: {}", totalRows);

        userModel.setBirthday(currentDate);
        userModel.setCreateTime(currentDate);
        userModel.setGender(gender);
        userModel.setModifyTime(currentDate);
        userModel.setPassWord(SecurityUtils.toMd5("123456"));
        userModel.setRoleId(24);
        userModel.setSignTime(currentDate);
        userModel.setUserId(studentId);
        userModel.setUserName(studentName);
        userModel.setAliasName(studentName);
        userModel.setEmail(studentId + "@fu.com");
        userModel.setUserType(UserTypeEnum.STUDENT.getCode());

        size++;

        userList.add(userModel);
        studentList.add(model);

        if (size % MAX_BATCH_SIZE == 0) {
            excelMapper.importStudent(studentList);
            excelMapper.importSysUser(userList);

            studentList = new ArrayList<StudentBaseModel>();
            userList = new ArrayList<UserModel>();
        }

    }

    if (studentList.size() > 0) {
        excelMapper.importStudent(studentList);
        excelMapper.importSysUser(userList);
    }

}