Excel自定义导出字段,单表 多表

Excel自定义导出字段

记录一下，试了半天才搞出来的。支持 excel 的 多个表格、单个表格、多个表头 查询数据库多张表， 这些都可以

需要的Jar包

 <!-- Hutool工具包 -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.15</version>
        </dependency>
<!--这个我也不确定要不要导入，我测试的别的导出用到的，先放上来，包不报错就不导入-->
  		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.1.0</version>
        </dependency>

我用的是mybatis-plus得查询方式，查询的是三张表，查到有数据就放到List里的Map里

/**
     * 数据导出
     *
     * @param response
     * @param particularsVo
     * @throws IllegalAccessException
     */
    @RequestMapping(value = "/exportList", method = RequestMethod.POST)
    @ApiOperation(value = "自定义字段Excel导出")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "names", value = "需要导出的字段", required = true, dataType = "string", paramType = "query"),
            @ApiImplicitParam(name = "Ids", value = "需要导出的数据Id", required = true, dataType = "string", paramType = "query")
    })
    public Result<String> exportList(HttpServletResponse response, @RequestBody ParticularsVo particularsVo) throws IllegalAccessException {
        List<String> names = particularsVo.getNames();
        List<String> ids = particularsVo.getIds();

        //A表
        LambdaQueryWrapper<ReportCustomer> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.in(ReportCustomer::getId, ids);
        List<ReportCustomer> customers = customerService.list(queryWrapper);
        if (null == customers) {
            return new ResultUtil<String>().setErrorMsg("无数据，检查Id是否正确");
        }


        // 封装 hutool 导出需要的 数据格式。String：表头;Object：数据
            //A信息
            List<Map<String, Object>> customerMaps = new LinkedList<>();
            //B信息
            List<Map<String, Object>> guarantorMaps = new LinkedList<>();
            //C信息
            List<Map<String, Object>> repayerMaps = new LinkedList<>();

            for (ReportCustomer customer : customers) {
                String reportId = customer.getReportId();

                //AMap
                Map<String, Object> customerMap = EntityTo.entityToMap(customer, names);
                customerMaps.add(customerMap);

                //BMap
                LambdaQueryWrapper<ReportGuarantor> guarantorWrapper = new LambdaQueryWrapper<>();
            guarantorWrapper.eq(ReportGuarantor::getReportId, reportId);
            ReportGuarantor guarantor = guarantorService.getOne(guarantorWrapper);
            if (null != guarantor) {
                Map<String, Object> guarantorMap = EntityTo.entityToMap(guarantor, names);
                guarantorMaps.add(guarantorMap);
            }

            //CMap
            LambdaQueryWrapper<ReportRepayer> repayerWrapper = new LambdaQueryWrapper<>();
            repayerWrapper.eq(ReportRepayer::getReportId, reportId);
            ReportRepayer repayer = repayerService.getOne(repayerWrapper);
            if (null != repayer) {
                Map<String, Object> repayerMap = EntityTo.entityToMap(repayer, names);
                repayerMaps.add(repayerMap);
            }

        }


        HuToolExcelUtil.universalExcelExport(response, customerMaps,guarantorMaps,repayerMaps);

        return null;

    }

接收的vo，names是自定义字段的名称，ids是自定义要导出的数据id（比如说一个列表里，勾选了你要导出的一条条数据，再选你要导出的字段名称，懂了吧），这里可以换成你自己要接收的数据

package XXX.XXX.vo;

import lombok.Data;

import java.util.List;

@Data
public class ParticularsVo {

    private List<String> names;

    private List<String> ids;

}

我的实体类例子：

  @ApiModelProperty(value = "姓名")
    private String name;

    @ApiModelProperty(value = "身份证号")
    private String idcard;

    @ApiModelProperty(value = "年龄")
    private String age;

    @ApiModelProperty(value = "性别")
    private String sex;

    @ApiModelProperty(value = "民族")
    private String nationality;

必须有一个可以写名称的注解，因为要取出名称取比较，不想用我这个注解的可以自定义一个，但我考虑单你都找到这了肯定不想写自定义注解，我就放出来了

用了自定义注解，你就在实体类上@HeaderName（“名称”），懂了吧

import java.lang.annotation.*;

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface HeaderName {
    String value() default "";
}

用到的比较的工具类，这里的思路是从数据库查出来的数据给到实体类，然后获取你实体类里的注解名称，然后去跟前端传给你的字段名去比较，比较一致，说明这个数据是你要导出的，不一致说明这个字段你不导出

package xxx.xxx.utli;

import io.swagger.annotations.ApiModelProperty;

import java.lang.reflect.Field;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class EntityTo {

    /**
     * 数据封装
     *
     * @param album
     * @param names
     * @return
     * @throws IllegalAccessException
     */
    public static Map<String, Object> entityToMap(Object album, List<String> names) throws IllegalAccessException {
        Map<String, Object> resMap = new LinkedHashMap<>();

        Class<?> albumClass = album.getClass();
        // 获取 实体类 中所有的字段
        Field[] declaredFields = albumClass.getDeclaredFields();
        for (Field declaredField : declaredFields) {
            // 这里设置为 true 才可以访问到 实体类中的 private 字段
            declaredField.setAccessible(true);
            // 获取字段所对应的注解
            ApiModelProperty annotation = declaredField.getAnnotation(ApiModelProperty.class);
            if (null != annotation) {
                // 有注解 则 获取 注解的值（表头名称）
                String value = annotation.value();
                // 再判断 实体类中是否有这个表头
                if (names.contains(value)) {
                    resMap.put(value, declaredField.get(album));
                }
            }
        }
        return resMap;
    }
}

导出的工具类：

如果你只是，单张表操作，只传一个List<Map<String, Object>> 就行，多张表，就跟我一样

package xxx.xxx.utli;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import io.netty.util.internal.StringUtil;
import org.apache.commons.lang3.time.DateFormatUtils;

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

public class HuToolExcelUtil {
    /**
     * 封装 通用导出方法
     *
     * @param response
     * @param
     */
    public static void universalExcelExport(HttpServletResponse response,
                                            List<Map<String, Object>> customerMaps,
                                            List<Map<String, Object>> guarantorMaps,
                                            List<Map<String, Object>> repayerMaps,
                                           ) {
        BigExcelWriter writer = (BigExcelWriter) ExcelUtil.getBigWriter();
        ServletOutputStream out = null;
        try {
            if (customerMaps.size() > 0) {
            	//自定义表头
                writer.merge(customerMaps.size(), "A信息");
                //为rtue说明你要把map里的key当作表头，比如姓名，年龄这些，false反之
                writer.write(customerMaps, true).passCurrentRow();
            }
            if (guarantorMaps.size() > 0) {
                writer.merge(guarantorMaps.size(), "B信息");
                writer.write(guarantorMaps, true).passCurrentRow();
            }
            if (repayerMaps.size() > 0) {
                writer.merge(repayerMaps.size(), "C信息");
                writer.write(repayerMaps, true).passCurrentRow();
            }
            /* writer.write(dataList, true);*/
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            //设置文件标题
            String dateTime = DateFormatUtils.format(new Date(), "yyyyMMddHHmm");
            String fileName = "详细信息" + dateTime + ".xls";
            response.setHeader("content-disposition", "attachment;filename="
                    + URLEncoder.encode(fileName, "UTF-8"));
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            writer.close();
            IoUtil.close(writer);
        }
    }
}

导出效果图：

传的Json：

{
    "names": [
        "教育",
        "住宅状况",
        "姓名",
        "关系"
    ],
    "ids": [
        "1",
        "2"
    ]
}