JAVA 导出excel(三)：导出zip压缩包

雨点打透心脏的1/2处 2023-09-27 16:02 116阅读 0赞

相信在大部分的web项目中都会有导入导出Excel的需求，之前我也写过两篇导出Excel的文章，没看过的小伙伴可以去看哈，链接也给大家放出来了：

1、[导出单个sheet][sheet]

2、[导出多个sheet][sheet 1]

但是在我们日常的工作中，需求往往没这么简单，可能需要将Excel打包成压缩包再导出等等。遇到类似的需求该怎么办呢，别慌，往下看。

## 一、pom引用 ##

pom文件中，添加以下依赖

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>5.2.2</version>
                <scope>compile</scope>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>5.2.2</version>
                <scope>compile</scope>
            </dependency>

## 二、工具类util ##

**1.ExportExcelZip**

package com.***.excel;
    
    import com.***.ServiceException;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.util.Map;
    import java.util.zip.ZipEntry;
    import java.util.zip.ZipOutputStream;
    
    /**
     * @description: 导出多个Excel 压缩成ZIP
     */
    
    public class ExportExcelZip {
    
        /**
         * 以压缩包的方式下载excel
         *
         * @param response    响应
         * @param workbookMap
         * @param zipName     压缩包名称
         */
        public static void downloadExcelForZip(HttpServletResponse response, Map<String, XSSFWorkbook> workbookMap, String zipName) throws IOException {
            // 文件名外的双引号处理firefox的空格截断问题
            ZipOutputStream out = null;
            try {
                response.setContentType("application/*");
                response.setHeader("content-disposition", "attachment;filename=" + new String(zipName.getBytes("gb2312"), "ISO8859-1"));
                response.setCharacterEncoding("UTF-8");
                out = new ZipOutputStream(response.getOutputStream());
                for (String fileName : workbookMap.keySet()) {
                    XSSFWorkbook workbook = workbookMap.get(fileName);
                    ZipEntry entry = new ZipEntry(fileName);
                    out.putNextEntry(entry);
                    // 这里讲一下，workBook.write会指定关闭数据流，如果这里直接用workbook.write(out)，
                    // 下次就会抛出out已被关闭的异常，所有用ByteArrayOutputStream来拷贝一下。
                    ByteArrayOutputStream bos = new ByteArrayOutputStream();
                    workbook.write(bos);
                    bos.writeTo(out);
                    // 关闭输入流
                    out.closeEntry();
                }
                out.flush();
    
            } catch (IOException e) {
                throw new ServiceException("下载文件异常"+e.getMessage(), 9000);
            } finally {
                if (out != null) {
                    out.close();
                }
    
            }
        }
    
    }

## 三、相关业务代码 ##

**1.service层**

/*** 清单导出*/
        Map<String, XSSFWorkbook> exportSales(ExportSalesDto dto);

**2.impl实现类**

@Override
        public Map<String, XSSFWorkbook> exportSales(ExportSalesDto dto) {
            List<ExportSalesVo> list = new ArrayList<>();
            ExportSalesVo exportSalesVo = new ExportSalesVo();
            // 表格title（表格title需要根据自己的项目需求进行修改）
            List<String> titleList = Arrays.asList("单据编号", "单据序号", "客户名称", "客户税号", "客户地址电话", "客户银行账号", "客户联系方式", "商品名称", "规格型号", "计量单位", "数量", "单价", "金额", "税率", "商品编码", "使用优惠政策", "优惠政策", "零税率标识", "自行编码", "备注", "开票人", "收款人", "复核人", "折扣金额", "折扣汇总金额", "扣除额", "成品油");
            exportSalesVo.setTitleList(titleList);
            // 填充清单数据
            List<SalesListVo> salesList = invoiceMapper.queryInventory(dto.getInvoiceId());
            for (SalesListVo vo : salesList) {
                vo.setInvoiceNo("3000001");
                vo.setInvoiceSerialNum("");
                vo.setCustomerMobile("");
                vo.setUnit("吨");
                // 计算货物单价：金额/数量
                vo.setPrice(vo.getTransportMoneyReal().divide(vo.getInvoiceNum(), 5, RoundingMode.HALF_UP));
                vo.setRate(new BigDecimal("0.09"));
                vo.setGoodsCode("");
                vo.setIsDiscount("");
                vo.setDiscount("");
                vo.setIsZeroRate("");
                vo.setCustomCode("");
                vo.setDrawerName("");
                vo.setPayeeName("");
                vo.setReviewName("");
                vo.setDiscountAmount("");
                vo.setDiscountTotalAmount("");
                vo.setDeductionAmount("");
                vo.setRefinedOil("");
            }
            exportSalesVo.setDataList(salesList);
            list.add(exportSalesVo);
    
            Map<String, XSSFWorkbook> workbookMap = new HashMap<>();
            for (ExportSalesVo vo : list) {
                XSSFWorkbook wk = new XSSFWorkbook();
                XSSFSheet sheet = wk.createSheet("清单");
                XSSFRow row = sheet.createRow(0);
                // 添加excel title
                XSSFCell cell;
                for (int t = 0, size = vo.getTitleList().size(); t < size; t++) {
                    cell = row.createCell(t);
                    cell.setCellValue(vo.getTitleList().get(t));
                }
                // 在sheet中再添加1行，存放数据
                // 遍历将数据写入Excel中
                for (int t = 0, size = vo.getDataList().size(); t < size; t++) {
                    XSSFRow row1 = sheet.createRow(t + 1);
                    SalesListVo salesListVo = vo.getDataList().get(t);
                    Field[] declaredFields = salesListVo.getClass().getDeclaredFields();
                    for (int j = 0; j < declaredFields.length; j++) {
                        cell = row1.createCell(j);
                        Field field = declaredFields[j];
                        field.setAccessible(true);
                        String value = "";
                        try {
                            value = field.get(salesListVo).toString();
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                        cell.setCellValue(value);
                    }
                }
                workbookMap.put("清单.xlsx", wk);
            }
            return workbookMap;
        }

**3.controller层**

controller层的代码需要注意的是：

1.因为导出Excel一般都是通过浏览器进行下载的，所以入参中需要加入HttpServletResponse

2.调用封装的工具类**ExportExcelZip**中的**downloadExcelForZip**方法就可以了

/**
         * 导出销货清单（压缩包）
         */
        @GetMapping("/exportSales")
        public void exportSales(@Valid ExportSalesDto dto, HttpServletResponse response) {
            log.info("导出清单，入参：{}", dto);
            Map<String, XSSFWorkbook> workbookMap = invoiceReviewService.exportSales(dto);
            try {
                ExportExcelZip.downloadExcelForZip(response, workbookMap, "sales.zip");
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

**PS**：我这边只是提供了导出zip的demo，里面的表格title、数据等需要根据自己的项目需求替换成自己的代码，切记！！！

最终导出的效果：

![74f23283940149a1bffc91cd70e3345c.jpeg][]

![b660a9cd9924442e93b5ca771abffa03.jpeg][]

![bd38f6492e3141119fb53f7e1ee8d564.jpeg][]

![a8be7bfdd9a8479591fc0b6ba036c916.jpeg][]

#### **如果这篇文章对您有所帮助，或者有所启发的话，求一键三连：点赞、评论、收藏➕关注，您的支持是我坚持写作最大的动力。** ####

[sheet]: https://blog.csdn.net/weixin_42555014/article/details/127133925?spm=1001.2014.3001.5502
[sheet 1]: https://blog.csdn.net/weixin_42555014/article/details/127134343
[74f23283940149a1bffc91cd70e3345c.jpeg]: https://img-blog.csdnimg.cn/74f23283940149a1bffc91cd70e3345c.jpeg
[b660a9cd9924442e93b5ca771abffa03.jpeg]: https://img-blog.csdnimg.cn/b660a9cd9924442e93b5ca771abffa03.jpeg
[bd38f6492e3141119fb53f7e1ee8d564.jpeg]: https://img-blog.csdnimg.cn/bd38f6492e3141119fb53f7e1ee8d564.jpeg
[a8be7bfdd9a8479591fc0b6ba036c916.jpeg]: https://img-blog.csdnimg.cn/a8be7bfdd9a8479591fc0b6ba036c916.jpeg