easy poi 生产多表excel

冷不防 2022-12-28 09:01 151阅读 0赞

### 1. 需求 ###

要求根据查询，在同一个excel sheet中生成如下并列的四个excel表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70]

### 2. 模板制作 ###

根据查询字段，制作如下模板(项目/resources/templates/kpi/TCRR\_TEMPLATE.xlsx)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70 1]

### 3 代码 ###

#### 3.1 serviceImple ####

/** * 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());
                }
            }
        }
     }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70]: /images/20221120/7a86ff395c37467c843781f11a1c99b8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70 1]: /images/20221120/9d3f431d975e4f849ce55310bab58f7e.png