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://HdhCmsTestcnblogs测试数据/codecat/p/10948120.html
查看更多关于spring boot如何使用POI读取Excel文件的详细内容...