SpringBoot + Vue 导出 Excel 文件

需要引入poi Maven 依赖

后端导出分两种情况：

1.  无模板导出

2. 有模板导出

Vue 代码：

<el-button slot="btn" @click="exports">导出</el-button>

exports() {
      let handleRecordIds = [];
      if (this.$refs.handleRecordTable.getSelection().length > 0) {
        this.$refs.handleRecordTable.getSelection().forEach(item => {
          handleRecordIds.push(item.handleRecordId);
        });
      }
      let localFm = cloneObj(this.$refs.handleRecordTable.getFm());
      if (localFm.handleTime) {
        localFm.handleTimeStr = localFm.handleTime[0];
        localFm.handleTimeEnd = localFm.handleTime[1];
      }
      localFm.handleTime = null;
      localFm.handleRecordIds = handleRecordIds;
      /* 设置 responseType:'arraybuffer' 解决下载下来的文件乱码问题
      *  设置type:'application/vnd.ms-excel;' 设置excel文件的格式
      * */
      this.$axios.post('http://localhost:50103/handleRecord/exportHandleRecord', localFm, {responseType: 'arraybuffer'})
        .then((res) => {
          const blob = new Blob([res.data], {type: 'application/vnd.ms-excel;'});
          const a = document.createElement('a');
          // 创建下载的链接
          let href = window.URL.createObjectURL(blob);
          a.href = href;
          //显示下载文件名
          let _fileName = res.headers['content-disposition'].split('=')[1];
          // 文件名中有中文 则对文件名进行转码
          a.download = decodeURIComponent(_fileName);
          // 利用a标签做下载
          document.body.appendChild(a);
          // 点击下载
          a.click();
          // 下载后移除元素
          document.body.removeChild(a);
          // 释放掉blob对象
          window.URL.revokeObjectURL(href);
        }).catch(err => {
        console.log(err)
        this.$confirm("下载失败", "提示", {
          type: "error",
        });
      });
    }

后端代码：

1. 无模板导出

    @RequestMapping("/exportHandleRecord")
    public void exportHandleRecord(@RequestBody HandleRecordQuery handleRecordQuery){
        handleRecordService.exportHandleRecord(handleRecordQuery);
    }

        // 只查询有效状态的记录
        queryWrapper.eq("VALID_STATUS","00");
        queryWrapper.orderByDesc("HANDLE_TIME");
        List<HandleRecord> handleRecords = this.baseMapper.selectList(queryWrapper);
        DictUtil.convert(handleRecords);

        // 无模板直接导出
        EmsExcel.newInstance(handleRecords)
                .headers("序号","巡视编号","巡视时间","业务来源","业务类型","业务对象","巡视人","详细地址")
                .row((t,i,row)->{ row.column(i+1,t.getHandleRecordId(),convert(t.getHandleTime()),t.getBusinessSourceTypeName(),t.getBusinessTypeName(),t.getObjectName(),t.getHandleBy(),t.getAddr());})
                .export("处理记录");

2. 有模板导出

 将模板放置项目中

 前端的代码不变，后端调用EmsExcel 工具类时需要注意

        // 只查询有效状态的记录
        queryWrapper.eq("VALID_STATUS","00");
        queryWrapper.orderByDesc("HANDLE_TIME");
        List<HandleRecord> handleRecords = this.baseMapper.selectList(queryWrapper);
        DictUtil.convert(handleRecords);

        EmsExcel.newInstance(handleRecords)
                .useTemplate("/excel/资源.xlsx")
                .startRow(2) // 有模板时一般从第2行开始
                .headers("序号","巡视编号","巡视时间","业务来源","业务类型","业务对象","巡视人","详细地址")
                .row((t,i,row)->{ row.column(i+1,t.getHandleRecordId(),convert(t.getHandleTime()),t.getBusinessSourceTypeName(),t.getBusinessTypeName(),t.getObjectName(),t.getHandleBy(),t.getAddr());})
                .export("处理记录");

EmsExcel.java 内容

package com.nari.common.utils;

import java.io.File;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.springframework.core.io.ClassPathResource;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

/**
 * 2020-05-18 excel导出
 *
 */
public class EmsExcel<T> {
	
	
	public static final IPage fullPage = new Page<>(1, Long.MAX_VALUE);
	public static final int current = 1;
	public static final int size = Integer.MAX_VALUE;
	
	List<String> headerList= new ArrayList<>();
	Converter<T> converter;
	
	List<T> voList;
	boolean isTemplate;
	int start=0;
	File templateFile;
	private EmsExcel(List<T> dataList)
	{
		this.voList= dataList;
	}
	
	public static <T> EmsExcel<T> newInstance(List<T> dataList)
	{
		return new EmsExcel<>(dataList);
	}
	public EmsExcel<T> useTemplate(String filepath)
	{
		isTemplate= true;
		try
		{
			templateFile= new ClassPathResource(filepath).getFile();
						
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new RuntimeException("找不到该模板："+filepath);
		}
		if(!templateFile.exists()) throw new RuntimeException("找不到该模板："+filepath);
		
		return this;
	}
	public EmsExcel<T> row(Converter<T> c)
	{
		this.converter= c;
		return this;
	}
	public EmsExcel<T> headers(String ...headers)
	{
		if(headers.length>0) headerList= Arrays.asList(headers);
		return this;
	}
	public EmsExcel<T> startRow(int n)
	{
		if(n>0) start= n;
		return this;
	}
	
	public static class Row
	{
		private List<Object> objectList= new ArrayList<>();
		public List<Object> getData()
		{
			return objectList;
		}
		public Row column(Object ...o)
		{
			//for(Object )
			objectList = Arrays.asList(o);
			return this;
			
		}
	}

	
	/**
	 * 
	 * 2020-05-18
	 * @param list 查询的信息
	 * @param filename 比如"用户"，后面可以省略后缀.xlsx
	 * @param converter 把一个对象转换为excel的一行，次序代表显示顺序
	 * 
	 */
	public void export(String filename)
	{
		//
		
		List<List<? extends Object>> rowList= new ArrayList<>();
		
		
		if(!isTemplate)
			if(headerList!=null) rowList.add(headerList);
		
		for(int i=0;i<voList.size();i++)
		{
			T t= voList.get(i);
			try
			{
				Row row= new Row();
				converter.convert(t,i,row);
				rowList.add(row.getData());
			}
			catch(Exception e)
			{
				e.printStackTrace();
				
				throw new RuntimeException("对象转换失败！"+e.toString());
			}
			
		}
		File excel = ExcelWriter.createExcel(rowList,isTemplate?templateFile:null,start);
		
		try
		{
			filename= URLEncoder.encode(filename,"utf-8");
			ExcelWriter.outputStream(excel, filename);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new RuntimeException("写入流失败！");
		}
	}
	
	
}