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

该问题是部署到服务器上遇到的，简单记录一下，具体原因和解决方案可参考以下两篇文章

• docker部署使用EasyExcel导出因未安装中文字体报错及处理方法

• EasyExcel导出报空指针异常FontConfiguration.getVersion

二、下载文件

参考链接

此处不再赘述直接上链接，想尝试其他不同写法的可参考这篇文章