导入导出的工具类(可以自定义导出列)

1.导出接口

    @RequestMapping(value = "/test", method = RequestMethod.GET)
    public void add(String customExportFieldStr,   //前段传来的自定义字段，以.隔开
                    Integer  showIncreaseFlag,    //是否需要导出自定义字段，1导出 其他不导出
                    HttpServletRequest request,
                    HttpServletResponse response) throws ValidateException, Exception {
        TestVo t = new TestVo();
        t.setName("111");
        t.setNumber("1");
        t.setTest("测试导出");
        t.setTest2("222222");
        List<TestVo> list = new ArrayList<>();
        list.add(t); 
        //这个是导出所有
         ExcelUtil.exportExcel(list,null,null,TestVo.class,"活动报表.xls",response);
         //这个是按照传递的参数导出
        ExcelUtil.customFieldExport(list, TestVo.class, customExportFieldStr, showIncreaseFlag, "测试自定义导出.xls",
                                    response);
    }

2.具体方法（util）

package com.bxm.advertiser.controllers;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity; 
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;

/**
 * 表格工具类
 *
 * @author 阿导
 * @version v1.1.4_Report
 * @fileName com.bxm.adsmanager.utils.ExcelUtil.java
 * @CopyRright (c) 2018-杭州微财科技有限公司
 * @created 2018-02-27 16:00:00
 * @modifier 阿导
 * @updated 2018-02-27 16:00:00
 */
public class ExcelUtil {


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

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

    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){
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    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){
        } catch (Exception e) {
        }
        return list;
    }

    /**
     * Description: 自定义列导出
     * [customExportFieldStr] customFieldExport//前段传来的自定义字段，以.隔开
     * [showIncreaseFlag]  //是否需要导出自定义字段，1导出 其他不导出
     *
     * JDK version used: <JDK1.8>
     * Author： hxpeng
     * Create Date： 2018/7/25 18:39
     * 注意：注释了一部分，如果前段不传要后端自己判断的话，则需要将注释的部分打开，并且  @ExcelReportIncrease(belongField = "test") ，test要对应字段
     */
    public static <T> void customFieldExport(List<T> target, Class<T> clazz, String customExportFieldStr, Integer showIncreaseFlag, String fileName, HttpServletResponse response) {
        // step 0: 参数校验 初始化
        if (null == target || null == clazz || StringUtils.isBlank(fileName) || null == response) {
            throw new NullPointerException();
        }
        // 是否导出涨幅字段(为空则默认显示)
      //  boolean isShowIncrease = null!=showIncreaseFlag&& 1 == showIncreaseFlag;
        // 将类的默认导出属性 填充到集合中去
        Set<String> customFieldArray = new LinkedHashSet<>(ExcelUtil.addDefaultFields(clazz));
        // 再把前端传过来的属性 填充进去
        if (StringUtils.isNotEmpty(customExportFieldStr)) {
            customFieldArray.addAll(Arrays.asList(customExportFieldStr.split("\\.")));
        }

        // step 1: 拼装类使用了主角的属性成map
        Field[] fields = clazz.getDeclaredFields();
        if (fields.length < 1) {
            throw new RuntimeException("the class has no field!");
        }
        // 目标类中使用了 @Excel 注解的属性的关系map(涨幅字段不在里面)，key:自己的field名， value：@excel的name属性值
        Map<String, String> fieldMap = new HashMap<>(fields.length);
        // 涨幅属性关系map, key:所跟随的field名， value：[@excel的name属性值 + ":" + 自己的field名]
   //     Map<String, String> increaseFieldMap = null;
    /*    if (isShowIncrease){
            increaseFieldMap = new HashMap<>(fields.length);
        }*/
       for (Field field : fields) {
            // easypoi 的 excel 注解
            Annotation excelAnnotation = field.getAnnotation(Excel.class);
            // 展示涨幅的注解
            Annotation excelReportIncreaseAnnotation = field.getAnnotation(ExcelReportIncrease.class);
            if (null == excelAnnotation){
                continue;
            }
            // excelReportIncreaseAnnotation 不为空 表示这个属性是导出字段
           /* if (null != excelReportIncreaseAnnotation){
                // 是否导出涨幅字段
                if (isShowIncrease) {
                    increaseFieldMap.put(((ExcelReportIncrease) excelReportIncreaseAnnotation).belongField(),  ((Excel) excelAnnotation).name() + ":" + field.getName());
                
                }
                continue;
            }*/
            fieldMap.put(field.getName(), ((Excel) excelAnnotation).name());
        }
        if (MapUtils.isEmpty(fieldMap)) {
            throw new RuntimeException("no field to export!");
        }

        // step 2: 校验自定义导出字段，并拼装excel头
        // 校验前端传的自定义属性值，不存在在导出类中则从set中剔除
       // customFieldArray.removeIf(fieldName -> !fieldMap.containsKey(fieldName));
        // 最终导出目标字段集合
        List<String> exportFieldArray = new ArrayList<>(customFieldArray);

        List<ExcelExportEntity> excelHeader = new ArrayList<>(fieldMap.size());
        int orderNum = 0;
        for (String field : customFieldArray) {
            ExcelExportEntity excelExportEntity = new ExcelExportEntity(fieldMap.get(field), field);
            excelExportEntity.setOrderNum(orderNum);
            excelHeader.add(excelExportEntity);
            /*if (isShowIncrease){
                // 在后面加一个涨幅字段
                String belongFieldName = increaseFieldMap.get(field);
                if (StringUtils.isBlank(belongFieldName)){
                    continue;
                }
                int index = belongFieldName.indexOf(":");
                String name = belongFieldName.substring(0, index);
                String key = belongFieldName.substring(index + 1, belongFieldName.length());
                orderNum ++;
                excelExportEntity = new ExcelExportEntity(name, key);
                excelExportEntity.setOrderNum(orderNum);
                excelHeader.add(excelExportEntity);
                exportFieldArray.add(key);
            }*/
            orderNum ++;
        }

        // step 3: 拼装body
        List<Map<String, Object>> excelBody = new ArrayList<>();
        try {
            for (T t : target) {
                Class tClass = t.getClass();
                Field[] tClassField = tClass.getDeclaredFields();
                Map<String, Object> valueMap = new HashMap<>();
                for (Field field : tClassField) {
                    if (!exportFieldArray.contains(field.getName())){
                        continue;
                    }
                    PropertyDescriptor propertyDescriptor = new PropertyDescriptor(field.getName(), tClass);
                    Method method = propertyDescriptor.getReadMethod();
                    valueMap.put(field.getName(), method.invoke(t));
                }
                excelBody.add(valueMap);
            }
        } catch (IllegalAccessException | InvocationTargetException | IntrospectionException e) {
            e.printStackTrace();
            throw new RuntimeException("custom export fail! message: " + e.getMessage());
        }

        // step 4: 开始导出
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), excelHeader, excelBody);
        downLoadExcel(fileName, response, workbook);
    }


    /**
     * Description: 获取导出类的默认导出类集合，类必须继承BaseExportVo, 并重写getDefaultExportFields方法
     * JDK version used: <JDK1.8>
     * Author： hxpeng
     * Create Date： 2018/7/27 17:32
     */
    @SuppressWarnings("unchecked")
    private static List<String> addDefaultFields(Class<?> clazz) {
        try {
            Method method = clazz.getMethod("getDefaultExportFields");
            if (null != method) {
                Object result = method.invoke(clazz.newInstance());
                if (null != result){
                    String[] defaultFields = (String[]) result;
                    return new ArrayList<>(Arrays.asList(defaultFields));
                }
            }
        } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException | InstantiationException e) {
            e.printStackTrace();
            throw new RuntimeException("get default export field fail!!!");
        }
        return Collections.emptyList();
    }

}

 

 package com.bxm.advertiser.controllers;
 

public abstract class BaseExportVo {


    /**
     * Description: 返回默认的导出属性集合
     * JDK version used: <JDK1.8>
     * Author： hxpeng
     * Create Date： 2018/7/26 14:53
     */
    public abstract String[] getDefaultExportFields();

}

package com.bxm.advertiser.controllers;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;


@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.FIELD })
public @interface ExcelReportIncrease {

    /**
     * 自定义字段： field 值
     * 
     * @return
     */
    String belongField();

}

 

package com.bxm.advertiser.controllers;
 

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

public class TestVo extends BaseExportVo {

    
 
    @Excel(name = "名称")
    private String name;

    @Excel(name = "编号")
    private String number;

    @ExcelReportIncrease(belongField = "test")
    @Excel(name = "自定义导出字段")
    private String test;

    @ExcelReportIncrease(belongField = "test2")
    @Excel(name = "自定义导出字段2")
    private String test2; 
    

    @Override
    public String[] getDefaultExportFields() {
        return new String[]{"number","name" };
    }
     get 和set方法自己写
}

需要导入的包

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

注意：他会和下面这个jar包冲突

    <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.5-FINAL</version>
</dependency>