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.lhfei.fu.service.impl.ExcelServiceImpl.java

License:Apache License

/**
 * //ww  w  . ja v a  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);

    int totalRows = sheet.getPhysicalNumberOfRows();
    Cell teacherCell = null;/*from  www.j a v a 2s.  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();
    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  w  w .  j a v a 2s . 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 ww w  . j ava 2 s  .  c o  m*/
    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;//from   w w w.jav  a2  s.c om
    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;
}

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * ??ExcelTitle//from w  w w  .  j a va  2 s.  co m
 *
 * @param excelFile
 * @param sheetName sheet???
 * @return
 */
public static List<String> scanExcelTitles(File excelFile, String... sheetName) {
    List<String> titles = new ArrayList<>();
    try {
        Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFile));

        Sheet sheet;
        if (sheetName.length == 0) {
            sheet = workbook.getSheetAt(0);
        } else {
            sheet = workbook.getSheet(sheetName[0]);
        }
        Row row = sheet.getRow(0);
        if (row != null) {
            int i = 0;
            while (true) {
                Cell cell = row.getCell(i);
                if (cell == null) {
                    break;
                }
                titles.add(cell.getStringCellValue());
                i++;
            }
        }
    } catch (Exception e) {
        logger.debug("Scan Excel [" + excelFile.getPath() + excelFile.getName() + "] Error");
        throw new RuntimeException(e);
    }
    return titles;
}

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * @param workbook /*w ww  .j  av  a 2 s  .c  om*/
 * @param fieldNames ??
 * @param sheetName ???
 * @return
 */
private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) {
    String[] strKey = fieldNames.split(",");
    List<Map<String, String>> listMap = new ArrayList<>();
    int i = 1;
    try {
        Sheet sheet;
        if (sheetName.length == 0) {
            sheet = workbook.getSheetAt(0);
        } else {
            sheet = workbook.getSheet(sheetName[0]);
        }
        while (true) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            Map<String, String> map = new HashMap<String, String>();
            map.put("rowid", String.valueOf(row.getRowNum()));
            for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) {
                Cell cell;
                cell = row.getCell(keyIndex);
                String cellValue = "";
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        // ?cell?Date
                        if (DateUtil.isCellDateFormatted(cell)) {
                            // Date?CellDate
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        // 
                        else {
                            // ??Cell
                            Integer num = new Integer((int) cell.getNumericCellValue());
                            cellValue = String.valueOf(num);
                        }
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getRichStringCellValue().getString();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        System.out.println(cell.getCellFormula());
                        break;
                    default:
                        cellValue = " ";
                    }
                }
                map.put(strKey[keyIndex], cellValue);
            }
            listMap.add(map);
            i++;
        }
    } catch (Exception e) {
        logger.debug("?" + i + "??");
        throw new RuntimeException(e);
    }
    return listMap;
}

From source file:cn.poi.api.example.ExcelExample.java

License:Open Source License

public static void ReadExcel(String excel, String brandcode)
        throws EncryptedDocumentException, InvalidFormatException, IOException {
    List<BrandConfigCommand> list = new ArrayList<>();
    InputStream inp = resourceLoader.getResource(excel).getInputStream();
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(4);
    Row tempRow = null;//from   w w  w  . j a v  a2 s  . c  om
    for (int i = 1; i < sheet.getLastRowNum(); i++) {
        BrandConfigCommand brandConfigCommand = new BrandConfigCommand();
        String[] array = new String[5];
        Row row = sheet.getRow(i);
        if (StringUtils.isEmpty(row.getCell(4).toString())) {
            continue;
        }
        for (int j = 0; j < row.getLastCellNum(); j++) {
            if (j == 0 && StringUtils.isEmpty(row.getCell(j).toString())) {
                System.out.print(tempRow.getCell(j).getStringCellValue() + "    ");
                array[j] = tempRow.getCell(0).getStringCellValue();
                continue;
            } else if (j == 0) {
                tempRow = row;
            }
            if (j == 1 && StringUtils.isEmpty(row.getCell(j).toString())) {
                System.out.print(tempRow.getCell(j).getStringCellValue() + "    ");
                array[j] = tempRow.getCell(j).getStringCellValue();
                continue;
            }
            array[j] = row.getCell(j).getStringCellValue();
            System.out.print(row.getCell(j).toString() + "    ");

        }

        brandConfigCommand.setStoreCode(array[0]);
        brandConfigCommand.setStoreDate(array[1]);
        brandConfigCommand.setProvice(array[2]);
        brandConfigCommand.setCity(array[3]);
        brandConfigCommand.setArea(array[4]);
        brandConfigCommand.setArea_name(array[2] + array[3] + array[4]);
        list.add(brandConfigCommand);
        System.out.println();
    }

    List<String> listStr = new ArrayList<>();
    for (BrandConfigCommand brandConfigCommand : list) {
        String str = sqlStart + brandConfigCommand.getArea_name() + sqlEnd + provice
                + brandConfigCommand.getProvice() + city + brandConfigCommand.getCity() + area
                + brandConfigCommand.getArea() + code + brandConfigCommand.getStoreCode() + brand_code
                + brandcode + "'";
        listStr.add(str);
    }
    System.out.println(JSON.toJSONString(listStr));

}

From source file:cn.study.innerclass.Test.java

License:Open Source License

public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException {
    /*Exsample ex = new Exsample();
    Exsample.PublicInnerClass pub = ex.new PublicInnerClass();
    pub.setPuAge(111);/*from   w  w  w  .j  a v a 2  s . c  o m*/
    pub.test();*/

    /*Workbook book = new HSSFWorkbook();
    Cell cell = book.createSheet().createRow(1).createCell(1);
    cell.setCellValue(5l);
    System.out.println(cell.getCellType());*/

    FileInputStream fin = new FileInputStream("E:/test.xlsx");
    Workbook book = WorkbookFactory.create(fin);
    Sheet sheet = book.getSheetAt(0);
    ;
    PoiUtil.getCellData(sheet.getRow(0).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(0).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(0).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(1).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(1).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(1).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(2).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(2).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(2).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(3).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(3).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book));
    PoiUtil.getCellData(sheet.getRow(3).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book));
}

From source file:CODIGOS.EditaPlanilha.java

public static void edita(String diretorio, String arquivo, int linha, int coluna, String texto) {
    try {//from  w w  w  . j  a  va2s. com
        File dir = new File(diretorio);
        File file = new File(dir, arquivo + ".xlsx");
        InputStream inp = new FileInputStream(file);
        try {
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            Row row = sheet.getRow(linha);//LINHA
            Cell cell = row.getCell(0);
            cell = row.createCell(coluna);//COLUNA
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(texto);
            try (FileOutputStream fileOut = new FileOutputStream(file)) {
                wb.write(fileOut);
            }
        } catch (IOException | InvalidFormatException ex) {
            Logger.getLogger(EditaPlanilha.class.getName()).log(Level.SEVERE, null, ex);
        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(EditaPlanilha.class.getName()).log(Level.SEVERE, null, ex);
    }
}