前言:
最近项目有个需求,需要生成多个 excel并打包成 zip下载;由于需要生成的 excel头字段过多,这里有96个时间段的表头,如果建一个有96个字段的实体不太好,还好 easyExcel支持使用 List生成表头。
1、导入 Maven坐标。
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency>
2、详细代码如下:
@Testpublic void noModelWrite() throws IOException {// 生成表头List<String> times = get96Times();List<List<String>> collect = times.stream().map(Arrays::asList).collect(Collectors.toList());List<InputStream> ins = new ArrayList<>();for (int i = 0; i < 2; i++) {// 使用 easyExcel 写到 OutputStreamOutputStream out = new ByteArrayOutputStream();EasyExcel.write(out).head(collect).sheet("sheet1").doWrite(dataList());ins.add(outputStream2InputStream(out));}// 保存的 zip 文件名File zipFile = new File("G:/noModelWrite.zip");// 压缩包内流的文件名List<String> paths = Arrays.asList("1.xlsx", "2.xlsx");ZipUtil.zip(zipFile, paths, ins);}/*** 输出流转输入流;数据量过大请使用其他方法** @param out* @return*/private ByteArrayInputStream outputStream2InputStream(OutputStream out) {Objects.requireNonNull(out);ByteArrayOutputStream bos;bos = (ByteArrayOutputStream) out;return new ByteArrayInputStream(bos.toByteArray());}/*** 生成96个数据点** @return*/private List<String> get96Times() {List<String> res = new ArrayList<>(96);LocalDate localDate = LocalDate.now();LocalDateTime now = LocalDateTime.of(localDate.getYear(), localDate.getMonth(), localDate.getDayOfMonth(), 0, 0);for (int i = 1; i < 96; i++) {String format = now.plusMinutes(15 * i).format(DateTimeFormatter.ofPattern("HH:mm"));res.add(format);}res.add("24:00");return res;}/*** 生成表头对应的数据,这里有10行** @return*/private List<List<Object>> dataList() {List<List<Object>> list = new ArrayList<>();for (int i = 0; i < 10; i++) {List<Object> data = new ArrayList<>();for (int j = 0; j < 96; j++) {BigDecimal bigDecimal = new BigDecimal("0.5");double v = bigDecimal.add(BigDecimal.valueOf(new Random().nextDouble())).doubleValue();data.add(v);}list.add(data);}return list;}
3、效果图
3.1生成的压缩包
3.2Excel内容
4. 封装成工具
/*** 压缩工具类** @author zxb*/public class ZipUtil {/*** 默认编码,使用平台相关编码*/private static final Charset DEFAULT_CHARSET = Charset.defaultCharset();/*** 将文件流压缩到目标流中** @param out 目标流,压缩完成自动关闭* @param fileNames 流数据在压缩文件中的路径或文件名* @param ins 要压缩的源,添加完成后自动关闭流*/public static void zip(OutputStream out, List<String> fileNames, List<InputStream> ins) {zip(out, fileNames.toArray(new String[0]), ins.toArray(new InputStream[0]));}/*** 将文件流压缩到目标流中** @param out 目标流,压缩完成自动关闭* @param fileNames 流数据在压缩文件中的路径或文件名* @param ins 要压缩的源,添加完成后自动关闭流*/public static void zip(File out, List<String> fileNames, List<InputStream> ins) throws IOException {FileOutputStream outputStream = new FileOutputStream(out);zip(outputStream, fileNames.toArray(new String[0]), ins.toArray(new InputStream[0]));outputStream.flush();}/*** 将文件流压缩到目标流中** @param out 目标流,压缩完成自动关闭* @param fileNames 流数据在压缩文件中的路径或文件名* @param ins 要压缩的源,添加完成后自动关闭流*/public static void zip(OutputStream out, String[] fileNames, InputStream[] ins) {ZipOutputStream zipOutputStream = null;try {zipOutputStream = getZipOutputStream(out, DEFAULT_CHARSET);zip(zipOutputStream, fileNames, ins);} catch (IOException e) {throw new SystemException("压缩包导出失败!", e);} finally {IOUtils.closeQuietly(zipOutputStream);}}/*** 将文件流压缩到目标流中** @param zipOutputStream 目标流,压缩完成不关闭* @param fileNames 流数据在压缩文件中的路径或文件名* @param ins 要压缩的源,添加完成后自动关闭流* @throws IOException IO异常*/public static void zip(ZipOutputStream zipOutputStream, String[] fileNames, InputStream[] ins) throws IOException {if (ArrayUtils.isEmpty(fileNames) || ArrayUtils.isEmpty(ins)) {throw new IllegalArgumentException("文件名不能为空!");}if (fileNames.length != ins.length) {throw new IllegalArgumentException("文件名长度与输入流长度不一致!");}for (int i = 0; i < fileNames.length; i++) {add(ins[i], fileNames[i], zipOutputStream);}}/*** 添加文件流到压缩包,添加后关闭流** @param in 需要压缩的输入流,使用完后自动关闭* @param fileName 压缩的路径* @param out压缩文件存储对象* @throws IOException IO异常*/private static void add(InputStream in, String fileName, ZipOutputStream out) throws IOException {if (null == in) {return;}try {out.putNextEntry(new ZipEntry(fileName));IOUtils.copy(in, out);} catch (IOException e) {throw new IOException(e);} finally {IOUtils.closeQuietly(in);closeEntry(out);}}/*** 获得 {@link ZipOutputStream}** @param out压缩文件流* @param charset 编码* @return {@link ZipOutputStream}*/private static ZipOutputStream getZipOutputStream(OutputStream out, Charset charset) {if (out instanceof ZipOutputStream) {return (ZipOutputStream) out;}return new ZipOutputStream(out, ObjectUtils.defaultIfNull(charset, DEFAULT_CHARSET));}/*** 关闭当前Entry,继续下一个Entry** @param out ZipOutputStream*/private static void closeEntry(ZipOutputStream out) {try {out.closeEntry();} catch (IOException e) {// ignore}}}
参考文档:
hutool工具箱文档
EasyExcel文档