JAVA POI导出excel(二)：多个sheet

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

1.导出单个sheet

2.导出zip压缩包

        但是在我们日常的工作中，需求往往没这么简单，可能需要将数据按类型分类导出或者数据量过大，需要分多张表导出等等。遇到类似的需求该怎么办呢，别慌，往下看。

一、pom引用

        pom文件中，添加以下依赖

        <!--Excel工具-->
        <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.ExportSheetUtil

package com.***.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.http.MediaType;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;

/**
 * @description: excel导出多个sheet工具类
 * @author: ***
 * @date: 2022/9/15
 */
public class ExportSheetUtil {

    /**
     * 拆解并导出多重Excel
     */
    public static void exportManySheetExcel(String fileName, List<ExcelSheet> mysheets, HttpServletResponse response) {
        //创建工作薄
        HSSFWorkbook wb = new HSSFWorkbook();
        //表头样式
        HSSFCellStyle style = wb.createCellStyle();
        // 垂直
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 水平
        style.setAlignment(HorizontalAlignment.CENTER);
        //字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        style.setFont(fontStyle);
        for (ExcelSheet excel : mysheets) {
            //新建一个sheet
            //获取该sheet名称
            HSSFSheet sheet = wb.createSheet(excel.getFileName());
            //获取sheet的标题名
            String[] handers = excel.getHanders();
            //第一个sheet的第一行为标题
            HSSFRow rowFirst = sheet.createRow(0);
            //写标题
            for (int i = 0; i < handers.length; i++) {
                //获取第一行的每个单元格
                HSSFCell cell = rowFirst.createCell(i);
                //往单元格里写数据
                cell.setCellValue(handers[i]);
                //加样式
                cell.setCellStyle(style);
                //设置每列的列宽
                sheet.setColumnWidth(i, 4000);
            }
            //写数据集
            List<String[]> dataset = excel.getDataset();
            for (int i = 0; i < dataset.size(); i++) {
                //获取该对象
                String[] data = dataset.get(i);
                //创建数据行
                HSSFRow row = sheet.createRow(i + 1);
                for (int j = 0; j < data.length; j++) {
                    //设置对应单元格的值
                    row.createCell(j).setCellValue(data[j]);
                }
            }
        }

        // 下载文件谷歌文件名会乱码,用IE
        try {
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
            response.setHeader("Cache-Control", "No-cache");
            response.flushBuffer();
            wb.write(response.getOutputStream());
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

2.ExcelSheet

package com.***.excel;

import java.util.Arrays;
import java.util.List;

/**
 * @description: 导出多个sheet表
 * @author: ***
 * @date: 2022/9/15
 */
public class ExcelSheet {

    /*** sheet的名称*/
    private String fileName;

    /*** sheet里的标题*/
    private String[] handers;

    /*** sheet里的数据集*/
    private List<String[]> dataset;

    public ExcelSheet(String fileName, String[] handers, List<String[]> dataset) {
        this.fileName = fileName;
        this.handers = handers;
        this.dataset = dataset;
    }

    public String getFileName() {
        return this.fileName;
    }

    public String[] getHanders() {
        return this.handers;
    }

    public List<String[]> getDataset() {
        return this.dataset;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public void setHanders(String[] handers) {
        this.handers = handers;
    }

    public void setDataset(List<String[]> dataset) {
        this.dataset = dataset;
    }

    public boolean equals(Object o) {
        if (o == this) {
            return true;
        } else if (!(o instanceof ExcelSheet)) {
            return false;
        } else {
            ExcelSheet other = (ExcelSheet)o;
            if (!other.canEqual(this)) {
                return false;
            } else {
                label39: {
                    Object this$fileName = this.getFileName();
                    Object other$fileName = other.getFileName();
                    if (this$fileName == null) {
                        if (other$fileName == null) {
                            break label39;
                        }
                    } else if (this$fileName.equals(other$fileName)) {
                        break label39;
                    }

                    return false;
                }

                if (!Arrays.deepEquals(this.getHanders(), other.getHanders())) {
                    return false;
                } else {
                    Object this$dataset = this.getDataset();
                    Object other$dataset = other.getDataset();
                    if (this$dataset == null) {
                        if (other$dataset != null) {
                            return false;
                        }
                    } else if (!this$dataset.equals(other$dataset)) {
                        return false;
                    }

                    return true;
                }
            }
        }
    }

    protected boolean canEqual(Object other) {
        return other instanceof ExcelSheet;
    }

    public int hashCode() {
        int PRIME = true;
        int result = 1;
        Object $fileName = this.getFileName();
        result = result * 59 + ($fileName == null ? 43 : $fileName.hashCode());
        result = result * 59 + Arrays.deepHashCode(this.getHanders());
        Object $dataset = this.getDataset();
        result = result * 59 + ($dataset == null ? 43 : $dataset.hashCode());
        return result;
    }

    public String toString() {
        return "ExcelSheet(fileName=" + this.getFileName() + ", handers=" + Arrays.deepToString(this.getHanders()) + ", dataset=" + this.getDataset() + ")";
    }

}

三、相关业务代码

1.service层

    /*** 导出开票及运单信息*/
    List<ExcelSheet> exportInvoiceAndBillInfo(IdListDto dto);

2.impl实现类

        实现类里的代码，需要各位根据自己的业务场景进行改动，无非就是将需要导出的数据先查出来，带入模板中。

@Override
    public List<ExcelSheet> exportInvoiceAndBillInfo(IdListDto dto) {
        Integer billType = dto.getBillType();
        if (billType == null) {
            throw new ServiceException("业务类型不能为空");
        }
        if (StringUtils.isEmpty(dto.getIdList())) {
            throw new ServiceException("请勾选票据");
        }
        InvoiceDto invoiceDto = new InvoiceDto();
        invoiceDto.setInvoiceList(dto.getIdList());
        List<Invoice> invoiceList = invoiceMapper.queryInvoiceList(invoiceDto);

        List<ExcelSheet> mysheet = new ArrayList<>();
        //票据
        String[] invoiceSheetHead = {"开票编号", "票号", "公司名称", "收票方名称", "结算类型", "纳税识别码", "收票联系人", "联系人电话", "运单总金额(元)", "含税总金额(元)", "开票状态", "提交开票时间", "运营审核时间", "运营审核人", "财务审核时间", "财务审核人", "开票完成时间", "冲销操作人", "冲销时间"};
        List<String[]> invoiceDataList = this.getInvoiceDataList(invoiceList);
        ExcelSheet invoiceExcel = new ExcelSheet("开票信息", invoiceSheetHead, invoiceDataList);
        //票据运单
        ExcelSheet billExcel = null;
        List<String[]> billList = new ArrayList<>();
        for (Invoice invoice : invoiceList) {
            if (billType == DriveTypeEnum.TRUCK.getCode()) {
                List<String[]> list = this.getBillInfo(invoice.getInvoiceId(), invoice.getInvoiceNo());
                billList.addAll(list);
            }
            if (billType == DriveTypeEnum.SHIP.getCode()) {
                List<String[]> list = this.getShipBillInfo(invoice.getInvoiceId(), invoice.getInvoiceNo());
                billList.addAll(list);
            }
        }
        String[] billSheetHead = {"开票编号", "运单号", "公司名称", "发货地", "收货地", "司机", "司机电话", "货物名称", "货物数量", "单位", "货物重量(吨)", "运单状态", "运单金额(元)", "含税金额(元)", "支付类型", "支付渠道", "支付时间"};
        String[] shipBillSheetHead = {"开票编号", "运单号", "公司名称", "发货地", "收货地", "船长", "船长电话", "货物名称", "货物数量", "单位", "货物重量(吨)", "运单状态", "运单金额(元)", "含税金额(元)", "支付类型", "支付渠道", "支付时间"};
        if (billType == DriveTypeEnum.TRUCK.getCode()) {
            billExcel = new ExcelSheet("运单信息", billSheetHead, billList);
        }
        if (billType == DriveTypeEnum.SHIP.getCode()) {
            billExcel = new ExcelSheet("运单信息", shipBillSheetHead, billList);
        }
        mysheet.add(invoiceExcel);
        mysheet.add(billExcel);
        return mysheet;
    }


    /**
     * 获取开票数据
     */
    private List<String[]> getInvoiceDataList(List<Invoice> invoiceList) {
        List<String[]> list = Lists.newArrayList();
        for (Invoice vo : invoiceList) {
            // 将int、date类型数据 转为string类型;且数据需判空

            Integer invoiceStatus = vo.getStatus();
            String status = InvoiceStatusEnum.getNameByCode(invoiceStatus);
            if (invoiceStatus != InvoiceStatusEnum.INVALID.getCode()) {
                vo.setUpdateTime(null);
            }
            String settle = vo.getSettleType() == SettleTypeEnum.NORMAL.getCode() ? SettleTypeEnum.NORMAL.getName() : SettleTypeEnum.PRE.getName();
            String createTime = vo.getCreateTime() == null ? "" : DateUtil.dateTimeToStr(vo.getCreateTime());
            String operationReviewTime = vo.getOperationReviewTime() == null ? "" : DateUtil.dateTimeToStr(vo.getOperationReviewTime());
            String financeReviewTime = vo.getFinanceReviewTime() == null ? "" : DateUtil.dateTimeToStr(vo.getFinanceReviewTime());
            String billTime = vo.getBillTime() == null ? "" : DateUtil.dateTimeToStr(vo.getBillTime());
            String updateTime = vo.getUpdateTime() == null ? "" : DateUtil.dateTimeToStr(vo.getUpdateTime());
            String[] data = {String.valueOf(vo.getInvoiceNo()), vo.getInvoiceCode(), vo.getCompanyName(), vo.getReceiptOrgName(), settle, vo.getIdentifyCode(), vo.getReceiptName(), vo.getReceiptMobile(), String.valueOf(vo.getTransportMoney()), String.valueOf(vo.getTransportMoneyReal()), status, createTime, operationReviewTime, vo.getOperationReviewName(), financeReviewTime, vo.getFinanceReviewName(), billTime, vo.getUpdateUser(), updateTime};
            list.add(data);
        }
        return list;
    }

3.controller层

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

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

2.调用封装的工具类ExportSheetUtil中的exportManySheetExcel方法就可以了

    /**
     * 导出开票和运单信息
     */    
    @PostMapping("/exportInvoiceAndBillInfo")
    public void exportInvoiceAndBillInfo(@RequestBody IdListDto dto, HttpServletResponse response) {
        log.info("导出开票和运单信息，入参：{}", dto);
        long a = System.currentTimeMillis();
        List<ExcelSheet> mySheet = invoiceReviewService.exportInvoiceAndBillInfo(dto);
        long b = System.currentTimeMillis();
        long time = b - a;
        log.info("导出开票和运单信息,处理数据耗时：{}", time);
        ExportSheetUtil.exportManySheetExcel("开票及运单信息", mySheet, response);
    }

最终导出的Excel文件：

 

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