导出excel按动态表头导出

最新推荐文章于 2024-05-03 09:27:23 发布

孰能生巧-LWP

最新推荐文章于 2024-05-03 09:27:23 发布

阅读量348

点赞数 7

文章标签： excel windows linux

本文链接：https://blog.csdn.net/qq_33816292/article/details/136902782

版权

一、实现代码使用Map方式添加head头，对应填充值

  /**
     * 导出查看发车明细
     * @param query
     * @return
     */
    @Override
    public ExportResult monthResourcePlanDepartureProgressDetailsExportExcelData(ResourceSituationListQuery query) {
        log.info("导出参数:{}",DstJsonUtil.toString(query));
        //List<ResourcePlanShipDetailVO> shipDetail = resourcePlanService.getShipDetail(query);
        List<ResourcePlanShipDetailVO> shipDetail = resourceSituationCoreService.getCodData();
        if (CollectionUtils.isEmpty(shipDetail)) {
            log.error("导出数据为空");
            return null;
        }
        List<String> dispatchDate = new ArrayList<>();
        shipDetail.stream().forEach(e->{
            e.getShipDetails().stream().forEach(it->{
                dispatchDate.add(DstDateUtil.format(it.getDispatchDate(),DstDateUtil.Y_M_D));
            });
        });
        List<String> newDispatchDate = Lists.newArrayList(new HashSet(dispatchDate));
        List<String> newDispatchDateList = newDispatchDate.stream().sorted().collect(Collectors.toList());
        List<String> monthAndDays = new ArrayList<>();
        for (String date : newDispatchDateList) {
            monthAndDays.add(date);
        }
        List<String> headers = new ArrayList<String>() {
            {
                add("数据类型");
                add("资源情况编码");
                add("关联发车计划编码");
                add("大区城市");
                add("车辆品牌（属性）");
                add("车辆型号（属性）");
                add("车型族群（属性）");
                add("电池包度数（属性）");
                add("电池包厂商（属性）");
                add("车辆用途（属性）");
                add("发车总数");
                addAll(monthAndDays);
            }
        };
        List<String> columns = new ArrayList<String>() {
            {
                add("dataType");
                add("resourceSituationCode");
                add("shipCode");
                add("cityName");
                add("carBrandName");
                add("carModelName");
                add("carModelGroupName");
                add("esdTotalPowerName");
                add("esdCompanyName");
                add("vehicleUseName");
                add("needShipCountTotal");
                addAll(monthAndDays);
            }
        };
        List<Map<String, Object>> mapDatas = new ArrayList<>();
        shipDetail.forEach(p -> {
            mapDatas.add(converItemToMap(p,1,monthAndDays));
            mapDatas.add(converItemToMap(p,2,monthAndDays));

        });
        return ExportResult.wrap(headers, columns, mapDatas, 1);
    }

二、核心转换方法

/**
     * @Description 转换map
     * @Param item type 1:计划发车 2:实际发车
     * @Return java.util.Map<java.lang.String,java.lang.Object>
     * @Date 2024/3/12 18:59
     * @Author lwp
     */
    private Map<String,Object> converItemToMap(ResourcePlanShipDetailVO item,Integer type,List<String> monthAndDays){
        Map<String,Object> temp =new HashMap<>();
        String dataType = type == 1 ? "计划发车" :"实际发车";
        Integer needShipCountTotal = type == 1 ? item.getTotalAssignCount() : item.getTotalShipCount();
        temp.put("dataType", dataType);
        temp.put("resourceSituationCode",item.getResourceSituationCodes());
        temp.put("shipCode",item.getShipCodes());
        temp.put("cityName",item.getFullCityName());
        temp.put("carBrandName",item.getCarBrandName());
        temp.put("carModelName",item.getCarModelName());
        temp.put("carModelGroupName",item.getCarModelGroupName());
        temp.put("esdTotalPowerName",item.getEsdTotalPowerName());
        temp.put("esdCompanyName",item.getEsdCompanyName());
        temp.put("vehicleUseName",item.getVehicleUseName());
        temp.put("needShipCountTotal",needShipCountTotal);
        item.setShipDetails(item.getShipDetails().stream().sorted(Comparator.comparing(AssignDetailVO::getDispatchDate))
                .collect(Collectors.toList()));
        for (String monthAndDay : monthAndDays) {
            A: for (AssignDetailVO shipDetail : item.getShipDetails()) {
                String dispatchDate = DstDateUtil.format(shipDetail.getDispatchDate(), DstDateUtil.Y_M_D);
                if (dispatchDate.equals(monthAndDay)) {
                    temp.put(monthAndDay, type == 1 ? shipDetail.getDispatchNum() : shipDetail.getShipCount());
                    break A;
                } else {
                    temp.put(monthAndDay, type == 1 ? 0 : 0);
                }
            }
        }
        temp = MapUtils.sortMapByKey(temp);
        return temp;
    }

三、导出实体类

package com.dst.steed.fulfillment.common.domain.biz.resourceplan;

import com.dst.steed.common.convert.translation.Translation;
import lombok.Data;

import java.util.List;

/**
 * 资源计划发车明细
 */
@Data
public class ResourcePlanShipDetailVO {

    /**
     * 资源情况编码
     */
    private String resourceSituationCodes;

    /**
     * 发车计划编码
     */
    private String shipCodes;

    /**
     * 城市编码
     */
    @Translation(convertName = "convertRegionAndCityName", convertTo = "fullCityName")
    private String cityCode;

    /**
     * 城市名称
     */
    private String fullCityName;

    /**
     * 车辆品牌
     */
    @Translation(convertName = "commonConvertAttrValueIdMapValue",convertTo = "attrValueName-carBrandName")
    private String carBrand;
    private String carBrandName;

    /**
     * 车辆型号
     */
    @Translation(convertName = "commonConvertAttrValueIdMapValue",convertTo = "attrValueName-carModelName")
    private String carModel;
    private String carModelName;

    /**
     * 车型族群
     */
    @Translation(convertName = "commonConvertAttrValueIdMapValue",convertTo = "attrValueName-carModelGroupName")
    private String carModelGroup;
    private String carModelGroupName;

    /**
     * 动力电池包电量
     */
    @Translation(convertName = "commonConvertAttrValueIdMapValue",convertTo = "attrValueName-esdTotalPowerName")
    private String esdTotalPower;
    private String esdTotalPowerName;

    /**
     * 动力电池包厂商
     */
    @Translation(convertName = "commonConvertAttrValueIdMapValue",convertTo = "attrValueName-esdCompanyName")
    private String esdCompany;
    private String esdCompanyName;

    /**
     * 资产分类
     */
    @Translation(convertName = "commonConvertAttrValueIdMapValue",convertTo = "attrValueName-assetClassifyName")
    private String assetClassify;
    private String assetClassifyName;

    /**
     * 车辆用途
     */
    @Translation(convertName = "commonConvertAttrValueIdMapValue",convertTo = "attrValueName-vehicleUseName")
    private String vehicleUse;
    private String vehicleUseName;

    /**
     * 发车总计-计划发车量
     */
    private Integer totalAssignCount;

    /**
     * 发车总计-已发车量
     */
    private Integer totalShipCount;

    /**
     * 发车总计-剩余量
     */
    private Integer totalResidueCount;

    /**
     * 发车明细
     */
    private List<AssignDetailVO> shipDetails;
}

实体类属性明细类

package com.dst.steed.fulfillment.common.domain.biz.resourceplan;

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.util.Date;

/**
 * 分配详情VO
 */
@Data
public class AssignDetailVO {

    /**
     * 中心分配ID
     */
    private String id;

    /**
     * 分配类型 1. 中心分配 2. 大区分配
     */
    private Integer referenceType;

    /**
     * 发车日期
     */
    @JsonFormat(pattern = "M月d日", timezone = "GMT+8")
    private Date dispatchDate;

    /**
     * 中心分配发车数量
     */
    private Integer centerDispatchNum;

    /**
     * 城市计划发车数量
     */
    private Integer dispatchNum;

    /**
     * 实际发车数量(发车明细字段)
     */
    private Integer shipCount;
}