SpringBoot解析Excel

向右看齐 2022-02-20 17:45 355阅读 0赞

现在很多web应用中，导入excel导出excel很常见，这篇文章就讲讲导入excel文件。

**以批量导入课程为例**

首先加入需要的jar包

<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi</artifactId>
    			<version>RELEASE</version>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml</artifactId>
    			<version>RELEASE</version>
    		</dependency>

数据库中创建一个表course

DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course`  (
      `course_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '课程id',
      `course_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '课程代码',
      `course_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '课程名称',
      `teacher_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '教师id',
      `course_time` date NOT NULL DEFAULT '1996-01-01' COMMENT '开课时间',
      `class_room` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '开课地点',
      `course_week` int(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程学时',
      `course_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '必修课' COMMENT '课程类型',
      `college_id` int(11) UNSIGNED NOT NULL COMMENT '所属院系id',
      `score` int(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT '学分',
      `is_on` tinyint(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否开启了选课，默认0未开启',
      PRIMARY KEY (`course_id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;

新建一个ExcelUtil.java

/**
     * excel工具类
     */
    public class ExcelUtils {
    
        private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    
        /**
         * 课程excel
         * @param in
         * @param fileName
         * @return
         * @throws Exception
         */
        public static List getCourseListByExcel(InputStream in, String fileName) throws Exception {
    
            List list = new ArrayList<>();
    
            // 创建excel工作簿
            Workbook work = getWorkbook(in, fileName);
            if (null == work) {
                throw new Exception("创建Excel工作薄为空！");
            }
    
            Sheet sheet = null;
            Row row = null;
            Cell cell = null;
    
            for (int i = 0; i < work.getNumberOfSheets(); i++) {
    
                sheet = work.getSheetAt(i);
                if(sheet == null) {
                    continue;
                }
    
                // 滤过第一行标题
                for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                    row = sheet.getRow(j);
                    if (row == null || row.getFirstCellNum() == j) {
                        continue;
                    }
    
                    List<Object> li = new ArrayList<>();
    
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        cell = row.getCell(y);
                        // 日期类型转换
                        if(y == 3) {
                            //cell.setCellType(CellType.STRING);
                            double s1 = cell.getNumericCellValue();
                            Date date = HSSFDateUtil.getJavaDate(s1);
                            li.add(date);
                            continue;
                        }
    
                        li.add(cell);
                    }
                    list.add(li);
                }
            }
            work.close();
            return list;
        }
    
        /**
         * 判断文件格式
         * @param in
         * @param fileName
         * @return
         */
        private static Workbook getWorkbook(InputStream in, String fileName) throws Exception {
    
            Workbook book = null;
            String filetype = fileName.substring(fileName.lastIndexOf("."));
    
            if(".xls".equals(filetype)) {
                book = new HSSFWorkbook(in);
            } else if (".xlsx".equals(filetype)) {
                book = new XSSFWorkbook(in);
            } else {
                throw new Exception("请上传excel文件！");
            }
    
            return book;
        }
    }

**这里主要注意一下上面的日期转换，在excel中的日期，通过Java读出来之后，变成了26 四月 2019这样的形式，而数据库中我们的字段类型为date，所以总是插入失败。**

上面我的写法直接是知道那个字段是Date类型，所以直接使用y==3，这样写可复用性很差。

接下来直接看和数据库交互的逻辑代码

/**
         * 通过excel文件，批量增加课程
         * @param request
         * @return
         * @throws Exception
         */
        @PostMapping("/upload/course")
        public String uploadCourseExcel(HttpServletRequest request) {
    
    
            MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
    
            MultipartFile file = multipartHttpServletRequest.getFile("courseFile");
            if(file.isEmpty()) {
                return "redirect:/admin/course/list";
            }
    
            try {
                InputStream inputStream = file.getInputStream();
                List<List<Object>> list = ExcelUtils.getCourseListByExcel(inputStream, file.getOriginalFilename());
                inputStream.close();
    
                for (int i = 0; i < list.size(); i++) {
                    List<Object> courseList = list.get(i);
    
                    Course course = new Course();
    
                    course.setCourseCode(courseList.get(0).toString());
                    course.setCourseName(courseList.get(1).toString());
                    // 通过教师姓名查教师id
                    String teacherId = teacherService.getTeacByName(courseList.get(2).toString());
                    // 教师信息错误，直接跳过这条记录
                    if(teacherId == null) {
                        continue;
                    }
                    course.setTeacherId(teacherId);
    
                    // 格式化时间
                    Date date = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US).parse(courseList.get(3).toString());
                    course.setCourseTime(new SimpleDateFormat("yyyy-MM-dd").parse(new SimpleDateFormat("yyyy-MM-dd").format(date)));
    
                    course.setClassRoom(courseList.get(4).toString());
                    course.setCourseWeek(Integer.parseInt(new DecimalFormat("0").format(Double.parseDouble(courseList.get(5).toString()))));
    
                    course.setCourseType(courseList.get(6).toString());
                    // 通过院系名称查询院系id
                    Integer collegeId = collegeService.getCollegeByName(courseList.get(7).toString());
                    // 院系有误，直接跳过这条记录
                    if(collegeId == null || collegeId == 0) {
                        continue;
                    }
                    course.setCollegeId(collegeId);
                    course.setScore(Integer.parseInt(new DecimalFormat("0").format(Double.parseDouble(courseList.get(8).toString()))));
                    // 默认不开启选课
                    course.setIsOn(0);
    
                    logger.error("course = " + course);
    
                    // 判断课程是否重复(同一门课程可以有多个教师教师course_code, course_name, teacher_id联合)
                    Integer courseId = null;
                    courseId = courseService.getCourseByThree(course);
    
                    // 存在重复的
                    if(courseId != null) {
                        // 跳过不添加
                        continue;
                    }
    
                    // 执行插入操作
                    courseService.addCourse(course);
                }
            } catch (Exception e) {
                return "redirect:/admin/course/list";
            }
    
            return "redirect:/admin/course/list";
        }

可以看到，我又对时间类型进行了处理，才能最终插入数据库

// 格式化时间
    Date date = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US).parse(courseList.get(3).toString());
    course.setCourseTime(new SimpleDateFormat("yyyy-MM-dd").parse(new SimpleDateFormat("yyyy-MM-dd").format(date)));

同时，在excel中的整数类型，取出来之后就会变成Double类型比如5变成5.0，所以我对此也进行了处理

course.setCourseWeek(Integer.parseInt(new DecimalFormat("0").format(Double.parseDouble(courseList.get(5).toString()))));

最后调用代码进行插入操作。

看看前端代码

<button class="btn btn-default col-md-2" style="margin-top: 20px">
        <a data-toggle="modal" href="#uploadExcel" role="button" style="color: black; text-decoration: none">
            批量添加<sapn class="glyphicon glyphicon-plus"/>
        </a>
    </button>
    
    
    <div class="modal fade" tabindex="-1" role="dialog" id="uploadExcel">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <form class="form-horizontal" role="form" th:action="@{/admin/upload/course}"
                          enctype="multipart/form-data" method="post">
                    <div class="modal-body">
                        请选择文件:<input type="file" name="courseFile">
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
                        <button type="submit" class="btn btn-success">添加</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

这里我通过button唤醒一个模态框来添加

最后测试结果  
![excel记录][excel]

![数据库记录][e987051e3be45fd23fd125f327c8d988.png]

**关注微信公众号：秃头哥编程（ID：xp\_1311664842），领取编程大礼包。**  
![在这里插入图片描述][20190521135143488.jpg]

[excel]: /images/20220220/4b8c862023a046f5a074a33303b47302.png
[e987051e3be45fd23fd125f327c8d988.png]: /images/20220220/39f0f27116ea45a6a2ef6d0c3b675a80.png
[20190521135143488.jpg]: /images/20220220/0fbf274c1c1d4680a95b7bcd0fc46ee4.png