SpringBoot之Excel表动态导入数据库
在后台系统开发中我们经常使用到这样的功能，就是有时候会把数据库中的某张表导出到Excel文件中对比数据，或者修改某些数据等。然后在这写操作完成之后就是涉及到把Excel表中的数据重新写入到数据库中。这个时候我们应该怎么做呢？下面就是本人在用SpringBoot 开发一个后台管理系统的时候涉及这部分的功能实现，所以今天就把这部分的代码实现分享给大家。当然这其中我也是参考了很多篇的博客再加上自己的一些及具体功能需求结合起来的。希望能帮到各位。
接下来我就是直接上代码了
- 用maven导入一些jar包
<!--导出excel表的依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<!-- 响应前端时转换json数据 -->
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.11</version>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-core-asl</artifactId>
<version>1.9.11</version>
</dependency>
<!-- 转换json用 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.7</version>
</dependency>
<!-- jackson转换json用 @ResponseBody默认调用jackson，将java对象转为json -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.8.7</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.8.0</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.8.7</version>
</dependency>
- 前端代码（采用的是layui 框架，当然我是修改框架里面的源码的）
<!-- 导入/导出到 Excel 操作 -->
<div class="layui-btn-group" style="float: right; height: 55px; margin-top: 10px; margin-right: 35px;">
<button class="layui-btn layui-btn-sm refresh" dw-url="/BirthdayRecord/exportExcel">
<i class="layui-icon"></i>导出Excel
</button>
<button class="layui-btn layui-btn-sm operation-excel" id="operation-excel" dw-url="/BirthdayRecord/updateExcel">
<i class="layui-icon"></i>Excel导入
</button>
</div>
- JavaScript前端页面逻辑操作
<!-- 设置Excel文件上传 -->
<script>
layui.use('upload', function() {
var $ = layui.jquery
, upload = layui.upload;
//Excel文件上传
var uploadInst = upload.render({
elem: '#operation-excel',//触发元素
url: $('#operation-excel').attr("dw-url"),//上传服务器端的接收路径
auto: true,//可自动上传（选好文件后）
accept: 'file',//接收所有的文件类型
exts: 'xls|xlsx|excel',//校验上传文件的格式
before: function(){//文件上传前的钩子
if ($('#operation-excel').attr("dw-url") == undefined){
layer.msg("请给button加上dw-url属性");
return false;
}
},
done: function (res) {//文件上传成功后的钩子
if (res.code == 1) {
layer.msg('上传成功',{icon:6});
}else {
layer.msg('上传失败',{icon:6});
}
},
error: function () {//文件上传失败后的钩子
//演示失败状态，并实现重传
picText.html('<span style="color: #FF5722;">上传失败</span> <a class="layui-btn layui-btn-xs demo-reload">重试</a>');
picText.find('.demo-reload').on('click', function () {
uploadInst.upload();
});
},
});
});
</script>
- Controller控制层
/**
* 功能需求：完成客户端的Excel表数据写入数据库功能
*
* @param file //用户上传的Excel文件
* @param uploadUtils //上传文件的工具类 cn.zdxh.personnelmanage.utils.UploadUtils
* @return
* @throws Exception
*/
@PostMapping("/BirthdayRecord/updateExcel")
@ResponseBody
public JSONObject updateExcel(@RequestParam("file") MultipartFile file, UploadUtils uploadUtils) throws Exception {
JSONObject json = new JSONObject();
try {
//第一个参数为Excel表，第二个参数为从第几行读取Excel的内容，返回值为一个字符串数组集合（每一个数组代表Excel表的一行数据）
List<String[]> list = uploadUtils.updateExcelUtils(file, 1);
//遍历字符串数组集合中的数据
for (String[] str:list){
//获取实体类对象封装数据（每一个实体类对象封装Excel表中的一行数据）
BirthdayRecord birthdayRecord = new BirthdayRecord();
//一个工具类，把字符串数组数据封装到实体类对象中，第一个参数为实体类对象，第二个参数为字符串数组
ExcelValuesHelperUtils.setAttributeValue(birthdayRecord, str);
/**
* 在完成Excel表中数据写入数据库操作之前先判断
* 该实体类对象的是否为数据库已有（进行更新操作）
* 该实体类对象的数据为数据库没有（进行插入操作）
* 对对数据库的CRUD的操作都是使用mybatis-plus框架自带的操作方法
*/
if (birthdayRecordService.findBirthdayRecord(Integer.parseInt(str[0])) != null){
birthdayRecordService.updateBirthdayRecord(birthdayRecord);
} else {
birthdayRecordService.insertBirthdayRecord(birthdayRecord);
}
}
} catch (Exception e){
/**
* 做一个报错检测
*/
throw new MyException(ResultEnum.UPDATE_EXCEL_ERROR.getCode(), ResultEnum.UPDATE_EXCEL_ERROR.getMsg());
}
//返回客户端的数据
json.put("code", 1);
json.put("data", "Excel表上传成功！");
json.put("ret", true);
return json;
}
- 实体类
/**
* 生育纪录表
*/
public class BirthdayRecord implements Serializable {
private static final long serialVersionUID = 4849616019539107195L;
/**
* 自增主键
*/
@TableId(value = "bir_id", type = IdType.AUTO)
private Integer birId;
/**
* 头胎还是其他胎
*/
private String birType;
/**
* 产检医院
*/
private String birProdInspHos;
/**
* 分娩医院
*/
private String birMaterHos;
/**
* 计生证号码
*/
private String birNumber;
/**
* 手术日期
*/
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date birOperationTime;
/**
* 员工外键
*/
private Integer empId;
public BirthdayRecord() {
}
public BirthdayRecord(Integer birId, String birType, String birProdInspHos, String birMaterHos, String birNumber, Date birOperationTime, Integer empId) {
this.birId = birId;
this.birType = birType;
this.birProdInspHos = birProdInspHos;
this.birMaterHos = birMaterHos;
this.birNumber = birNumber;
this.birOperationTime = birOperationTime;
this.empId = empId;
}
public Integer getBirId() {
return birId;
}
public void setBirId(Integer birId) {
this.birId = birId;
}
public String getBirType() {
return birType;
}
public void setBirType(String birType) {
this.birType = birType;
}
public String getBirProdInspHos() {
return birProdInspHos;
}
public void setBirProdInspHos(String birProdInspHos) {
this.birProdInspHos = birProdInspHos;
}
public String getBirMaterHos() {
return birMaterHos;
}
public void setBirMaterHos(String birMaterHos) {
this.birMaterHos = birMaterHos;
}
public String getBirNumber() {
return birNumber;
}
public void setBirNumber(String birNumber) {
this.birNumber = birNumber;
}
public Date getBirOperationTime() {
return birOperationTime;
}
public void setBirOperationTime(Date birOperationTime) {
this.birOperationTime = birOperationTime;
}
public Integer getEmpId() {
return empId;
}
public void setEmpId(Integer empId) {
this.empId = empId;
}
@Override
public String toString() {
return "BirthdayRecord{" +
", birId=" + birId +
", birType=" + birType +
", birProdInspHos=" + birProdInspHos +
", birMaterHos=" + birMaterHos +
", birNumber=" + birNumber +
", birOperationTime=" + birOperationTime +
", empId=" + empId +
"}";
}
}
- 一些辅助的工具方法
/**
* 功能分析：把Excel表中的数据封装为一个字符串数组集合中（每一行对应一个数组）
* @param file
* @param start
* @return
* @throws Exception
*/
public List<String[]> updateExcelUtils(MultipartFile file, int start) throws Exception {
boolean notNull=false; //判断Excel文件是否有内容
List<String[]> list = null;
String filename=file.getOriginalFilename();//获取上传文件的名字
if (!filename.matches("^.+\\.(?i)(xls)$") && !filename.matches("^.+\\.(?i)(xlsx)$")){
throw new Exception("上传文件格式不正确");
}
//判断Excel文件的版本
boolean isExcel2003=true;
if (filename.matches("^.+\\.(?i)(xlsx)$")){
isExcel2003 = false;
}
//获取Excel文件的输入流
InputStream is=file.getInputStream();
//设置Excel对象
Workbook wb=null;
//先判断是哪个版本的Excel表
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
//2007以后版本
wb = new XSSFWorkbook(is);
}
//获取Excel文件的第一页sheet，判断是否有信息
Sheet sheet=wb.getSheetAt(0);
if (sheet != null){
notNull = true;//sheet内容不为空
}
if (notNull){//上传的Excel文件不为空时才启动
//遍历Excel文件
int totalRows = sheet.getPhysicalNumberOfRows(); //获取行数，第一行是标题
//初始化字符串数组集合
list = new ArrayList<String[]>();
//设置一个对Excel表的行操作对象
Row row=null;
//设置一个代表每一个的列元素变量
int totalCells=0;
//变量Excel表
for (int i=start; i<totalRows; i++){
//获取Excel表中的每一个行单元数据
row=sheet.getRow(i);
//获取每一行中的列单元个数
totalCells = row.getPhysicalNumberOfCells();
//循环设置每个单元格的数据类型为String类型
for (int j=0; j<totalCells; j++){
if (row.getCell(j) != null){
//设置每一个单元格的类型
row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
}
}
//创建一个数组存放每一行的数据
String[] str = new String[totalCells];
//获取每个单元格的数据，保存到集合中
for (int z=0; z<totalCells; z++){
//判断单元格类型是否为空
if (row.getCell(z, row.RETURN_BLANK_AS_NULL) == null){
//如果单元格为空则写入"NULL"
row.createCell(z).setCellValue("NULL");
}
//判断单元格内容是否为空
String cellInfo = row.getCell(z).getStringCellValue();
//StringUtils 有Apache提供的对字符串操作的工具类，isBlank方法是用来判断该字符串是否含有数据
if (StringUtils.isBlank(cellInfo)){
cellInfo = "NULL";
}
//把一行中的每个单元格写入一个数组中
str[z] = cellInfo;
}
//把每个代表行数据的字符串数组存放入集合中
list.add(str);
}
//关闭输入流
is.close();
}
//返回该数组集合
return list;
}
/**
* 功能分析：第一个参数为对象根据该对象中的属性设置一个对象数组，把第二个数组中的数据赋值给对象属性数组中的元素（为对象属性赋值）
*
* @param object
* @param str
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalAccessException
* @throws ParseException
*/
public static void setAttributeValue(Object object, Object [] str) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, ParseException {
//设置用到的变量参数
String name = null, type = null, m_name = null;
//获取实体类方法操作对象
Method m = null;
Object value = null;
Field[] field = object.getClass().getDeclaredFields();
for (int i=1; i<field.length; i++){
name = field[i].getName();//获取属性的名字
name = name.substring(0,1).toUpperCase()+name.substring(1); //将属性的首字符大写，方便构造get，set方法
type = field[i].getGenericType().toString(); //获取属性的类型
//判断属性的类型修改数据结构
switch (type){
case "class java.lang.String":
m = object.getClass().getMethod("get"+name);
value = (String) m.invoke(object); //调用getter方法获取属性值
if (value == null){
m = object.getClass().getMethod("set"+name,String.class); //组装setter方法
m.invoke(object, str[i-1].toString()); //为属性数组元素赋值
}
break;
case "class java.lang.Double":
m = object.getClass().getMethod("get"+name);
value = m.invoke(object); //调用getter方法获取属性值
if (value == null){
m = object.getClass().getMethod("set"+name,Double.class);
m.invoke(object, Double.parseDouble(str[i-1].toString()));
}
break;
case "class java.lang.Boolean":
m = object.getClass().getMethod("get"+name);
value = m.invoke(object); //调用getter方法获取属性值
if (value == null){
m = object.getClass().getMethod("set"+name,Boolean.class);
m.invoke(object, Boolean.parseBoolean(str[i-1].toString()));
}
break;
case "class java.lang.Integer":
m = object.getClass().getMethod("get"+name);
value = m.invoke(object); //调用getter方法获取属性值
if (value == null){
m = object.getClass().getMethod("set"+name,Integer.class);
m.invoke(object, Integer.parseInt(str[i-1].toString()));
}
break;
case "class java.util.Date":
m = object.getClass().getMethod("get"+name);
value = m.invoke(object); //调用getter方法获取属性值
if (value == null){
m = object.getClass().getMethod("set"+name,Date.class);
m.invoke(object, new SimpleDateFormat("yyyy-MM-dd").parse(str[i-1].toString()));
}
break;
case "class java.lang.Float":
m = object.getClass().getMethod("get"+name);
value = m.invoke(object); //调用getter方法获取属性值
if (value == null){
m = object.getClass().getMethod("set"+name,Float.class);
m.invoke(object, Float.parseFloat(str[i-1].toString()));
}
break;
case "class java.lang.Long":
m = object.getClass().getMethod("get"+name);
value = m.invoke(object); //调用getter方法获取属性值
if (value == null){
m = object.getClass().getMethod("set"+name,Long.class);
m.invoke(object, Long.parseLong(str[i-1].toString()));
}
break;
case "class java.lang.Byte":
m = object.getClass().getMethod("get"+name);
value = m.invoke(object); //调用getter方法获取属性值
if (value == null){
m = object.getClass().getMethod("set"+name,Byte.class);
m.invoke(object, Byte.parseByte(str[i-1].toString()));
}
break;
}
}
}
- mybatis-plus 的CRUD操作
/**
* 功能分析：根据id查询数据表操作
* @param id
* @return
*/
@Override
public BirthdayRecord findBirthdayRecord(Integer id) {
BirthdayRecord birthdayRecord = birthdayRecordMapper.selectById(id);
if (birthdayRecord == null){
throw new MyException(ResultEnum.SELECT_ERROR.getCode(), ResultEnum.SELECT_ERROR.getMsg());
}
return birthdayRecord;
}
/**
* 功能分析：对数据表进行插入操作
*
* @param birthdayRecord
*/
@Override
public void insertBirthdayRecord(BirthdayRecord birthdayRecord) {
try {
birthdayRecordMapper.insert(birthdayRecord);
} catch (Exception e){
throw new MyException(ResultEnum.INSERT_FAIL.getCode(),ResultEnum.INSERT_FAIL.getMsg());
}
}
/**
* 功能分析：先进行id查询数据表是否含有此记录，有则进行更新操作
*
* @param birthdayRecord
*/
@Override
public void updateBirthdayRecord(BirthdayRecord birthdayRecord) {
BirthdayRecord birthdayRecord1 = birthdayRecordMapper.selectById(birthdayRecord.getBirId());
if (birthdayRecord1 == null){
throw new MyException(ResultEnum.UPDATE_ERROR.getCode(),ResultEnum.UPDATE_ERROR.getMsg());
}
birthdayRecordMapper.updateById(birthdayRecord);
}
- 检测报错代码模块
/**
* 自定义异常管理
*/
public class MyException extends RuntimeException {
//增加一个错误码
private Integer code;
public MyException(Integer code,String message) {
super(message);
this.code = code;
}
public Integer getCode() {
return code;
}
}
================================================================================
/**
* 统一错误管理
* 给自定义异常使用
* 需要根据业务场景来添加相应的枚举类型。。。。。。。可扩展
*/
public enum ResultEnum {
CHECK_ERROR(10,"填写内容不正确"),
EMP_NOT_EXIST(11,"此员工不存在"),
INSERT_FAIL(12,"数据插入操作失败"),
DELETE_ERROR(13, "数据删除操作失败"),
UPDATE_ERROR(14, "数据更新操作失败"),
SELECT_ERROR(15, "数据查询操作失败"),
EXPORT_EXCEL_ERROR(16, "Excel表导出操作发送错误"),
UPDATE_EXCEL_ERROR(17,"Excel表上传操作发生错误"),
;
private Integer code;
private String msg;
ResultEnum(Integer code, String msg) {
this.code = code;
this.msg = msg;
}
public Integer getCode() {
return code;
}
public String getMsg() {
return msg;
}
}
===============================================================================
/**
* 拦截抛出的自定义异常，并统一处理
* @ControllerAdvice 标记这是控制层的通知
*/
@ControllerAdvice//向应用注册自定义组件
public class MyControllerAdvice {
/**
* 自己定义的异常
* @param myException
* @return
*/
@ExceptionHandler(MyException.class)
public ModelAndView myExceptionHandler(MyException myException){
//视图对象
ModelAndView modelAndView = new ModelAndView();
//存储错误信息
modelAndView.addObject("code",myException.getCode());
modelAndView.addObject("msg",myException.getMessage());
modelAndView.addObject("getLocalize", myException.getLocalizedMessage());
modelAndView.addObject("suppressed", myException.getSuppressed());
//跳到显示错误的页面
modelAndView.setViewName("result/exception_error");
return modelAndView;
}
}
到这里已经可以完成把Excel表中的数据上传服务器写入数据库了。
请前往学习下一篇《SpringBoot之把数据库导出到Excel表》
9612
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
