今天项目有个需求是需要将excel文件里的数据自动导入到数据库,以下是我的实现方式
注:前端我使用的是layui 其他前端框架的就不太清楚了
后台步骤
- 步骤一:导入easypoi的maven坐标
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
- 步骤二:写一个service里面写入下面方法然后在controller层调用
public KemeanResult<String> importUserInfo(MultipartFile file) {
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
XSSFWorkbook book = new XSSFWorkbook(inputStream);
XSSFSheet sheet = book.getSheetAt(0);
/**
* 除去标题与表头
*/
for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
XSSFRow row = sheet.getRow(i);
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//因为在excel会有数据是int类型所以在这儿需要转换下不然会报错 getCell(0)这个意思是第几列需要转换
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
String name = row.getCell(0).getStringCellValue();
String nickName = row.getCell(1).getStringCellValue();
String organ = row.getCell(2).getStringCellValue();
String position = row.getCell(3).getStringCellValue();
String level = row.getCell(4).getStringCellValue();
String field = row.getCell(5).getStringCellValue();
String phone = row.getCell(6).getStringCellValue();
String email = row.getCell(7).getStringCellValue();
String card = row.getCell(8).getStringCellValue();
String money = row.getCell(9).getStringCellValue();
String investor = row.getCell(10).getStringCellValue();
String pioneer = row.getCell(11).getStringCellValue();
//这里开始写你要写入数据库的逻辑代码
}
} catch (IOException e) {
return new KemeanResult<>(false, "导入失败,请联系管理员");
}
return new KemeanResult<>(true, "导入成功");
}
//这里controller层的代码
@PostMapping("/user_info")
@ResponseBody
public KemeanResult<String> Excel(@RequestParam MultipartFile file){
return adminImportUserInfosService.importUserInfo(file);
}
前端步骤(layui)
- 步骤一:写入按钮上传文件
<div class="layui-inline">
<button class="layui-btn" id="import"><i
class="fa fa-file-excel-o" aria-hidden="true"></i> 导入用户
</button>
</div>
- 步骤二:写入js开始请求
layui.use(['table', 'upload', 'laydate'], function () {
var table = layui.table;
var laydate = layui.laydate;
var form = layui.form;
var upload = layui.upload;
var index = parent.layer.getFrameIndex(window.name);
upload.render({ //允许上传的文件后缀
elem: '#import'
, url: url_import_user_data
, accept: 'file' //普通文件
, exts: 'xls|xlsx' //上传文件格式
, done: function (res) {
layui.layer.open({
title: "操作提示",
content: "<span>" + res.info + "</span>",
skin: "layui-layer-molv",
area: ['380px', '180px'],
btn: ['确定'],
offset: '100px',
btn1: function () {
parent.location.reload();
parent.layer.close(index);
},
closeBtn: 1,
anim: 0
});
}
});
})