java POI 动态选择导出字段列

超、凢脫俗 2023-10-13 14:52 45阅读 0赞

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

#### 目录 ####

*  一、效果图展示
 *   *  1.1postman 模拟前端传参
     *  1.2导出的 excel 效果图
 *  二、代码展示
 *   *  2.1.实体类
     *   *  2.1.1用于接收前端的dto
         *  2.1.2 mysql 实体类
         *  2.1.3 用于接收前端哪些需要导出字段的dto
         *  2.1.4 给前端可选择字段实体
     *  2.2 工具类以及注解
     *   *  2.2.1 自定义注解
         *  2.2.2 动态字段列工具类
         *  2.2.3 核心POI工具类
     *  2.3 业务调用层
     *   *  2.3.1 controller
         *  2.3.2 service代码
 *  三 、postman接口调用示例
 *   *  3.1 获取可选择导出字段列
     *  3.2 选择字段后导出excel

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

## 一、效果图展示 ##

### 1.1postman 模拟前端传参 ###

下面的传了字段值的代表需要进行导出的列  
![在这里插入图片描述][6cb4ddbd87d744be9e08a87faa714ecf.png_pic_center]

### 1.2导出的 excel 效果图 ###

![在这里插入图片描述][1eb65c73b82945a0bf6b1c4f719ad4cb.png_pic_center]

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

## 二、代码展示 ##

### 2.1.实体类 ###

#### 2.1.1用于接收前端的dto ####

/**
 * @Description: 用于接收前端传参的dto
 * @param 
 */
 @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;
 
 /**
 * @Description: 实体类
 * @date 18:58 18:58
 * @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;
 
 /**
 * @Description: 用于接收前端哪些需要导出字段的dto
 * @date 9:28 9:28
 */
 @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;
 
 /**
 * @Description: 用于返回给前端的可选择导出列 
 * @date 14:23 14:23
 */
 @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.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;
 
 /**
 * @Description: 动态导出字段, 将可选字段加上该注解
 * @date 10:26 10:26
 */
 @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;
 
 /**
 * @Description: 可选择动态字段列工具类
 * @date 14:26 14:26
 */
 public class DynamicFieldUtil {
 
 
 /**
 * @Description: 带有 @DynamicExport 注解的实体类，利用反射返回可选择动态列
 * @param [object]
 * @return java.util.List<com.hjza.environment.response.export.DynamicField>
 * @throws 
 */
 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;
 
 /**
 * @Description: 将 Apache POI 的一些常用api进行封装
 */
 public class ExportExcel {
 
 
 
 
 /**
 * @Description: 根据 所选动态列，以及数据集进行导出excel
 * @param [exportBeanFrom, dataList, fileName]
 * @return void
 * @throws 
 */
 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);
 }
 }
 
 /**
 * @Description: 将文件流写会回 response 中
 * @param [workbook, fileName]
 * @return void
 * @throws 
 */
 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());
 }
 }
 
 
 /**
 * @Description: 根据 数据集、导出列、表头汉字 创建工作簿
 * @param [dataSet 数据集, exportFieldArr 需要导出的字段列, titles 导出列对应的中文表头]
 * @return org.apache.poi.xssf.usermodel.XSSFWorkbook
 * @date 15:19 15:19
 */
 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;
 }
 
 /**
 * @Description: object就是每一行的数据
 * @param [rowData, exportFieldArr]
 * @return java.lang.String[]
 * @throws 
 */
 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;
 }
 
 
 /**
 * @Description: 拼接前缀以及方法名，驼峰形式
 * @param [prefix, fieldName]
 * @return java.lang.String
 * @throws 
 */
 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.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":"上架时间"
        }
    }

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

[6cb4ddbd87d744be9e08a87faa714ecf.png_pic_center]: https://img-blog.csdnimg.cn/6cb4ddbd87d744be9e08a87faa714ecf.png#pic_center
[1eb65c73b82945a0bf6b1c4f719ad4cb.png_pic_center]: https://img-blog.csdnimg.cn/1eb65c73b82945a0bf6b1c4f719ad4cb.png#pic_center