使用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 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);
        //调用工具类
        // 自定义动态Title
        List<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 org.apache.commons.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<>();

    @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 = 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-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" 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 = 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、后台处理：

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;
    }

    @Override
    public void invoke(TradingRecord tradingRecord, AnalysisContext analysisContext) {
        list.add(tradingRecord.getUuid());
        if (list.size() >= BATCH_COUNT) {
            list.clear(); //保证doAfterAllAnalysed方法执行数据为不重复数据
        }
    }

    //这里是数据全部读完之后走  为保证数据不够批量最小值也能存进数据库
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这个方法便是拿到excel的数据之后，进行修改数据库的操作。
        appreciationService.updateWriteOffState(list);
    }
}

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