1.基于注解的excel导出

1.导入poi依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.0.3</version>
</dependency>

2.创建实体类
@Excel中的name便是excel中的列名.orderNum为列的顺序,replace是替换符号

package com.xuecheng.manage_cms.controller;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

@Data
public class User {
    @Excel(name = "姓名", orderNum = "0")
    private String name;
    @Excel(name = "证件类型", replace = {"身份证_1"}, orderNum = "1")
    private String identifyType;
    @Excel(name = "证件号码", orderNum = "2")
    private String identifyNo;
    @Excel(name = "手机号1", orderNum = "3")
    private String phoneA;
    @Excel(name = "手机号2", orderNum = "4")
    private String phoneB;
    @Excel(name = "手机号3", orderNum = "5")
    private String phoneC;
    @Excel(name = "固定电话", orderNum = "6")
    private String telephone;
    @Excel(name = "电子邮箱", orderNum = "7")
    private String email;
    @Excel(name = "身份证地址", orderNum = "8",width = 15)
    private String idcardAdress;
    @Excel(name = "户籍地址", orderNum = "9")
    private String householdAddress;
    @Excel(name = "居住地址", orderNum = "10")
    private String liveAddress;
    @Excel(name = "工作地址", orderNum = "11")
    private String workAddress;
}

3.封装工具类FileWithExcelUtil

import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
import org.apache.commons.lang.StringUtils;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;

public class FileWithExcelUtil {
    private static final Logger log = LoggerFactory.getLogger(ExcelExportUtil.class);

    /**
     *
     * @param isCreateHeader 是否创建表头,默认创建
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }

    /**
     *
     * @param list  数据记恨
     * @param title 标题
     * @param sheetName sheet名称
     * @param pojoClass 集合对行的class对象
     * @param fileName 文件名称
     * @param response 响应
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }
    /**
     * 一个excel 创建多个sheet
     *
     * @param list
     *            多个Map key title 对应表格Title key entity 对应表格对应实体 key data
     *            Collection 数据
     * @return
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            log.error("[monitor][IO][表单功能]", e);
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * Excel 导入 数据源本地文件,不返回校验结果 导入 字 段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param file
     * @param pojoClass
     * @param params
     * @return
     */
    public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return list;
    }
    /**
     * Excel 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param inputstream
     * @param pojoClass
     * @param params
     * @return
     * @throws Exception
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("[monitor][表单功能]", e);
        }
        return list;
    }

}

3.编写业务代码

/**
 * 导出模版
 *
 * @param response
 */
@GetMapping("/exportExcel/model")
public boolean export(HttpServletResponse response) {
    try {
        //模拟从数据库获取需要导出的数据
        List<User> personList = new ArrayList<>();
        for (int i = 10; i >= 0; i--) {
            User user = new User();
            user.setName("张磊" + i);
            user.setEmail("fhsdhg@-" + i);
            user.setIdcardAdress("zxhdn主驾驶本=" + i);
            personList.add(user);
        }

        FileWithExcelUtil.exportExcel(personList, "客户信息表", "客户表", User.class, "客户表.xls", response);
        return true;
    } catch (Exception e) {
        log.info("getCustomerPage", e);
        return false;
        // TODO: handle exception
    }
}

@GetMapping("/exportExcel/model1")
@ApiOperation(value = "不设置表头")
public boolean export1(HttpServletResponse response) {
    try {
        //模拟从数据库获取需要导出的数据
        List<User> personList = new ArrayList<>();
        for (int i = 10; i >= 0; i--) {
            User user = new User();
            user.setName("张一磊" + i);
            user.setEmail("fhsdhg@-" + i);
            user.setIdcardAdress("zxhdn主驾驶本=" + i);
            personList.add(user);
        }

        FileWithExcelUtil.exportExcel(personList, "客户信息表", "客户表", User.class, "客户表.xls", false, response);
        return true;
    } catch (Exception e) {
        log.info("getCustomerPage", e);
        return false;
        // TODO: handle exception
    }

}

4.结果
4.1默认设置表头

4.2,不设置表头