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