• 生成带分表和水印的excel压缩文件

    功能描述

    将查询结果生成带分表和水印的excel压缩文件


    功能点

    1、将查询结果导出为excel文件

    2、每个表格存放50万条数据，超过50万条数据，生成新的分表

    3、生成的表格需要添加水印

    4、将生成的全部分表，打包成zip压缩文件


    引入依赖

    1.  
    2. 		<dependency>
    3. 			<groupId>com.alibabagroupId>
    4. 			<artifactId>easyexcelartifactId>
    5. 			<version>3.3.2version>
    6. 			<exclusions>
    7. 				<exclusion>
    8. 					<artifactId>commons-compressartifactId>
    9. 					<groupId>org.apache.commonsgroupId>
    10. 				exclusion>
    11. 			exclusions>
    12. 		dependency>
    13. 		<dependency>
    14. 			<groupId>org.apache.poigroupId>
    15. 			<artifactId>ooxml-schemasartifactId>
    16. 			<version>1.4version>
    17. 		dependency>
    18. 		
    19. 		<dependency>
    20. 			<groupId>net.lingala.zip4jgroupId>
    21. 			<artifactId>zip4jartifactId>
    22. 			<version>1.3.2version>
    23. 		dependency>
    24. 		<dependency>
    25. 			<groupId>cn.hutoolgroupId>
    26. 			<artifactId>hutool-allartifactId>
    27. 			<version>5.8.19version>
    28. 			<scope>compilescope>
    29. 		dependency>
    1. package com.cusc.product.common.util.excel;
    2.  
    3. import com.alibaba.excel.EasyExcel;
    4. import com.alibaba.excel.EasyExcelFactory;
    5. import com.alibaba.excel.ExcelWriter;
    6. import com.alibaba.excel.write.metadata.WriteSheet;
    7. import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
    8. import com.cusc.product.common.util.WaterMarkCommonUtil;
    9. import lombok.extern.slf4j.Slf4j;
    10. import org.apache.commons.collections4.CollectionUtils;
    11. import org.apache.commons.lang3.StringUtils;
    12. import org.apache.poi.ss.usermodel.Workbook;
    13.  
    14. import java.io.File;
    15. import java.io.IOException;
    16. import java.util.LinkedHashSet;
    17. import java.util.List;
    18. import java.util.Objects;
    19. import java.util.Set;
    20. import java.util.UUID;
    21. import java.util.function.Supplier;
    22.  
    23. /**
    24.  * Excel文件生成
    25.  *
    26.  * @author liubin
    27.  */
    28. @Slf4j
    29. public class EasyExcelWriteCommonUtil {
    30.  
    31.     /**
    32.      * 文件行数限制
    33.      */
    34.     private static final int EXCEL_LIMIT_NUM = 500000;
    35.  
    36.  
    37.     /**
    38.      * 数据写入Excel文件，多文件 自定义表头
    39.      *
    40.      * @param suppliers 自定义函数
    41.      * @param listFiled 表头
    42.      * @param dirName   期待的压缩文件的名称
    43.      * @return zip压缩后的文件
    44.      */
    45.     public static File createMultiFileExcelZipFiles(List> suppliers, List listFiled,
    46.                                                     Class clzss, String dirName, String waterMarkUserName, String waterMarkUsePhone) {
    47.         File zipFile;
    48.         // 创建临时目录
    49.         if (Objects.isNull(dirName)) {
    50.             dirName = UUID.randomUUID().toString();
    51.         }
    52.         File localDir = createDir(FileBaseCommonUtil.TEMPORARY_DIRECTORY_PATH + "/" + dirName);
    53.         log.info("临时目录: " + localDir.getPath());
    54.         String sheet = "Sheet1";
    55.         // 文件批次编号
    56.         int fileId = 1;
    57.         // 单个文件累积写入数据行数
    58.         int number = 0;
    59.         ExcelWriter excelWriter = null;
    60.         WriteSheet writeSheet = null;
    61.         //只导出这几列
    62.         Set includeColumnFiledNames = new LinkedHashSet<>();
    63.         for (int j = 0; j < listFiled.size(); j++) {
    64.             includeColumnFiledNames.add(listFiled.get(j));
    65.         }
    66.         try {
    67.             for (int i = 0; i < suppliers.size(); i++) {
    68.                 List dataList = suppliers.get(i).get();
    69.                 if (dataList.size() > EXCEL_LIMIT_NUM) {
    70.                     log.error("Supplier返回数据量最大不可超过{}", EXCEL_LIMIT_NUM);
    71.                     throw new RuntimeException(String.format("Supplier返回数据量最大不可超过%s", EXCEL_LIMIT_NUM));
    72.                 }
    73.                 number += dataList.size();
    74.                 if (number > EXCEL_LIMIT_NUM) {
    75.                     // 数据量累积超出了， 需要新建文件记录
    76.                     int comNum = number - EXCEL_LIMIT_NUM;
    77.                     List compensate = dataList.subList(dataList.size() - comNum, dataList.size());
    78.                     List oldDataList = dataList.subList(0, dataList.size() - comNum);
    79.  
    80.                     // 旧文件追加数据
    81.                     File oldFile = new File(localDir, dirName + "_" + fileId + ".xlsx");
    82.                     if (oldFile.exists()) {
    83.                         excelWriter.write(oldDataList, writeSheet);
    84.                         // 添加水印
    85.                         Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
    86.                         WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
    87.                         excelWriter.finish();
    88.                     }
    89.                     if (CollectionUtils.isNotEmpty(compensate)) {
    90.                         // 写入新的文件数据
    91.                         File file = new File(localDir, dirName + "_" + (++fileId) + ".xlsx");
    92.                         excelWriter = EasyExcelFactory.write(file.getAbsolutePath()).build();
    93.                         writeSheet = EasyExcel.writerSheet(i, sheet).head(clzss).includeColumnFieldNames(includeColumnFiledNames).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
    94. //                        writeSheet = EasyExcelFactory.writerSheet(sheet).build();
    95.                         excelWriter.write(dataList, writeSheet);
    96.                         // 添加水印
    97.                         Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
    98.                         WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
    99.                         // 重置记录数
    100.                         number = compensate.size();
    101.                     }
    102.                     if (CollectionUtils.isNotEmpty(oldDataList)) {
    103.                         oldDataList.clear();
    104.                     }
    105.                     if (CollectionUtils.isNotEmpty(compensate)) {
    106.                         compensate.clear();
    107.                     }
    108.                     if (CollectionUtils.isNotEmpty(dataList)) {
    109.                         dataList.clear();
    110.                     }
    111.                     continue;
    112.                 }
    113.                 File file = new File(localDir, dirName + "_" + fileId + ".xlsx");
    114.                 if (file.exists()) {
    115.                     excelWriter.write(dataList, writeSheet);
    116.                     // 添加水印
    117.                     Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
    118.                     WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
    119.                 } else {
    120.                     excelWriter = EasyExcelFactory.write(file.getAbsolutePath()).build();
    121.                     writeSheet = EasyExcel.writerSheet(i, sheet).head(clzss).includeColumnFieldNames(includeColumnFiledNames).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
    122.                     excelWriter.write(dataList, writeSheet);
    123.                     // 添加水印
    124.                     Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
    125.                     WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
    126.                 }
    127.                 if (CollectionUtils.isNotEmpty(dataList)) {
    128.                     dataList.clear();
    129.                 }
    130.             }
    131.         } catch (RuntimeException e) {
    132.             log.error("数据导出失败", e);
    133.             throw new RuntimeException(e);
    134.         } catch (IOException e) {
    135.             log.error("数据导出失败", e);
    136.             throw new RuntimeException(e);
    137.         } finally {
    138.             if (excelWriter != null) {
    139.                 excelWriter.finish();
    140.             }
    141.         }
    142.  
    143.         String zipPath = Zip4jCommonUtil.zip(localDir.getPath());
    144.         deleteFile(localDir);
    145.  
    146.         if (StringUtils.isEmpty(zipPath)) {
    147.             log.error("文件压缩失败");
    148.             throw new RuntimeException("文件压缩失败");
    149.         }
    150.         zipFile = new File(zipPath);
    151.         log.info("临时目录压缩后路径: " + zipPath);
    152.         return zipFile;
    153.     }
    154.  
    155.     /**
    156.      * 删除文件目录
    157.      *
    158.      * @param file
    159.      * @return
    160.      */
    161.     private static Boolean deleteFile(File file) {
    162.         // 判断文件不为null或文件目录存在
    163.         if (file == null || !file.exists()) {
    164.             log.error("文件删除失败,请检查文件是否存在以及文件路径是否正确");
    165.             return false;
    166.         }
    167.         // 获取目录下子文件
    168.         File[] files = file.listFiles();
    169.         // 遍历该目录下的文件对象
    170.         for (File f : files) {
    171.             // 判断子目录是否存在子目录,如果是文件则删除
    172.             if (f.isDirectory()) {
    173.                 // 递归删除目录下的文件
    174.                 deleteFile(f);
    175.             } else {
    176.                 // 文件删除
    177.                 f.delete();
    178.             }
    179.         }
    180.         // 文件夹删除
    181.         file.delete();
    182.         return true;
    183.     }
    184.  
    185.     /**
    186.      * 创建目录
    187.      *
    188.      * @param localDirPath 目录路径
    189.      * @return File对象
    190.      */
    191.     private static File createDir(String localDirPath) {
    192.         File localDir = new File(localDirPath);
    193.         if (!localDir.exists()) {
    194.             localDir.mkdirs();
    195.         }
    196.         return localDir;
    197.     }
    198.  
    199. }
    1. package com.cusc.product.common.util;
    2.  
    3. import cn.hutool.core.date.DateUtil;
    4. import cn.hutool.core.util.ReflectUtil;
    5. import org.apache.poi.openxml4j.opc.PackagePartName;
    6. import org.apache.poi.openxml4j.opc.PackageRelationship;
    7. import org.apache.poi.openxml4j.opc.TargetMode;
    8. import org.apache.poi.ss.usermodel.Workbook;
    9. import org.apache.poi.xssf.streaming.SXSSFSheet;
    10. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    11. import org.apache.poi.xssf.usermodel.XSSFPictureData;
    12. import org.apache.poi.xssf.usermodel.XSSFRelation;
    13. import org.apache.poi.xssf.usermodel.XSSFSheet;
    14. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    15.  
    16. import javax.imageio.ImageIO;
    17. import java.awt.*;
    18. import java.awt.image.BufferedImage;
    19. import java.io.ByteArrayOutputStream;
    20. import java.io.IOException;
    21.  
    22. public class WaterMarkCommonUtil {
    23.  
    24.  
    25.     /**
    26.      * Excel 导出添加水印
    27.      *
    28.      * @param workbook ExcelWorkbook
    29.      * @param userName 导出人姓名
    30.      */
    31.     public static void insertWaterMarkNamePhoneToXlsx(Workbook workbook, String userName, String phone) throws IOException {
    32.         String date = DateUtil.now();
    33.         String waterMarkText = userName + "\n" + phone;
    34.         if (workbook instanceof SXSSFWorkbook) {
    35.             insertWaterMarkTextToXlsx((SXSSFWorkbook) workbook, waterMarkText);
    36.         } else if (workbook instanceof XSSFWorkbook) {
    37.             insertWaterMarkTextToXlsx((XSSFWorkbook) workbook, waterMarkText);
    38.         }
    39.         //throw new RemoteException("HSSFWorkbook 模式不支持 Excel 水印");
    40.     }
    41.  
    42.     /**
    43.      * Excel 导出添加水印
    44.      *
    45.      * @param workbook ExcelWorkbook
    46.      * @param userName 导出人姓名
    47.      */
    48.     public static void insertWaterMarkTextToXlsx(Workbook workbook, String userName) throws IOException {
    49.         String date = DateUtil.now();
    50.         String waterMarkText = userName + "\n" + date;
    51.         if (workbook instanceof SXSSFWorkbook) {
    52.             insertWaterMarkTextToXlsx((SXSSFWorkbook) workbook, waterMarkText);
    53.         } else if (workbook instanceof XSSFWorkbook) {
    54.             insertWaterMarkTextToXlsx((XSSFWorkbook) workbook, waterMarkText);
    55.         }
    56.         //throw new RemoteException("HSSFWorkbook 模式不支持 Excel 水印");
    57.     }
    58.  
    59.  
    60.     /**
    61.      * 给 Excel 添加水印
    62.      *
    63.      * @param workbook      SXSSFWorkbook
    64.      * @param waterMarkText 水印文字内容
    65.      */
    66.     private static void insertWaterMarkTextToXlsx(SXSSFWorkbook workbook, String waterMarkText) throws IOException {
    67.         BufferedImage image = createWatermarkImage(waterMarkText);
    68.         ByteArrayOutputStream imageOs = new ByteArrayOutputStream();
    69.         ImageIO.write(image, "png", imageOs);
    70.         int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
    71.         XSSFPictureData pictureData = (XSSFPictureData) workbook.getAllPictures().get(pictureIdx);
    72.         for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
    73.             SXSSFSheet sheet = workbook.getSheetAt(i);
    74.             //这里由于 SXSSFSheet 没有 getCTWorksheet() 方法，通过反射取出 _sh 属性
    75.             XSSFSheet shReflect = (XSSFSheet) ReflectUtil.getFieldValue(sheet, "_sh");
    76.             PackagePartName ppn = pictureData.getPackagePart().getPartName();
    77.             String relType = XSSFRelation.IMAGES.getRelation();
    78.             PackageRelationship pr = shReflect.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
    79.             shReflect.getCTWorksheet().addNewPicture().setId(pr.getId());
    80.         }
    81.         imageOs.close();
    82.     }
    83.  
    84.  
    85.     /**
    86.      * 给 Excel 添加水印
    87.      *
    88.      * @param workbook      XSSFWorkbook
    89.      * @param waterMarkText 水印文字内容
    90.      */
    91.     private static void insertWaterMarkTextToXlsx(XSSFWorkbook workbook, String waterMarkText) throws IOException {
    92.         BufferedImage image = createWatermarkImage(waterMarkText);
    93.         ByteArrayOutputStream imageOs = new ByteArrayOutputStream();
    94.         ImageIO.write(image, "png", imageOs);
    95.         int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
    96.         XSSFPictureData pictureData = workbook.getAllPictures().get(pictureIdx);
    97.         for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
    98.             XSSFSheet sheet = workbook.getSheetAt(i);
    99.             PackagePartName ppn = pictureData.getPackagePart().getPartName();
    100.             String relType = XSSFRelation.IMAGES.getRelation();
    101.             PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
    102.             sheet.getCTWorksheet().addNewPicture().setId(pr.getId());
    103.         }
    104.         imageOs.close();
    105.     }
    106.  
    107.  
    108.     /**
    109.      * 创建水印图片
    110.      *
    111.      * @param waterMark 水印文字
    112.      */
    113.     private static BufferedImage createWatermarkImage(String waterMark) {
    114.         String[] textArray = waterMark.split("\n");
    115.         Font font = new Font("microsoft-yahei", Font.PLAIN, 24);
    116. //        int width = 500;
    117. //        int height = 400;
    118.         int width = 300;
    119.         int height = 200;
    120.  
    121.         BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
    122.         // 背景透明 开始
    123.         Graphics2D g = image.createGraphics();
    124.         image = g.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
    125.         g.dispose();
    126.         // 背景透明 结束
    127.         g = image.createGraphics();
    128.         g.setColor(new Color(237, 235, 233));// 设定画笔颜色
    129.         g.setFont(font);// 设置画笔字体
    130.         //   g.shear(0.1, -0.26);// 设定倾斜度
    131.  
    132. //        设置字体平滑
    133.         g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
    134.  
    135.         //文字从中心开始输入，算出文字宽度，左移动一半的宽度，即居中
    136.         FontMetrics fontMetrics = g.getFontMetrics(font);
    137.  
    138.         // 水印位置
    139.         int x = width / 2;
    140.         int y = height / 2;
    141.         // 设置水印旋转
    142.         g.rotate(Math.toRadians(-40), x, y);
    143.         for (String s : textArray) {
    144.             // 文字宽度
    145.             int textWidth = fontMetrics.stringWidth(s);
    146.             g.drawString(s, x - (textWidth / 2), y);// 画出字符串
    147.             y = y + font.getSize();
    148.         }
    149.  
    150.         g.dispose();// 释放画笔
    151.         return image;
    152.     }
    153.  
    154.  
    155. }

  • 相关阅读:
    MM32F0020 UART1中断接收
    Linux的打包和压缩
    随手笔记-Jackson -＞ @JsonFormat -＞ 失真问题解决
    自动化测试框架（二）读取配置文件中内容
    麒麟桌面系统CVE-2024-1086漏洞修复
    Java.Integer.bitCount(int)源码解析
    HarmonyOS4.0系统性深入开发34栅格布局（GridRow/GridCol）
    第十章《日期与时间》第9节：日期时间对象的转换
    请求的接口响应状态为已取消的原因
    Spring IoC源码：createBean
  • 原文地址：https://blog.csdn.net/liubin5620/article/details/134216770