第一步：
导入依赖

    <!-- 导处Excel -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.0.0-beta3</version>
    </dependency>

第二步：
新建方法类

package com.lc.yangzi.module.marketing.sell;

import lombok.extern.slf4j.Slf4j;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.apache.poi.util.IOUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;

/**
 * Excel工具类
 */
@Slf4j
public class ExcelUtils {

    /**
     * 导出Excel(07版.xlsx)到指定路径下
     *
     * @param path      路径
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     */
    public static void export2File(String path, String excelName, String sheetName, Class clazz, List data) {
        String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());
        EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 导出Excel(07版.xlsx)到web
     *
     * @param response  响应
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     * @throws Exception
     */
    public static void export2Web(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 将指定位置指定名称的Excel导出到web
     *
     * @param response  响应
     * @param path      文件路径
     * @param excelName 文件名称
     * @return
     * @throws UnsupportedEncodingException
     */
    public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException {
        File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()));
        if (!file.exists()) {
            return "文件不存在！";
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

        try (
                FileInputStream in = new FileInputStream(file);
                ServletOutputStream out = response.getOutputStream();
        ) {
            IOUtils.copy(in, out);
            return "导出成功！";
        } catch (Exception e) {
            log.error("导出文件异常：", e);
        }

        return "导出失败！";
    }

}

第二步：
新建要导出的列表类

package com.lc.yangzi.module.marketing.sell.customerinfo.domain;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

@Data
public class CustomerInfoExcel {

    @ColumnWidth(20) // 定义列宽
    @ExcelProperty(value = {"客户名称"}, index = 0)
    private String customerName;//客户名称
    @ColumnWidth(20) // 定义列宽
    @ExcelProperty(value = {"客户等级"}, index = 1)
    private Integer customerLevel;//客户等级：A，B，C，D
    @ColumnWidth(20) // 定义列宽
    @ExcelProperty(value = {"地址"}, index = 2)
    private String address;//地址
    @ColumnWidth(20) // 定义列宽
    @ExcelProperty(value = {"联系电话"}, index = 3)
    private String contactPhone;//联系电话
    @ColumnWidth(20) // 定义列宽
    @ExcelProperty(value = {"审核状态"}, index = 4)
    private String auditStatusName;//状态
    @ColumnWidth(20) // 定义列宽
    @ExcelProperty(value = {"提货状态"}, index = 5)
    private String pickingStatusName;//提货状态
    @ColumnWidth(20) // 定义列宽
    @ExcelProperty(value = {"管理人"}, index = 6)
    private String managerUserName;//管理人姓名(多个逗号分隔)


    @ExcelIgnore
    private Integer auditStatus;//审核状态审核状态：0草稿1审核中2已完结
    @ExcelIgnore
    private Long id;//主键ID
    @ExcelIgnore
    private Long orgId;//和组织管联 yz_sys_org where category = 3、
    @ExcelIgnore
    private Integer pickingStatus;//提货状态
    @ExcelIgnore
    private Long createUserId;//创建人
}

第四步：
调用方法

    //导出Excel
    @RequestMapping(value="/exportExcel", method = RequestMethod.GET)
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
        try {
            String customerId = request.getParameter("customerId");
            String auditStatus = request.getParameter("auditStatus");
            String pickingStatus = request.getParameter("pickingStatus");

            if(StringUtils.isBlank(customerId)){
                customerId="0";
            }if(StringUtils.isBlank(auditStatus)){
                auditStatus="-1";
            }if(StringUtils.isBlank(pickingStatus)){
                pickingStatus="0";
            }
            List<CustomerInfoExcel> list = customerInfoService.selectAllCustomerInfoExcel(Long.parseLong(customerId),Integer.parseInt(auditStatus),Integer.parseInt(pickingStatus));
            ExcelUtils.export2Web(response, "客户信息", "客户信息", CustomerInfoExcel.class, list);
        } catch (Exception e) {
            log.error("报表导出异常:", e);
        }
    }

<template>
  <div>
    <div class="elian-box">
      <div class="elian-title">
        <i></i>
        <div>查询</div>
      </div>
      <el-form
        ref="form"
        :model="form"
        label-width="120px"
        @keyup.enter.native.prevent="formSearch"
      >
        <el-row :gutter="10">

          <el-col :span="6" class="form-button-group">
            <el-form-item label-width="0">
              <el-button @click="collapsed = !collapsed"
                >{{ collapsed ? '收起' : '展开' }}
                <i
                  :class="{
                    'el-icon-arrow-down': !collapsed,
                    'el-icon-arrow-up': collapsed
                  }"
                />
              </el-button>
              <el-button
                type="primary"
                :loading="gridLoading"
                @click="formSearch()"
                >查询</el-button
              >
              <el-button @click="clearForm('form'), formSearch()"
                >重置</el-button
              >
            </el-form-item>
          </el-col>
        </el-row>
      </el-form>
    </div>
    <elian-tool>
      <template #left>
        <div class="elian-title">
          <i></i>
          <div>列表</div>
        </div></template
      >
      <template #right>
        <el-button size="small" @click="downloadTemplateExcel" style="float: left;margin-top: 10px;margin-right: 10px">导入模板下载</el-button>
        <el-upload
          :action="importExcel()"
          :file-list="fileList"
          :limit="9"
          :data="routerInfo"
          :show-file-list="false"
          :on-success="handleSuccess"
          :filetype="['xlxs']"
          :headers="$download.addHeadToken()"
          style="float: left"
        >
        <el-button type="primary" style="margin-right: 10px">导入</el-button>
        </el-upload>
        <el-button type="primary" @click="exportExcel" >导出</el-button>
      </template>
    </elian-tool>
    <div class="elian-box">
      <elian-grid
        ref="table"
        :data="tableData"
        :loading="gridLoading"
        :page-size="pageSize"
        :page-sizes="pageSizes"
        :current-page="currentPage"
        :total="total"
        height="200"
        resize
        @pagination-current-change="paginationCurrentChange"
        @pagination-size-change="paginationSizeChange"
        @selection-change="selectionChange"
      >
        <el-table-column
          label="序号"
          type="index"
          
          fixed
          :index="handleIndex"
          width="50"
        >
        </el-table-column>
        <el-table-column
          prop="tenditmId"
          label="招标项目代码"
          min-width="240"
          
          show-overflow-tooltip
        >
        </el-table-column>
      </elian-grid>
    </div>
  </div>
</template>
<script>
import gridMixins from '@/components/ElianGrid/grid-mixins' //列表方法引入
import formMixins from '@/mixins/form' //form方法引入
import download from "downloadjs";
import { getToken } from "@/utils/auth";
import { queryDrugDclaFilterPage, deleteDrugDclaFilterById, downloadTemplateExcel, importExcel, exportExcel } from '@/api/std/drugDclaFilter' //列表接口引入
export default {
  props: {
    routerInfo: {
      type: Object
    }
  },
  components: {},
  mixins: [gridMixins, formMixins],
  data() {
    return {
      fileList:[],
      form: {},
      tableData: []
    }
  },
  created() {},
  methods: {
    getToken,
    importExcel,
    //多选的事件
    selectionChange() {},
    // 列表 数据获取
    async getList() {
      let response
      this.gridLoading = true
      try {
        this.queryData.confirmStas = 0
        response = await queryDrugDclaFilterPage(this.queryData)
      } catch (error) {
        this.gridLoading = false
        return
      }
      const { current, total, records } = response.data
      this.tableData = records
      this.currentPage = current
      this.total = total
      this.gridLoading = false
    },

    deletedetel(drugInfoId) {
      this.$confirm('是否确认删除?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      })
        .then(() => {
          deleteDrugDclaFilterById(drugInfoId).then(res => {
            if (res.success === true) {
              this.$message({
                type: 'success',
                message: '删除成功!'
              })
              this.getList()
            }
          })
        })
        .catch(() => {
          this.$message({
            type: 'info',
            message: '已取消删除'
          })
        })
    },
    downloadTemplateExcel(){
      this.$confirm('是否下载导入模板?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      }).then(() => {
        downloadTemplateExcel().then(resp => {
          this.$message.success("导入下载模板成功!")
          let mimetypes =
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' // 此mimetype根据文档类型切换
          let blob = new Blob([resp.data], {type: mimetypes})
          let filename = decodeURI(resp.headers.filename)
          download(blob, filename, mimetypes)
        })
      })
    },
    exportExcel(){
      this.$confirm('是否导出?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      }).then(() => {
        exportExcel(this.queryData).then(resp => {
          this.$message.success("导出成功!")
          let mimetypes =
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' // 此mimetype根据文档类型切换
          let blob = new Blob([resp.data], {type: mimetypes})
          let filename = decodeURI(resp.headers.filename)
          download(blob, filename, mimetypes)
        })
      })
    },
    handleSuccess(res){
      if (res.success==true){
        this.$message.success("导入成功")
      }else{
        this.$message.warning("导入失败")
      }
      this.getList();
    },
  }
}
</script>

import request from '@/utils/request'
import dowrequest from "@/utils/downloadrequest";

/**
 * 下载导入模板
 * @param {*} id
 */
export function downloadTemplateExcel() {
  return dowrequest.get(
    '/tps-local-bd/web/std/dlcg/drugDclaFilter/downloadTemplateExcel'
  )
}
/**
 * 导入
 * @returns {string}
 */
export function importExcel() {
  return '/tps-local-bd/web/std/dlcg/drugDclaFilter/import';
}

/**
 * 导出
 * @returns {string}
 */
export function exportExcel(params) {
  return dowrequest.post(
    '/tps-local-bd/web/std/dlcg/drugDclaFilter/export',params
  )
}

controller
/**
	 * 下载模板文件
	 *
	 * @param response
	 */
	@SneakyThrows
	@ApiOperation(value = "下载导入模板", notes = "下载导入模板")
	@GetMapping("/downloadTemplateExcel")
	public void downloadTemplateExcel(HttpServletResponse response) {
		List<ExDrugDclaFilter> data = new ArrayList<>();
		Map<Integer,String[]> mapDropDown = new HashMap<>();
		//0未确认，1已确认，2已生成
		String[] confirmStas = {"未确认","已确认","已生成"};
		mapDropDown.put(4,confirmStas);
		ExcelUtil.writeExcel(response, data,mapDropDown,"中选申报药品信息导入模板","sheet", ExDrugDclaFilter.class);
	}

	@PostMapping("/export")
	@ApiOperation(value = "excel导出", notes = "Excel导出")
	public void excelDownload(HttpServletResponse response, @RequestBody DrugDclaFilterDTO drugDclaFilterDTO) {
		drugDclaFilterService.excelDownload(response,drugDclaFilterDTO);
	}

	@SneakyThrows
	@PostMapping("/import")
	@ApiOperation(value = "excel导入", notes = "excel导入")
	public Result<String> excelImport(@RequestParam("file") MultipartFile file, @CurrentUserContext UserContext userContext) {
		if (ObjectUtil.isEmpty(file)){
			return Results.failure("file 为空！");
		}
		return drugDclaFilterService.excelImport(file.getInputStream(), userContext);
	}

service
    
@Override
    @SneakyThrows
    public Result<String> excelImport(@NotNull InputStream inputStream, UserContext userContext) {
        String redisKey = "drugDclaFilterExcel"+ IdWorker.getIdStr();
        EasyExcel.read(inputStream, ExDrugDclaFilter.class, new DrugDclaFilterExcelDataListener(
                drugDclaFilterBO, userContext, redisKey, cacheUtil)).sheet().doRead();
        String errStr = cacheUtil.get(redisKey,String.class);
        // 如果有异常信息返回失败
        if (StringUtils.isNotBlank(errStr)){
            log.debug(errStr);
            // 清空
            cacheUtil.del(redisKey);
            return Results.failure(errStr);
        }else {
            return Results.success("导入成功！");
        }
    }

    @SneakyThrows
    @Override
    public void excelDownload(HttpServletResponse response, DrugDclaFilterDTO drugDclaFilterDTO) {
        // 查询数据 最好根据查询日期查询 临时写死5001 测试 可以切sheet
        drugDclaFilterDTO.setSize(5001);
        List<DrugDclaFilterDTO> list = this.queryList(drugDclaFilterDTO);
        if (CollectionUtil.isNotEmpty(list)){
            ExcelUtil.writeExcel(response,DozerUtil.convertList(list, ExDrugDclaFilter.class),
                    "中选申报药品信息导出", ExDrugDclaFilter.class);
        }
    }

dto

@Data
public class ExDrugDclaFilter {

    @ColumnWidth(15)
    private String tenditmId;
}

package cn.hsa.spp.std.listener;

import cn.hsa.spp.comm.cache.CacheUtil;
import cn.hsa.spp.comm.common.context.UserContext;
import cn.hsa.spp.comm.common.exception.Result;
import cn.hsa.spp.comm.util.DozerUtil;
import cn.hsa.spp.comm.util.ValidationUtil;
import cn.hsa.spp.std.bo.DrugDclaFilterBO;
import cn.hsa.spp.std.dao.model.DrugDclaFilterDO;
import cn.hsa.spp.std.dto.ExDrugDclaFilter;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import lombok.extern.slf4j.Slf4j;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.concurrent.TimeUnit;

/**
 * EXCEL导入的监听器
 *
 * @author songlc
 * @version V1.0
 * @since 2020-9-02 19:45
 */
@Slf4j
public class DrugDclaFilterExcelDataListener extends AnalysisEventListener<ExDrugDclaFilter> {
    /**
     * 每隔3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;

    private DrugDclaFilterBO drugDclaFilterBO;

    /**
     * 用户信息
     */
    private UserContext userContext;

    /**
     * 缓存工具
     */
    private CacheUtil cacheUtil;

    /**
     * 用来存储异常信息
     */
    private String redisKey;

    /**
     * 异常信息
     */
    private StringBuffer errorStr = new StringBuffer();

    List<DrugDclaFilterDO> list = new ArrayList<>();


    List<DrugDclaFilterDO> listCheck = new ArrayList<>();


    public DrugDclaFilterExcelDataListener(DrugDclaFilterBO drugDclaFilterBO,UserContext userContext, String redisKey, CacheUtil cacheUtil) {
        this.drugDclaFilterBO = drugDclaFilterBO;
        this.userContext = userContext;
        this.redisKey = redisKey;
        this.cacheUtil = cacheUtil;
    }

    @Override
    public void invoke(ExDrugDclaFilter exDrugDclaFilter, AnalysisContext analysisContext) {
        // 逻辑判断 并添加到List容器中
        Result<String> result = ValidationUtil.fastFailValidate(exDrugDclaFilter);
        if (ObjectUtil.isNotEmpty(result) && result.getCode() == 0){
            DrugDclaFilterDO drugDclaFilterDO = DozerUtil.convert(exDrugDclaFilter, DrugDclaFilterDO.class);
            //Optional.ofNullable(drugDclaFilterDO).map(o -> list.add(o));
            Optional.ofNullable(drugDclaFilterDO).map(o -> listCheck.add(o));
        }else {
            errorStr.append("第")
                    .append(analysisContext.readRowHolder().getRowIndex())
                    .append("行:")
                    .append(result.getMessage())
                    .append(" ");
        }
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
/*        if (list.size() >= BATCH_COUNT) {
            drugDclaFilterBO.saveBatch(list);
            // 存储完成清理 list
            list.clear();
        }*/
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 收尾
/*        if (CollectionUtil.isNotEmpty(list)){
            drugDclaFilterBO.saveBatch(list);
        }*/
        // 存缓存 调用方获取展示
        cacheUtil.set(redisKey,errorStr.toString(),60, TimeUnit.SECONDS);
        // 没有异常信息 调用导入功能
        if (StrUtil.isBlank(errorStr.toString())){
            if (CollectionUtil.isNotEmpty(listCheck)){
                // 填充数据
                listCheck.forEach(o -> this.convertBaseDo(o,userContext));
                drugDclaFilterBO.saveOrUpdateAll(listCheck);
            }
        }

       /* Map<String, List<DrugDclaFilterDO>> collect = listCheck.parallelStream().collect(Collectors.groupingBy(o -> o.getDrugCode() + "_" +
                o.getTenditmId() + "_0"));

        List<DrugDclaFilterDO> addList = new ArrayList<>();
        List<DrugDclaFilterDO> updateList = new ArrayList<>();
        for (Map.Entry<String,List<DrugDclaFilterDO>> entry : collect.entrySet()){
            if (entry.getValue().size() > 1){
                updateList.addAll(entry.getValue());
            }else {
                addList.addAll(entry.getValue());
            }
        }
        if (CollectionUtil.isNotEmpty(addList)){
            drugDclaFilterBO.saveBatch(list);
        }
        if (CollectionUtil.isNotEmpty(updateList)){
            drugDclaFilterBO.updateAll(updateList);
        }*/
    }

    /**
     * 转换
     * @param userContext
     */
    public DrugDclaFilterDO convertBaseDo(DrugDclaFilterDO t, UserContext userContext){
        t.setDrugInfoId(IdWorker.getIdStr());
        t.setConfirmStas("0");
        t.setRid(IdWorker.getIdStr());
        t.setCrterId(userContext.getUserId());
        t.setCrterName(userContext.getUserName());
        t.setCrteOptinsNo(userContext.getOrgId());
        t.setCrteTime(LocalDateTime.now());
        t.setOptTime(LocalDateTime.now());
        t.setOpterId(userContext.getUserId());
        t.setOpterName(userContext.getUserName());
        t.setOptinsNo(userContext.getOrgId());
        t.setUpdtTime(LocalDateTime.now());
        t.setInvdFlag("0");
        return t;
    }
}

工具类

package cn.hsa.spp.comm.util;


import cn.hsa.spp.comm.common.exception.ServiceException;
import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * EXCEL的工具类
 *
 * @author liwenjun
 * @version V1.0
 * @since 2019-12-02 18:03
 */
@Slf4j
public class ExcelUtil {

    private static final int MAXROWS = 5000;

    /**
     * 生成EXCEL工具类
     *
     * @param fileName
     * @param sheetName
     * @param data
     * @param clazz
     * @throws FileNotFoundException
     */
    public static void expExcel(String fileName, String sheetName, List data, Class clazz) {
        // 生成EXCEL并指定输出路径
        OutputStream out = null;
        ExcelWriter excelWriter = null;
        try {
            out = new FileOutputStream(fileName);
            excelWriter = EasyExcel.write(out, clazz).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
        } catch (Exception e) {
            throw new ServiceException("导出EXCEL异常");
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                log.info(LogUtil.getBaseLog() + "生成EXCEL异常" + e.getMessage());
            }
        }
    }

    private ExcelUtil() {
    }

    /**
     * excel下载
     *
     * @param response  返回对象
     * @param fileName  文件名
     * @param sheetName 页签名称
     * @param head      对象
     * @param data      数据
     */
    public static void writeExcel(HttpServletResponse response, String fileName, String sheetName, Class head, List data) {
        try (OutputStream outputStream = response.getOutputStream()) {
            setRespHeader(response, fileName);

            EasyExcel.write(outputStream, head).sheet(sheetName).doWrite(data);
        } catch (IOException e) {
            log.error("excel下载失败", e);
            throw new ServiceException("excel下载失败");
        }
    }

    /**
     * 设置响应头
     *
     * @param response
     * @param fileName
     * @throws UnsupportedEncodingException
     */
    public static void setRespHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileNameUrl = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("content-disposition", "attachment;filename=" + fileNameUrl + ".xlsx");
        response.setHeader("filename", fileNameUrl + ".xlsx");
    }

    /**
     * 获取默认表头内容的样式
     * @return
     */
    private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy(){
        /** 表头样式 **/
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色（浅灰色）
        // 可以参考：https://www.cnblogs.com/vofill/p/11230387.html
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体大小
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        /** 内容样式 **/
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 内容字体样式（名称、大小）
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置内容垂直居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置内容水平居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 头样式与内容样式合并
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 导出
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class<?> clazz) throws Exception {
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: "+data.size()+"条。");
        setRespHeader(response,fileName);
        EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(getDefaultHorizontalCellStyleStrategy()).doWrite(data);
        log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
    }

    /**
     * 导出 下拉框
     * @param response
     * @param data
     * @param mapDropDown Map<Integer,String[]> key是列的index 从0开始  value 是数据集
     * @param fileName
     * @param sheetName
     * @param clazz
     */
    @SneakyThrows
    public static void writeExcel(HttpServletResponse response, List<?> data, Map<Integer,String[]> mapDropDown, String fileName, String sheetName, Class<?> clazz){
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: "+data.size()+"条。");
        setRespHeader(response,fileName);
        EasyExcel.write(response.getOutputStream(), clazz)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .registerWriteHandler(getDefaultHorizontalCellStyleStrategy())
                .registerWriteHandler(new TemplateCellWriteHandlerDate(mapDropDown))
                .doWrite(data);
        log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
    }

    /**
     * @author QiuYu
     * @createDate 2020-11-16
     * @param response
     * @param data  查询结果
     * @param fileName 导出文件名称
     * @param clazz 映射实体class类
     * @param <T>  查询结果类型
     * @throws Exception
     */
    public static<T> void writeExcel(HttpServletResponse response, List<T> data, String fileName, Class<?> clazz) throws Exception {
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: "+data.size()+"条。");

        //List<List<T>> lists = SplitListUtil.splitList(data,MAXROWS); // 分割的集合
        List<List<T>> lists = CollectionUtil.split(data, MAXROWS); // 分割的集合
        setRespHeader(response,fileName);
        OutputStream out = response.getOutputStream();
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());
        ExcelWriter excelWriter = excelWriterBuilder.build();
        ExcelWriterSheetBuilder excelWriterSheetBuilder;
        WriteSheet writeSheet;
        if (CollectionUtil.isNotEmpty(lists)){
            for (int i =1;i<=lists.size();i++){
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                excelWriterSheetBuilder.sheetNo(i);
                excelWriterSheetBuilder.sheetName("sheet"+i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(lists.get(i-1),writeSheet);
            }
        }
        out.flush();
        excelWriter.finish();
        out.close();
        log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
    }
}

导出excel的工具有很多，如poi，jxl等但是他们导出excel会比较的消耗内存，一旦数据大起来可能造成内存溢出，而easyexcel很好的避免了内存溢出的问题。

首先下载easyexcel的依赖包：

<dependency>
    	<groupId>com.alibaba</groupId>
    	<artifactId>easyexcel</artifactId>
    	<version>1.0.4</version>
</dependency>

java模型映射(省略了get和set方法)：

public class ClsModal extends BaseRowModel{
	
	@ExcelProperty(value="班级编号",index=0)
	private Integer cid;
	
	@ExcelProperty(value="班级名称",index=1)
	private String cname;

导出类：

package exportExcel;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;

public class ExportExcel {

	public void export(List<ClsModal> clsList) {
		try(OutputStream out=new FileOutputStream("C:\\Users\\hc\\Desktop\\exportCls.xlsx")) {
			ExcelWriter writer=new ExcelWriter(out,ExcelTypeEnum.XLSX);
			
			if(!clsList.isEmpty()) {
				/**
				  * 1表示第1个工作表，
				  * 0表示从第几行开始读取数据，最小值为0
				  * clsList.get(0).getClass()表示要导出的实体类 
				  * /
				Sheet sheet=new Sheet(1,0,clsList.get(0).getClass());
				
				sheet.setSheetName("导出班级测试");
				writer.write(clsList, sheet);
			}
			writer.finish();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

测试类：

package exportExcel;

import java.util.ArrayList;
import java.util.List;

public class ExportTest {
	public static void main(String[] args) {
		//准备数据
		List<ClsModal> clsList=new ArrayList<ClsModal>();
		for(int i=0;i<3;i++) {
			ClsModal cls=new ClsModal();
			cls.setCid(i);
			cls.setCname("java"+i);
			clsList.add(cls);
		}
		
		new ExportExcel().export(clsList);
	}
}

测试结果：

如果是前后端分离，则只需要使用HttpServletResponse获得OutputStream流，然后再形成excel表，将其写入到输出流中，然后响应给前端即可。但注意要将响应的头设置成“Content-Disposition”。