JAVA - EasyExcel根据自定义模板导出Excel

pom ：

                          com.alibaba                easyexcel                3.1.0                                        org.apache.maven.plugins                maven-resources-plugin                                                            xls                        xlsx                                                

xlsx模板：

{name}和{num} 是单项填充数据，{data} 为集合。

业务实现：

 public void export(QueryVillageConsumerAssistanceRecordDTO request, HttpServletResponse response) {                    // 业务逻辑             Map map = new HashMap();        // 名字        map.put("name","导出名字");        // 总价        map.put("num", "合计金额");        // 集合        ArrayList list = new ArrayList();        Integer sum = 1;        // 子表记录        for (VillageConsumerAssistanceRecordVO x : recordVOList) {            HashMap hashMap = new HashMap();            // 序列            hashMap.put("1", sum);            // 商品类型            hashMap.put("2",x.getProductType() );                    hashMap.put("3", x.getProducerName());                        hashMap.put("4", x.getVillageName());                    hashMap.put("5", x.getPurchasingEnterprise());                    hashMap.put("6", x.getProductNum())            // 金额            hashMap.put("7", x.getProductAllPrice());            sum++;            list.add(hashMap);        }        try {            InputStream is = ResourceUtil.getStream("classpath:template/assistance.xlsx");            // 这里定义最终导出的文件名称            response.setContentType("application/vnd.ms-excel");            response.setCharacterEncoding("utf-8");            String fileName = name + ".xlsx";            response.setHeader("Content-Disposition",                    String.format("attachment; filename=%s",                            URLEncoder.encode(fileName, StandardCharsets.UTF_8.name())));            // 设置样式            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(null, StyleUtils.getContentStyle());            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(horizontalCellStyleStrategy)                    .withTemplate(is).excelType(ExcelTypeEnum.XLSX).build();            WriteSheet writeSheet = EasyExcel.writerSheet().build();            // 单独得map            excelWriter.fill(map, writeSheet);            // data 集合            excelWriter.fill(new FillWrapper("data", list), writeSheet);            excelWriter.finish();        } catch (Exception ex) {            log.error("消费帮扶导出失败 , exception: {}", ex.getMessage());        }    }

utils：

package com.zhihe.village.tempalte;import com.alibaba.excel.write.metadata.style.WriteCellStyle;import com.alibaba.excel.write.metadata.style.WriteFont;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.VerticalAlignment;/** * EasyExcel 样式工具类 */public class StyleUtils {    /**     * 标题样式     * @return     */    public static WriteCellStyle getHeadStyle(){        // 头的策略        WriteCellStyle headWriteCellStyle = new WriteCellStyle();        // 背景颜色//        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE1.getIndex());//        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);        // 字体        WriteFont headWriteFont = new WriteFont();        headWriteFont.setFontName("宋体");//设置字体名字        headWriteFont.setFontHeightInPoints((short)16);//设置字体大小        headWriteFont.setBold(true);//字体加粗        headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;        // 样式        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;        headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;        headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;        headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;        headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;        headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;        headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;        headWriteCellStyle.setWrapped(true);  //设置自动换行;        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;        headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适        return headWriteCellStyle;    }    /**     * 内容样式     * @return     */    public static WriteCellStyle getContentStyle(){        // 内容的策略        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();        // 背景绿色        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);        // 设置字体        WriteFont contentWriteFont = new WriteFont();        contentWriteFont.setFontHeightInPoints((short) 16);//设置字体大小        contentWriteFont.setFontName("宋体"); //设置字体名字        contentWriteCellStyle.setWriteFont(contentWriteFont);//在样式用应用设置的字体;        //设置样式;        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;        contentWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;        contentWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;        contentWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;        contentWriteCellStyle.setTopBorderColor((short) 0); ///设置顶边框颜色;        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中        contentWriteCellStyle.setWrapped(true); //设置自动换行;//        contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适        return contentWriteCellStyle;    }}

项目结构：