Example usage for org.apache.poi.ss.usermodel WorkbookFactory create

List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. WorkbookFactory
  6. create

Introduction

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

Prototype

public static Workbook create(File file) throws IOException, EncryptedDocumentException

Source Link

Document

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

Usage

From source file:cn.afterturn.easypoi.excel.imports.ExcelImportService.java

License:Apache License

/**
 * Excel  field  Integer,Long,Double,Date,String,Boolean
 *//*from   w  w w.  ja  v  a2 s.c  om*/
public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass, ImportParams params,
        boolean needMore) throws Exception {
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Excel import start ,class is {}", pojoClass);
    }
    List<T> result = new ArrayList<T>();
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    ExcelImportResult importResult;
    try {
        byte[] buffer = new byte[1024];
        int len;
        while ((len = inputstream.read(buffer)) > -1) {
            baos.write(buffer, 0, len);
        }
        baos.flush();

        InputStream userIs = new ByteArrayInputStream(baos.toByteArray());
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Excel clone success");
        }
        Workbook book = WorkbookFactory.create(userIs);

        boolean isXSSFWorkbook = !(book instanceof HSSFWorkbook);
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Workbook create success");
        }
        importResult = new ExcelImportResult();
        createErrorCellStyle(book);
        Map<String, PictureData> pictures;
        for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex() + params.getSheetNum(); i++) {
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug(" start to read excel by is ,startTime is {}", new Date());
            }
            if (isXSSFWorkbook) {
                pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i),
                        (XSSFWorkbook) book);
            } else {
                pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i),
                        (HSSFWorkbook) book);
            }
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug(" end to read excel by is ,endTime is {}", new Date());
            }
            result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug(" end to read excel list by sheet ,endTime is {}", new Date());
            }
            if (params.isReadSingleCell()) {
                readSingleCell(importResult, book.getSheetAt(i), params);
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug(" read Key-Value ,endTime is {}", System.currentTimeMillis());
                }
            }
        }
        if (params.isNeedSave()) {
            saveThisExcel(params, pojoClass, isXSSFWorkbook, book);
        }
        importResult.setList(result);
        if (needMore) {
            InputStream successIs = new ByteArrayInputStream(baos.toByteArray());
            try {
                Workbook successBook = WorkbookFactory.create(successIs);
                importResult.setWorkbook(removeSuperfluousRows(successBook, failRow, params));
                importResult.setFailWorkbook(removeSuperfluousRows(book, successRow, params));
                importResult.setFailList(failCollection);
                importResult.setVerfiyFail(verifyFail);
            } finally {
                successIs.close();
            }
        }
    } finally {
        IOUtils.closeQuietly(baos);
    }

    return importResult;
}

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

License:Apache License

public static <T> List<T> parse(InputStream inputStream, Class<T> type, @Nonnull Locale locale) {
    Objects.requireNonNull(locale, "locale");
    boolean support = inputStream.markSupported();
    InputStream is = support ? inputStream : new BufferedInputStream(inputStream);
    try (Workbook workbook = WorkbookFactory.create(is)) {
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        return parse(workbook, evaluator, type, locale);
    } catch (IOException | IllegalStateException ex) {
        throw new BusinessException(BusinessCode.INVALID_EXCEL);
    }//from www.ja  va  2  s  .c o m
}

From source file:cn.edu.zucc.chenxg.preview.ToHtml.java

License:Apache License

/**
 * Creates a new converter to HTML for the given workbook.  This attempts to
 * detect whether the input is XML (so it should create an {@link
 * XSSFWorkbook} or not (so it should create an {@link HSSFWorkbook}).
 *
 * @param in     The input stream that has the workbook.
 * @param output Where the HTML output will be written.
 *
 * @return An object for converting the workbook to HTML.
 *//* w ww  .jav  a 2s  . c  om*/
public static ToHtml create(InputStream in, Appendable output) throws IOException {
    try {
        Workbook wb = WorkbookFactory.create(in);
        return create(wb, output);
    } catch (InvalidFormatException e) {
        throw new IllegalArgumentException("Cannot create workbook from stream", e);
    }
}

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 . jav a  2s  .c o 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);//w ww .j a  v a2 s. co  m

    int totalRows = sheet.getPhysicalNumberOfRows();
    Cell teacherCell = null;
    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 w w  w  .j ava  2s  . c o 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

/**
 * //www .  jav  a  2s. 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);//from  w  ww.  j a  va  2s  . co  m

    int totalRows = sheet.getPhysicalNumberOfRows();
    Cell teacherCell = null;
    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);//from   ww  w  .  j  av  a2s.c  o  m

    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;
    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);//from   w ww .  j  a v  a2  s.co m

    int totalRows = sheet.getPhysicalNumberOfRows();

    Cell teacherIdCell = null;
    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);
    }

}