Excel文件的导出(easyExcel)
vue
<el-form-item>
<el-button type="primary" icon="el-icon-printer":size="condition.size"
@click="handleClick('partPrint')">导出excel
</el-button>
</el-form-item>
<el-form-item>
<el-button size="mini" type="primary" icon="el-icon-download"
@click="handleClick('downloadDemo')">下载模板
</el-button>
</el-form-item>
导出使用easyExcel实现
调用封装导出的axios.js
xhrFields需要加上
var easyExcel = { config: { //LocalStorage存储token的关键字 tokenLocalStorageKey: function () { return "MQD_TOKEN_" + easyExcel.config.contextPath().toUpperCase(); }, contextPath: function () { var doc = window.document, pathName = doc.location.pathname, contextPath = pathName.substring(0, pathName.substr(1).indexOf('/') + 1); return contextPath; }, baseUrl: function () { var doc = window.document, curURLPath = doc.location.href, pathName = doc.location.pathname, pos = curURLPath.indexOf(pathName), hostPath = curURLPath.substring(0, pos), baseUrl = hostPath + easyExcel.config.contextPath(); //调试写死模式 return baseUrl; } }, post: function (url, arg, callback, asyncFlag) { if (asyncFlag == undefined) asyncFlag = true;//默认异步 jQuery.ajax({ headers: { token: localStorage.getItem(easyExcel.config.tokenLocalStorageKey()) }, type: "post", url: easyExcel.config.baseUrl() + url, data: JSON.stringify(arg), contentType: "application/json", xhrFields: { responseType: 'blob' }, async: asyncFlag, success: function (res) { console.log("success") callback(res); }, error: function (XMLHttpRequest, textStatus, errorThrown) { console.log(XMLHttpRequest) console.log(textStatus) console.log(errorThrown) } }); }, }
调用的按钮函数
handleClick(event, data) {
switch (event) {
case "partPrint":
var obj = this.partTabTable.data
this.$confirm("确定导出?", "提示", {
confirmButtonText: "确 定",
cancelButtonText: "取 消",
type: "warning",
callback: (action) => {
if (action === "confirm") {
console.log('confirm')
easyExcel.post(this.partTabUrl.prints, obj, async (res) => {
if (res) {
await this.downloadExprotFile(res, '重要配件', 'xlsx')
}
var {code, msg, data} = res;
if (code == "200") {
this.$message.success(msg);
} else {
this.$message.error(msg);
}
this.specialCheckListData();//刷新重要配件List界面
}
);
}
}
});
break
通过按钮事件调用downloadExprotFile方法
//导出excel
downloadExprotFile(fileStream, name, extension, type = "") {
return new Promise((resolve, reject) => {
const blob = new Blob([fileStream], {type: type || fileStream.type});
const fileName = `${name}.${extension}`;
if ("download" in document.createElement("a") && fileStream.type) {
// 非IE下载
const elink = document.createElement("a");
elink.download = fileName;
elink.style.display = "none";
elink.href = URL.createObjectURL(blob);
document.body.appendChild(elink);
elink.click();
URL.revokeObjectURL(elink.href);
document.body.removeChild(elink);
resolve(fileStream)
}
})
},
后端代码
@PostMapping("/print")
public HttpResult hasPrint(@RequestBody List<StandingBookPart> sbp, HttpServletRequest request, HttpServletResponse response) throws IOException {
File excelFile = null;
// 设置防止中文名乱码
String filename = URLEncoder.encode("重要配件", "utf-8");
String excelFileName = null;
String mimeType = "";
try {
excelFile = ExcelWriteTest.excelWrite(sbp, response);
excelFileName = excelFile.getName();
mimeType = request.getServletContext().getMimeType(excelFileName);
} catch (IOException e) {
e.printStackTrace();
}
FileInputStream inputStream = new FileInputStream(excelFile);
OutputStream outputStream = response.getOutputStream();
try {
byte[] data = new byte[1024];
// 全文件类型(传什么文件返回什么文件流)
response.setContentType("application/blob");
//response.setContentType("application/octet-stream");
//response.setHeader("Context-type",mimeType);
response.setHeader("Content-Disposition", "attachment;filename=\"" + excelFileName + "\"");
int read;
while ((read = inputStream.read(data)) != -1) {
outputStream.write(data, 0, read);
}
// 将缓存区数据进行输出
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
inputStream.close();
outputStream.close();
}
return HttpResult.ok("打印成功");
}
调用ExcelWriteTest的封装类来实现文件的处理
public class ExcelWriteTest {
public static File excelWrite(List<StandingBookPart> entitys, HttpServletResponse response) throws IOException {
long nowLong = System.currentTimeMillis();
System.out.println(nowLong);
//1、创建一个文件对象
// File excelFile = new File("D://easyExcel//"+nowLong+"重要配件.xlsx");
File excelFile = new File("D://easyExcel//重要配件.xlsx");
//2、判断文件是否存在,先删除文件,不存在则创建一个Excel文件
if (excelFile.exists()) {
if(excelFile.delete()){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}else{
excelFile.createNewFile();//创建一个新的文件
}
//3、指定需要那个class去写。然后写到第一个sheet,名字为模版,然后文件流会自动关闭
EasyExcel.write(excelFile,StandingBookPart.class).sheet("重要配件").doWrite(data(entitys));
return excelFile;
}
private static List<StandingBookPart> data(List<StandingBookPart> entity){
//创建一个List集合
// List excelOrderList = new ArrayList<>();
/*
*xls版本的Excel最多一次可写0 ...65535行
* xlsx 版本的Excel最多一次可写0...1048575行
*/
//超出报异常:java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
return entity;//返回list集合
}
}
excel导入(XLSX前端处理数据)
<el-button size="mini" type="primary" icon="el-icon-printer"
@click="handleClick('partOutPrint')">导入excel
</el-button>
点击按钮后打开弹窗
case "partOutPrint":
this.importDialogVisible = true;
break
弹窗 弹窗中做了文件的限制
<!-- 导入对话框-->
<!-- 点击导入后弹出框 -->
<el-dialog title="导入excel" :visible.sync="importDialogVisible" width="40%" :append-to-body="true">
<input
type="file"
accept=".xls,.xlsx"
class="upload-file"
@change="Excel($event)"/>
</el-dialog>
解析excel文件
Excel(e) {
let that = this
// 错误情况判断
const files = e.target.files
console.log(files)
if (files.length <= 0) {
return false;
} else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
this.$message({
message: "上传格式不正确,请上传xls或者xlsx格式",
type: "warning"
});
return false
} else {
that.upload_file = files[0].name
}
// 读取表格
const fileReader = new FileReader()
fileReader.onload = ev => {
try {
const data = ev.target.result;
const workbook = XLSX.read(data, {
type: "binary"
})
var fromTo = '';
// 遍历每张表读取
for (var sheet in workbook.Sheets) {
if (workbook.Sheets.hasOwnProperty(sheet)) {
fromTo = workbook.Sheets[sheet]['!ref'];
// console.log(fromTo);
// 读取第一张表
const wsname = workbook.SheetNames[0]
const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname])
for(var a = 0;a<ws.length;a++){
var objs= Object.keys(ws).reduce((newData, key) => {
let newKey = this.fileObj[key] || key
newData[newKey] = data[key]
return newData
}, {})
}
// 打印 ws 就可以看到读取出的表格数据
console.log(ws)
this.submitForm(ws)
this.importDialogVisible = false;
// break; // 如果只取第一张表,就取消注释这行
}
}
} catch (e) {
return false
}
}
fileReader.readAsBinaryString(files[0])
},
//导入excel文件
submitForm(data) {
var param = {
equId: null,
equNo: null,
sbpList: null
};
param.equId = this.partTabCondition.form.equId;
param.equNo = this.partTabCondition.form.equNo;
param.sbpList = data;
// 在这里发送数据
outExcel.post(this.partTabUrl.outPrint, param,(res)=>{
this.partListData()
this.importDialogVisible = false;
if (res.code === 200) {
this.$message({
type: 'success',
message: '数据导入成功!'
})
} else {
this.$message({
type: "error",
message: res.msg
})
}
})
},
因为跟导出不一样 将js中的
xhrFields: {
responseType: 'blob'
},
改为
xhrFields: {
responseType: 'json'
},
解析完成后将json数据传给后端
@PostMapping(value = "/partOutPrint")
@ResponseBody
//public HttpResult taskUploadExcel(@RequestBody List<StandingBookPart> partList) throws Exception {
public HttpResult taskUploadExcel(@RequestBody Map<String,Object> param) throws Exception {
String equId = String.valueOf(param.get("equId"));
String equNo = String.valueOf(param.get("equNo"));
List<StandingBookPart> partList = (ArrayList) param.get("sbpList");
String s = JSON.toJSONString(partList); //json转换有问题,需要重新转,并指明类型
List<StandingBookPart> skuHasStockVos1 = JSON.parseArray(s, StandingBookPart.class);
for (StandingBookPart sbp : skuHasStockVos1) {
sbp.setEquId(equId);
sbp.setEquNo(equNo);
standingBookPartService.save(sbp);
}
System.out.println("总条数=" + partList.size());
System.out.println("结束");
System.out.println("退出循环");
System.out.println("----------------");
return HttpResult.ok("导入成功");
}
}