Example usage for org.apache.poi.ss.usermodel Cell getStringCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getStringCellValue

Introduction

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

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

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

License:Apache License

/**
 * ,??//  ww w.ja v a  2s .  c o  m
 * @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.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ?Cell??set/*from w  w  w .j a  v a2s .  c o  m*/
 * 
 * @param cell
 * @param map
 */
private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {
    int cellType = cell.getCellType();
    if (cellType != Cell.CELL_TYPE_STRING && cellType != Cell.CELL_TYPE_NUMERIC) {
        return;
    }
    String oldString;
    cell.setCellType(Cell.CELL_TYPE_STRING);
    oldString = cell.getStringCellValue();
    if (oldString != null && oldString.indexOf(START_STR) != -1 && !oldString.contains(FOREACH)) {
        // step 2. ???
        String params = null;
        boolean isNumber = false;
        if (isNumber(oldString)) {
            isNumber = true;
            oldString = oldString.replaceFirst(NUMBER_SYMBOL, "");
        }
        while (oldString.indexOf(START_STR) != -1) {
            params = oldString.substring(oldString.indexOf(START_STR) + 2, oldString.indexOf(END_STR));

            oldString = oldString.replace(START_STR + params + END_STR, eval(params, map).toString());
        }
        // ,
        if (isNumber && StringUtils.isNotBlank(oldString)) {
            cell.setCellValue(Double.parseDouble(oldString));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        } else {
            cell.setCellValue(oldString);
        }
    }
    //foreach ?
    if (oldString != null && oldString.contains(FOREACH)) {
        addListDataToExcel(cell, map, oldString.trim());
    }

}

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

License:Apache License

/**
 * ??//  www. j  av  a 2s  . c o  m
 * @param cell
 * @param name
 * @param mergedRegionHelper 
 * @return
 */
private Object[] getAllDataColumns(Cell cell, String name, MergedRegionHelper mergedRegionHelper) {
    List<ExcelForEachParams> columns = new ArrayList<ExcelForEachParams>();
    cell.setCellValue("");
    columns.add(getExcelTemplateParams(name.replace(END_STR, EMPTY), cell, mergedRegionHelper));
    int rowspan = 1, colspan = 1;
    if (!name.contains(END_STR)) {
        int index = cell.getColumnIndex();
        //?col 
        int startIndex = cell.getColumnIndex();
        Row row = cell.getRow();
        while (true) {
            int colSpan = columns.get(columns.size() - 1) != null ? columns.get(columns.size() - 1).getColspan()
                    : 1;
            index += colSpan;
            for (int i = 1; i < colSpan; i++) {
                //??,???,,?
                columns.add(null);
                continue;
            }
            cell = row.getCell(index);
            //???
            if (cell == null) {
                //?,
                columns.add(null);
                continue;
            }
            String cellStringString;
            try {//?? ?
                cellStringString = cell.getStringCellValue();
                if (StringUtils.isBlank(cellStringString) && colspan + startIndex <= index) {
                    throw new ExcelExportException("for each ,?");
                } else if (StringUtils.isBlank(cellStringString) && colspan + startIndex > index) {
                    //?,,?,?,?
                    columns.add(new ExcelForEachParams(null, cell.getCellStyle(), (short) 0));
                    continue;
                }
            } catch (Exception e) {
                throw new ExcelExportException(ExcelExportEnum.TEMPLATE_ERROR, e);
            }
            //?cell 
            cell.setCellValue("");
            if (cellStringString.contains(END_STR)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(END_STR, EMPTY), cell,
                        mergedRegionHelper));
                break;
            } else if (cellStringString.contains(WRAP)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
                //??,??
                colspan = index - startIndex + 1;
                index = startIndex - 1;
                row = row.getSheet().getRow(row.getRowNum() + 1);
                rowspan++;
            } else {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
            }
        }
    }
    colspan = 0;
    for (int i = 0; i < columns.size(); i++) {
        colspan += columns.get(i) != null ? columns.get(i).getColspan() : 0;
    }
    colspan = colspan / rowspan;
    return new Object[] { rowspan, colspan, columns };
}

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

License:Apache License

/**
 * ?key,?????/* w  ww  . j  a va 2 s. co m*/
 * 
 * @param cell
 * @return
 */
private String getKeyValue(Cell cell) {
    Object obj = null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        obj = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        obj = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        obj = cell.getNumericCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        obj = cell.getCellFormula();
        break;
    default:
        cell.setCellType(Cell.CELL_TYPE_STRING);
        obj = cell.getStringCellValue();
    }
    return obj == null ? null : obj.toString().trim();
}

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

License:Apache License

/**
 * ??/*from w  w  w .java  2s .  c o  m*/
 * @param object
 * @param row
 * @param params
 * @param pojoClass
 * @return
 */
private boolean verifyingDataValidity(Object object, Row row, ImportParams params, Class<?> pojoClass) {
    boolean isAdd = true;
    Cell cell = null;
    if (params.isNeedVerfiy()) {
        String errorMsg = PoiValidationUtil.validation(object);
        if (StringUtils.isNotEmpty(errorMsg)) {
            cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(errorMsg);
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(errorMsg);
            } else {
                isAdd = false;
            }
            verfiyFail = true;
        }
    }
    if (params.getVerifyHanlder() != null) {
        ExcelVerifyHanlderResult result = params.getVerifyHanlder().verifyHandler(object);
        if (!result.isSuccess()) {
            if (cell == null)
                cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(
                    (StringUtils.isNoneBlank(cell.getStringCellValue()) ? cell.getStringCellValue() + "," : "")
                            + result.getMsg());
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(
                        (StringUtils.isNoneBlank(model.getErrorMsg()) ? model.getErrorMsg() + "," : "")
                                + result.getMsg());
            } else {
                isAdd = false;
            }
            verfiyFail = true;
        }
    }
    if (cell != null)
        cell.setCellStyle(errorCellStyle);
    return isAdd;
}

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

License:Apache License

private static JsonElement parseAsJsonElement(Cell cell, FormulaEvaluator evaluator) {
    switch (cell.getCellType()) {
    case NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return new JsonPrimitive(DateFormatterHolder.FORMATTER.format(cell.getDateCellValue().toInstant()));
        } else {/*from  w  w  w .jav  a  2s .  com*/
            return new JsonPrimitive(cell.getNumericCellValue());
        }
    case STRING:
        return new JsonPrimitive(cell.getStringCellValue());
    case FORMULA:
        CellValue cellValue = evaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
        case NUMERIC:
            return new JsonPrimitive(cellValue.getNumberValue());
        case STRING:
            return new JsonPrimitive(cellValue.getStringValue());
        case BLANK:
            return new JsonPrimitive("");
        case BOOLEAN:
            return new JsonPrimitive(cellValue.getBooleanValue());
        case ERROR:
        default:
            return null;
        }
    case BLANK:
        return new JsonPrimitive("");
    case BOOLEAN:
        return new JsonPrimitive(cell.getBooleanCellValue());
    case ERROR:
    default:
        return null;
    }
}

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);//  ww  w  .j av a 2 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>/* w w w . j  a  v a2s  .  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

/**
 * /*from   w  w  w . j a  va  2 s  .co  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);//ww w.  j a  v a 2  s. c om

    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;
}