本文介绍如何使用Java POI库结合Spring Boot实现Excel文件的导出功能。具体包括依赖配置、工具类编写、控制器方法定义等内容。
源码：
依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
<!-- poi -->
<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>
<!-- web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
</dependencies>
创建模板
说明：只参考（只是模板样式）
下载的工具包
**说明：**不用修改，详见DownloadUtil
package com.maizi.excel.POIExcel;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
public class DownloadUtil {
/**
* @param filePath 要下载的文件路径
* @param returnName 返回的文件名
* @param response HttpServletResponse
* @param delFlag 是否删除文件
*/
protected void download(String filePath, String returnName, HttpServletResponse response, boolean delFlag) {
this.prototypeDownload(new File(filePath), returnName, response, delFlag);
}
/**
* @param file 要下载的文件
* @param returnName 返回的文件名
* @param response HttpServletResponse
* @param delFlag 是否删除文件
*/
protected void download(File file, String returnName, HttpServletResponse response, boolean delFlag) {
this.prototypeDownload(file, returnName, response, delFlag);
}
/**
* @param file 要下载的文件
* @param returnName 返回的文件名
* @param response HttpServletResponse
* @param delFlag 是否删除文件
*/
public void prototypeDownload(File file, String returnName, HttpServletResponse response, boolean delFlag) {
// 下载文件
FileInputStream inputStream = null;
ServletOutputStream outputStream = null;
try {
if (!file.exists()) return;
response.reset();
//设置响应类型 PDF文件为"application/pdf"，WORD文件为："application/msword"， EXCEL文件为："application/vnd.ms-excel"。
response.setContentType("application/octet-stream;charset=utf-8");
//设置响应的文件名称,并转换成中文编码
//returnName = URLEncoder.encode(returnName,"UTF-8");
returnName = response.encodeURL(new String(returnName.getBytes(), "iso8859-1")); //保存的文件名,必须和页面编码一致,否则乱码
//attachment作为附件下载；inline客户端机器有安装匹配程序，则直接打开；注意改变配置，清除缓存，否则可能不能看到效果
response.addHeader("Content-Disposition", "attachment;filename=" + returnName);
//将文件读入响应流
inputStream = new FileInputStream(file);
outputStream = response.getOutputStream();
int length = 1024;
int readLength = 0;
byte buf[] = new byte[1024];
readLength = inputStream.read(buf, 0, length);
while (readLength != -1) {
outputStream.write(buf, 0, readLength);
readLength = inputStream.read(buf, 0, length);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
//删除原文件
if (delFlag) {
file.delete();
}
}
}
/**
* 2021-11-02
*
* @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream
* @param response HttpServletResponse 写入response
* @param returnName 返回的文件名
*/
public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
response.setContentType("application/octet-stream;charset=utf-8");
returnName = response.encodeURL(new String(returnName.getBytes(), "iso8859-1")); //保存的文件名,必须和页面编码一致,否则乱码
response.addHeader("Content-Disposition", "attachment;filename=" + returnName);
response.setContentLength(byteArrayOutputStream.size());
ServletOutputStream outputstream = response.getOutputStream(); //取得输出流
byteArrayOutputStream.writeTo(outputstream); //写到输出流
byteArrayOutputStream.close(); //关闭
outputstream.flush(); //刷数据
}
}
/**
* 三个参数 ： ByteArrayOutputStream byteArrayOutputStream,
* HttpServletResponse response,
* String returnName 下载后的表名
*/
/*
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
new DownloadUtil().download(outputStream, response, "出货表.xlsx");
*/
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TemplateExcelData {
private String UUID;
private String name;
private String age;
private String nickName;
private String ip;
private String birthday;
private String card;
private String remarks;
// setter getter ……
}
controller
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@Controller
public class TemplateExcel {
@Autowired
private HttpServletResponse response;
//模板下载
@GetMapping("/printExcel")
public void printExcel(String inputDate) throws IOException {
//先调用service查询要打印的内容,这里先模拟数据
List<TemplateExcelData> list = new ArrayList<>();
list.add(new TemplateExcelData("20211102","王五","18","易烊千玺说我是他的小姑娘","192.168.0.123","2021-10-15","513436200011028752","千玺年的诗"));
list.add(new TemplateExcelData("20211103","赵毛豆","25","会飞的猪","192.168.0.158","2021-11-15","513436200011026255","备注"));
list.add(new TemplateExcelData("20211104","闻人萱","96","海豹突击队队长的烊烊","192.168.35.196","2021-12-15","513436200011027936","时倾_贰捌"));
list.add(new TemplateExcelData("20211105","徐离伟","13","黑不溜秋的二十耶","10.168.0.123","2021-06-15","513436200011026837","烊烊不熬夜"));
list.add(new TemplateExcelData("20211106","邹忠","52","我跟所有人都不熟","20.168.0.255","2021-05-23","513436200011029210","备注哦"));
list.add(new TemplateExcelData("20211107","巩娇","06","易流年不复返","192.896.0.258","2021-01-23","513436200011027178","祁澜致，发给"));
System.out.println("数据长度"+list.size());
//先读取模板
//1.获取模板的路径
// String path = session.getServletContext().getRealPath("/") + "/xlsprint/tOUTPRODUCT.xlsx";
// 推荐使用流读取 InputStream resourceAsStream = this.getClass().getResourceAsStream("xlsprint/tOUTPRODUCT.xlsx");
String path = this.getClass().getClassLoader().getResource("xlsprint/tOUTPRODUCT.xlsx").getPath();
//2.创建Workbook对象
Workbook workbook = new XSSFWorkbook(path);
//3.获取页
Sheet sheetAt = workbook.getSheetAt(0);
//4.处理大标题
//获取标题行
Row row = sheetAt.getRow(0);
//获取标题单元格
Cell cell = row.getCell(1);
//设置标题内容
inputDate = inputDate.replaceAll("-0", "-").replaceAll("-", "年");
// 设置文件名称
cell.setCellValue(inputDate + "用户表");
//5.处理小标题行内容（小标题不改变，所以不用处理）
//6.提取数据列的样式
row = sheetAt.getRow(2);//获取数据的行
CellStyle css[] = new CellStyle[9];//有8个单元格
for (int i = 1; i < css.length; i++) {
cell = row.getCell(i); //获取单元格
css[i] = cell.getCellStyle();//获取样式
}
//7.填充数据
int index = 2;
for (TemplateExcelData productVo : list) {
row = sheetAt.createRow(index);//创建第3行，索引为2
cell = row.createCell(1); //创建单元格
cell.setCellValue(productVo.getUUID()); //设置内容
cell.setCellStyle(css[1]); //样式
/*一次类推*/
cell = row.createCell(2);
cell.setCellValue(productVo.getName());
cell.setCellStyle(css[2]);
cell = row.createCell(3);
cell.setCellValue(productVo.getAge());
cell.setCellStyle(css[3]);
cell = row.createCell(4);
cell.setCellValue(productVo.getNickName());
cell.setCellStyle(css[4]);
cell = row.createCell(5);
cell.setCellValue(productVo.getIp());
cell.setCellStyle(css[5]);
cell = row.createCell(6);
cell.setCellValue(productVo.getBirthday());
cell.setCellStyle(css[6]);
cell = row.createCell(7);
cell.setCellValue(productVo.getCard());
cell.setCellStyle(css[7]);
cell = row.createCell(8);
cell.setCellValue(productVo.getRemarks());
cell.setCellStyle(css[8]);
index++;
}
/**
* 下载
* 三个参数 ： ByteArrayOutputStream byteArrayOutputStream,
* HttpServletResponse response,
* String returnName
*/
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
new DownloadUtil().download(outputStream, response, inputDate + "月用户表.xlsx");
}
}
测试
扫一扫
1552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
