使用EasyPOI實現多sheet動態(tài)列導出

更新時間：2025年03月10日 10:11:40 作者：高山不再高2

這篇文章主要為大家詳細介紹了如何使用EasyPOI根據指定時間范圍創(chuàng)建動態(tài)列,以及如何將數據組織成符合要求的格式并導出,感興趣的可以了解下

POM

<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 {
        // 這里時間是用來做動態(tài)列的
        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列（里面有固定列，也有根據時間動態(tài)算出的列）
            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動態(tài)列.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) {

        // 創(chuàng)建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("大區(qū)", "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;
    }
}

結果：