导出示例：

后台：

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@Getterpublic 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 String(name.getBytes("gb2312"),"ISO8859-1");response.setContentType("application/vnd.ms-excel;chartset=utf-8");response.setHeader("Content-Disposition","attachment;filename=" + fileName);//调用工具类ExcelWriter writer = EasyExcel.write(response.getOutputStream()).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 String(name.getBytes("gb2312"),"ISO8859-1");response.setContentType("application/vnd.ms-excel;chartset=utf-8");response.setHeader("Content-Disposition","attachment;filename=" + fileName);//调用工具类// 自定义动态TitleList<List<String>> headTitles = Lists.newArrayList();// 第一行表头String basicInfo = "沃联之家VGM对账清单";// 第二行表头SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");String createDate = "账单创建时间: " + sdf.format(new Date());String amount1 = "账单总金额: ￥" + df2.format(amount);// 第三行表头headTitles.add( Lists.newArrayList(basicInfo , createDate,"ID"));headTitles.add( Lists.newArrayList(basicInfo , createDate,"关联业务编号"));headTitles.add( Lists.newArrayList(basicInfo , createDate,"金额"));headTitles.add( Lists.newArrayList(basicInfo , createDate,"交易时间"));headTitles.add( Lists.newArrayList(basicInfo , amount1,"交易说明"));ExcelWriter writer = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomCellWriteHandler()).build();WriteSheet sheet = EasyExcel.writerSheet(0,"sheet").head(headTitles).build();writer.write(list,sheet);writer.finish();}

若想使导出的表格宽度自适应：添加宽度自适应工具类，并在使用时.registerWriteHandler。如：EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomCellWriteHandler()).build();

package com.walltech.oms.util;import com.alibaba.excel.enums.CellDataTypeEnum;import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;import mons.collections4.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<>();@Overrideprotected 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 = CACHE.get(writeSheetHolder.getSheetNo());if (maxColumnWidthMap == null) {maxColumnWidthMap = new HashMap<>();CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);}Integer columnWidth = this.dataLength(cellDataList, cell, isHead);if (columnWidth >= 0) {if (columnWidth > 255) {columnWidth = 255;}Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);}}}}private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData cellData = cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch (type) {case STRING:return cellData.getStringValue().getBytes().length;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}}

4、VUE端：在使用过程中，发现不能直接使用axios直接调用，会报错，需要使用window.localtion调用。

5、导出示例：

----导出end-----

导入示例：

1、VUE:使用element的el-upload

<el-uploadclass="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" style="border-radius:25px;"><i class="el-icon-upload2"/>点击上传</el-button><div slot="tip" class="el-upload__tip">一次只能上传一个文件，文件大小不可超过20M</div></el-upload>

2、传参调用后台：

importExcel () {let self = thisif (self.fileList.length <= 0) {self.$message("请选择要导入的文件！")}let formData = new FormData();let file = this.fileList[0]formData.append('file', file.raw)self.importLoding = trueaxios.post('/appreciation/import', formData).then(res => {self.$message('导入成功')self.importDialog = falseself.fetchVgmList(1)self.fileList = []self.importLoding = false}).catch (err=> {self.importLoding = false})}

3、后台处理：

EasyExcel.read(file.getInputStream(), TradingRecord.class,new ImportTradingListener(appreciationService)).sheet().doRead();

4、添加监听，处理excel表格内容：

package com.walltech.oms.listener;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.walltech.oms.pojo.excelModel.TradingRecord;import com.walltech.oms.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;}@Overridepublic void invoke(TradingRecord tradingRecord, AnalysisContext analysisContext) {list.add(tradingRecord.getUuid());if (list.size() >= BATCH_COUNT) {list.clear(); //保证doAfterAllAnalysed方法执行数据为不重复数据}}//这里是数据全部读完之后走 为保证数据不够批量最小值也能存进数据库@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {// 这个方法便是拿到excel的数据之后，进行修改数据库的操作。appreciationService.updateWriteOffState(list);}}

------导入END------