spring boot 使用POI读取Excel文件
Excel文件目录
Excel模板文件存了resourse目录下，如下图：
|
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唤醒一个模态框来添加
最后测试结果
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://www.cnblogs.com/codecat/p/10948120.html
