java POI 动态选择导出字段列

业务场景：一个页面的导出按钮，由于字段非常多，并且客户每次都想导出不同的字段便于观察，所以需要动态的选择Excel导出列

本文采用 自定义注解 + POI 的方式进行实现
话不多说，先看效果，如果不符合你的效果，直接划走。

一、效果图展示

1.1postman 模拟前端传参

下面的传了字段值的代表需要进行导出的列

1.2导出的 excel 效果图

下面简单说下代码思路与流程：
1.前端传参需要导出的字段，以及字段对应的excel表头
2.循环第一步的字段，根据查到的数据集绘制每一行的单元格
3.关键一步：找到前端传参与我们数据集(从mysql查到的数据实体列表)的对应关系，也正是根据这一步进行每个单元格的绘制。
在本文中，是利用实体类的反射进行获取对应关系的，具体请看 《ExportExcel.getValues(Object rowData, String[] exportFieldArr)》处的代码, 核心代码！

二、代码展示

2.1.实体类

2.1.1用于接收前端的dto

/**
 * <p>@Description: 用于接收前端传参的dto</p >
 * <p>@param </p >
 */
@Data
@JsonIgnoreProperties(ignoreUnknown = true)
public class DynamicExportRequest {

    //@ApiModelProperty("前端选择的需要导出的字段")
    private GoodsExportBean goodsExportBean;
    // 分页参数
    private int pageIndex;
    private int pageSize;
}

2.1.2 mysql 实体类

package com.lzq.learn.test.动态选择列导出excel;

import com.lzq.learn.anno.DynamicExport;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.time.LocalDateTime;

/**
 * <p>@Description: 实体类</p >
 * <p>@date 18:58 18:58</p >
 * @author 吴巴格
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Goods {

    private Integer id;

    private String name;

    private BigDecimal price;

    private String type;

    private BigDecimal quantity;

    private LocalDateTime createTime;

}

2.1.3 用于接收前端哪些需要导出字段的dto

package com.lzq.learn.test.动态选择列导出excel.byExportBean;

import com.lzq.learn.anno.DynamicExport;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * <p>@Description: 用于接收前端哪些需要导出字段的dto</p >
 * <p>@date 9:28 9:28</p >
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class GoodsExportBean {


    @DynamicExport(sort = 1, name = "id")
    private String id;

    @DynamicExport(sort = 2, name = "姓名")
    private String name;

    @DynamicExport(sort = 3, name = "价格")
    private String price;

    @DynamicExport(sort = 4, name = "类型")
    private String type;

    @DynamicExport(sort = 5, name = "库存")
    private String quantity;

    @DynamicExport(sort = 6, name = "创建时间")
    private String createTime;
}

2.1.4 给前端可选择字段实体

package com.lzq.learn.test.动态选择列导出excel;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * <p>@Description: 用于返回给前端的可选择导出列 </p >
 * <p>@date 14:23 14:23</p >
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DynamicField {

    //("字段英文名")
    private String englishFiledName;

    //("excel显示表头中文名")
    private String chineseTitleName;

    public static DynamicField build(String englishFiledName, String chineseTitleName) {
        DynamicField dynamicField = new DynamicField();
        dynamicField.setEnglishFiledName(englishFiledName);
        dynamicField.setChineseTitleName(chineseTitleName);
        return dynamicField;
    }
}

2.2 工具类以及注解

2.2.0 辅助实体类dto

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.io.Serializable;

/**
 * @author x
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class ExcelFiled implements Serializable {
    private String fieldName;
    private String fieldValue;
    private int sort;
}

2.2.1 自定义注解

package com.lzq.learn.anno;

import java.lang.annotation.Retention;
import java.lang.annotation.Target;
import static java.lang.annotation.ElementType.*;
import static java.lang.annotation.RetentionPolicy.RUNTIME;

/**
 * <p>@Description: 动态导出字段, 将可选字段加上该注解</p >
 * <p>@date 10:26 10:26</p >
 */
@Target({TYPE, FIELD, METHOD})
@Retention(RUNTIME)
public @interface DynamicExport {

    int sort() default 0;

    String name() default "";
}

2.2.2 动态字段列工具类

package com.lzq.learn.test.动态选择列导出excel;

import com.lzq.learn.anno.DynamicExport;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>@Description: 可选择动态字段列工具类</p >
 * <p>@date 14:26 14:26</p >
 */
public class DynamicFieldUtil {

    /**
     * <p>@Description: 带有 @DynamicExport 注解的实体类，利用反射返回可选择动态列</p >
     * <p>@param [object]</p >
     * <p>@return java.util.List<com.hjza.environment.response.export.DynamicField></p >
     * <p>@throws </p >
     */
    public static <T> List<DynamicField> exportPageListDynamicFieldList(Class<T> objectClass){
        Field[] declaredFields = objectClass.getDeclaredFields();
        List<DynamicField> dynamicFieldList = new ArrayList<>();
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            DynamicExport dynamicExportAnnotation = declaredField.getAnnotation(DynamicExport.class);
            String chineseTitle = dynamicExportAnnotation.name();
            dynamicFieldList.add(DynamicField.build(declaredField.getName(), chineseTitle));
        }
        return dynamicFieldList;
    }

}

2.2.3 核心POI工具类

package com.lzq.learn.test.动态选择列导出excel.byExportBean;

import cn.hutool.core.util.StrUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * <p>@Description: 将 Apache POI 的一些常用api进行封装</p >
 */
public class ExportExcel {



    /**
     * <p>@Description: 根据 所选动态列，以及数据集进行导出excel</p >
     * <p>@param [exportBeanFrom, dataList, fileName]</p >
     * <p>@return void</p >
     * <p>@throws </p >
     */
    public static <T> void exportByTitleAndData(Object exportBeanFrom, List<T> dataList, String fileName) {
        // 处理参数: 需要导出的英文字段名 exportFieldArr, 中文表头 title
        Map<String, Object> beanExportFieldMap = ExportExcelSortUtil.filterAndSort(exportBeanFrom);
        Map<String, String[]> fieldAndTitleMap = ExportExcelSortUtil.getExportFieldAndExportTitle(beanExportFieldMap);
        String[] title = ExportExcelSortUtil.getExportTitleArr(fieldAndTitleMap);
        String[] exportFieldArr = ExportExcelSortUtil.getExportFieldArr(fieldAndTitleMap);
        // 根据参数 生成工作簿，并写入文件流
        if (title.length > 0 && exportFieldArr.length > 0) {
            Workbook workbook = ExportExcel.getWorkbook(dataList, exportFieldArr, title);
            ExportExcel.writeToResponse(workbook, fileName);
        }
    }

    /**
     * <p>@Description: 将文件流写会回 response 中</p >
     * <p>@param [workbook, fileName]</p >
     * <p>@return void</p >
     * <p>@throws </p >
     */
    public static void writeToResponse(Workbook workbook, String fileName) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = attributes.getResponse();
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }


    /**
     * <p>@Description: 根据 数据集、导出列、表头汉字 创建工作簿</p >
     * <p>@param [dataSet 数据集, exportFieldArr 需要导出的字段列, titles 导出列对应的中文表头]</p >
     * <p>@return org.apache.poi.xssf.usermodel.XSSFWorkbook</p >
     * <p>@date 15:19 15:19</p >
     */
    public static <T> XSSFWorkbook getWorkbook(Collection<T> dataSet, String[] exportFieldArr, String[] titles) {
        // 校验变量和预期输出excel列数是否相同
        if (exportFieldArr.length != titles.length) {
            return null;
        }
        // 存储每一行的数据
        List<String[]> list = new ArrayList<>();
        for (Object obj : dataSet) {
            // 获取到每一行的属性值数组
            list.add(getValues(obj, exportFieldArr));
        }
        return getWorkbook(titles, list);
    }

    public static XSSFWorkbook getWorkbook(String[] titles, List<String[]> list) {
        // 定义表头
        String[] title = titles;
        // 创建excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建工作表sheet
        XSSFSheet sheet = workbook.createSheet();
        // 创建第一行
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;
        // 插入第一行数据的表头
        row.setHeight((short) (24 * 20));
        CellStyle headerCommonStyle = getHeaderCommonStyle(workbook);
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(headerCommonStyle);
        }
        // 数据行渲染
        CellStyle bodyStyle = getBodyStyle(workbook);
        int idx = 1;
        for (String[] strings : list) {
            XSSFRow nrow = sheet.createRow(idx++);
            XSSFCell ncell = null;
            for (int i = 0; i < strings.length; i++) {
                ncell = nrow.createCell(i);
                ncell.setCellValue(strings[i]);
                ncell.setCellStyle(bodyStyle);
            }
        }
        // 设置固定列宽
        setColumnWidth(titles, sheet);
        return workbook;
    }

    // 设置固定列宽
    public static void setColumnWidth(String[] titles, Sheet sheet) {
        for (int i = 0; i < titles.length; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        }
    }

    private static CellStyle getHeaderCommonStyle(Workbook workbook) {
        CellStyle header = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(Boolean.TRUE);
        font.setFontHeightInPoints((short) 14);
        font.setFontName("宋体");
        header.setFont(font);
        header.setBorderTop(BorderStyle.THIN);
        header.setBorderLeft(BorderStyle.THIN);
        header.setBorderBottom(BorderStyle.THIN);
        header.setBorderRight(BorderStyle.THIN);
        header.setAlignment(HorizontalAlignment.CENTER);
        header.setVerticalAlignment(VerticalAlignment.CENTER);
        header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        header.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        return header;
    }
    private static CellStyle getBodyStyle(Workbook workbook) {
        CellStyle body = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("宋体");
        body.setFont(font);
        body.setWrapText(Boolean.TRUE);
        body.setBorderTop(BorderStyle.THIN);
        body.setBorderLeft(BorderStyle.THIN);
        body.setBorderBottom(BorderStyle.THIN);
        body.setBorderRight(BorderStyle.THIN);
        body.setAlignment(HorizontalAlignment.LEFT);
        body.setVerticalAlignment(VerticalAlignment.CENTER);
        body.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        body.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        return body;
    }

    /**
     * <p>@Description: object就是每一行的数据</p >
     * <p>@param [rowData, exportFieldArr]</p >
     * <p>@return java.lang.String[]</p >
     * <p>@throws </p >
     */
    public static String[] getValues(Object rowData, String[] exportFieldArr) {
        String[] values = new String[exportFieldArr.length];
        try {
            for (int i = 0; i < exportFieldArr.length; i++) {

                Field field = null;

                try {
                    field = rowData.getClass().getDeclaredField(exportFieldArr[i]);
                } catch (Exception e) {
                    field = rowData.getClass().getField(exportFieldArr[i]);
                }
                // 设置访问权限为true
                field.setAccessible(true);
                values[i] = setCellValue(field, rowData);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return values;
    }

    public static String setCellValue(Field field, Object data) {
        Class<?> fieldType = field.getType();
        String result = "";
        try {
            Method method = data.getClass().getMethod(getMethodNameByCamel("get", field.getName()));
            Object fieldValue = method.invoke(data);
            if (fieldType == String.class) {
                result = (method.invoke(data) == null ? null : method.invoke(data).toString());
            } else if (fieldType == Short.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Integer.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Long.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Float.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Double.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == BigDecimal.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Boolean.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == LocalDate.class) {
                String pattern = "yyyy-MM-dd";
                LocalDate date = method.invoke(data) == null ? null : (LocalDate) method.invoke(data);
                if (date != null) {
                    result = (date.format(DateTimeFormatter.ofPattern(pattern)));
                }
            } else if (fieldType == LocalDateTime.class) {
                String pattern = "yyyy-MM-dd HH:mm:ss";
                LocalDateTime date = method.invoke(data) == null ? null : (LocalDateTime) method.invoke(data);
                if (date != null) {
                    result = (date.format(DateTimeFormatter.ofPattern(pattern)));
                }
            } else {
                result = (method.invoke(data) == null ? null : method.invoke(data).toString());
            }
            return result;
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return result;
    }

    // 将数字转换为 保留两位小数的字符串
    public static String returnStringFromNumber(Object data) {
        if (data == null || StrUtil.isBlank(data.toString())) {
            return "";
        }
        Double aDouble = Double.valueOf(data.toString());
        DecimalFormat decimalFormat = new DecimalFormat("0.00");
        String result = decimalFormat.format(aDouble);
        return result;
    }


    /**
     * <p>@Description: 拼接前缀以及方法名，驼峰形式</p >
     * <p>@param [prefix, fieldName]</p >
     * <p>@return java.lang.String</p >
     * <p>@throws </p >
     */
    private static String getMethodNameByCamel(String prefix, String fieldName) {
        StringBuilder builder = new StringBuilder()
                .append(prefix)
                .append(fieldName.substring(0, 1).toUpperCase())
                .append(fieldName.substring(1));
        return builder.toString();
    }
}

2.2.4 辅助工具类

package com.lzq.learn.test.动态选择列导出excel.byExportBean;

import cn.hutool.core.collection.CollUtil;
import com.lzq.learn.anno.DynamicExport;
import com.lzq.learn.anno.ExcelFiled;

import java.lang.reflect.Field;
import java.util.*;

/**
 * <p>@Description: </p >
 */
public class ExportExcelSortUtil {

    public static final String TITLE_KEY = "exportTitleArr";
    public static final String FIELD_KEY = "exportFieldArr";

    public static Map<String, Object> filterAndSort(Object exportBean) {
        Map<String, Object> result = new LinkedHashMap<>();
        List<ExcelFiled> excelFiledList = new ArrayList<>();
        boolean allNull = true;
        try {
            Optional.ofNullable(exportBean).orElseThrow(() -> new RuntimeException("传入参数为空"));
            Field[] fields = exportBean.getClass().getDeclaredFields();
            for (Field field : fields) {
                ExcelFiled excelFiled = new ExcelFiled();
                field.setAccessible(true);
                // 对应 class 对象，如果有@DynamicExport注解，并且该字段前端确定要导出才会放入到 excelFiledList中，以供后续POI导出使用
                if (field.isAnnotationPresent(DynamicExport.class)) {
                    String fieldValueFromFront = (String) field.get(exportBean);
                    if (null == fieldValueFromFront) {
                        continue;
                    }
                    allNull = false;
                    DynamicExport dynamicExport = field.getAnnotation(DynamicExport.class);
                    excelFiled.setFieldName(field.getName());
                    excelFiled.setFieldValue(fieldValueFromFront);
                    excelFiled.setSort(dynamicExport.sort());
                    excelFiledList.add(excelFiled);
                }
            }
            if (allNull) {
                throw new RuntimeException("您所选导出列为空");
            }
            // 将上方过滤出来的 excelFiledList ，封装为 fieldName 和 中文表头 的格式
            if (CollUtil.isNotEmpty(excelFiledList)) {
                excelFiledList.sort(Comparator.comparing(ExcelFiled::getSort));
                for (int i = 0; i < excelFiledList.size(); i++) {
                    result.put(excelFiledList.get(i).getFieldName(), excelFiledList.get(i).getFieldValue());
                }
            }
            return result;
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * <p>@Description: 将bean需要导出的fileName和title的map集合，转换为 对应的fileName数组和title数组, 例如：key= name,value = 废物名称</p >
     * <p>@param [beanExportFieldMap bean需要导出的fileName和title的map集合]</p >
     * <p>@return java.util.Map<java.lang.String,java.lang.String[]></p >
     * <p>@throws </p >
     * <p>@author LiuZhiQiang</p >
     * <p>@date 15:34 15:34</p >
     */
    public static Map<String, String[]> getExportFieldAndExportTitle(Map<String, Object> beanExportFieldMap) {
        if (CollUtil.isEmpty(beanExportFieldMap)) {
            return null;
        }
        StringBuffer exportFieldStr = new StringBuffer();
        StringBuffer exportTitleStr = new StringBuffer();
        if (CollUtil.isNotEmpty(beanExportFieldMap)) {
            for (Map.Entry<String, Object> map : beanExportFieldMap.entrySet()) {
                if (null != map.getValue()) {
                    exportFieldStr.append(map.getKey());
                    exportFieldStr.append(",");
                    exportTitleStr.append(map.getValue());
                    exportTitleStr.append(",");
                }
            }
        }
        String[] exportTitleArr = exportTitleStr.toString().split(",");
        String[] exportFieldArr = exportFieldStr.toString().split(",");
        LinkedHashMap<String, String[]> resultMap = new LinkedHashMap<>();
        resultMap.put(TITLE_KEY, exportTitleArr);
        resultMap.put(FIELD_KEY, exportFieldArr);
        return resultMap;
    }

    public static String[] getExportTitleArr(Map<String, String[]> map) {
        return map.get(TITLE_KEY);
    }

    public static String[] getExportFieldArr(Map<String, String[]> map) {
        return map.get(FIELD_KEY);
    }

}

2.3 业务调用层

2.3.1 controller

import com.lzq.learn.domain.CommonResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @author 吴巴哥
 */
@RestController
@RequestMapping("/dynamic")
public class TestDynamicExcelController {


    @Autowired
    private DynamicExportService dynamicExportService;

    // 方式一：通过注解的形式，动态导出excel
    @PostMapping("/exportByFields")
    public void exportByFields(@RequestBody DynamicExportRequest  dynamicExportRequest) {
        dynamicExportService.exportByFields(dynamicExportRequest , "1.xlsx");
    }


    //("返回给前端的导出Excel的动态列")
    @GetMapping("/getDynamicFields")
    public CommonResult getDynamicFields() {
        List<DynamicField> dynamicFieldList = dynamicExportService.getDynamicFields();
        return CommonResult.success(dynamicFieldList, "获取成功");
    }

}

2.3.2 service代码

import com.lzq.learn.test.动态选择列导出excel.byExportBean.ExportExcel;
import com.lzq.learn.test.动态选择列导出excel.byExportBean.GoodsExportBean;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

@Service
@Slf4j
public class DynamicExportService {



    // 模拟从 mysql 获取数据
    public List getDataList() {
        ArrayList<Goods> goodsList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            BigDecimal quantity = BigDecimal.TEN.add(BigDecimal.valueOf(i));
            Goods goods = new Goods(i, "商品"+i, BigDecimal.valueOf(i), "类型"+i, quantity, LocalDateTime.now());
            goodsList.add(goods);
        }
        return goodsList;
    }



    public void exportStaticsByReflect(DynamicExportRequest dynamicExportRequest, String fileName) {

    }

    public void exportByFields(DynamicExportRequest dynamicExportRequest, String fileName) {
        List<Goods> dataList = this.getDataList();
        GoodsExportBean goodsExportBean = dynamicExportRequest.getGoodsExportBean();
        ExportExcel.exportByTitleAndData(goodsExportBean, dataList, fileName);
    }

    public List<DynamicField> getDynamicFields() {
        List<DynamicField> dynamicFieldList = DynamicFieldUtil.exportPageListDynamicFieldList(GoodsExportBean.class);
        return dynamicFieldList;
    }
}

三 、postman接口调用示例

3.1 获取可选择导出字段列

接口地址： localhost:8089/dynamic/getDynamicFields
接口返回值如下

{
	"code": 200,
	"message": "获取成功",
	"data": [
		{
			"englishFiledName": "id",
			"chineseTitleName": "id"
		},
		{
			"englishFiledName": "name",
			"chineseTitleName": "姓名"
		},
		{
			"englishFiledName": "price",
			"chineseTitleName": "价格"
		},
		{
			"englishFiledName": "type",
			"chineseTitleName": "类型"
		},
		{
			"englishFiledName": "quantity",
			"chineseTitleName": "库存"
		},
		{
			"englishFiledName": "createTime",
			"chineseTitleName": "创建时间"
		}
	]
}

3.2 选择字段后导出excel

接口地址： localhost:8089/dynamic/exportByFields
post请求，传参格式如下

{
    "goodsExportBean":{
        //"id":"主键",
        "name":"商品名称",
        "price":"价格",
        //"type":"类型",
        "quantity":"库存哦"
        //"createTime":"上架时间"
    }
}

最后，文本哪里写的不周到之处，望各位大佬积极提出意见，本人会虚心接受并及时更正！