Java之poi导入excel数据

以你之姓@ 2024-03-25 21:40 46阅读 0赞

使用开源poi框架进行导入数据，废话不多说，直接上代码。

1.pom依赖：

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

2.Controller代码

public AjaxResult importData(MultipartFile file) throws Exception {
            List<Student> studentList = new ArrayList<>();
            //根据路径获取这个操作excel表格
            XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
            //根据页面index 获取sheet页
            XSSFSheet sheet = wb.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                XSSFRow row = sheet.getRow(i);
                if (row!= null) {
                    List<String> list = new ArrayList<>();
                    for (Cell cell : row) {
                        if (cell!=null){
                            cell.setCellType(CellType.STRING);
                            String value = cell.getStringCellValue();
                            if (value!=null && !"".equals(value))
                                list.add(value);
                        }
                    }
                    if (list.size()>0){
                        Student student = new Student();
                        student.setName(row.getCell(0).getStringCellValue());
                        student.setSex(row.getCell(0).getStringCellValue());
                        student.setAge(Integer.valueOf((int)row.getCell(2).getNumericCellValue()));
                        student.setAddress(row.getCell(0).getStringCellValue());
                        student.setName(row.getCell(0).getStringCellValue());
                        student.setName(row.getCell(0).getStringCellValue());
                        studentList.add(student);
    //                    System.out.println(studentList);
                    }
                }
            }
            //调取实体对应的service层新增方法
            studentService.importByExcel(studentList);
            return AjaxResult.success();
        }

以上是后缀为xlsx，将以下代码替换即是后缀为xls的excel

//根据路径获取这个操作excel(后缀为.xls) 
     
    HSSFWorkbook hb = new HSSFWorkbook(file.getInputStream())
     
    //.xls格式 
     
    HSSFSheet sheet = hb.getSheetAt(0);
     
    XSSFRow row = sheet.getRow(i);

嫌麻烦？直接两个都判断的代码

public AjaxResult importData(MultipartFile file) throws Exception {
            List<Student> studentList = new ArrayList<>();
            String fileName = file.getOriginalFilename();
            String name = fileName.substring(0, fileName.indexOf("."));
            String hzm =fileName.substring(name.length()+1);
            if (StringUtils.equals(hzm,"xlsx")) {
            //根据路径获取这个操作excel表格
            XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
            //根据页面index 获取sheet页
            XSSFSheet sheet = wb.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                XSSFRow row = sheet.getRow(i);
                if (row!= null) {
                    List<String> list = new ArrayList<>();
                    for (Cell cell : row) {
                        if (cell!=null){
                            cell.setCellType(CellType.STRING);
                            String value = cell.getStringCellValue();
                            if (value!=null && !"".equals(value))
                                list.add(value);
                        }
                    }
                    if (list.size()>0){
                        Student student = new Student();
                        student.setName(row.getCell(0).getStringCellValue());
                        student.setSex(row.getCell(0).getStringCellValue());
                        student.setAge(Integer.valueOf((int)row.getCell(2).getNumericCellValue()));
                        student.setAddress(row.getCell(0).getStringCellValue());
                        student.setName(row.getCell(0).getStringCellValue());
                        student.setName(row.getCell(0).getStringCellValue());
                        studentList.add(student);
    //                    System.out.println(studentList);
                    }
                }
            }
        } else if (StringUtils.equals(hzm,"xls")) {
            //根据路径获取这个操作excel表格
            HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
            //根据页面index 获取sheet页
            HSSFSheet sheet = wb.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                HSSFRow row = sheet.getRow(i);
                if (row!= null) {
                    List<String> list = new ArrayList<>();
                    for (Cell cell : row) {
                        if (cell!=null){
                            cell.setCellType(CellType.STRING);
                            String value = cell.getStringCellValue();
                            if (value!=null && !"".equals(value))
                                list.add(value);
                        }
                    }
                    if (list.size()>0){
                        Student student = new Student();
                        student.setName(row.getCell(0).getStringCellValue());
                        student.setSex(row.getCell(0).getStringCellValue());
                        student.setAge(Integer.valueOf((int)row.getCell(2).getNumericCellValue()));
                        student.setAddress(row.getCell(0).getStringCellValue());
                        student.setName(row.getCell(0).getStringCellValue());
                        student.setName(row.getCell(0).getStringCellValue());
                        studentList.add(student);
    //                    System.out.println(studentList);
                    }
                }
            }
        }
            //调取实体对应的service层新增方法
            studentService.importByExcel(studentList);
            return AjaxResult.success();
        }

以上就是导入功能，看完理解理解发现也不难！

————————————————

原文链接：[https://blog.csdn.net/qq\_43384268/article/details/125913952][https_blog.csdn.net_qq_43384268_article_details_125913952]

[https_blog.csdn.net_qq_43384268_article_details_125913952]: https://blog.csdn.net/qq_43384268/article/details/125913952