This action will force synchronization from ychng577/excel-batch-picture-support, which will overwrite any changes that you have made since you forked the repository, and can not be recovered!!!
Synchronous operation will process in the background and will refresh the page when finishing processing. Please be patient.
用户在导出统计数据时需要导出大量图片.目前用的比较多的poi导出能支持批量导出大量数据(不包括自媒体).但是当需要导出大量图片时,即使设置了flushSize ,但是对于图片对象却没有效果,图片在内存中无法释放,写的图片越多,占用内存越大,导致频繁GC,甚至OOM
excel文件由声明,表数据,单元格数据,媒体文件等等组件组成, 这些组件分别对应了不同的数据单元.只要把数据分别写入对应的组件,最后构建成一个需要的excel文件.
采用流式方法写入文件,不会导致内存堆积而占用太多系统资源,有效避免频繁GC问题
经测试,可以生成几个G的文件。(保证生成的文件没问题,文件是否能打开由使用者计算机决定)
在项目的pom.xml的dependencies中加入以下内容: 点击查看最新版本 ${excel-x.version}
<dependency>
<groupId>top.minwk</groupId>
<artifactId>excel-x</artifactId>
<version>${excel-x.version}</version>
</dependency>
@GetMapping("/export/lastversion/{row}")
public void exportLastVersion(HttpServletResponse response, @PathVariable int row) throws IOException {
/*
操作窗口
当写入excel数据行数大于flushSize时{@link Sheet.SheetHandler#createRow(int)},
会刷新数据到流,调用该方法
{@link com.ibiz.excel.picture.support.flush.DrawingXmlRelsHandler#copyPictureAppendDrawingRelsXML(Sheet, Picture)}
将图片刷新在磁盘中
不会占用内存空间
flushSize = -1 时不刷新流
*/
Workbook workBook = Workbook.getInstance(1);
Sheet sheet = workBook.createSheet("测试");
// 给标题行加上背景色,加颜色时,会对字体加粗
sheet.addCellStyle(new CellStyle(0, "66cc66"));
List<UserPicture> list = new ArrayList<>();
UserPicture userPicture;
for (int r = 0; r < row; r++) {
userPicture = new UserPicture();
userPicture.setAge(15);
userPicture.setName("测试-" + r);
// 导出本地单张图片
userPicture.setPicture("E:\\test\\img\\1.jpg");
// 导出url单张图片
userPicture.setHeaderPicture("https://portrait.gitee.com/uploads/avatars/user/552/1657608_mwk719_1641537497.png");
// 导出本地图片集合
userPicture.setPictures(Arrays.asList("E:\\test\\img\\1.jpg","E:\\test\\img\\2.jpg"));
// 导出url图片集合
userPicture.setUrlPictures(Arrays.asList("https://portrait.gitee.com/uploads/avatars/user/552/1657608_mwk719_1641537497.png",
"https://img2.baidu.com/it/u=2602880481,728201544&fm=26&fmt=auto"));
list.add(userPicture);
}
sheet.write(UserPicture.class).createRow(list);
WebUtil.writeExcel(workBook, "最新使用示例代码导出".concat(String.valueOf(System.currentTimeMillis())).concat(".xlsx"), response);
}
/**
* @auther 喻场
* @date 2020/7/813:41
*/
public class UserPicture {
public UserPicture() {
}
@ExportModel( sort = 0, title = "姓名")
private String name;
@ExportModel(sort = 1, title = "年龄")
private Integer age;
@ExportModel(sort = 3, title = "部门")
private String department;
@ExportModel(sort = 2, isPicture = true, title = "图片1")
private String picture;
@ExportModel(sort = 4, isPicture = true, title = "图片2")
private String headerPicture;
@ExportModel(sort = 5, isPicture = true, title = "多图片")
private List<String> pictures;
@ExportModel(sort = 6, isPicture = true, title = "url多图片")
private List<String> urlPictures;
public UserPicture(String name, Integer age, String department, String picture) {
this.name = name;
this.age = age;
this.department = department;
this.picture = picture;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getPicture() {
return picture;
}
public void setPicture(String picture) {
this.picture = picture;
}
public String getHeaderPicture() {
return headerPicture;
}
public void setHeaderPicture(String headerPicture) {
this.headerPicture = headerPicture;
}
public List<String> getPictures() {
return pictures;
}
public void setPictures(List<String> pictures) {
this.pictures = pictures;
}
public List<String> getUrlPictures() {
return urlPictures;
}
public void setUrlPictures(List<String> urlPictures) {
this.urlPictures = urlPictures;
}
}
实体对象需要实现BizExcelPojoInterface接口
@GetMapping("/export/dynamic-config-header")
public void exportDynamicConfigHeader(HttpServletResponse response) throws IOException {
// 模拟需要导出的数据集合
List<Student> students = new ArrayList<>();
students.add(new Student("李四", 16, null, null, 0));
students.add(new Student("张三", 17, null,
Arrays.asList("https://portrait.gitee.com/uploads/avatars/user/552/1657608_mwk719_1641537497.png",
"https://img2.baidu.com/it/u=2602880481,728201544&fm=26&fmt=auto"), 1));
students.add(new Student("王五", 15, IMG_PATH_1, null, 2));
// 配置导出excel的表头、顺序、对应导出的数据集合的字段、是否是图片、单元格宽度等
List<BizExcelRel> excels = new ArrayList<>();
excels.add(new BizExcelRel("姓名", "name", 2));
excels.add(new BizExcelRel("年龄", "age", 3));
excels.add(new BizExcelRel("表现", "performance", 4));
excels.add(new BizExcelRel("头像", "headPicture", 5, true, 20));
excels.add(new BizExcelRel("相册", "album", 6, true));
// 创建excel
Workbook workBook = Workbook.getInstance(100);
Sheet sheet = workBook.createSheet("测试");
// 创建样式
CellStyle cellStyle = new CellStyle(0, "F0F0F0");
// 创建数据字典
Map<String, String> performanceMap = new HashMap<>(3);
performanceMap.put("0", "一般");
performanceMap.put("1", "良好");
performanceMap.put("2", "优秀");
// 构建sheet
ExcelTableProcessor.sheet(sheet)
// 添加样式
.addCellStyle(cellStyle)
// 添加对应属性字段的数据字典
.registryEnumMap("performance", performanceMap)
// 构建excel
.buildExcel(excels, students);
WebUtil.writeExcel(workBook, "ExportExampleDynamicConfigHeader".concat(String.valueOf(System.currentTimeMillis())).concat(".xlsx"), response);
}
public class Student implements BizExcelPojoInterface {
public Student(String name, Integer age) {
this.name = name;
this.age = age;
}
public Student(String name, Integer age, String headPicture) {
this.name = name;
this.age = age;
this.headPicture = headPicture;
}
public Student(String name, Integer age, String headPicture, List<String> album, Integer performance) {
this.name = name;
this.age = age;
this.headPicture = headPicture;
this.album = album;
this.performance = performance;
}
private String name;
private Integer age;
private String headPicture;
/**
* 相册
*/
private List<String> album;
/**
* 表现 0一般;1良好;2优秀
*/
private Integer performance;
public Integer getPerformance() {
return performance;
}
public void setPerformance(Integer performance) {
this.performance = performance;
}
public List<String> getAlbum() {
return album;
}
public void setAlbum(List<String> album) {
this.album = album;
}
public String getHeadPicture() {
return headPicture;
}
public void setHeadPicture(String headPicture) {
this.headPicture = headPicture;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
excel含图片导出demo地址,具体使用以后缀最新日期为准,其他示例仅供测试
设置项目jvm堆栈大小都是20m
-Xms20m -Xmx20m -Dfile.encoding=UTF-8 -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=D:\log\springlearn.hprof
复制上方 【最新使用示例代码】到项目中
找一堆图片随机添加到UserPicture中
导出一个5000条的记录,在最大堆栈占用为20m的情况下,导出excel大小为700m,未发生内存溢出情况
点击可跳转链接可以查看功能使用示例
EXCEL由几大组件构建而成
注解
需要导出的字段使用ExportModel注解,程序会自动识别标题,合并单元格,插入图片
/**
* 导出模型
* 使用该注解程序会根据相应属性对单元格做设置
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface ExportModel {
/**排序*/
int sort() default 0;
/**是否是图片*/
boolean isPicture() default false;
/**表头*/
String title() default "";
/**已这列为准进行合并列*/
boolean mergeMaster() default false;
/**这一列是否要合并*/
boolean merge() default false;
}
创建一个工作文件,需要经过一个工作周期,分别对应init(初始化) , write(写数据), close(关闭)
init 初始化各个组件,构建流并写入组件头部信息 write 写入内容 close 写入剩余内存中的数据到流, 写入组件尾部信息,关闭工作文件
init, write, close分别对应了三种事件,程序使用事件驱动机制,当监听器监听到组件在执行对应注册事件时分别进行相应操作
事件
监听器
部分代码
public interface WorkbookEvent<E extends ContentListener> {
WorkbookEvent registry(E listener);
void onEvent(Sheet sheet);
}
public class InitListener extends AbstractContentListener {
@Override
public void invoke(Sheet sheet) {
repositories.forEach(r -> r.write(sheet));
}
}
触发init事件,最后一行
void init() {
sheetContext = SheetContext.getInstance(Sheet.this);
ContentListener init = ListenerFactory.getInstance(InitListener.class);
ContentListener flush = ListenerFactory.getInstance(FlushListener.class);
ContentListener close = ListenerFactory.getInstance(CloseListener.class);
sheetContext.getRepositoryHolder().forEach((alias, rep) -> {
if (!closeAlias.contains(alias)) {
//app.xml workbook.xml最后写,考虑getSheetName
init.addRepository(rep);
flush.addRepository(rep);
}
close.addRepository(rep);
});
sheetContext.getEvents().add(EventFactory.getInstance(InitEvent.class).registry(init));
sheetContext.getEvents().add(EventFactory.getInstance(FlushEvent.class).registry(flush));
sheetContext.getEvents().add(EventFactory.getInstance(CloseEvent.class).registry(close));
//创建组件文件
sheetContext.getEvents().stream().filter(e -> e instanceof InitEvent).forEach(e -> e.onEvent(Sheet.this));
}
Write方法很简单,只是把内存中的缓存写入流。
public void write(Sheet sheet) {
try {
if (null == write) {
return;
}
writeToStream();
} catch (Exception e) {
throw new RuntimeException("写文件异常 :" + file.getAbsolutePath(), e);
}
}
private void writeToStream() {
try {
if (!hasWriteXmlHead && writeXmlHead) {
write.write(AutoXmlHeadEndContent.XML_HEAD);
hasWriteXmlHead = true;
}
write.write(content.toString());
clearContent();
} catch (IOException e) {
throw new RuntimeException("写文件异常 :" + file.getAbsolutePath(), e);
}
}
在写入流之前,需要把写入的数据先写入缓存中,这里使用代理。部分代码:
public class Sheet1Handler implements InvocationHandler {
private IRepository target;
public Sheet1Handler(IRepository proxy) {
this.target = proxy;
}
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
Sheet sheet = (Sheet)args[0];
if (method.getName().equals("write")) {
List<Row> rows = sheet.getRows();
if (!rows.isEmpty()) {
//未刷新过说明没有写入过流,这里主要为了写表头
//如果写过了,则从脚标1开始,原因是为了对比合并单元格在row1中保存上一次刷新的最后一条数据
int subIndex = !sheet.hasFlush() ? 0 : 1;
setMergeCell(sheet, rows);
rows.subList(subIndex, rows.size()).stream().forEach(r -> writeSheetXML(r));
}
} else if (method.getName().equals("close")) {
setEndSheetData();
setMergeContent(sheet);
}
return method.invoke(target, args);
}
}
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。