基于VUE+SpringBoot实现excel模板下载、导入功能
背景
最近在工作中经常遇到批量导入的功能,而且前端还要提示导入成功几条、失败几条、哪一条数据重复、是哪一条导入的数据出现问题等,抽空写了一个简单的demo,记录一下。
框架:前端vue,使用的是element-ui组件,后端springboot
前端代码及效果
因为使用频繁,所以封装了一个文件上传、下载的组件,代码如下:
<template>
<!-- 弹窗,上传文件 -->
<el-dialog
:title="title"
:visible.sync="fileUploadVisible"
width="500px"
style="padding:0;"
@close="close"
>
附件名称:
<el-input
v-model="fileName"
autocomplete="off"
size="small"
style="width: 300px;"
></el-input>
<div class="add-file-right" style="height:70px;margin-top:5px;">
<div class="add-file-right-img">上传文件:支持扩展名:.xlsx,.xls</div>
<input
type="file"
ref="clearFile"
@change="getFile($event)"
multiple="multiplt"
class="add-file-right-input"
style="margin-left: 18px;margin-top:5px"
accept=".xlsx, .xls"
/>
</div>
<div slot="footer" class="dialog-footer">
<el-button
icon="el-icon-upload2"
type="primary"
@click="submitAddFile"
size="small"
>开始上传</el-button
>
<el-button icon="el-icon-download" @click="downLoadTemplate" size="small"
>下载导入模板</el-button
>
<el-button icon="el-icon-delete" @click="resetAdd" size="small"
>全部删除</el-button
>
</div>
</el-dialog>
</template>
<script>
export default {
name: "file-upload",
props: {
title: {
type: String,
default: "批量上传"
},
templateDownloadUrl: {
type: String,
default: "",
require: true
},
fileUploadUrl: {
type: String,
default: "",
require: true
},
templateName: {
type: String,
default: "模板",
require: true
}
},
components: {},
mounted() {},
data() {
return {
fileUploadVisible: false,
fileName: "",
addArr: []
};
},
methods: {
close() {
this.resetAdd();
this.fileUploadVisible = false;
},
resetAdd() {
this.addArr.length = 0;
this.fileName = "";
//清空选择的文件
this.$refs.clearFile.type = "text";
this.$refs.clearFile.value = "";
this.$refs.clearFile.type = "file";
},
//获取本地文件
getFile(event) {
var file = event.target.files;
for (var i = 0; i < file.length; i++) {
// 上传类型判断
var imgName = file[i].name;
var idx = imgName.lastIndexOf(".");
if (idx !== -1) {
var ext = imgName.substr(idx + 1).toUpperCase();
ext = ext.toLowerCase();
if (ext != "xlsx" && ext != "xls") {
} else {
this.addArr.push(file[i]);
this.fileName = this.addArr[0].name;
}
} else {
}
}
},
//提交上传
submitAddFile() {
if (0 === this.addArr.length) {
this.$message({
type: "info",
message: "请选择要上传的文件"
});
return;
}
let formData = new FormData();
formData.append("file", this.addArr[0]);
this.$axios({
url: this.fileUploadUrl,
method: "post",
data: formData,
headers: { "Content-Type": "multipart/form-data" }
}).then(({ data }) => {
if (data && data.code === 0) {
this.resetAdd();
this.$alert(data.msg, "导入结果", { dangerouslyUseHTMLString: true });
// 调用父组件的刷新列表事件
this.$emit("refreshDataList");
} else {
this.$message.error(data.msg, 3000);
}
});
},
//下载模板
downLoadTemplate() {
this.$axios({
url: this.templateDownloadUrl,
method: "get",
responseType: "blob" //服务器返回的数据类型
})
.then(res => {
//流的转储属于浅拷贝
const content = res.data;
const blob = new Blob([content]); //构造一个blob对象来处理数据
const fileName = this.templateName + ".xlsx";
//对于<a>标签,只有 Firefox 和 Chrome(内核) 支持 download 属性
//IE10以上支持blob但是依然不支持download
if ("download" in document.createElement("a")) {
//支持a标签download的浏览器
const link = document.createElement("a"); //创建a标签
link.download = fileName; //a标签添加属性
link.style.display = "none";
link.href = URL.createObjectURL(blob);
document.body.appendChild(link);
link.click(); //执行下载
URL.revokeObjectURL(link.href); //释放url
document.body.removeChild(link); //释放标签
} else {
//其他浏览器
navigator.msSaveBlob(blob, fileName);
}
})
.catch(err => {
console.log(err);
this.$message.error("下载文件失败");
});
}
}
};
</script>
<style scoped></style>
页面效果如下(样式不太好看,可自行调整):
页面使用
<template>
<div>
<el-button type="primary" @click="uploadData()">批量导入</el-button>
<!-- 弹窗, 批量上传 -->
<file-upload
ref="fileUpload"
title="批量导入"
:templateDownloadUrl="templateDownloadUrl"
:fileUploadUrl="fileUploadUrl"
templateName="用户导入模板"
@refreshDataList="getList"
>
</file-upload>
</div>
</template>
<script>
import FileUpload from "./excel/file-upload.vue";
export default {
components: {
FileUpload
},
data() {
return {
tableData: [],
templateDownloadUrl: "/user/download",
fileUploadUrl: "/user/upload"
};
},
methods: {
getList() {
this.$axios({
url: "/user/list",
method: "get"
}).then(res => {
this.tableData = res.data.list;
});
},
uploadData() {
this.$refs["fileUpload"].fileUploadVisible = true;
}
},
created() {
this.getList();
}
};
</script>
说明:
1、title:弹窗的title
2、templateDownloadUrl:模板下载后端地址
3、fileUploadUrl:文件导入后端地址
4、templateName:模板文件名
5、refreshDataList:列表刷新方法(导入成功后重新请求列表数据)
后端代码
需要用到两个工具类,工具类代码如下(不用深究方法里的具体代码,网上一大堆):
Tool4Excel
package com.linht.train.excel.utils;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
/**
* excel工具类
*
*/
public class Tool4Excel {
/**
* 写出excel文件到浏览器(下载文件)
* @param wb excel对象
* @param name 文件名,带后缀
* 相比上个方法,该方法通用性更强
*/
public static void export(Workbook wb, String name, HttpServletResponse res){
BufferedInputStream bis = null;
try (
ServletOutputStream out = res.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(out);
){
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
res.reset();
res.setContentType("application/vnd.ms-excel;charset=utf-8");
res.setHeader("Content-Disposition",
"attachment;filename=" + new String(name.getBytes(), "iso-8859-1"));
bis = new BufferedInputStream(is);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
//log.info(e.getMessage());
} finally {
try {
if (bis != null)
bis.close();
}catch (IOException e){
//log.info(e.getMessage());
}
}
}
}
ExcelUtil
package com.linht.train.excel.utils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* Excel编辑工具类
*
* @author linht
* @date 2021-04-16 17:12:22
*/
public class ExcelUtil {
/**
* 下载Excel
* @param request request对象
* @param response response对象
* @return
*/
public static void downloadFile(String fileName, String realPath, HttpServletRequest request , HttpServletResponse response){
//校验文件名后缀是否合法
String fileType = fileName.substring(fileName.length()-4,fileName.length());
if (fileType.equals(".xls")){
//校验是否包含非法字符
if (!fileName.contains("/")){
//通过文件的保存文件夹路径加上文件的名字来获得文件
File file=new File(realPath+ File.separator +fileName);
//当文件存在
if(file.exists()){
String userAgent = request.getHeader("User-Agent");
//针对IE或者以IE为内核的浏览器:
if (userAgent.contains("MSIE")||userAgent.contains("Trident")) {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
} else {
//非IE浏览器的处理:
try {
fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}
//通过设置头信息给文件命名,也即是,在前端,文件流被接受完还原成原文件的时候会以你传递的文件名来命名
response.addHeader("Content-disposition",String.format("attachment; filename=\"%s\"", fileName));
//首先设置响应的内容格式是force-download,那么你一旦点击下载按钮就会自动下载文件了
response.setContentType("application/force-download;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
//进行读写操作
byte[]buffer=new byte[1024];
FileInputStream fis=null;
BufferedInputStream bis=null;
try{
fis=new FileInputStream(file);
bis=new BufferedInputStream(fis);
OutputStream os=response.getOutputStream();
//从源文件中读
int i=bis.read(buffer);
while(i!=-1){
//写到response的输出流中
os.write(buffer,0,i);
i=bis.read(buffer);
}
}catch (IOException e){
e.printStackTrace();
}finally {
//善后工作,关闭各种流
try {
if(bis!=null){
bis.close();
}
if(fis!=null){
fis.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
}
/**
* 写出excel文件到浏览器(下载文件)
* @param wb excel对象
* @param name 文件名,带后缀
* 相比上个方法,该方法通用性更强
*/
public static void export(Workbook wb, String name, HttpServletResponse res){
BufferedInputStream bis = null;
try (
ServletOutputStream out = res.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(out);
){
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
res.reset();
res.setContentType("application/vnd.ms-excel;charset=utf-8");
res.setHeader("Content-Disposition",
"attachment;filename=" + new String(name.getBytes(), "iso-8859-1"));
bis = new BufferedInputStream(is);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
// log.info(e.getMessage());
} finally {
try {
if (bis != null)
bis.close();
}catch (IOException e){
// log.info(e.getMessage());
}
}
}
/**
*
* @param req
* @param filePath:webapp文件夹下的文件路径,如:指标模版.xlsx ; 文件夹名/xxx.xx
* @return
* @throws Exception
* @throws InvalidFormatException
*/
public static Workbook read(HttpServletRequest req, String filePath) throws Exception{
FileInputStream fileInputStream = null;
File file = null;
Workbook workbook = null;
try {
//读入文件到流
String ctxPath = req.getSession().getServletContext().getRealPath("") + File.separator;
String path = ctxPath + filePath;
file = new File(path);
fileInputStream = new FileInputStream(file);
//用流创建文件
workbook = WorkbookFactory.create(fileInputStream);
fileInputStream.close();
} catch (Exception e) {
if(fileInputStream != null) {
fileInputStream.close();
}
throw e;
} finally {
if(fileInputStream != null) {
fileInputStream.close();
}
}
return workbook;
}
/**
* 获取下标为n的sheet
* @param file
* @param index
* @return
* @throws Exception
*/
public static Sheet getSheet(MultipartFile file, int index) throws Exception{
InputStream inputStream = null;
//文件的原名
String url = file.getOriginalFilename();
// 文件的后缀名
String suffix = url.substring(url.lastIndexOf("."));
inputStream = file.getInputStream();
Workbook book = WorkbookFactory.create(inputStream);
return book.getSheetAt(index);
}
/**
* 删除列的子方法
* @param cNew
* @param cOld
*/
public static void cloneCell(Cell cNew, Cell cOld ){
cNew.setCellComment( cOld.getCellComment() );
cNew.setCellStyle( cOld.getCellStyle() );
switch ( cNew.getCellType() ){
case Cell.CELL_TYPE_BOOLEAN:{
cNew.setCellValue( cOld.getBooleanCellValue() );
break;
}
case Cell.CELL_TYPE_NUMERIC:{
cNew.setCellValue( cOld.getNumericCellValue() );
break;
}
case Cell.CELL_TYPE_STRING:{
cNew.setCellValue( cOld.getStringCellValue() );
break;
}
case Cell.CELL_TYPE_BLANK:{
cNew.setCellValue( cOld.getNumericCellValue() );
break;
}
case Cell.CELL_TYPE_ERROR:{
cNew.setCellValue( cOld.getErrorCellValue() );
break;
}
case Cell.CELL_TYPE_FORMULA:{
cNew.setCellFormula( cOld.getCellFormula() );
break;
}
}
}
/**
* 删除列(实质上是用后面的单元格覆盖前面的)
*
* @param sheet
* @param columnToDelete
*/
public static void deleteColumn(Sheet sheet, int columnToDelete) {
for (int rId = 0; rId <= sheet.getLastRowNum(); rId++) {
Row row = sheet.getRow(rId);
for (int cID = columnToDelete; cID <= row.getLastCellNum(); cID++) {
Cell cOld = row.getCell(cID);
if (cOld != null) {
row.removeCell(cOld);
}
Cell cNext = row.getCell(cID + 1);
if (cNext != null) {
Cell cNew = row.createCell(cID, cNext.getCellType());
cloneCell(cNew, cNext);
//Set the column width only on the first row.
//Other wise the second row will overwrite the original column width set previously.
if (rId == 0) {
sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));
}
}
}
}
}
/**
* 右边列左移
*
*/
@SuppressWarnings("deprecation")
public static Date getDate(Cell cell){
if(cell == null ) return null;
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
String val = cell.toString();
if("".equals(val)){
return null;
}else {
Date date = cell.getDateCellValue();
//如果单元格内容为空,cell.getDateCellValue();会返回1900-02-01或1899-12.31等非常早的时间
//date.getYear() 返回 年份-1900
if( date == null || date.getYear() <= 20 ){
return null;
}
//System.out.println(date.getYear());
return cell.getDateCellValue();
}
}
/**
* 判断行是否是空行
* @param row
* @return
*/
public static boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
return false;
}
}
return true;
}
/**
* 创建一个日期格式Cell
* @param row
* @param value
*/
public static void CreateNumericCell(Row row, int col, Date value, HSSFCellStyle dateCellStyle){
//创建cell并设置值
Cell cell = row.createCell(col);
if(value!=null){//set空的Date会报错
cell.setCellValue(value);
}
//将style设置到cell中
cell.setCellStyle(dateCellStyle);
}
/**
* 获取单元格的值
* @param cell
* @return
*/
public static String getValue(Cell cell){
if(cell == null ) return "";
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case Cell.CELL_TYPE_STRING: // 文本
cellValue = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC: // 数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()) + "";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getRichStringCellValue().getString()) + "";
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔型
cellValue = String.valueOf(cell.getBooleanCellValue()) + "";
break;
case Cell.CELL_TYPE_BLANK: // 空白
cellValue = cell.getStringCellValue() + "";
break;
case Cell.CELL_TYPE_ERROR: // 错误
cellValue = "";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
// 得到对应单元格的公式
//cellValue = cell.getCellFormula() + "#";
// 得到对应单元格的字符串
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getRichStringCellValue().getString()) + "";
break;
default:
cellValue = "";
}
return cellValue.trim();
}
/**
* 描述:对表格中数值进行格式化
*/
public static Object getCellValue(Cell cell){
if(cell == null) return null;
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化字符类型的数字
SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
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;
}
/**
* //创建一个cell并为其设置style和value
* @param row
* @param col
* @param style
* @param value:如果需要格式化请传入格式化好的String
* @return XSSFCellStyle
*/
public static Cell createCell(Row row, int col, CellStyle style, Object value){
Cell cell = row.createCell(col);
//style为空应该设置一个默认值以避免
cell.setCellStyle(style);
if (value instanceof String) {
//cell.setCellType( Cell.CELL_TYPE_STRING );
cell.setCellValue( (String)value );
} else if (value instanceof Integer) {
cell.setCellValue( (Integer)value );
} else if (value instanceof Double) {
cell.setCellValue( (Double)value );
} else if (value instanceof Long) {
cell.setCellValue( (Long)value );
} else if (value instanceof Date) {
cell.setCellValue( (Date)value );
} else if (value instanceof Float) {
cell.setCellValue( (Float)value );
} else if (value instanceof Boolean) {
cell.setCellValue( (Boolean)value );
}
return cell;
}
/**
* 文字居中带边框的cellStyle
* @param wb
* @return
*/
public static CellStyle getStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
return style;
}
}
具体的后端接口
模板下载接口serviceImpl
@Override
public void downLoad(HttpServletResponse response, HttpServletRequest request) {
Workbook workbook = null;
ClassPathResource classPathResource = new ClassPathResource("static/excel/用户导入模板.xlsx");
try {
InputStream is = classPathResource.getInputStream();
workbook = WorkbookFactory.create(is);
String filename = URLEncoder.encode("导入模板.xlsx", "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
Tool4Excel.export(workbook, "导入模板.xlsx", response);
} catch (IOException | InvalidFormatException e) {
logger.error("下载模板出错了:" + e.getMessage());
}
}
需要注意的是模板文件路径不要错了,要细心!!!(曾经因为一个单词写错,看了半天)
文件导入接口serviceImpl
@Override
public R upload(MultipartFile file) {
try {
return readFile(file);
} catch (Exception e) {
logger.error("导入出错了:" + e.getMessage());
return R.error(400, "导入出错了:" + e.getMessage());
}
}
private R readFile(MultipartFile file){
StringBuilder failureMsg = new StringBuilder();
StringBuilder successMsg = new StringBuilder();
int successNum = 0;
int failureNum = 0;
int i = 0;
int j = 0;
try {
//1获取下标为n的sheet
Sheet sheet = ExcelUtil.getSheet(file, 0);
//2读取数据封装到对象中
Row row = null;
User entity = null;
for ( i = 1; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
j = 0;
entity = new User();
String name = ExcelUtil.getValue(row.getCell(j++));
if ("".equals(name)){
failureMsg.append("<br/>第").append(i).append("行,第 ").append(j).append("列,用户名为空!");
failureNum++;
continue;
}
entity.setName(name);
String date = ExcelUtil.getValue(row.getCell(j++));
if ("".equals(date)){
failureMsg.append("<br/>第").append(i).append("行,第 ").append(j).append("列,生日为空!");
failureNum++;
continue;
}
SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");
entity.setDate(sdf.parse(date));
String address = ExcelUtil.getValue(row.getCell(j++));
if ("".equals(address)){
failureMsg.append("<br/>第").append(i).append("行,第 ").append(j).append("列,地址为空!");
failureNum++;
continue;
}
entity.setAddress(address);
this.save(entity);
successNum++;
}
} catch (Exception e) {
String msg = "<br/>第" + i + "行,第" + j + "列出现问题,导致导入失败:";
logger.error(msg, e);
}
successMsg.insert(0, "成功导入 " + successNum + " 条数据,失败" + failureNum + "条");
if (failureNum > 0) {
successMsg.append(",错误信息如下:").append(failureMsg);
}
return R.ok(successMsg.toString());
}
这一段就是导入的逻辑了,可在此进行判重、判空等逻辑处理,并把错误信息传回前端。
效果展示
demo的模板如下,可以看到第二个人的姓名为空
导入后
以上就是简单批量导入的demo了,over。