一、业务背景
业务需求:从数据库查询多个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<XSSFWorkbook>中
- 重复步骤6至步骤8直至写完所有数据
- 最后统一封装zip压缩包并导出,调用downFileByStream方法
- 初始化ZipOutputStream对象
- 循环遍历List<XSSFWorkbook>将每一个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格式不对。文章来源:https://www.toymoban.com/news/detail-740863.html
正确的应该设置为如下,一定要设置为application/zip:文章来源地址https://www.toymoban.com/news/detail-740863.html
response.setHeader("Content-disposition", "attachment; filename=" + "test.zip");
response.setContentType("application/zip; charset=utf-8");
到了这里,关于Java实现导出多个excel表打包到zip文件中,供客户端另存为窗口下载的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!