快捷導航

利用EasyPOI實現(xiàn)多sheet和列數(shù)的動態(tài)生成

更新時間：2025年03月08日 14:45:48 作者：程序員朱永勝

EasyPoi功能如同名字,主打的功能就是容易,讓一個沒見接觸過poi的人員就可以方便的寫出Excel導出,Excel導入等功能,本文主要來講講如何利用EasyPOI實現(xiàn)多sheet和列數(shù)的動態(tài)生成,需要的可以了解下

一、背景

公司有個報表需求是根據(jù)指定日期范圍導出指定數(shù)據(jù)，并且要根據(jù)不同邏輯生成兩個Sheet，這個日期影響的是列數(shù)而不是行數(shù)，即行的數(shù)量和列的數(shù)量都是動態(tài)變化的，根據(jù)用戶的選擇動態(tài)生成的，這個問題花了不少時間才解決的，這邊記下筆記。

二、效果圖

動態(tài)生成30個列，兩張Sheet

動態(tài)生成1個列，兩張Sheet

三、準備

我們公司使用的版本是3.2.0，我們項目沒有引入所有模塊，只用到了base和annotation

 <dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-base</artifactId>
	<version>3.2.0</version>
	<exclusions>
		<exclusion>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
		</exclusion>
		<exclusion>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
		</exclusion>
	</exclusions>
</dependency>
	<dependency>
		<groupId>cn.afterturn</groupId>
		<artifactId>easypoi-annotation</artifactId>
	<version>3.2.0</version>
</dependency>

四、詳細步驟

定義表格樣式

    /**
     * 定義表格樣式
     *
     * @param start 查詢起始日期
     * @param end   查詢結(jié)束日期
     * @return java.util.List<cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity>
     * @author huan
     * @date 2019/6/21
     * @since 2.8.2
     */
    private List<ExcelExportEntity> setExportExcelStyle(DateTime start, DateTime end) {
        //定義表格列名,該集合存放的就是表格的列明，每個對象就是表格中的一列
        List<ExcelExportEntity> modelList = new ArrayList<ExcelExportEntity>();
        //該對象就是定義列屬性的對象
        ExcelExportEntity excelentity = null;

        //定義第一個列
        excelentity = new ExcelExportEntity("企業(yè)全稱", "companyName");
        excelentity.setWidth(20);
        excelentity.setHeight(10);
        modelList.add(excelentity);

        //定義第二個列
        excelentity = new ExcelExportEntity("企業(yè)簡稱", "companyShortName");
        excelentity.setWidth(20);
        excelentity.setHeight(10);
        modelList.add(excelentity);

        //定義第三個列，這里指定了日期顯示格式
        excelentity = new ExcelExportEntity("認證日期", "openDate");
        excelentity.setWidth(20);
        excelentity.setHeight(10);
        excelentity.setFormat("yyyy-MM-dd");
        modelList.add(excelentity);

        //定義第四個列,這邊就是動態(tài)生成的,跟用用戶選擇的日期范圍,動態(tài)生成列的數(shù)量
        excelentity = new ExcelExportEntity(null, "recordDate");
        //設(shè)置一個集合,存放動態(tài)生成的列
        List<ExcelExportEntity> modelListChild = new ArrayList<ExcelExportEntity>();
        start = DateUtils.getDateZeroTime(start);
        while (start.isBefore(end)) {
            String date = start.toString("yyyy-MM-dd");
            modelListChild.add(new ExcelExportEntity(date, date, 15));
            start = start.plusDays(1);
        }
        //日期按從小到大順序排序,這里用了最簡單的冒泡排序
        for (int i = 0; i < modelListChild.size(); i++) {
            for (int j = 0; j < modelListChild.size(); j++) {
                String e1 = modelListChild.get(i).getKey().toString();
                String e2 = modelListChild.get(j).getKey().toString();
                if (e1.compareTo(e2) < 0) {
                    ExcelExportEntity x1 = modelListChild.get(i);
                    ExcelExportEntity x2 = modelListChild.get(j);
                    modelListChild.set(j, x1);
                    modelListChild.set(i, x2);
                }
            }
        }
        //將定義好的字列放到父列中
        excelentity.setList(modelListChild);
        modelList.add(excelentity);

        //定義第五個列
        excelentity = new ExcelExportEntity("應(yīng)當使用天數(shù)", "shouldUseDay");
        excelentity.setWidth(20);
        excelentity.setHeight(10);
        modelList.add(excelentity);

        //定義第六個列
        excelentity = new ExcelExportEntity("實際使用天數(shù)", "actualUseDay");
        excelentity.setWidth(20);
        excelentity.setHeight(10);
        modelList.add(excelentity);

        //定義第七個列
        excelentity = new ExcelExportEntity("使用率", "rate");
        excelentity.setWidth(20);
        excelentity.setHeight(10);
        modelList.add(excelentity);

        //定義第八個列
        excelentity = new ExcelExportEntity("推薦人", "commandMan");
        excelentity.setWidth(20);
        excelentity.setHeight(10);
        modelList.add(excelentity);

        //定義第九個列
        excelentity = new ExcelExportEntity("拓客", "tk");
        excelentity.setWidth(20);
        excelentity.setHeight(10);
        modelList.add(excelentity);

        //定義第十個列
        excelentity = new ExcelExportEntity("對接人", "connector");
        excelentity.setWidth(20);
        excelentity.setHeight(10);
        modelList.add(excelentity);
        return modelList;
    }

定義表格數(shù)據(jù)

private List<Map<String, Object>> getData(AnalyseStockQuery analyseStockQuery, boolean type) {
        //獲取數(shù)據(jù)源
        ArrayList<AnalyseStockExportDto> dtoList = listDetailDataWithNum(analyseStockQuery, type);

        List<Map<String, Object>> dataList = new ArrayList<>();

        //存儲沒一行中的日期數(shù)據(jù)
        List<Map<String, Object>> dataListChild = null;
        //存儲表格中的每一行數(shù)據(jù)
        Map<String, Object> mapParent = null;

        //數(shù)據(jù)排序
        dtoList.sort(new ExportComparator());

        //定義表格數(shù)據(jù)
        for (AnalyseStockExportDto dto : dtoList) {
            mapParent = new HashMap(7);
            
 			//這邊只要和定義表格樣式的時候 名稱一致就行 我這邊因為有三個字段不需要我這邊后臺生成，所以就沒有設(shè)置默認值了
            mapParent.put("companyName", dto.getCompanyName());
            mapParent.put("companyShortName", dto.getCompanyShortName());
            mapParent.put("openDate", dto.getOpenDate());
            mapParent.put("shouldUseDay", dto.getShouldUseDay());
            mapParent.put("actualUseDay", dto.getActualUseDay());
            mapParent.put("rate", dto.getRate());
            Map<String, Object> map = dto.getDateList();
            dataListChild = new ArrayList<>();
            dataListChild.add(map);
            mapParent.put("recordDate", dataListChild);

            dataList.add(mapParent);
        }
        return dataList;
    }

主體方法

	/**
     * 報表導出
     *
     * @param analyseStockQuery analyseStockQuery
     * @param response          response
     * @return javax.servlet.http.HttpServletResponse
     * @author huan
     * @date 2019/6/21
     * @since 2.8.2
     */
    public HttpServletResponse exportStock(AnalyseStockQuery analyseStockQuery, HttpServletResponse response) {
        try {
            //設(shè)置默認查詢?nèi)掌?
            analyseStockQuery = setDefaultQueryDate(analyseStockQuery);

            //參數(shù)校驗
            checkListDetailDataParam(analyseStockQuery);

            //日期格式化
            DateTime start = new DateTime().withDate(new LocalDate(analyseStockQuery.getQueryStartDate()));
            DateTime end = new DateTime().withDate(new LocalDate(analyseStockQuery.getQueryLastDate()));

            //定義表格樣式
            List<ExcelExportEntity> modelList = setExportExcelStyle(start, end);

            //定義表格名稱
            String fileName = URLEncoder.encode("客戶庫存使用統(tǒng)計表-" + start.toString("yyyy年MM月dd日") + "~" + end.toString("yyyy年MM月dd日"), "utf-8");

            // Sheet1樣式
            ExportParams sheet1ExportParams = new ExportParams();
            // 設(shè)置sheet得名稱
            sheet1ExportParams.setSheetName("入庫統(tǒng)計");
            // 創(chuàng)建sheet1使用得map
            Map<String, Object> sheet1ExportMap = new HashMap<>();
            // title的參數(shù)為ExportParams類型，目前僅僅在ExportParams中設(shè)置了sheetName
            sheet1ExportMap.put("title", sheet1ExportParams);
            //sheet1樣式
            sheet1ExportMap.put("entityList", modelList);
            //sheet1中要填充得數(shù)據(jù),true表示查詢?nèi)霂鞌?shù)據(jù)，false表示查詢易簽待入庫數(shù)據(jù)
            sheet1ExportMap.put("data", getData(analyseStockQuery, true));

            //Sheet2設(shè)置
            ExportParams sheet2ExportParams = new ExportParams();
            sheet2ExportParams.setSheetName("易簽待入庫統(tǒng)計");
            Map<String, Object> sheet2ExportMap = new HashMap<>();
            sheet2ExportMap.put("title", sheet2ExportParams);
            sheet2ExportMap.put("entityList", modelList);
            sheet2ExportMap.put("data", getData(analyseStockQuery, false));

            // 將sheet1、sheet2使用得map進行包裝
            List<Map<String, Object>> sheetsList = new ArrayList<>();
            sheetsList.add(sheet1ExportMap);
            sheetsList.add(sheet2ExportMap);

            // 執(zhí)行方法
            Workbook workBook = exportExcel(sheetsList, ExcelType.HSSF);

            //設(shè)置response
            response.setHeader("content-disposition", "attachment;filename=" + fileName + ".xls");

            //設(shè)置編碼格式
            response.setCharacterEncoding("GBK");

            //將表格內(nèi)容寫到輸出流中并刷新緩存
            @Cleanup ServletOutputStream out = response.getOutputStream();
            workBook.write(out);
            out.flush();
            workBook.close();
        } catch (FileNotFoundException e) {
            log.debug("FileNotFoundException：{}", e.getMessage());
        } catch (UnsupportedEncodingException e) {
            log.debug("UnsupportedEncodingException：{}", e.getMessage());
        } catch (IOException e) {
            log.debug("IOException：{}", e.getMessage());
        }
        return response;
    }

導出Excel

    /**
     * 導出Ecel
     *
     * @return org.apache.poi.ss.usermodel.Workbook
     * @author zhuyongsheng
     * @date 2019/11/6
     */
    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;
    }

自定義導出邏輯

package com.ccb.service.analyse;

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);
            //獲取所有參數(shù)后,后面的邏輯判斷就一致了
            createSheetForMap(workbook, entity, excelParams, dataSet);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
        }
    }
}

以上就是利用EasyPOI實現(xiàn)多sheet和列數(shù)的動態(tài)生成的詳細內(nèi)容，更多關(guān)于EasyPOI生成多sheet和列數(shù)的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: