使⽤VUE+SpringBoot+EasyExcel整合导⼊导出demo 导出⽰例：
后台：
1、引⼊依赖：需要引⼊easyExcel的依赖，但是我在使⽤过程中发现也是需要poi的依赖，不然会报错，就同时引⼊了。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
2、实体类定义导出字段及Title：@ExcelProperty
@Setter
@Getter
public class TradingRecord {
@ExcelProperty({"沃联之家VGM对账清单", "账单创建时间","ID"})
private String uuid;
@ExcelProperty({"沃联之家VGM对账清单", "账单创建时间","关联业务编号"})
private String referenceNo;
@ExcelProperty({"沃联之家VGM对账清单", "账单创建时间","⾦额"})
private Double changeTicket;
@ExcelProperty({"沃联之家VGM对账清单", "账单创建时间","交易时间"})
private Date recordDate;
@ExcelProperty({"沃联之家VGM对账清单", "账单总⾦额","交易说明"})
private String remark;
}
3、直接调⽤
public void writeTradingRecord(HttpServletResponse response,AppreciationServiceRequest appreciationServiceRequest) throws IOException { List<TradingRecord> list = new ArrayList<TradingRecord>();
String name = "VGM对账清单.xlsx";
// 导出时候会出现中⽂⽆法识别问题，需要转码
String fileName = new Bytes("gb2312"),"ISO8859-1");
response.setContentType("application/vnd.ms-excel;chartset=utf-8");
response.setHeader("Content-Disposition","attachment;filename=" + fileName);
//调⽤⼯具类
ExcelWriter writer = EasyExcel.OutputStream()).build();
WriteSheet sheet = EasyExcel.writerSheet(0,"sheet").head(TradingRecord.class).build();
writer.write(list,sheet);
writer.finish(); // 使⽤完毕之后要关闭
}
也⽀持动态复杂表头：
public void writeTradingRecord(HttpServletResponse response,AppreciationServiceRequest appreciationServiceRequest) throws IOException { List<TradingRecord> list = new ArrayList<TradingRecord>();
String name = "VGM对账清单.xlsx";
// 导出时候会出现中⽂⽆法识别问题，需要转码
String fileName = new Bytes("gb2312"),"ISO8859-1");
response.setContentType("application/vnd.ms-excel;chartset=utf-8");
response.setHeader("Content-Disposition","attachment;filename=" + fileName);
//调⽤⼯具类
// ⾃定义动态Title
List<List<String>> headTitles = wArrayList();
// 第⼀⾏表头
String basicInfo = "沃联之家VGM对账清单";
// 第⼆⾏表头
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
String createDate = "账单创建时间: " + sdf.format(new Date());
String amount1 = "账单总⾦额: ￥" + df2.format(amount);
// 第三⾏表头
headTitles.add( wArrayList(basicInfo , createDate,"ID"));
headTitles.add( wArrayList(basicInfo , createDate,"关联业务编号"));
headTitles.add( wArrayList(basicInfo , createDate,"⾦额"));
headTitles.add( wArrayList(basicInfo , createDate,"交易时间"));
headTitles.add( wArrayList(basicInfo , amount1,"交易说明"));
ExcelWriter writer = EasyExcel.OutputStream()).registerWriteHandler(new CustomCellWriteHandler()).build();
WriteSheet sheet = EasyExcel.writerSheet(0,"sheet").head(headTitles).build();
writer.write(list,sheet);
writer.finish();
}
若想使导出的表格宽度⾃适应：添加宽度⾃适应⼯具类，并在使⽤时.registerWriteHandler。如：
EasyExcel.OutputStream()).registerWriteHandler(new CustomCellWriteHandler()).build();
package s.util;
import l.enums.CellDataTypeEnum;
import l.metadata.CellData;
import l.metadata.Head;
import adata.holder.WriteSheetHolder;
import l.lumn.AbstractColumnWidthStyleStrategy;
import llections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
/**
* Excel 导出列宽度⾃适应
* @author phli
*/
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = (SheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>();
CACHE.SheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = (ColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.ColumnIndex(), columnWidth);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
StringCellValue().getBytes().length;
} else {
CellData cellData = (0);
CellDataTypeEnum type = Type();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
StringValue().getBytes().length;
case BOOLEAN:
BooleanValue().toString().getBytes().length;
case NUMBER:
NumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
4、VUE端：在使⽤过程中，发现不能直接使⽤axios直接调⽤，会报错，需要使⽤window.localtion调⽤。
5、导出⽰例：
----导出end-----
导⼊⽰例：
1、VUE：使⽤element的el-upload
<el-upload
class="upload-demo"
name="file"
:action="url"
:with-credentials="true"
:on-change="handleChange"
:file-list="fileList"
:limit="1"
:on-exceed="handleExceed"
:on-preview="handlePreview"
accept=".xlsx"
:auto-upload="false"
>
<el-button size="mini" >
<i class="el-icon-upload2"/>点击上传</el-button>
<div slot="tip" class="el-upload__tip">⼀次只能上传⼀个⽂件，⽂件⼤⼩不可超过20M</div> </el-upload>
2、传参调⽤后台：
importExcel () {
let self = this
if (self.fileList.length <= 0) {
self.$message("请选择要导⼊的⽂件！")
}
let formData = new FormData();
let file = this.fileList[0]
formData.append('file', file.raw)
self.importLoding = true
axios.post('/appreciation/import', formData).then(res => {
self.$message('导⼊成功')
self.importDialog = false
self.fetchVgmList(1)
self.fileList = []
self.importLoding = false
}).catch (err=> {
self.importLoding = false
})
}
3、后台处理：
new ImportTradingListener(appreciationService)).sheet().doRead();
4、添加监听，处理excel表格内容：
package s.listener;
import l.context.AnalysisContext;
import l.event.AnalysisEventListener;
import lModel.TradingRecord;
import s.service.AppreciationService;
import java.util.ArrayList;
import java.util.List;
/**
* 监听：交易记录导⼊获取UUID
* @author phli
*/
public class ImportTradingListener extends AnalysisEventListener<TradingRecord> {
/**
* 每隔1000条存储数据库，然后清理list,⽅便内存回收
*/
private static final Integer BATCH_COUNT = 1000;
List<String> list = new ArrayList<>();
private AppreciationService appreciationService;
public ImportTradingListener(AppreciationService appreciationService){
this.appreciationService = appreciationService;
}
@Override
public void invoke(TradingRecord tradingRecord, AnalysisContext analysisContext) { list.Uuid());
if (list.size() >= BATCH_COUNT) {
list.clear(); //保证doAfterAllAnalysed⽅法执⾏数据为不重复数据
}
}
//这⾥是数据全部读完之后⾛为保证数据不够批量最⼩值也能存进数据库
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这个⽅法便是拿到excel的数据之后，进⾏修改数据库的操作。
appreciationService.updateWriteOffState(list);
}
springboot是啥}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论