easy poi 模板生成多表excel

1. 需求

要求根据查询，在同一个excel sheet中生成如下并列的四个excel表

2. 模板制作

根据查询字段，制作如下模板(项目/resources/templates/kpi/TCRR_TEMPLATE.xlsx)

3 代码

3.1 serviceImpl

    /**
     * TCRR 下载后的文件名称
     */
    public static final String TCRR_FILE = "TCRR.xlsx";
      /**
     * TCRR 下载模板名称
     */
    public static final String TCRR_PATH = "templates/kpi/TCRR_TEMPLATE.xlsx";
    /**
     * TCRR excel报表下载
     * 1. 模板包含多张表的数据，先获取多张表的list数据；
     * 2. 组合数据为poi需要的格式；
     * 3. 调用poi接口
     *
     * @param queryParam 查询参数
     * @param response   响应参数
     * @return 下载结果
     * @author: leiming5
     */
    public void downloadExcel(StTcrrQueryParam queryParam, HttpServletResponse response) {

        // 1
        List<OverallTrendVo> overallTrendVoList = getOverallTrend(queryParam);
        List<GeoSerieVo> geoSerieVoList = getGeoByWeekOrMonth(queryParam);
        List<OdmProductVo> odmProductVoList = getODMByWeekOrMonth(queryParam);
        List<PartsWarrantyVo> partsWarrantyVoList = getPartsByWeekOrMonth(queryParam);

        // 2
        JSONObject result = new JSONObject();
        result.put("OverallTCRRTrend", overallTrendVoList);
        result.put("TCRRByGeo", geoSerieVoList);
        result.put("TCRRByODM", odmProductVoList);
        result.put("PartsWarranty", partsWarrantyVoList);

        // 3
        commomExportExcel.commonDownLoad(result, TCRR_PATH, TCRR_FILE, response);
    }

3.2 commomExportExcel

package com.leinovo.qes.portal.modules.report.poi;

import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;


/**
 * Student导出excel工具类
 */
@Component
public class CommomExportExcel extends AbstractExportTemplate {

    /**
     * map格式数据下载
     *
     * @param excelData 数据
     * @param templatePath 模板存在相对路劲
     * @param downFileName 下载后的文件名称
     * @param response 相应对象
     * @param sheetNum 模板列数
     */
    public void commonDownLoad(Map<String, Object> excelData, String templatePath, String downFileName,
                               HttpServletResponse response, Integer... sheetNum) {

        commonDownLoadExcel(excelData, templatePath, downFileName, response, sheetNum);
    }

    /**
     * 列表下载
     *
     * @param rowList 数据
     * @param templatePath 模板存在相对路劲
     * @param downFileName 下载后的文件名称
     * @param response 相应对象
     */
    public <T> void commonDownLoad(List<T> rowList, String templatePath, String downFileName, HttpServletResponse response) {
        simpleDownLoadExcel(rowList, templatePath, downFileName, response);
    }

    public <T> void commonDownLoad(List<T> rowList, String templatePath, String downFileName, HttpServletResponse response,
                                   Integer... number) {
        simpleDownLoadExcel(rowList, templatePath, downFileName, response, number);
    }
}

3.3 AbstractExportTemplate

package com.leinovo.qes.portal.modules.report.poi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.leinovo.qes.portal.modules.report.utils.CloseableUtils;
import com.leinovo.qes.portal.modules.report.utils.FileUtils;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 根据模板导出excel抽象父类
 */
@Slf4j
@Getter
@Setter
@ToString
public abstract class AbstractExportTemplate {

    private static final String COMMON_ROWLIST_NAME = "rowList";

    // 导出的模板
    protected String templatePath;

    // 导出的文件名
    protected String downLoadFileName;

    // 导出到excel的数据
    protected Map<String, Object> excelData = new HashMap<>();

    private InputStream buildExcelInputStream(Map<String, Object> excelData, Integer... sheetNum) {
        Workbook workbook = null;
        ByteArrayOutputStream outputStream = null;
        ByteArrayInputStream inputStream = null;
        try {
            TemplateExportParams params = new TemplateExportParams(templatePath, sheetNum);
            workbook = ExcelExportUtil.exportExcel(params, excelData);

            // 获取excel输出流
            outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);

            // 根据输出流程获取excel的输入流
            inputStream = new ByteArrayInputStream(outputStream.toByteArray());
        } catch (IOException e) {
            log.error("导出excel出错", e);
        } finally {
            CloseableUtils.close(outputStream, workbook);
        }
        return inputStream;
    }

    /**
     * 給excel设置数据
     *
     * @param
     * @param sourceListData
     * @return
     */
    public <T> void setRowList(List<T> sourceListData) {
        this.excelData.put(COMMON_ROWLIST_NAME, sourceListData);
    }

    /**
     * 下载方式一：这种方式适用于下载列表数据
     *
     * @param sourceListData
     * @param templatePath
     * @param downFileName
     * @param response
     * @param <T>
     */
    protected <T> void simpleDownLoadExcel(List<T> sourceListData, String templatePath, String downFileName, HttpServletResponse response) {
        setRowList(sourceListData);
        this.commonDownLoadExcel(this.excelData, templatePath, downFileName, response);
    }

    protected <T> void simpleDownLoadExcel(List<T> sourceListData, String templatePath, String downFileName,
                                           HttpServletResponse response,Integer... number) {
        setRowList(sourceListData);
        this.commonDownLoadExcel(this.excelData, templatePath, downFileName, response, number);
    }
    /**
     * 下载方式二：这种方式，需要子类封装好下载的Map数据
     */
    protected void commonDownLoadExcel(Map<String, Object> excelData, String templatePath, String downFileName,
                                       HttpServletResponse response, Integer... sheetNum) {
        this.excelData = excelData;
        this.templatePath = templatePath;
        this.downLoadFileName = downFileName;

        InputStream inputStream = null;
        try {
            setExcelData();
            inputStream = buildExcelInputStream(this.excelData, sheetNum);
            FileUtils.writeToResponse(response, inputStream, downFileName);
        } finally {
            CloseableUtils.close(inputStream);
        }
    }

    /**
     * 模板方法，子类设置excel中需要展示的数据
     */
    protected void setExcelData() {
        // 设置excelData
    }

}

3.4 FileUtils

package com.leinovo.qes.portal.modules.report.utils;

import org.apache.commons.compress.utils.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;

public final class FileUtils {
    /**
     * 下载文件到浏览器
     *
     * @param response
     * @param inputStream
     * @param fileName 浏览器下载的文件名称
     */
    public static void writeToResponse(HttpServletResponse response, InputStream inputStream, String fileName) {
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            // 设置强制下载不打开
            response.setContentType("application/force-download");
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(fileName, "UTF-8"));
            IOUtils.copy(inputStream, outputStream);
        } catch (Exception e) {
            log.error("浏览器下载文件出错, fileName:{}", fileName, e);
        } finally {
            // close stream
            CloseableUtils.close(inputStream, outputStream);
        }
    }

    /**
     * 删除本地文件
     *
     * @param filePath
     */
    public static void deleteLocalFile(String filePath) {
        File file = new File(filePath);
        file.delete();
    }
}

3.5 CloseableUtils

package com.leinovo.qes.portal.modules.report.utils;


import java.io.Closeable;
import java.io.IOException;

/**
 * Created by kongkp on 16-8-26.
 */
public final class CloseableUtils {

    private CloseableUtils() {
    }

    public static void close(Closeable... closables) {
        if (closables == null || closables.length == 0) {
            return;
        }

        for (Closeable closable : closables) {
            try {
                if (closable != null) {
                    closable.close();
                }
            } catch (IOException e) {
                System.err.println("Close resource exception:" + e.getStackTrace());
            }
        }
    }
 }