Jxls 实现动态导出功能

引言

在实际做项目的过程中，导出报表时需要根据每个人所关注的点不一样，所需导出的字段也不一样，这时后端就需要根据每个所选的字段去相应的报表，这就是本文要讲的动态导出报表。

前端页面

1. 用户在页面上选择要导出的字段，后端根据所选的字段进行导出
   
2. 将要导出的所有字段做成字典进行管理方便后端进行转换，具体思路请看后端代码
   
   

后端代码

1. 请求参数实体 OutOrderParam.java

package com.hw.admin.domain.outer.vo;

import com.alibaba.fastjson.JSONObject;
import lombok.Data;

import java.util.Date;
import java.util.List;

/**
 1. @Description
 2. @Author liqinglong
 3. @DateTime 2022-03-30 19:05
 4. @Version 1.0
 */
@Data
public class OutOrderParam {
    /** 搜索关键字 */
    private String searchValue;
    /** 出库单状态 */
    private Integer status;
    /** 创建日期 */
    private Date createDate;
    /** 扫描条码 */
    private String scanBarcode;
    /** 导出字段 */
    private JSONObject fields;
    private Integer pageSize;
    private Integer startIndex;
    private List<String> exportFields;
}

5. controller方法

 /**
     * 出库单导出
     *
     * @param response
     * @param request
     */
    @Log(title = "出库单导出", businessType = BusinessType.EXPORT)
    @GetMapping("exportOutOrderXls")
    public void downStockExcel(HttpServletResponse response,
                               HttpServletRequest request,
                               OutOrderParam param) throws IOException, NoSuchFieldException, IllegalAccessException {
        String fileFullName = airOutOrderService.exportOutOrderXls(param);
        String fileName = fileFullName.split("\\|")[1];
        FileUtils.downloadFile(fileFullName.split("\\|")[0], fileName, response, request);
    }

3. 服务层方法
   接口 IOutOrderService.java

/**
 * 出库单服务接口
 * @author liql
 * @date 2022-03-30 16:29:15
 */
public interface IOutOrderService extends IBaseService<OutOrder>  {
    /**
     * @Description 服务层导出出库单方法
     * @param param
     * @return java.lang.String
     */
String exportOutOrderXls(OutOrderParam param) throws NoSuchFieldException, IllegalAccessException;
}

实现类 OutOrderServiceImpl.java

Slf4j
@Service
public class OutOrderServiceImpl extends IBaseServiceImpl<OutOrderMapper, AirOutOrder> implements IOutOrderService {
 @Override
    public String exportOutOrderXls(OutOrderParam param) throws NoSuchFieldException, IllegalAccessException {
        log.info("开始执行导出出库单，返回参数：{}",param);
        //1、获取参数-导出数据限制条数
        SysConfig config = new SysConfig();
        config.setConfigKey("export.excel.count");
        SysConfig retConfig = configMapper.selectConfig(config);
        int exportCount = Integer.parseInt(retConfig.getConfigValue());
        //2、获取导出记录总条数
        int total = mapper.countOutTotal(param);
        //3、导出的总条数不能超过限制的总条数
        total = total > exportCount ? exportCount : total;
        //4、获取选取的字段，默认导出所有字段
        List<String> fieldsList = param.getExportFields();
        //获取字典字段列表
        List<SysDictData> outFieldList = sysDictTypeService.selectDictDataByType("out_field_list");
        JSONObject paramObject = new JSONObject();
        //表头
        List<String> headerList = new ArrayList();
        if(ObjectUtils.isEmpty(fieldsList)){
            fieldsList = new ArrayList<>();
            for (SysDictData dicData:outFieldList) {
                paramObject.put(dicData.getDictValue(),1);
                headerList.add(dicData.getDictLabel());
                fieldsList.add(dicData.getDictValue());
            }
        }else{
            for (String field: fieldsList) {
                paramObject.put(field,1);
                for (SysDictData dicData:outFieldList) {
                    if(field.equals(dicData.getDictValue())){
                        headerList.add(dicData.getDictLabel());
                        break;
                    }
                }
            }
        }
        param.setFields(paramObject);

        //5、获取数据字典转换字段
        //出库状态
        Map<String,String> statusDictMap = new HashMap();
        List<SysDictData> statusDictDatas = sysDictTypeService.selectDictDataByType("outbound_status");
        for(SysDictData dictData : statusDictDatas){
            statusDictMap.put(dictData.getDictValue(),dictData.getDictLabel());
        }
        //出库类型
        Map<String,String> outTypeMap = new HashMap();
        List<SysDictData> outTypeDatas = sysDictTypeService.selectDictDataByType("outbound_type");
        for(SysDictData dictData : outTypeDatas){
            outTypeMap.put(dictData.getDictValue(),dictData.getDictLabel());
        }

        //6、计算分页查询次数
        //每次查询条数
        int pageSize = 1000;
        int totalPage = (total / pageSize) + (total % pageSize > 0 ? 1 : 0);
        //7、循环查询数据
        //excel表实际上是一个二维表
        List<List<Object>> lastResult = new ArrayList<>();
        param.setPageSize(pageSize);
        for(int i = 0;i < totalPage;i++){
            param.setStartIndex(i * pageSize);
            List<ExportOutOrderVo> outOrderList = mapper.queryExportOutOrderList(param);
            for(ExportOutOrderVo orderVo:outOrderList){
            // 出库类型转化为中文
         orderVo.setOutTypeStr(outTypeMap.get(String.valueOf(orderVo.getOutType())));
          // 出库状态转化为中文
        orderVo.setStatusStr(statusDictMap.get(String.valueOf(orderVo.getStatus())));
                //excel中的一行数据
                List<Object> rowList = new ArrayList<>();
                for (String header:fieldsList){
                    Field field = orderVo.getClass().getDeclaredField(header);
                    field.setAccessible(true);
                    if("tabId".equals(header)){
                        //将长整型转化为字符串
                        rowList.add(String.valueOf(field.get(orderVo)));
                    }else if("outTime".equals(header)){
                       //将出库时间格式化
                        Date outTime = (Date) field.get(orderVo);
                        if(ObjectUtils.isEmpty(field.get(orderVo))){
                            rowList.add(field.get(orderVo));
                        }else{
                            rowList.add(DateUtils.formatDate(outTime,"yyyy-MM-dd HH:mm:ss"));
                        }
                    }else{
                        rowList.add(field.get(orderVo));
                    }

                }
                lastResult.add(rowList);
            }
        }

        //8、生成exel
        Map<String, Object> model = new HashMap<>();
        model.put("cols",headerList);
        model.put("orders", lastResult);
        String xlsName = "出库单_"+ DateUtils.formatDate(new Date(),"yyyyMMddHHmmss") + ".xlsx";
        String filePath = "";
        String geneXlsName = "";
        try {
            String orderFileNameTemp = "exportOutOrder.xlsx";
            try {
                filePath = new File(ResourceUtils.getURL("classpath:").getPath()).getParentFile().getParentFile().getParent();
                filePath += File.separator + "report_file" + File.separator;
            } catch (FileNotFoundException e) {
                log.error("执行导出出库单，系统异常：" + e);
                e.printStackTrace();
            }
            File exportFilePath = new File(filePath);
            if (exportFilePath.exists() == false) {
                exportFilePath.mkdirs();
            }
            geneXlsName = filePath + xlsName;
            JxlsUtils.geneExcel(orderFileNameTemp, geneXlsName, model);
        } catch (IOException e) {
            e.printStackTrace();
        }
        log.info("结束执行导出出库单，返回参数：" + geneXlsName);
        return geneXlsName + "|" + xlsName;
    }
	
}

mapper OrderInfoSpeMapper.java

public interface OrderInfoSpeMapper extends BaseMapper<OrderInfo> {

/**
     * 查询订单导出列表
     *
     * @param queryVo 订单查询信息
     * @return 订单导出列表
     */
    List<OrderExportResultVo> selectOrderListExport(OrderQueryVo queryVo);
	
}

xml OrderInfoSpeMapper.xml

  <resultMap type="com.hw.admin.domain.order.vo.OrderExportResultVo" id="OrderExportResult">

    </resultMap>

<!-- 查询订单导出列表 -->
    <select id="selectOrderListExport" parameterType="com.hw.admin.domain.order.vo.OrderQueryVo" resultMap="OrderExportResult">
        SELECT
        o.id,
        o.cust_name,
        o.order_status,
        o.order_type,
        o.create_time,
        o.oper_name,
        b.address,
        d.logistics_no,
        e.id_number,
        e.contact_phone,
        f.mat_code,
        f.goods_name,
        f.unit_name,
        sum(f.sale_volume) sale_volume,
        sum(f.sale_amount) sale_amount
        FROM
        air_order_info o
        LEFT JOIN air_out_order d ON d.tab_id = o.id
        left join air_order_receive b on b.order_id = o.id
        left join air_customer e on e.id = o.cust_id
        left join air_order_goods f on f.order_id = o.id
        <where>
            o.del_flag = 0
            and o.source_type = 0
            AND o.order_type in (0,1)
            <if test="searchValue != null and searchValue != ''">
                AND (o.order_name like concat('%', #{searchValue}, '%')
                OR o.cust_name like concat('%', #{searchValue}, '%'))
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND o.create_time >= STR_TO_DATE(#{beginTime}, '%Y-%m-%d %H:%i:%s')
            </if>
            <if test="endTime != null and endTime != ''">
                AND o.create_time &lt;= STR_TO_DATE(#{endTime}, '%Y-%m-%d %H:%i:%s')
            </if>
            <if test="bottleCode != null and bottleCode != ''">
                AND exists (select 1
                from air_out_order_detail e,air_out_good_record f
                where e.order_id = d.id
                and f.order_detail_id = e.id
                AND (f.scan_bottlecode like concat('%', #{bottleCode}, '%') OR f.scan_barcode like concat('%', #{bottleCode}, '%')))
            </if>
        </where>
        group by
        o.id,o.cust_name,
        o.order_status,o.order_type,
        o.create_time,o.oper_name,
        b.address,d.logistics_no,
        e.id_number,e.contact_phone,
        f.mat_code,f.goods_name,f.unit_name
        order by o.id desc
        <if test="indexPage != null">
            LIMIT #{indexPage}, #{sizePage}
        </if>
    </select>

vo ExportOutOrderVo.java

package com.hw.admin.domain.order.vo;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.hw.common.annotation.SensitiveEntity;
import com.hw.common.annotation.SensitiveField;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * 功能描述: 导出结果信息
 *
 * @author: liqinglong
 * @date: 2023/10/9
 * 
 */
@Data
public class OrderExportResultVo implements Serializable {
    private static final long serialVersionUID = 1L;
    /**
     * 订单id
     */
    private Long id;
    /**
     * 订单id
     */
    private String idDesc;
    /**
     * 客户名称
     */
    private String custName;
    /**
     * 订单状态: 0-审核中 1-待付款 2-待发货 3-待收货 4-已完成 5-售后 6-已关闭 7-酒业出库 8-物流出库
     */
    private Integer orderStatus;
    /**
     * 订单状态: 0-审核中 1-待付款 2-待发货 3-待收货 4-已完成 5-售后 6-已关闭 7-出库 8-物流出库
     */
    private String orderStatusDesc;
    /**
     * 订单类型 0-普通 1-换货销售
     */
    private Integer orderType;
    /**
     * 订单类型 0-普通 1-换货销售
     */
    private String orderTypeDesc;
    /**
     * 创建时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;
    /**
     * 创建时间
     */
    private String createTimeDesc;
    /**
     * 操作员姓名
     */
    private String operName;
    /**
     * 收货地址
     */
    private String address;
    /**
     * 运单号
     */
    private String logisticsNo;
    /**
     * 身份证号码
     */
    @SensitiveField
    private String idNumber;
    /**
     * 联系人电话
     */
    @SensitiveField
    private String contactPhone;
    /**
     * 物料编码
     */
    private String matCode;
    /**
     * 商品名称，多个商品名称合并，如飞天茅台等产品
     */
    private String goodsName;
    /**
     * 商品单位
     */
    private String unitName;
    /**
     * 商品销售数量
     */
    private Long saleVolume;
    /**
     * 销售金额
     */
    private BigDecimal saleAmount;
    /**
     * 扫描物流条码
     */
    private String scanBarcode;
    /**
     * 扫描物流瓶码
     */
    private String scanBottlecode;
}

excel模板

A1的注解

jx:area(lastCell="A3") //区域范围
jx:mergeCells(cols="cols.size()" lastCell="A1") //标题合并居中

A2的注解

jx:grid(lastCell="A3" headers="cols" data="orders"  areas=["A2:A2,"A3:A3"])

注意：headers 设置为代码中的key :“cols”, data 设置为代码中的key:“orders”

导出效果

测试数据，仅供参考