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

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

一、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 lombok.Data;
 
import java.util.List;
 
/**
 * @description: 导出多个sheet表
 * @author: ***
 * @date: 2022/9/15
 */
@Data
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;
    }
 
}

三、相关业务代码

1.service层

 
    /*** 导出开票及运单信息*/
    ExportInvoiceAndBillVo exportInvoiceAndBillInfo(InvoiceReviewListDto dto);

2.impl实现类

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

查看代码

 
 package com.***.vo.invoicereview;
 
import lombok.Data;
 
import java.io.Serializable;
import java.util.List;
 
/**
 * @description: 导出开票和运单信息Vo
 * @author: ***
 * @date: 2022/9/19
 */
@Data
public class ExportInvoiceAndBillVo implements Serializable {
 
    /*** 开票信息*/ 
    private List<String[]> invoiceList;
 
    /*** 运单信息*/ 
    private List<String[]> billList;
 
}

查看代码

 
     @Override 
    public ExportInvoiceAndBillVo exportInvoiceAndBillInfo(InvoiceReviewListDto dto) {
        ExportInvoiceAndBillVo invoiceAndBill = new ExportInvoiceAndBillVo();
        // 查询需要导出的开票信息
        PageInfo<InvoiceReviewListVo> pageInfo = queryInvoiceReviewList(dto);
        List<InvoiceReviewListVo> invoiceList = pageInfo.getList();
        if (invoiceList.size() > 10000) {
            throw new ServiceException("开票数据过多,请分批次导出");
        }
        // 查询需要导出的车运运单信息
        List<Long> invoiceIdList = invoiceList.stream().map(InvoiceReviewListVo::getInvoiceId).collect(Collectors.toList());
        List<ExportBillVo> billList = getBillInfo(invoiceIdList);
        if (billList.size() > 10000) {
            throw new ServiceException("运单数据过多,请分批次导出");
        }
        // 将表1 表2的数据 放入定义的对象Vo中
        invoiceAndBill.setInvoiceList(getInvoiceDataList(invoiceList));
        invoiceAndBill.setBillList(getBillDataList(billList));
        return invoiceAndBill;
    }

3.controller层

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

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

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

3.表头和表名需要各位根据自身的业务场景修改哈

查看代码

 
     /**
     * 导出开票和运单信息
     */ 
    @Log 
    @PostMapping("/exportInvoiceAndBillInfo") 
    public void exportInvoiceAndBillInfo(@RequestBody InvoiceReviewListDto dto, HttpServletResponse response) {
        ExportInvoiceAndBillVo invoiceAndBillVo = invoiceFacadeService.exportInvoiceAndBillInfo(dto);
        //设置sheet的表头与表名
        String[] invoiceSheetHead = {"开票编号", "票号", "公司名称", "收票方名称", "结算类型", "纳税识别码", "收票联系人", "联系人电话", "运单总金额(元)", "含税总金额(元)", "开票状态", "提交开票时间", "运营审核时间", "运营审核人", "财务审核时间", "财务审核人", "开票完成时间", "冲销操作人", "冲销时间"};
        String[] billSheetHead = {"开票编号", "运单号", "发货地", "收货地", "司机", "司机电话", "货物名称", "货物数量", "单位", "货物重量(吨)", "运单状态", "运单金额(元)", "含税金额(元)"};
        ExcelSheet invoiceExcel = new ExcelSheet("开票信息", invoiceSheetHead, invoiceAndBillVo.getInvoiceList());
        ExcelSheet billExcel = new ExcelSheet("运单信息", billSheetHead, invoiceAndBillVo.getBillList());
        List<ExcelSheet> mysheet = new ArrayList<>();
        mysheet.add(invoiceExcel);
        mysheet.add(billExcel);
        ExportSheetUtil.exportManySheetExcel("开票及运单信息", mysheet, response);
    }

最终导出的Excel文件：

	<!--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>

	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();
	}
	}

	}

	package com.***.excel;

	import lombok.Data;

	import java.util.List;

	/**
	* @description: 导出多个sheet表
	* @author: ***
	* @date: 2022/9/15
	*/
	@Data
	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;
	}

	}

	/*** 导出开票及运单信息*/
	ExportInvoiceAndBillVo exportInvoiceAndBillInfo(InvoiceReviewListDto dto);

	package com.***.vo.invoicereview;

	import lombok.Data;

	import java.io.Serializable;
	import java.util.List;

	/**
	* @description: 导出开票和运单信息Vo
	* @author: ***
	* @date: 2022/9/19
	*/
	@Data
	public class ExportInvoiceAndBillVo implements Serializable {

	/*** 开票信息*/
	private List<String[]> invoiceList;

	/*** 运单信息*/
	private List<String[]> billList;

	}

	@Override
	public ExportInvoiceAndBillVo exportInvoiceAndBillInfo(InvoiceReviewListDto dto) {
	ExportInvoiceAndBillVo invoiceAndBill = new ExportInvoiceAndBillVo();
	// 查询需要导出的开票信息
	PageInfo<InvoiceReviewListVo> pageInfo = queryInvoiceReviewList(dto);
	List<InvoiceReviewListVo> invoiceList = pageInfo.getList();
	if (invoiceList.size() > 10000) {
	throw new ServiceException("开票数据过多,请分批次导出");
	}
	// 查询需要导出的车运运单信息
	List<Long> invoiceIdList = invoiceList.stream().map(InvoiceReviewListVo::getInvoiceId).collect(Collectors.toList());
	List<ExportBillVo> billList = getBillInfo(invoiceIdList);
	if (billList.size() > 10000) {
	throw new ServiceException("运单数据过多,请分批次导出");
	}
	// 将表1 表2的数据放入定义的对象Vo中
	invoiceAndBill.setInvoiceList(getInvoiceDataList(invoiceList));
	invoiceAndBill.setBillList(getBillDataList(billList));
	return invoiceAndBill;
	}

	/**
	* 导出开票和运单信息
	*/
	@Log
	@PostMapping("/exportInvoiceAndBillInfo")
	public void exportInvoiceAndBillInfo(@RequestBody InvoiceReviewListDto dto, HttpServletResponse response) {
	ExportInvoiceAndBillVo invoiceAndBillVo = invoiceFacadeService.exportInvoiceAndBillInfo(dto);
	//设置sheet的表头与表名
	String[] invoiceSheetHead = {"开票编号", "票号", "公司名称", "收票方名称", "结算类型", "纳税识别码", "收票联系人", "联系人电话", "运单总金额(元)", "含税总金额(元)", "开票状态", "提交开票时间", "运营审核时间", "运营审核人", "财务审核时间", "财务审核人", "开票完成时间", "冲销操作人", "冲销时间"};
	String[] billSheetHead = {"开票编号", "运单号", "发货地", "收货地", "司机", "司机电话", "货物名称", "货物数量", "单位", "货物重量(吨)", "运单状态", "运单金额(元)", "含税金额(元)"};
	ExcelSheet invoiceExcel = new ExcelSheet("开票信息", invoiceSheetHead, invoiceAndBillVo.getInvoiceList());
	ExcelSheet billExcel = new ExcelSheet("运单信息", billSheetHead, invoiceAndBillVo.getBillList());
	List<ExcelSheet> mysheet = new ArrayList<>();
	mysheet.add(invoiceExcel);
	mysheet.add(billExcel);
	ExportSheetUtil.exportManySheetExcel("开票及运单信息", mysheet, response);
	}

java导出excel(二)：多个sheet

一、pom引用

二、工具类util

三、相关业务代码