Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:cn.bzvs.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * Excel  field  Integer,Long,Double,Date,String,Boolean
 * /*from  w w  w  .  ja  v a 2 s .c  o  m*/
 * @param inputstream
 * @param pojoClass
 * @param params
 * @return
 * @throws Exception
 */
public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass, ImportParams params)
        throws Exception {
    List<T> result = new ArrayList<T>();
    Workbook book = null;
    boolean isXSSFWorkbook = true;
    if (!(inputstream.markSupported())) {
        inputstream = new PushbackInputStream(inputstream, 8);
    }
    if (POIFSFileSystem.hasPOIFSHeader(inputstream)) {
        book = new HSSFWorkbook(inputstream);
        isXSSFWorkbook = false;
    } else if (POIXMLDocument.hasOOXMLHeader(inputstream)) {
        book = new XSSFWorkbook(OPCPackage.open(inputstream));
    }
    createErrorCellStyle(book);
    Map<String, PictureData> pictures;
    for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex() + params.getSheetNum(); i++) {
        if (isXSSFWorkbook) {
            pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);
        } else {
            pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i), (HSSFWorkbook) book);
        }
        result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));
    }
    if (params.isNeedSave()) {
        saveThisExcel(params, pojoClass, isXSSFWorkbook, book);
    }
    return new ExcelImportResult(result, verfiyFail, book);
}

From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java

License:Apache License

private static <T> List<T> parse(Workbook workbook, FormulaEvaluator evaluator, Class<T> type, Locale locale) {
    MetaInfo metaInfo = MetaInfo.forType(type, locale);
    Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Iterator<Row> rows = sheet.rowIterator();
    if (!rows.hasNext()) {
        return Collections.emptyList();
    }/* ww w  .j av a 2  s  . c  om*/
    Row firstRow = rows.next();
    Map<Integer, String> columnIndexToFieldName = Maps.newHashMapWithExpectedSize(metaInfo.size());
    for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) {
        Cell cell = it.next();
        JsonElement jsonElement = parseAsJsonElement(cell, evaluator);
        if (jsonElement != null) {
            Field field = metaInfo.getField(jsonElement.getAsString());
            if (field != null) {
                String name = field.getName();
                int index = cell.getColumnIndex();
                columnIndexToFieldName.put(index, name);
            }
        }
    }
    if (columnIndexToFieldName.isEmpty()) {
        return Collections.emptyList();
    }
    List<T> result = new ArrayList<>(sheet.getLastRowNum() - sheet.getFirstRowNum());
    while (rows.hasNext()) {
        result.add(parseRow(evaluator, rows.next(), columnIndexToFieldName, type));
    }
    return result;
}

From source file:cn.hanbell.war.control.InvmasImportManagedBean.java

@Override
public void handleFileUploadWhenNew(FileUploadEvent event) {
    super.handleFileUploadWhenNew(event);
    if (this.fileName != null) {
        InvmasImport e;/*  ww  w.j ava 2  s . co m*/
        if (addedList != null) {
            addedList.clear();
        }
        try {
            InputStream is = new FileInputStream(getAppResPath() + "/" + fileName);
            Workbook wb = WorkbookFactory.create(is);
            Sheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());
            Cell c;
            for (Row r : sheet) {
                if (r.getRowNum() == 0) {
                    continue;
                }
                e = new InvmasImport();
                c = r.getCell(0);
                e.setItcls(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(1);
                e.setItnbr(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(2);
                e.setItdsc(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(3);
                e.setSpdsc(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(4);
                e.setEitdsc(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(5);
                e.setEspdsc(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(6);
                e.setKind(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(7);
                e.setUnmsr1(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(8);
                e.setUsed(BaseLib.convertExcelCell(String.class, c).trim());
                e.setStatus("N");
                e.setCreator(userManagedBean.getCurrentUser().getUsername());
                e.setCredateToNow();
                addedList.add(e);
            }
            doAdd = true;
            showInfoMsg("Info", "?");
        } catch (IOException | InvalidFormatException ex) {
            showErrorMsg("Info", ",??");
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        }
    }
}

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  w  ww .  j  a va  2  s. c  om
    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>/* ww  w .  ja  va2s .co  m*/
 * <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);
    }

}

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

License:Apache License

/**
 * //  w w w.j av  a 2  s.c  o  m
 * -?-?-??-()-()-??--?-??-??
 * 
 * @param file
 * @param degreeEnum
 * @return
 * @throws IOException 
 * @throws InvalidFormatException 
 */
public void importThesis(InputStream file, DegreeEnum degreeEnum) throws InvalidFormatException, IOException {

    Date currentDate = new Date();
    List<ThesisBaseModel> thesisList = new ArrayList<ThesisBaseModel>();

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

    int totalRows = sheet.getPhysicalNumberOfRows();

    Cell academicYearCell = null;
    Cell classCell = null;
    Cell studentIdCell = null;
    Cell studentNameCell = null;
    Cell titleCell = null;
    Cell enTitleCell = null;
    Cell originCell = null;
    Cell thesisTypeCell = null;
    Cell teacherIdCell = null;
    Cell teacherNameCell = null;
    Cell teacherTitleCell = null;

    String desc = "?";
    Integer degree = degreeEnum.getCode();

    int size = 0;

    for (int i = 1; i < totalRows; i++) {
        String academicYear = "";
        String className = "";
        String studentId = "";
        String studentName = "";
        String title = "";
        String enTitle = "";
        String origin = "";
        String thesisType = "";
        String teacherId = "";
        String teacherName = "";
        String teacherTitle = "";

        Row row = sheet.getRow(i);

        academicYearCell = row.getCell(0);
        classCell = row.getCell(1);
        studentIdCell = row.getCell(2);
        studentNameCell = row.getCell(3);
        titleCell = row.getCell(4);
        enTitleCell = row.getCell(5);
        originCell = row.getCell(6);
        thesisTypeCell = row.getCell(7);
        teacherIdCell = row.getCell(8);
        teacherNameCell = row.getCell(9);
        teacherTitleCell = row.getCell(10);

        studentIdCell.setCellType(Cell.CELL_TYPE_STRING);
        teacherIdCell.setCellType(Cell.CELL_TYPE_STRING);
        academicYearCell.setCellType(Cell.CELL_TYPE_STRING);

        academicYear = academicYearCell.getStringCellValue();
        className = classCell.getStringCellValue();
        studentId = studentIdCell.getStringCellValue();
        studentName = studentNameCell.getStringCellValue();
        title = titleCell.getStringCellValue();
        enTitle = enTitleCell.getStringCellValue();
        origin = originCell.getStringCellValue();
        thesisType = thesisTypeCell.getStringCellValue();
        teacherId = teacherIdCell.getStringCellValue();
        teacherName = teacherNameCell.getStringCellValue();
        teacherTitle = teacherTitleCell.getStringCellValue();

        ThesisBaseModel model = new ThesisBaseModel();
        model.setAcademicYear(academicYear);
        model.setClassName(className);
        model.setStudentId(studentId);
        model.setStudentName(studentName);
        model.setThesisTitle(title);
        model.setThesisEnTitle(enTitle);
        model.setOrigin(origin);
        model.setThesisType(thesisType);
        model.setTeacherId(teacherId);
        model.setTeacherName(teacherName);
        model.setTeacherTitle(teacherTitle);

        model.setDescription(desc);
        model.setDegree(degree);
        model.setActionType("" + OperationTypeEnum.PLSC.getCode());

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

        thesisList.add(model);

        size++;

        if (size % 50 == 0) {
            excelMapper.importThesis(thesisList);
            thesisList = new ArrayList<ThesisBaseModel>();
        }
    }

    if (thesisList.size() > 0) {
        excelMapper.importThesis(thesisList);
    }

}

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

License:Apache License

@Override
public boolean importHomework(String filePath) throws Exception {
    InputStream inp = new FileInputStream(filePath);

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

    int totalRows = sheet.getPhysicalNumberOfRows();
    Cell teacherCell = null;//from www  . ja  v a2 s .  co 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();
    List<HomeworkBaseModel> homeworkList = new ArrayList<HomeworkBaseModel>();

    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.setTeacherName(teacherName);

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

                    model.setName("");
                    model.setTeacherId("");
                    model.setCourseCode("0");

                    homeworkList.add(model);
                }
            }

        }

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

    for (HomeworkBaseModel baseModel : homeworkList) {
        excelFactoryMapper.importHomework(baseModel);
    }

    return false;
}

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

License:Apache License

@Override
public boolean importDataByExcel(String filePath, Map<String, Object> params) throws Exception {
    InputStream inp = new FileInputStream(filePath);

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

    int totalRows = sheet.getPhysicalNumberOfRows();
    Cell homeworkNameCell = null; // ??
    Cell academicYearCell = null; // 
    Cell semesterCell = null; // 
    Cell teacherIdCell = null; // ?      
    Cell teacherNameCell = null; // ??      
    Cell classCell = null; // ???
    Cell courseCodeCell = null; // ?      
    Cell courseNameCell = null; // ??         

    String homeworkName = null;// w  ww .java  2 s . c  om
    String teacherId = null;
    String teacherName = null;
    String courseName = null;
    String courseCode = null;
    String academicYear = null;
    String semester = null;
    String className = null;

    HomeworkBaseModel model = null;
    Date currentDate = new Date();
    List<HomeworkBaseModel> homeworkList = new ArrayList<HomeworkBaseModel>();

    for (int i = 1; i < totalRows; i++) {
        Row row = sheet.getRow(i);

        homeworkNameCell = row.getCell(0); // ??
        academicYearCell = row.getCell(1); // 
        semesterCell = row.getCell(2); // 
        teacherIdCell = row.getCell(3); // ?
        teacherNameCell = row.getCell(4); // ??
        classCell = row.getCell(5); // ???
        courseCodeCell = row.getCell(6); // ?
        courseNameCell = row.getCell(7); // ??

        if (homeworkNameCell != null) {
            homeworkName = homeworkNameCell.getStringCellValue().trim();
        }
        if (academicYearCell != null) {
            academicYear = academicYearCell.getStringCellValue().trim();
        }
        if (semesterCell != null) {
            semester = semesterCell.getStringCellValue().trim();
        }
        if (teacherIdCell != null) {
            teacherId = teacherIdCell.getStringCellValue().trim();
        }
        if (teacherNameCell != null) {
            teacherName = teacherNameCell.getStringCellValue().trim();
        }
        if (courseCodeCell != null) {
            courseCode = courseCodeCell.getStringCellValue().trim();
        }
        if (courseNameCell != null) {
            courseName = courseNameCell.getStringCellValue().trim();
        }
        if (classCell != null) {
            className = classCell.getStringCellValue().trim();

            model = new HomeworkBaseModel();

            model.setName(homeworkName);
            model.setAcademicYear(academicYear);
            model.setSemester(semester);
            model.setTeacherId(teacherId);
            model.setTeacherName(teacherName);
            model.setClassName(className);
            model.setCourseCode(courseCode);
            model.setCourseName(courseName);

            model.setActionType(OperationTypeEnum.SC.getCode().toString());
            model.setOperationTime(currentDate);
            model.setCreateTime(currentDate);
            model.setModifyTime(currentDate);

            homeworkList.add(model);

        }

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

    for (HomeworkBaseModel baseModel : homeworkList) {
        excelFactoryMapper.importHomework(baseModel);
    }

    return false;
}

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

License:Apache License

@Test
public void updateTitle() throws Exception {
    String filePath = "src\\test\\resource\\excel\\??? - 2014-10-31???.xlsx";
    String teacherId = "";
    String teacherTitle = "";

    InputStream inp = new FileInputStream(filePath);

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

    int totalRows = sheet.getPhysicalNumberOfRows();

    Cell teacherIdCell = null;/*from   w w  w.j a  v  a  2  s .  com*/
    Cell teacherTitleCell = null;

    for (int i = 1; i < totalRows; i++) {
        Row row = sheet.getRow(i);
        teacherIdCell = row.getCell(1);
        teacherTitleCell = row.getCell(8);

        teacherId = teacherIdCell.getStringCellValue();

        if (teacherId != null && teacherId.startsWith("S")) {
            teacherId = teacherId.replaceAll("S", "");
        }

        teacherTitle = teacherTitleCell.getStringCellValue();

        log.info("ID: {}, Title: {}", teacherId, teacherTitle);

        teacherService.updateTeacherTitle(teacherId, teacherTitle);
    }

}

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

License:Apache License

@Override
public boolean importDataByExcel(String filePath, Map<String, Object> params) throws Exception {

    Date currentDate = new Date();
    List<ThesisBase> thesisList = new ArrayList<ThesisBase>();

    InputStream inp = new FileInputStream(filePath);
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);

    int totalRows = sheet.getPhysicalNumberOfRows();

    Cell classCell = null;/*  w  ww .ja v  a2s  . co  m*/
    Cell studentNameCell = null;
    Cell studentIdCell = null;

    String className = "";
    String studentId = "";
    String studentName = "";

    String desc = "?";
    Integer degree = (Integer) params.get("degree");

    for (int i = 1; i < totalRows; i++) {
        Row row = sheet.getRow(i);

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

        className = classCell.getStringCellValue();
        studentName = studentNameCell.getStringCellValue();
        studentId = studentIdCell.getStringCellValue();

        ThesisBase model = new ThesisBase();
        model.setClassName(className);
        model.setStudentName(studentName);
        model.setStudentId(studentId);
        model.setDegree(degree);
        model.setDesc(desc);
        model.setActionType("" + OperationTypeEnum.PLSC.getCode());

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

        thesisList.add(model);
    }

    log.info("Batch size: ", thesisList.size());

    for (ThesisBase thesis : thesisList) {

        thesisBaseService.save(thesis);
    }

    return false;
}