EasyExcel 动态表头生成且带合并

EasyExcel

原来写EXCEL导出用的是POI，如果遇到复杂些的EXCEL导出，各路合并计算单元格超级累无比啊！！万一数学幼儿园毕业的，经常数错！年前给组内同学推荐了阿里的EasyExcel，在CSDN上看到的推荐贴，据小组内同学反馈相当轻便好用。
所以这次项目里我也来小尝试一下。。。。。

下载源码及demo

先去github上下了源代码及demo，地址为:https://github.com/alibaba/easyexcel

打开官网参考

https://www.yuque.com/easyexcel/doc/write#cac25459
看了一遍后，确实轻巧噢

应用中需要导出的格式

其中表头是动态生成的，灵活可变，官网中给出的例子head可以是List<List> list，也可以是一个class，研究了一会，打算自己先搞个demo试试看，以下直接上代码了

我的demo尝试

先上一下我这边几个小时的成果，最近脑袋不太灵光，学东西越来越慢~~

springboot 项目pom.xml引入依赖

网上好多例子用的是 1.1.2-beta5版本的，官网给出的最新版本是2.2.0注意使用上有些类已过期。

   <!--Excel 导出 EasyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta1</version>
        </dependency>

来个例子官网的demo先跑跑

  @Test
    public void dynamicHeadWrite() {
        String fileName = TestFileUtil.getPath() + "dynamicHeadWrite" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName)
            // 这里放入动态头
            .head(head()).sheet("模板")
            // 当然这里数据也可以用 List<List<String>> 去传入
            .doWrite(data());
    }
     private List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("字符串" + System.currentTimeMillis());
        List<String> head1 = new ArrayList<String>();
        head1.add("数字" + System.currentTimeMillis());
        List<String> head2 = new ArrayList<String>();
        head2.add("日期" + System.currentTimeMillis());
        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }
      private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

执行了下，好使~~~就不上图了，github上下了源码后，直接在test包下找WriteTest 找到需要的相应方法即可，不废话了，自己动手改一个吧

自己搞一下

package com.zhanglu.test;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EasyExcelWiter {
    public static void main(String args[]) {
        // 文件输出位置
        String fileName = "D:\\easyExcel\\test.xlsx";
        ExcelWriterBuilder builder = EasyExcel.write(fileName);
        List<Object> data = new ArrayList<>();
        List<List<String>> header = head(data);
        System.out.println(data.toString());
        List<List<Object>> list = new ArrayList<>();
        list.add(data);
        builder.head(header).sheet("模板").doWrite(list);

    }

    /**
    * 返回的数据结构如下
     * {"data":{"xxxxxxx":{"":32,"1011":42,"1010":8,"1008":52,"0006":3,"0004":86,"1004":2,"0013":7,"0002":7,"1003":4,"0003":56,"1002":18,"0011":13,"1001":12,"0012":6,"0001":66,"1009":42}},
     * "deviceTypeSet":{"switching":["1008","1004","1003","1002","1001","1011","1010"],
     * "analog":["0006","0004","0013","0002","0003","0011","0012","0001"],
     * "switchingOff":["1009"],"substation":[""]},
     * "success":true}
     *
     * @return
     */
    private static List<List<String>> head(List<Object> data) {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("机构名称");
        list.add(head0);
        data.add("张璐的矿");
        Map<String, List<String>> map = getHeader();
        Map<String, Long> dataMap = getData();
        map.forEach((k, v) -> {
            String deviceCategory = k;
            List<String> ls = v;
            ls.forEach(e -> {
                List<String> head = new ArrayList<>();
                head.add(deviceCategory);
                head.add(e);
                list.add(head);
                if (dataMap.containsKey(e)) {
                    data.add(dataMap.get(e));
                } else {
                    data.add(0);
                }
            });

        });
        List<String> headn = new ArrayList<String>();
        headn.add("合计");
        list.add(headn);
        data.add(dataMap.get("合计"));
        return list;
    }

    private static Map<String, Long> getData() {
        //{"10017904-1":
        //{"":32,"1011":42,"1010":8,"1008":52,"0006":3,"0004":86,"1004":2,"0013":7,"0002":7,"1003":4,"0003":56,"1002":18,"0011":13,"1001":12,"0012":6,"0001":66,"1009":42}},
        Map<String, Long> data = new HashMap<>();
        long atotal = 0;
        long stotal = 0;
        long subtotal = 0;
        for (int i = 0; i < 10; i++) {
            String column = "温度" + i;
            atotal += Math.round(Math.random()) + i;
            data.put(column, Math.round(Math.random()) + i);
            String scolumn = "风门" + i;
            stotal += Math.round(Math.random()) + i;
            data.put(scolumn, Math.round(Math.random()) + i);
        }
        data.put("小计1", atotal);
        data.put("小计2", stotal);
        String subColumn = "其它";
        data.put(subColumn, 55l);
        data.put("小计3", 55l);
        data.put("合计", 55 + atotal + stotal);
        return data;
    }

    private static Map<String, List<String>> getHeader() {
        Map<String, List<String>> map = new HashMap<>();
        List<String> aList = new ArrayList<>();
        List<String> sList = new ArrayList<>();
        List<String> subList = new ArrayList<>();

        for (int i = 0; i < 10; i++) {
            String column = "温度" + i;
            aList.add(column);
            String scolumn = "风门" + i;
            sList.add(scolumn);
        }
        aList.add("小计1");
        sList.add("小计2");
        String subColumn = "其它";
        subList.add(subColumn);
        subList.add("小计3");
        map.put("模拟量传感器", aList);
        map.put("开关量传感器", sList);
        return map;
    }

}

注：需要的data也是一个List<List< Object >>list，开始给了一个List< Object >,死活不出来，后来想了一下，第一层List代表是第几行，第二个list代表的是列字段。嗯，重新封装了一下，Excel导出来了，数据的正确性不管了，明天开始写代码再去调，休息了！！！！！