如何在前端上传Excel数据批量导入数据库
- 需求：用户需要一个能够上传Excel表批量导入数据的功能。未成功导入的人员返回给前端一个Excel。个人觉得这样还不如直接通过连接工具手动将excel导入数据库。比如说这种：直接Navicat导入；因为之前自己也是这么搞得>>>》》传送门》》》
总之最后自己也做了，用的Poi ，由于之前项目中有用poi进行一些导出操作。所以就将就用了。就来记录一下吧！不过这种办法可能效率偏低！适合小数据量的Excel，比如千八百还行！当前仅限上传Excel文件，即 .xls 和 .xlsx。界面比较丑，勿喷；哈哈。效果如下：
- 注：那天问了好朋友，说 poi 这个东西比较out了，缺点比较多而且比较耗内存。推荐了个Alibaba-excel。感兴趣的同志可以研究一下。用了效果好记得回来告诉我一声。传送门>>>>>>Alibaba-Excel
- 好，接着看我的poi
一、效果图
二、代码实现
注：我是直接跟之前一样把Excel文件转成Base64格式字符串进行数据提交的。到后台再转回来！因为上传的Excel原文件要留存！所以到后台还需要把文件转回去进行存储！
1、前端代码
(1)、HTML代码
accept=“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel” 的意思是仅限上传excel文件，其他类型无法上传！
<div class="form-group" id="excel-file" style="margin-top: 10px">
<div class="col-sm-2 control-label" >上传表格(仅支持excel)</div>
<div class="col-sm-10">
<input id="person_list" type="file" class="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" @change="pushExcel()"/>
</div>
<span style="color:red">*</span>
</div>
(2)、JS代码
博主用的Vue进行的数据绑定，这里就简单把用到的几个参数列出来吧！
data:{
importInfo：{
//上传的Excel
personExcel : "",
orgId : ""
}
}
将文件转为Base64格式存在personExcel里面！
pushExcel : function(){
var file = document.getElementById("person_list").files[0];
var r = new FileReader();
r.readAsDataURL(file);
if(null != file){
var reader = new FileReader();
reader.readAsDataURL(file);
reader.onload = function (e) {
vm.importInfo.personExcel = e.target.result;
}
}
},
选择了文件以后进行文件上传导入
uploadExcel: function(){
//.......
//这里主要做一些数据的判断，或者其他参数的一个绑定。
if( "" === vm.importInfo.personExcel || null == vm.importInfo.personExcel){
layer.msg("请上传表格！")
return;
}
$.ajax({
type: "POST",
url: baseURL + "basic/person/bulkimport",
contentType: "application/json",
data: JSON.stringify(vm.importInfo),
success: function (r) {
if (r.code === 0) {
if(null != r.data && 0 !== r.data.length){
//下载未通过的人员Excel
vm.downloadExcel(r.data);
}else{
layer.msg("所有人员已全部导入成功！")
}
vm.showList = true;
vm.showImport = false;
location.reload();
} else {
alert(r.message);
}
}
})
},
2、后端代码
(1)、先将Base64转成文件留存在服务器上
这里就复用之前写的表单上传图片写的工具类里面的转码方法了！true 则表示上传成功了！
/** Base64转文件File存在本地
* @param baseStr Base64格式的字符串
* @param url 存文件的路径
* @param fileName 文件名称（包括后缀名）
* @return true 存储成功 false 失败
* */
public static boolean base64ToFile(String baseStr, String url, String fileName) {
if (null == baseStr || "".equals(baseStr) || null == url || "".equals(url) || null == fileName
|| "".equals(fileName)) {
return false;
}
String base64Data = baseStr.split(",")[1];
Base64.Decoder decoder = Base64.getDecoder();
byte[] bytes = decoder.decode(base64Data);
//.字节流转文件
String realUrl = url + "/" + fileName;
String newUrl = url;
try {
realUrl = URLDecoder.decode(realUrl,"utf-8");
newUrl = URLDecoder.decode(newUrl ,"utf-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
FileOutputStream fos = null;
if (!new File(newUrl).isDirectory()) {
new File(newUrl).mkdirs();
}
try {
fos = new FileOutputStream(realUrl);
fos.write(bytes);
return true;
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fos != null) {
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return false;
}
(2)、具体调用和实现
这是我们上传的表长得样子
@Override
@Transactional
public R<?> bulkImport(PersonQuery person) {
//文件名称
String fileName = UuidUtil.randomUUID() + ".xls";
//项目当前路径
String cspDir = CspUtils.getRealPath();
//文件存储路径
String folderDir = cspDir + "tempFile";
// 存储文件
boolean fFlag = CspUtils.base64ToFile(person.getPersonExcel(), folderDir, fileName);
//这个用来放文件缓存流
InputStream inputStream = null;
//这个用来存取出的每一行，封装成一个HashMap<表头，对应值>
List<Map<String, Object>> sourceList = new ArrayList<>();
List<PersonEntity> notPassPerson = new ArrayList<>();
try {
inputStream = new FileInputStream(folderDir+ "/" + fileName);
if(fFlag){
//将表头对应转换为相应字段
Map<String, String> m = new HashMap<String, String>();
m.put("姓名", "personName");
m.put("编号", "personnelNumber");
m.put("性别", "sex");
m.put("部门名称", "deptName");
m.put("部门编号", "deptNo");
m.put("身份证号", "idCard");
//传入一个文件流然后逐条解析，返回一个List<Map<String, Object>>
sourceList = CspUtils.parseExcel(inputStream, fileName, m);
}
} catch (Exception e) {
e.printStackTrace();
return R.error("文件解析失败！");
}
Map<String, String> deptIds = new HashMap<>();
for(Map<String, Object> map : sourceList){
//得到的这个sourceList便是，
//一个Map表 里面存着对应excel里面条数的对象。
//key表示的是表头比如
//map key=personName ; value = "张三"
// key=personnelNumber; value = "007"
//可以直接通过这个map进行MyBatis数据库查询，判断数据是否存在，存在则进行更新，不存在则进行插入。
//这边就根据自己需求了,因为这里是数据量并不是跟大。基本就是百来条数据。
//如果数据量大的话就别用工具类解析了。直接解析的时候就进行插入和更新，
//这样空间复杂度会稍微低点。一次遍历就行。
//notPassPerson 就是未成功插入的人员，可以保存在返回的结果里面。返回给前端然后自动下载成Excel文件给用户。
}
return R.ok(notPassPerson);
}
(3)、解析工具类
//其实我试了好像都一样 都只有XSSFWorkbook才能用
private final static String excel2003L = ".xls";
private final static String excel2007U = ".xlsx";
/**
* 将流中的Excel数据转成List<Map>
* @param in 输入流
* @param fileName 文件名（判断Excel版本）
* @param mapping 字段名称映射
* @return list<map>
* @throws Exception 读取excel异常
*/
public static List<Map<String, Object>> parseExcel(InputStream in, String fileName,
Map<String, String> mapping) throws Exception {
// 根据文件名来创建Excel工作薄
Workbook work = getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
// 返回数据
List<Map<String, Object>> ls = new ArrayList<Map<String, Object>>();
// 遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
// 取第一行标题
row = sheet.getRow(0);
String title[] = null;
if (row != null) {
title = new String[row.getLastCellNum()];
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
title[y] = (String) getCellValue(cell);
}
} else {
continue;
}
log.info( JSON.toJSONString(title));
// 遍历当前sheet中的所有行
for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
row = sheet.getRow(j);
Map<String, Object> m = new HashMap<String, Object>();
// 遍历所有的列
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
String key = title[y];
m.put(mapping.get(key), getCellValue(cell));
}
ls.add(m);
}
}
work.close();
return ls;
}
/**
* 描述：根据文件后缀，自适应上传文件的版本
* @param inStr ,fileName
* @return
* @throws Exception
*/
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (excel2003L.equals(fileType)) {
// 2003- 好像都一样
wb = new XSSFWorkbook(inStr);
} else if (excel2007U.equals(fileType)) {
// 2007+
wb = new XSSFWorkbook(inStr);
} else {
throw new Exception("解析的文件格式有误！");
}
return wb;
}
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
*/
public static Object getCellValue(Cell cell) {
Object value = null;
// 格式化number String字符
DecimalFormat df = new DecimalFormat("0");
// 日期格式化
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
// 格式化数字
DecimalFormat df2 = new DecimalFormat("0");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
} else {
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
3、返回前端未插入的excel并下载。
- 看到我们最上面的JS代码里面的downloadExcel方法。这个方法使我们将返回的list通过js转成Excel并下载：
success: function (r) {
if (r.code === 0) {
if(null != r.data && 0 !== r.data.length){
vm.downloadExcel(r.data);
}else{
layer.msg("所有人员已全部导入成功！")
}
vm.showList = true;
vm.showImport = false;
location.reload();
} else {
alert(r.message);
}
}
将后端返回的list转为Excel并下载
downloadExcel : function(tableData){
let str = `姓名,编号,部门名称,部门编号,身份证号\n`;
var jsonData = tableData;
//增加\t为了不让表格显示科学计数法或者其他格式
for (let i in jsonData) {
str += `${jsonData[i].personName + "\t"},` + `${jsonData[i].personnelNumber + "\t"},` + `${jsonData[i].deptName + "\t"},`
+`${jsonData[i].deptNo + "\t"},` + `${jsonData[i].idCard + "\t"},` + "\n";
}
//encodeURIComponent解决中文乱码
let uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);
//通过创建a标签
let link = document.createElement("a");
link.href = uri;
link.download = "未通过人员.xls";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
},
当我们存在未通过的人员返回给前端时，自动转成Excel并自动下载。
以上是博主项目中实践，传千百来条数据还行，估计数据量大就不行了。感谢网上分享经验的大佬。可能设计思路不是最优，代码也有点乱。希望能帮到你。如有错误、还望路过的大佬们指正！
7045
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
