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

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

目录spring boot 使用POI读取Excel文件Excel文件目录重要说明读取Excel文件获取sheet表格及读写单元格内容合并单元格SpringBoot解析Excel以批量导入课程为例

spring boot 使用POI读取Excel文件

Excel文件目录

Excel模板文件存了resourse目录下，如下图：

org.apache.poi

poi

3.16

org.apache.poi

poi-ooxml

3.16

重要说明

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

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

读取Excel文件

// 定义一个数据格式化对象

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表格及读写单元格内容

//获取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();

}

合并单元格

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

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

看一下CellRangeAddress的构造函数：

/**

* 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包

org.apache.poi

poi

RELEASE

org.apache.poi

poi-ooxml

RELEASE

数据库中创建一个表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

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 = ExcelUtils.getCourseListByExcel(inputStream, file.getOriginalFilename());

inputStream.close();

for (int i = 0; i < list.size(); i++) {

List

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()))));

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

看看前端代码

批量添加

enctype="multipart/form-data" method="post">

请选择文件:

关闭

添加

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

最后测试结果

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。