使用jxls生成多个excel并压缩打包自动下载

需求： 选择不同的时间段从数据库中获取报表文件的数据，生成对应的多个excel，然后把这些excel压缩打包下载下来。
过程大致如下面三幅图所示：



jxls官方英文教程

1、导入依赖

   <!--excel生成工具-->

        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-jexcel</artifactId>
            <version>1.0.9</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-poi</artifactId>
            <version>1.0.14</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls</artifactId>
            <version>2.4.4</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

加上plugin

 <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <configuration>
                    <nonFilteredFileExtensions>
                        <nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
                    </nonFilteredFileExtensions>
                </configuration>
            </plugin>

2、编写JxlsUtil，jxls的工具类

JxlsUtils 代码

 public class JxlsUtils {

    static {
        //添加自定义指令（可覆盖jxls原指令）
        XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);  //合并单元格命令
    }

    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {

        Context context = PoiTransformer.createInitialContext();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer = jxlsHelper.createTransformer(is, os);
        //获得配置
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
        //设置静默模式，不报警告
        //evaluator.getJexlEngine().setSilent(true);
        //函数强制，自定义功能
        Map<String, Object> funcs = new HashMap<String, Object>();
        funcs.put("utils", new JxlsUtils());    //添加自定义功能
        evaluator.getJexlEngine().setFunctions(funcs);
        //必须要这个，否者表格函数统计会错乱
        jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
    }

    //把excel打包成zip 然后再输出
    public static void exportZipExcel(String fileName, ZipOutputStream zos, InputStream is, Map<String, Object> model) throws IOException {

        Context context = PoiTransformer.createInitialContext();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }

        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        ByteArrayOutputStream output = new ByteArrayOutputStream();
        Transformer transformer = jxlsHelper.createTransformer(is, output);
        //获得配置
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
        //函数强制，自定义功能
        Map<String, Object> funcs = new HashMap<String, Object>();
        funcs.put("utils", new JxlsUtils());    //添加自定义功能
        evaluator.getJexlEngine().setFunctions(funcs);
        jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
        //命名文件
//        zos.putNextEntry(new ZipEntry(  URLEncoder.encode(fileName +  ".xlsx", "UTF-8")));
        zos.putNextEntry(new ZipEntry(fileName + ".xlsx"));
        //把xlxs输出流作为输入流输入到zip输出流中
        zos.write(output.toByteArray());
        zos.flush();
        zos.closeEntry();
        output.close();
        //必须要这个，否者表格函数统计会错乱
//        jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
    }

    public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
        exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
    }

    public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
        File template = getTemplate(templatePath);
        if (template != null) {
            exportExcel(new FileInputStream(template), os, model);
        } else {
            throw new Exception("Excel 模板未找到。");
        }
    }


    //获取jxls模版文件
    public static File getTemplate(String path) {
        File template = new File(path);
        if (template.exists()) {
            return template;
        }
        return null;
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        return dateFmt(date, fmt, "");
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt, String defaultStr) {
        if (date == null) {
            return defaultStr;
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // 静态的日期格式化
    public static String dateFormate(Date date, String fmt) {
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }
 

MergeCommand代码

/**
 * 合并单元格命令
 * Created by Zhusx. on 2019/4/24.
 */
public class MergeCommand extends AbstractCommand {
    private String cols;    //合并的列数
    private String rows;    //合并的行数
    private Area area;
    private CellStyle cellStyle;    //第一个单元格的样式

    @Override
    public String getName() {
        return "merge";
    }

    @Override
    public Command addArea(Area area) {
        if (super.getAreaList().size() >= 1) {
            throw new IllegalArgumentException("You can add only a single area to 'merge' command");
        }
        this.area = area;
        return super.addArea(area);
    }

    @Override
    public Size applyAt(CellRef cellRef, Context context) {
        int rows = 1, cols = 1;
        if(StringUtils.isNotBlank(this.rows)){
            Object rowsObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.rows, context.toMap());
            if(rowsObj != null && NumberUtils.isDigits(rowsObj.toString())){
                rows = NumberUtils.toInt(rowsObj.toString());
            }
        }
        if(StringUtils.isNotBlank(this.cols)){
            Object colsObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.cols, context.toMap());
            if(colsObj != null && NumberUtils.isDigits(colsObj.toString())){
                cols = NumberUtils.toInt(colsObj.toString());
            }
        }

        if(rows > 1 || cols > 1){
            Transformer transformer = this.getTransformer();
            if(transformer instanceof PoiTransformer){
                return poiMerge(cellRef, context, (PoiTransformer)transformer, rows, cols);
            }else if(transformer instanceof JexcelTransformer){
                return jexcelMerge(cellRef, context, (JexcelTransformer)transformer, rows, cols);
            }
        }
        area.applyAt(cellRef, context);
        return new Size(1, 1);
    }

    protected Size poiMerge(CellRef cellRef, Context context, PoiTransformer transformer, int rows, int cols){
        Sheet sheet = transformer.getWorkbook().getSheet(cellRef.getSheetName());
        CellRangeAddress region = new CellRangeAddress(
                cellRef.getRow(),
                cellRef.getRow() + rows - 1,
                cellRef.getCol(),
                cellRef.getCol() + cols - 1);
        sheet.addMergedRegion(region);

        //合并之后单元格样式会丢失，以下操作将合并后的单元格恢复成合并前第一个单元格的样式
        area.applyAt(cellRef, context);
        if(cellStyle == null){
            PoiCellData cellData = (PoiCellData)transformer.getCellData(cellRef);
            cellStyle = cellData.getCellStyle();
        }
        setRegionStyle(cellStyle, region, sheet);
        return new Size(cols, rows);
    }

    protected Size jexcelMerge(CellRef cellRef, Context context, JexcelTransformer transformer, int rows, int cols){
        try {
            transformer.getWritableWorkbook().getSheet(cellRef.getSheetName())
                    .mergeCells(
                            cellRef.getRow(),
                            cellRef.getCol(),
                            cellRef.getRow() + rows - 1 ,
                            cellRef.getCol() + cols - 1);
            area.applyAt(cellRef, context);
        } catch (WriteException e) {
            throw new IllegalArgumentException("合并单元格失败");
        }
        return new Size(cols, rows);
    }

    private static void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                }
                cell.setCellStyle(cs);
            }
        }
    }

    public String getCols() {
        return cols;
    }

    public void setCols(String cols) {
        this.cols = cols;
    }

    public String getRows() {
        return rows;
    }

    public void setRows(String rows) {
        this.rows = rows;
    }
}

在JxlsUtil中写上excel要使用到方法(不要写成静态方法)

    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        return dateFmt(date, fmt, "");
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt, String defaultStr) {
        if (date == null) {
            return defaultStr;
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

在excel可以通过上文定义的utils去使用这个方法

3、定义excel模板

我把模板放在了resources的templates/xls/文件夹下

//遍历List的批注 
jx:each(items="detailList" var="detail"  lastCell="G6")
//jxls扫码范围的批注
jx:area(lastCell="H18")

excel模板说明

excel表对应java代码

最终效果

4、调用接口生成的excel代码

取出数据生成excel

    @GetMapping(value = "/export/purchase")
    public void purchaseExport(HttpServletResponse response,String id) throws Exception {
        try {

            PurchaseOrderExportVo vo=new PurchaseOrderExportVo();
            //Excel导出配置 获取resources下面的模板
            File file = ResourceUtils.getFile("classpath:templates/xls/sell_accept.xlsx");
            String fileName = "销售验收单";
            //设置响应头
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName+".xlsx", "UTF-8"));
            response.setContentType("application/vnd.ms-excel");
            Map<String,Object> map=new HashMap();
            map.put("detailList",vo.getOrderList());
            map.put("vo",vo);

            try(InputStream is = new FileInputStream(file )) {
                try (OutputStream os =  response.getOutputStream()) {
                    //开始到处excel
                    JxlsUtils.exportExcel(file.getPath(), os, map);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

5、调用接口生成多个excel，并且打包下载

   @GetMapping(value = "/export/purchaseOrders")
    public String purchaseAcceptExports(HttpServletRequest request, HttpServletResponse response, @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd") Date startTime, @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd") Date endTime, @RequestParam String type) {

        endTime = new Date(endTime.getTime() + 86400000L);
        List<PurchaseOrderDto> purchaseOrderDtos = directionalService.getPurchaseOrderByTimes(startTime, endTime);

        if (purchaseOrderDtos == null || purchaseOrderDtos.size() == 0) return "参数为空";

        try (ServletOutputStream bos = response.getOutputStream();
             //定义个zip输出流
             ZipOutputStream zos = new ZipOutputStream(bos)) {
            //Excel导出配置 获取resources下面的模板
            String url = ResourceUtils.getURL("classpath:").getPath();
            String config = url + "templates/xls/sell_accept.xlsx";
            String fileName = "销售验收单";
            //以zip压缩包方式响应
            response.setContentType("application/zip");
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName + ".zip", "UTF-8"));

            //循环输出多个文件
            purchaseOrderDtos.stream().forEach(dto -> {
                PurchaseOrderExportVo vo = new PurchaseOrderExportVo();


                //中间逻辑过程 省略。。。。。。。。。。。。。。。。。。

                Map<String, Object> map = new HashMap();
                map.put("detailList", dto.getDetailList());
                map.put("vo", vo);

                try (InputStream is = new FileInputStream(config)) {
                    JxlsUtils.exportZipExcel(fileName + "_" + dto.getFileNo(), zos, is, map);
                } catch (Exception e) {
                }
            });
            //刷新zip输出流
            zos.flush();
            zos.close();
            bos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "下载成功";
    }

调用此接口就会生成多个excel表的数据流然后打包下载

6、vue端请求下载excel压缩包的代码

 var elemIF = document.createElement('iframe')
        elemIF.src = this.baseURL + this.CONSTAINT.getCenterUrl(this.title) + '?startTime=' + this.$moment(this.start_time).format('YYYY-MM-DD') + '&endTime=' +
          this.$moment(this.end_time).format('YYYY-MM-DD') +
          '&type=' + this.CONSTAINT.getTypp(this.title)
        elemIF.style.display = 'none'
        document.body.appendChild(elemIF)

点击下载按钮

7、each嵌套

在exel中有循环，循环中还有循环

8、excel动态合并单元格

//合并代码
jx:merge(rows="detailList.size()" lastCell="A4")

如果多列都要合并，没列都要添加合并批注


合并效果

9、excel插入序号的方式

可以在List的每一项中加入下标然后再取出来展示
或者可以通过此单元格自增实现序号功能