使用EasyPOI实现多sheet动态列导出

<dependency>
    <groupid>cn.afterturn</groupid>
    <artifactid>easypoi-base</artifactid>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupid>cn.afterturn</groupid>
    <artifactid>easypoi-annotation</artifactid>
    <version>3.2.0</version>
</dependency>

public class demo {

    @test
    void product() throws ioexception, parseexception {
        // 这里时间是用来做动态列的
        string starttime = "2019-09-01 00:00:00";
        string endtime = "2020-03-31 23:59:59";

        // sheetname 列表
        list<string> sheetnamelist = new arraylist<>();
        sheetnamelist.add("sheet1");
        sheetnamelist.add("sheet2");
        sheetnamelist.add("sheet3");

        // 将sheet1和sheet2使用得map进行包装
        list<map<string, object>> sheetslist = new arraylist<>();

        for (string sheetname : sheetnamelist) {
            // 获取每一个sheet列（里面有固定列，也有根据时间动态算出的列）
            list<excelexportentity> excelexportentities = getexportexceldefine(starttime, endtime);
            // 获取行数据（一般数据库拿）
            list<customermonthproductnum> rowsbean = new arraylist<>();
            getoridate(rowsbean);

            // 组装一个sheet出来（sheet名称，列名，数据）
            map<string, object> onesheet = this.getonesheet(sheetname, excelexportentities, rowsbean);
            // 加入到sheet列表里面
            sheetslist.add(onesheet);
        }
        // 导出多个sheet
        workbook workbook = exportexcel(sheetslist);
        fileoutputstream fos = new fileoutputstream("d:/多sheet动态列.xls");
        workbook.write(fos);
        fos.close();

    }

    /**
     * 获取原始数据（真实情况可能从数据库里面拿）
     *
     * @param rowsbean
     */
    void getoridate(list<customermonthproductnum> rowsbean) {
        customermonthproductnum productnumbean = new customermonthproductnum();
        productnumbean.setcustomername("张三");
        productnumbean.setauthcode("121-121");
        productnumbean.setregion("q");
        productnumbean.setcustomerid(212312);
        productnumbean.setsum(2323);
        treemap<string, integer> productnum = new treemap<>();
        productnum.put("2020-01", 1);
        productnum.put("2020-02", 12);
        productnum.put("2019-09", 19);
        productnumbean.setproductnum(productnum);
        rowsbean.add(productnumbean);
        rowsbean.add(productnumbean);


        customermonthproductnum productnumbean1 = new customermonthproductnum();
        productnumbean1.setcustomername("张三");
        productnumbean1.setauthcode("121-121");
        productnumbean1.setregion("q");
        productnumbean1.setcustomerid(212312);
        productnumbean1.setsum(2323);
        treemap<string, integer> productnum1 = new treemap<>();
        productnum1.put("2020-01", 1);
        productnum1.put("2020-02", 12);
        productnum1.put("2019-09", 19);
        productnumbean1.setproductnum(productnum);
        rowsbean.add(productnumbean1);
        rowsbean.add(productnumbean1);

    }


    /**
     * 导出ecel
     *
     * @return org.apache.poi.ss.usermodel.workbook
     */
    private static workbook exportexcel(list<map<string, object>> list) {
        workbook workbook = new hssfworkbook();
        for (map<string, object> map : list) {
            myexcelexportservice service = new myexcelexportservice();
            service.createsheetwithlist(workbook, (exportparams) map.get("title"), exportparams.class, (list<excelexportentity>) map.get("entitylist"), (collection<?>) map.get("data"));
        }
        return workbook;
    }


    private map<string, object> getonesheet(string sheetname, list<excelexportentity> collist, list<customermonthproductnum> datalist) {

        // 创建sheet1使用得map
        map<string, object> sheetexportmap = new hashmap<>();
        // sheet1样式
        exportparams sheet1exportparams = new exportparams();
        // 设置sheet得名称
        sheet1exportparams.setsheetname(sheetname);

        // title的参数为exportparams类型，目前仅仅在exportparams中设置了sheetname
        sheetexportmap.put("title", sheet1exportparams);
        //sheet1样式
        sheetexportmap.put("entitylist", collist);

        //sheet1中要填充得数据
        list<map<string, string>> rows = new arraylist<>();
        for (customermonthproductnum data : datalist) {
            rows.add(this.getrowdata(data));
        }

        sheetexportmap.put("data", rows);

        return sheetexportmap;
    }


    /**
     * 获取列定义
     *
     * @param start 开始时间
     * @param end   结束时间
     */
    private list<excelexportentity> getexportexceldefine(string start, string end) throws parseexception {
//        string start = "2019-09-01 00:00:00";
//        string end = "2020-03-14 00:00:12";
        list<string> monthbetweendates = dateutil.getmonthbetweendates(start, end);
        //定义表格列名,该集合存放的就是表格的列明，每个对象就是表格中的一列
        list<excelexportentity> modellist = new arraylist<>();
        //该对象就是定义列属性的对象
        excelexportentity excelentity;

        //定义第一个列
        excelentity = new excelexportentity("授权名", "agentname");
        excelentity.setwidth(20);
        excelentity.setheight(10);
        modellist.add(excelentity);

        //定义第一个列
        excelentity = new excelexportentity("大区", "region");
        excelentity.setwidth(20);
        excelentity.setheight(10);
        modellist.add(excelentity);

        //定义第一个列
        excelentity = new excelexportentity("授权码", "auth");
        excelentity.setwidth(20);
        excelentity.setheight(10);
        modellist.add(excelentity);
        for (string monthbetweendate : monthbetweendates) {
            //定义第一个列
            excelentity = new excelexportentity(monthbetweendate, monthbetweendate);
            excelentity.setwidth(10);
            excelentity.setheight(10);
            modellist.add(excelentity);
        }

        //定义列
        excelentity = new excelexportentity("合计", "sum");
        excelentity.setwidth(20);
        excelentity.setheight(10);
        modellist.add(excelentity);
        return modellist;

    }


    /**
     * 将对象数据转换为导出需要的map数据结构
     * <pre>
     *     map的可以对应了列定义里面的key。eg： excelexportentity("授权名", "agentname");
     * </pre>
     *
     * @param productnum bean
     */
    private map<string, string> getrowdata(customermonthproductnum productnum) {
        map<string, string> data = new hashmap<>();
        data.put("agentname", productnum.getcustomername());
        data.put("auth", productnum.getauthcode());
        data.put("region", productnum.getregion());
        data.put("sum", productnum.getsum().tostring());
        treemap<string, integer> productnum1 = productnum.getproductnum();
        for (map.entry<string, integer> stringintegerentry : productnum1.entryset()) {
            data.put(stringintegerentry.getkey(), stringintegerentry.getvalue().tostring());
        }
        return data;
    }


}

import cn.afterturn.easypoi.excel.annotation.exceltarget;
import cn.afterturn.easypoi.excel.entity.exportparams;
import cn.afterturn.easypoi.excel.entity.enmus.exceltype;
import cn.afterturn.easypoi.excel.entity.params.excelexportentity;
import cn.afterturn.easypoi.excel.export.excelexportservice;
import cn.afterturn.easypoi.exception.excel.excelexportexception;
import cn.afterturn.easypoi.exception.excel.enums.excelexportenum;
import cn.afterturn.easypoi.util.poipublicutil;
import lombok.extern.slf4j.slf4j;
import org.apache.poi.ss.usermodel.workbook;

import java.lang.reflect.field;
import java.util.arraylist;
import java.util.collection;
import java.util.list;
/**
 * 自定义下导出逻辑
 * @author huan
 * @version 2.8.2
 * @date  2019/7/5
 */
@slf4j
public class myexcelexportservice extends excelexportservice {
    public void createsheetwithlist(workbook workbook, exportparams entity, class<?> pojoclass, list<excelexportentity> entitylist, collection<?> dataset) {
        if (logger.isdebugenabled()) {
            logger.debug("excel export start ,class is {}", pojoclass);
            logger.debug("excel version is {}",
                    entity.gettype().equals(exceltype.hssf) ? "03" : "07");
        }
        if (workbook == null || entity == null || pojoclass == null || dataset == null) {
            throw new excelexportexception(excelexportenum.parameter_error);
        }
        try {
            list<excelexportentity> excelparams = entitylist;
            // 得到所有字段
            field[] fileds = poipublicutil.getclassfields(pojoclass);
            exceltarget etarget = pojoclass.getannotation(exceltarget.class);
            string targetid = etarget == null ? null : etarget.value();
            getallexcelfield(entity.getexclusions(), targetid, fileds, excelparams, pojoclass,
                    null, null);
            //获取所有参数后,后面的逻辑判断就一致了
            createsheetformap(workbook, entity, excelparams, dataset);
        } catch (exception e) {
            logger.error(e.getmessage(), e);
            throw new excelexportexception(excelexportenum.export_error, e.getcause());
        }
    }
}

public class dateutil {
    /**
     * 获取某个时间段内所有月份
     *
     * @param mindate
     * @param maxdate
     * @return
     */
    public static list<string> getmonthbetweendates(string mindate, string maxdate) throws parseexception {
        date d1 = new simpledateformat("yyyy-mm-dd hh:mm:ss").parse(mindate);//定义起始日期
        date d2 = new simpledateformat("yyyy-mm-dd hh:mm:ss").parse(maxdate);//定义结束日期  可以去当前月也可以手动写日期。
        arraylist<string> result = new arraylist<string>();
        simpledateformat sdf = new simpledateformat("yyyy-mm");//格式化为年月
        calendar min = calendar.getinstance();
        calendar max = calendar.getinstance();
        min.settime(d1);
//        min.set(min.get(calendar.year), min.get(calendar.month), 1);
        max.settime(d2);
//        max.set(max.get(calendar.year), max.get(calendar.month), 2);
        calendar curr = min;
        while (curr.before(max)) {
            result.add(sdf.format(curr.gettime()));
            curr.add(calendar.month, 1);
        }
        return result;
    }
}