spring boot如何使用POI读取Excel文件

时间:2022-05-15 23:27:13

spring boot 使用POI读取Excel文件

Excel文件目录

Excel模板文件存了resourse目录下,如下图:

spring boot如何使用POI读取Excel文件

?
1
2
3
4
5
6
7
8
9
10
<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

重要说明

如果是xls格式,使用HSSFWorkbook,HSSFSheet,HSSFRow来进行相关操作

如果是xlsx格式,使用XSSFWorkbook,XSSFSheet,XSSFRow来进行相关操作

读取Excel文件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
// 定义一个数据格式化对象
XSSFWorkbook wb = null;
try {
    //excel模板路径
    File cfgFile = ResourceUtils.getFile(ResourceUtils.CLASSPATH_URL_PREFIX + "static/ExcelTemplate/ContradictionMatrix.xlsx");
    InputStream in = new FileInputStream(cfgFile);
    //读取excel模板
    wb = new XSSFWorkbook(in);
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

获取sheet表格及读写单元格内容

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
//获取sheet表格,及读取单元格内容
XSSFSheet sheet = null;
try{
    sheet = wb.getSheetAt(0);
    //先将获取的单元格设置为String类型,下面使用getStringCellValue获取单元格内容
    //如果不设置为String类型,如果单元格是数字,则报如下异常
    //java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
    sheet.getRow(2).getCell(2).setCellType(CellType.STRING);
    //读取单元格内容
    String cellValue = sheet.getRow(2).getCell(2).getStringCellValue();
    
    //添加一行
    XSSFRow row = sheet.createRow(1); //第2行开始写数据
    row.setHeight((short)400); //设置行高
    //向单元格写数据
    row.createCell(1).setCellValue("名称");
}
catch (Exception e){
    e.printStackTrace();
}

合并单元格

使用下面的语句合并单元格:

?
1
sheet.addMergedRegion(new CellRangeAddress(0,2,15,18));

看一下CellRangeAddress的构造函数:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/**
 * Creates new cell range. Indexes are zero-based.
 *
 * @param firstRow Index of first row
 * @param lastRow Index of last row (inclusive), must be equal to or larger than {@code firstRow}
 * @param firstCol Index of first column
 * @param lastCol Index of last column (inclusive), must be equal to or larger than {@code firstCol}
 */
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) {
    super(firstRow, lastRow, firstCol, lastCol);
    
    if (lastRow < firstRow || lastCol < firstCol)
        throw new IllegalArgumentException("lastRow < firstRow || lastCol < firstCol");
}

SpringBoot解析Excel

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

以批量导入课程为例

首先加入需要的jar包

?
1
2
3
4
5
6
7
8
9
10
11
<!--解析excel-->
  <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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
/**
 * 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,这样写可复用性很差。

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
/**
  * 通过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";
 }

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

?
1
2
3
// 格式化时间
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,所以我对此也进行了处理

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

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

看看前端代码

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<button class="btn btn-default col-md-2" style="margin-top: 20px">
<a data-toggle="modal" href="#uploadExcel" rel="external nofollow"  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唤醒一个模态框来添加

最后测试结果

spring boot如何使用POI读取Excel文件

spring boot如何使用POI读取Excel文件

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。

原文链接:https://www.cnblogs.com/codecat/p/10948120.html