EasyExcel实现excel文件重复多次写入和导出&下载文件
一、EasyExcel实现excel文件的导出
导入依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
</dependencies>
创建excel对应的实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
@ContentRowHeight(100)
@ColumnWidth(100 / 8)
public class ImageDemoData {
@ExcelProperty(value = "图片数据，导出到excel文件，除了byte[]之外还可使用File InputStream String URL类型存储图片，具体可参考官方文档")
private byte[] image;
@ExcelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "年龄")
private Integer age;
}
excel文件重复多次写入(单个sheet) & 下载导出文件
@Slf4j
@RestController
public class TestController {
@Value("/tmp/")
private String path;
@GetMapping("/test")
public String test() {
return "<h1>hello world</h1>";
}
public static String getTimeStr() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
return sdf.format(new Date());
}
@GetMapping("/export")
public void export(HttpServletRequest request, HttpServletResponse response) {
String fileName = getTimeStr();
String filePath = path + fileName + ".xlsx";
File parentFolder = new File(path);
if (!parentFolder.exists()) {
parentFolder.mkdirs();
}
// 1. 导出excel文件
// 多次查询分页数据，重复写入同一个excel
try (ExcelWriter excelWriter = EasyExcel.write(filePath, ImageDemoData.class).build()) {
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
for (int pageNum = 1; pageNum <= 5; pageNum++) {
// 模拟分页去数据库查询数据 实际可以去数据库查询每一页的数据
List<ImageDemoData> list = findPage(pageNum);
// 写入
excelWriter.write(list, writeSheet);
}
}
// 2. 下载excel文件
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
try {
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
} catch (UnsupportedEncodingException e) {
log.error("导出excel文件名编码失败");
e.printStackTrace();
}
response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
try (BufferedInputStream bis = new BufferedInputStream(new FileInputStream(filePath))) {
byte[] buff = new byte[1024];
OutputStream os = response.getOutputStream();
int i;
while ((i = bis.read(buff)) != -1) {
os.write(buff, 0, i);
os.flush();
}
} catch (IOException e) {
log.error("导出excel文件失败");
e.printStackTrace();
}
}
private List<ImageDemoData> findPage(int pageNum) {
return MyData.DB.get(pageNum);
}
}
模拟DB的数据
这里直接使用内存数据，来模拟分页查询数据库这一操作
public class MyData {
public static byte[] getImage() {
// 注意：该路径是从当前磁盘根目录开始的
String imagePath = "/" + "img.jpg";
try {
return FileUtils.readFileToByteArray(new File(imagePath));
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public static Map<Integer, List<ImageDemoData>> DB = new HashMap<Integer, List<ImageDemoData>>(){{
put(1, new ArrayList<ImageDemoData>(){{
add(new ImageDemoData(getImage(), "张三1", 22));
add(new ImageDemoData(getImage(), "李四1", 22));
add(new ImageDemoData(getImage(), "王五1", 22));
add(new ImageDemoData(getImage(), "赵柳1", 22));
add(new ImageDemoData(getImage(), "田七1", 22));
}});
put(2, new ArrayList<ImageDemoData>(){{
add(new ImageDemoData(getImage(), "张三2", 24));
add(new ImageDemoData(getImage(), "李四2", 24));
add(new ImageDemoData(getImage(), "王五2", 24));
add(new ImageDemoData(getImage(), "赵柳2", 24));
add(new ImageDemoData(getImage(), "田七2", 24));
}});
put(3, new ArrayList<ImageDemoData>(){{
add(new ImageDemoData(getImage(), "张三3", 26));
add(new ImageDemoData(getImage(), "李四3", 26));
add(new ImageDemoData(getImage(), "王五3", 26));
add(new ImageDemoData(getImage(), "赵柳3", 26));
add(new ImageDemoData(getImage(), "田七3", 26));
}});
put(4, new ArrayList<ImageDemoData>(){{
add(new ImageDemoData(getImage(), "张三4", 28));
add(new ImageDemoData(getImage(), "李四4", 28));
add(new ImageDemoData(getImage(), "王五4", 28));
add(new ImageDemoData(getImage(), "赵柳4", 28));
add(new ImageDemoData(getImage(), "田七4", 28));
}});
put(5, new ArrayList<ImageDemoData>(){{
add(new ImageDemoData(getImage(), "张三5", 30));
add(new ImageDemoData(getImage(), "李四5", 30));
add(new ImageDemoData(getImage(), "王五5", 30));
}});
}};
}
测试
运行SpringBoot启动类，访问 http://localhost:8080/export 进行测试
可能遇到的问题 —— NullPointerException: FontConfiguration.getVersion
该问题是部署到服务器上遇到的，简单记录一下，具体原因和解决方案可参考以下两篇文章
二、下载文件
此处不再赘述直接上链接，想尝试其他不同写法的可参考这篇文章
