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

1、导出单个sheet

2、导出多个sheet

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

一、pom引用

        pom文件中，添加以下依赖

        org.apache.poipoi5.2.2compileorg.apache.poipoi-ooxml5.2.2compile

二、工具类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 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 exportSales(ExportSalesDto dto);

2.impl实现类

@Overridepublic Map exportSales(ExportSalesDto dto) {List list = new ArrayList<>();ExportSalesVo exportSalesVo = new ExportSalesVo();// 表格title（表格title需要根据自己的项目需求进行修改）List titleList = Arrays.asList("单据编号", "单据序号", "客户名称", "客户税号", "客户地址电话", "客户银行账号", "客户联系方式", "商品名称", "规格型号", "计量单位", "数量", "单价", "金额", "税率", "商品编码", "使用优惠政策", "优惠政策", "零税率标识", "自行编码", "备注", "开票人", "收款人", "复核人", "折扣金额", "折扣汇总金额", "扣除额", "成品油");exportSalesVo.setTitleList(titleList);// 填充清单数据List 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 workbookMap = new HashMap<>();for (ExportSalesVo vo : list) {XSSFWorkbook wk = new XSSFWorkbook();XSSFSheet sheet = wk.createSheet("清单");XSSFRow row = sheet.createRow(0);// 添加excel titleXSSFCell 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 workbookMap = invoiceReviewService.exportSales(dto);try {ExportExcelZip.downloadExcelForZip(response, workbookMap, "sales.zip");} catch (IOException e) {e.printStackTrace();}}

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

最终导出的效果：

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