@[toc]
一、业务背景
业务需求：从数据库查询多个list集合信息封装excel，每个excel都有2个sheet页，填充不同的信息，最后将所有excel打包成zip文件，以流的形式返回给客户端，供客户端另存为窗口下载。
- 只发出一次请求
- 每个excel表中到数据记录不能超过2条
- excel文件或者zip包不会上传服务器，而是查询后直接封装excel，然后把多个excel封装成zip包直接返回
之前看过其他人的方案大概有2种：
方案1：打包成zip包后上传到服务器某个路径下，然后在读取该路径的zip文件，以流的形式返回给客户端。方案2：不上传服务器，而是查询后直接封装excel，然后把多个excel封装成zip包直接返回。（本人采用的就是第二种方案）
最终的效果，如图
二、实现思路
- 设置HttpServletResponse的参数，比如header、contentType
- 新建一个Workbook对象并置为空，同时初始化相关对象，比如List、File等
- 从数据库查询多条list
- 以其中为主的一个list计算分页数量
- 循环遍历list开始
- 初始化新的Workbook对象，并设置相应的Title
- 将list拆分成多个段，分别写到的Workbook对象中
- 将Workbook对象填充到List\中
- 重复步骤6至步骤8直至写完所有数据
- 最后统一封装zip压缩包并导出，调用downFileByStream方法
- 初始化ZipOutputStream对象
- 循环遍历List\将每一个wb写入ZipOutputStream对象中，并将内存流写入Zip文件，即：将每一个excel封装到zip包中
- 关闭ZipOutputStream
二、准备工作
1.准备data模板.xlsx
2.引入poi相关依赖，用于操作excel
pom.xml
<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>
3.针对WorkBook+ZIP压缩输入/输出流，相关方法知识点要有所了解
Apache POI包中的HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook的区别如下:
ZipOutputStream类的常用方法如下表所示：
| 方法 | 返回值 | 说明 |
|---|---|---|
| putNextEntry(ZipEntry e) | void | 开始写一个新的ZipEntry，并将流内的位置移至此entry所指数据的开头 |
| write(byte[] b, int off, int len) | void | 将字节数组写入当前ZIP条目数据 |
| finish() | void | 完成写入ZIP输出流的内容，无须关闭它所配合的OutputStream |
| setComment(String comment) | void | 可设置此ZIP文件的注释文字 |
ZipInputStream类的常用方法如下表所示：
| 方法 | 返回值 | 说明 |
|---|---|---|
| read(byte[] b, int off, int len) | int | 读取目标b数组内off偏移量的位置，长度是len字节 |
| available() | int | 判断是否已读完目前entry所指定的数据。已读完返回0，否则返回1 |
| closeEntry() | void | 关闭当前ZIP条目并定位流以读取下一个条目 |
| skip(long n) | long | 跳过当前ZIP条目中指定的字节数 |
| getNextEntry() | ZipEntry | 读取下一个ZipEntry，并将流内的位置移至该entry所指数据的开头 |
| createZipEntry(String name) | ZipEntry | 以指定的name参数新建一个ZipEntry对象 |
Workbook类提供的方法
| 方法 | 返回值 | 说明 |
|---|---|---|
| getNumberOfSheets() | int | 获得工作薄（Workbook）中工作表（Sheet）的个数 |
| getSheets() | Sheet[] | 返回工作薄（Workbook）中工作表（Sheet）对象数组 |
| getVersion() | String | 返回正在使用的API的版本号，好像是没什么太大的作用。 |
| getName() | String | 获取Sheet的名称 |
| getColumns() | int | 获取Sheet表中所包含的总列数 |
| getColumn(int column) | Cell[] | 获取某一列的所有单元格，返回的是单元格对象数组 |
| getRows() | int | 获取Sheet表中所包含的总行数 |
| getRow(int row) | Cell[] | 获取某一行的所有单元格，返回的是单元格对象数组 |
| getCell(int column, int row) | Cell | 获取指定单元格的对象引用，需要注意的是它的两个参数，第一个是列数，第二个是行数，这与通常的行、列组合有些不同。 |
| write() | 写入Exel工作表 | |
| close() | 关闭Excel工作薄对象 | |
| getPhysicalNumberOfCells() | int | 获取该行的总列数 |
| getSheetAt(int index) | Sheet | 根据索引index获取对应的sheet页 |
| getBodyStyle(Workbook wb) | CellStyle | 设置excel中比如第一行Title样式 |
| setCellStyle(CellStyle var1) | void | 跟getBodyStyle()方法搭配设置样式 |
| setCellValue(String var1) | void | 设置值 |
| getStringCellValue() | String | 获取对应列的值 |
三、完整的项目代码
ExportUtil工具类
package com.excel.utils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
* 导出【用户、组、隶属关系】工具
* @Author 211145187
* @Date 2023/4/13 16:29
**/
public class ExportUtil {
private static Logger logger = LoggerFactory.getLogger(ExportUtil.class);
/**
* 自定义获取分页总页数的方法
* @param count 查询集合数量
* @param pageSize 配置文件中设置的单文件存储最大条数
* @return 总页数
*/
public static Integer getPageCount(Integer count, Integer pageSize){
Integer pageCount = 0;
if(count.equals(0)){
return pageCount;
}
pageCount = count/pageSize;
if(count % pageSize != 0){
pageCount++;
}
return pageCount;
}
/**
* 自定义List分页工具
* @param list 待分页的list数据
* @param pageNum 页码
* @param pageSize 页容量
* @param pageCount 总页数
* @return 分页后的list数据
*/
public static <T> List<T> getPageList(List<T> list, Integer pageNum, Integer pageSize, Integer pageCount){
/**开始索引*/
int beginIndex = 0;
/**结束索引*/
int endIndex = 0;
Integer compare = pageNum.compareTo(pageCount);
if(!compare.equals(0)){
beginIndex = (pageNum - 1) * pageSize;
endIndex = beginIndex + pageSize;
}else{
beginIndex = (pageNum - 1) * pageSize;
endIndex = list.size();
}
List pageList = list.subList(beginIndex, endIndex);
return pageList;
}
/**
* HSSFWorkbook转file
* @param wb wb
* @param name 文件名称
* @return File
*/
public static File xssfWorkbookToFile(Workbook wb, String name) {
File toFile = new File(name);
try {
OutputStream os = new FileOutputStream(toFile);
wb.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
return toFile;
}
/**
* 直接下载zip包
* @param response response
* @param excels wb集合
*/
public static void downFileByStream(HttpServletResponse response, List<XSSFWorkbook> excels){
try {
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
ZipOutputStream zipOutputStream = new ZipOutputStream(toClient);
for(int i=0; i<excels.size(); i++){
ByteArrayOutputStream baos = new ByteArrayOutputStream();
// 将Workbook写入内存流
excels.get(i).write(baos);
ZipEntry zipEntry = new ZipEntry("data" + i + ".xlsx");
zipOutputStream.putNextEntry(zipEntry);
// 将内存流写入Zip文件
zipOutputStream.write(baos.toByteArray());
}
zipOutputStream.closeEntry();
zipOutputStream.flush();
zipOutputStream.close();
}catch (Exception e){
logger.error("downFileByStream==========fail:{}", e.getMessage());
}
}
}
application.properties
server.port=8001
#导出excel配置，单文件存储最大数量
export.num=2
OperateExcelController
package com.excel.controller;
import com.excel.bean.Score;
import com.excel.bean.Teacher;
import com.excel.utils.ExportUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* 测试3.17版本操作Excel
* @Author 211145187
* @Date 2022/2/22 19:43
**/
@RequestMapping("/excel")
@Controller
public class OperateExcelController {
private static Logger logger = LoggerFactory.getLogger(OperateExcelController.class);
@Value("${export.num:10000}")
private Integer exportLimitNum;
//构建教师集合数据
public List<Teacher> buildTeacherList1() {
List<Teacher> teacherList = new ArrayList<>();
Teacher teacher1 = new Teacher();
teacher1.setName("周杰伦");
teacher1.setClasses("三年二班");
teacher1.setCollege("魔法学院");
teacher1.setAlias("Jay Chou");
teacherList.add(teacher1);
Teacher teacher2 = new Teacher();
teacher2.setName("陈奕迅");
teacher2.setClasses("三年二班");
teacher2.setCollege("魔法学院");
teacher2.setAlias("Eason");
teacherList.add(teacher2);
Teacher teacher3 = new Teacher();
teacher3.setName("林俊杰");
teacher3.setClasses("三年二班");
teacher3.setCollege("魔法学院");
teacher3.setAlias("Eason");
teacherList.add(teacher3);
Teacher teacher4 = new Teacher();
teacher4.setName("张杰");
teacher4.setClasses("三年二班");
teacher4.setCollege("魔法学院");
teacher4.setAlias("Eason");
teacherList.add(teacher4);
return teacherList;
}
//构建分数集合数据
public List<Score> buildScoreList1() {
List<Score> scoreList = new ArrayList<>();
Score score1 = new Score();
score1.setName("流川枫");
score1.setClasses("三年二班");
score1.setWriteScore("6");
score1.setComputerScore("4");
scoreList.add(score1);
Score score2 = new Score();
score2.setName("樱木花道");
score2.setClasses("三年二班");
score2.setWriteScore("6");
score2.setComputerScore("4");
scoreList.add(score2);
Score score3 = new Score();
score3.setName("大猩猩");
score3.setClasses("三年二班");
score3.setWriteScore("6");
score3.setComputerScore("4");
scoreList.add(score3);
Score score4 = new Score();
score4.setName("三井");
score4.setClasses("三年二班");
score4.setWriteScore("6");
score4.setComputerScore("4");
scoreList.add(score4);
return scoreList;
}
//方法5：java导出多个Excel为zip包
@RequestMapping("/exportMultipleExcelToZip")
public void exportMultipleExcelToZip(HttpServletResponse response) throws IOException {
response.setHeader("Content-disposition", "attachment; filename=" + "test.zip");
response.setContentType("application/zip; charset=utf-8");
//创建HSSFWorkbook对象(excel的文档对象)
XSSFWorkbook wb = null;
List<Teacher> teacherList = new ArrayList<>();
//构建sheet页集合
List<Score> scoreList = new ArrayList<>();
File templateFile = new File("C:\\Users\\211145187\\Desktop\\data模板.xlsx");
//.....省略部分代码
List<Teacher> buildTeacherList = buildTeacherList1();
List<Score> buildScoreList = buildScoreList1();
Integer pageLimitSize = exportLimitNum;
//计算list的分页数量
Integer pageCount = ExportUtil.getPageCount(buildTeacherList.size(), pageLimitSize);
List<XSSFWorkbook> excels = new ArrayList<>();
try {
for(Integer pageNum = 1; pageNum < pageCount + 1; pageNum++) {
//注意：每次循环遍历前都需要初始化新的wb对象
//注意情况1：如果是初始化wb空对象然后手动添加title，下方三行代码不会报错
// wb = new XSSFWorkbook();
// buildScoreSheetTitle(wb);
// buildTeacherSheetTitle(wb);
//注意情况2：如果是初始化wb对象，并且以流的形式初始化，那么io流必须放在里面才行，如果放在for循环外面会报“Stream Closed”错误
InputStream io = new FileInputStream(templateFile);
wb = new XSSFWorkbook(io);
teacherList = ExportUtil.getPageList(buildTeacherList, pageNum, pageLimitSize, pageCount);
scoreList = ExportUtil.getPageList(buildScoreList, pageNum, pageLimitSize, pageCount);
buildScoreSheetParams(wb, scoreList);
buildTeacherSheetParams(wb, teacherList);
excels.add(wb);
}
//最后统一封装zip压缩包并导出
ExportUtil.downFileByStream(response, excels);
} catch (Exception e) {
logger.error("IOException:", e);
}
}
/**
* 填充教师页信息
* @param wb wb
* @param bodyData bodyData
*/
private void buildTeacherSheetParams(Workbook wb, List<Teacher> bodyData){
int teacherColumnCount = wb.getSheetAt(1).getRow(0).getPhysicalNumberOfCells();
Sheet sheet = wb.getSheetAt(1);
// build data
for(int j=0; j<bodyData.size(); j++){
Teacher itm = bodyData.get(j);
Row rowData = sheet.createRow(j+1);
for(int k=0;k<teacherColumnCount; k++){
Cell cell = rowData.createCell(k);
cell.setCellValue(getValueByTeacher(k, itm));
}
}
}
private String getValueByTeacher(int columnIndex,Teacher itm){
String cellValue;
switch (columnIndex){
case 0:cellValue = itm.getName(); break;
case 1:cellValue = itm.getClasses()+""; break;
case 2:cellValue = itm.getCollege(); break;
case 3:cellValue = itm.getAlias(); break;
default:cellValue=""; break;
}
return cellValue;
}
/**
* 填充分数页信息
* @param wb wb
* @param bodyData bodyData
*/
private void buildScoreSheetParams(Workbook wb, List<Score> bodyData){
int scoreColumnCount = wb.getSheetAt(0).getRow(0).getPhysicalNumberOfCells();
Sheet sheet = wb.getSheetAt(0);
// build data
for(int j=0; j<bodyData.size(); j++){
Score itm = bodyData.get(j);
Row rowData = sheet.createRow(j+1);
for(int k=0;k<scoreColumnCount; k++){
Cell cell = rowData.createCell(k);
cell.setCellValue(getValueByScore(k, itm));
}
}
}
private String getValueByScore(int columnIndex,Score itm){
String cellValue;
switch (columnIndex){
case 0:cellValue = itm.getName(); break;
case 1:cellValue = itm.getClasses()+""; break;
case 2:cellValue = itm.getWriteScore(); break;
case 3:cellValue = itm.getComputerScore(); break;
default:cellValue=""; break;
}
return cellValue;
}
/**
* 构建分数表excel的标头
* @Author 211145187
* @Date 2022/2/22 20:20
* @Param wb wb
**/
private void buildScoreSheetTitle(XSSFWorkbook wb) {
//建立新的sheet对象（excel的表单）
XSSFSheet sheet=wb.createSheet("成绩表");
XSSFRow row=sheet.createRow(0);
//创建单元格并设置单元格内容
XSSFCell cell0 = row.createCell(0);
cell0.setCellValue("姓名");
cell0.setCellStyle(getHeadStyle(wb));
XSSFCell cell1 = row.createCell(1);
cell1.setCellValue("班级");
cell1.setCellStyle(getHeadStyle(wb));
XSSFCell cell2 = row.createCell(2);
cell2.setCellValue("笔试成绩");
cell2.setCellStyle(getHeadStyle(wb));
XSSFCell cell3 = row.createCell(3);
cell3.setCellValue("机试成绩");
cell3.setCellStyle(getHeadStyle(wb));
}
/**
* 构建教师表excel的标头
* @Author 211145187
* @Date 2022/2/22 20:20
* @Param wb wb
**/
private void buildTeacherSheetTitle(XSSFWorkbook wb) {
//建立新的sheet对象（excel的表单）
XSSFSheet sheet=wb.createSheet("教师表");
XSSFRow row=sheet.createRow(0);
//创建单元格并设置单元格内容
XSSFCell cell0 = row.createCell(0);
cell0.setCellValue("姓名");
cell0.setCellStyle(getHeadStyle(wb));
XSSFCell cell1 = row.createCell(1);
cell1.setCellValue("班级");
cell1.setCellStyle(getHeadStyle(wb));
XSSFCell cell2 = row.createCell(2);
cell2.setCellValue("所属学院");
cell2.setCellStyle(getHeadStyle(wb));
XSSFCell cell3 = row.createCell(3);
cell3.setCellValue("别名");
cell3.setCellStyle(getHeadStyle(wb));
}
/**
* 设置样式
* @Author 211145187
* @Date 2022/2/22 20:15
* @Param wb wb
* @Return CellStyle
**/
private CellStyle getHeadStyle(Workbook wb) {
CellStyle cellStyle = wb.createCellStyle();
//用于设置前景颜色
cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
/**
* setFillPattern用于设置单元格填充样式
* 注意：
* 1）setFillPattern必须设置否则光设置setFillForegroundColor无效
* 2）3.10.1版本支持short类型参数，而3.17版本支持FillPatternType类型参数
*/
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return cellStyle;
}
}
四、可能遇到的问题
错误场景1：java.io.IOException: Stream closed
原因分析：
我这里导致流关闭会有2种场景触发：
场景1：工具类方法downFileByStream()中的for循环执行write()方法。
场景2：wb = new XSSFWorkbook(io);通过IO流初始化wb对象时。
解决方案：
针对场景1中的解决方案就是：Workbook的write()方法最终会关闭它写入的输出流。如果只是一次性全部输出那就不会碰到这种问题，但是现在的场景是工具类方法downFileByStream()中的for循环执行write()方法，所以解决方案就是用一个ByteArrayOutputStream去接收存储当前Workbook的内容，这样哪怕Workbook关闭了，但是内容已经提前存入了ByteArrayOutputStream中，只要读取了就可以继续使用。因此才有这样的如下写法：
/**
* 直接下载zip包
* @param response response
* @param excels wb集合
*/
public static void downFileByStream(HttpServletResponse response, List<XSSFWorkbook> excels){
try {
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
ZipOutputStream zipOutputStream = new ZipOutputStream(toClient);
for(int i=0; i<excels.size(); i++){
ByteArrayOutputStream baos = new ByteArrayOutputStream();
// 将Workbook写入内存流
excels.get(i).write(baos);
ZipEntry zipEntry = new ZipEntry("data" + i + ".xlsx");
zipOutputStream.putNextEntry(zipEntry);
// 将内存流写入Zip文件
zipOutputStream.write(baos.toByteArray());
}
zipOutputStream.closeEntry();
zipOutputStream.flush();
zipOutputStream.close();
}catch (Exception e){
logger.error("downFileByStream==========fail:{}", e.getMessage());
}
}
针对场景2中的解决方案就是：把IO流初始化提到for循环里面，如实有了如下写法：
for(Integer pageNum = 1; pageNum < pageCount + 1; pageNum++) {
//注意：每次循环遍历前都需要初始化新的wb对象
//注意情况1：如果是初始化wb空对象然后手动添加title，下方三行代码不会报错
// wb = new XSSFWorkbook();
// buildScoreSheetTitle(wb);
// buildTeacherSheetTitle(wb);
//注意情况2：如果是初始化wb对象，并且以流的形式初始化，那么io流必须放在里面才行，如果放在for循环外面会报“Stream Closed”错误
InputStream io = new FileInputStream(templateFile);
wb = new XSSFWorkbook(io);
...
}
错误场景2：调用接口没有另存为弹窗，但是F12查看接口结果返回一堆乱码
错误原因分析：可能是返回结果HttpServletResponse设置的setContentType格式不对。
正确的应该设置为如下，一定要设置为application/zip：
response.setHeader("Content-disposition", "attachment; filename=" + "test.zip");
response.setContentType("application/zip; charset=utf-8");
