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

liubin5620

已于 2023-11-04 12:40:19 修改

阅读量745

点赞数

文章标签： excel

于 2023-11-04 12:36:31 首次发布

本文链接：https://blog.csdn.net/liubin5620/article/details/134216770

版权

功能描述

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

功能点

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

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

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

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

引入依赖


		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>3.3.2</version>
			<exclusions>
				<exclusion>
					<artifactId>commons-compress</artifactId>
					<groupId>org.apache.commons</groupId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>ooxml-schemas</artifactId>
			<version>1.4</version>
		</dependency>
		<!-- zip处理工具包 -->
		<dependency>
			<groupId>net.lingala.zip4j</groupId>
			<artifactId>zip4j</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>cn.hutool</groupId>
			<artifactId>hutool-all</artifactId>
			<version>5.8.19</version>
			<scope>compile</scope>
		</dependency>

package com.cusc.product.common.util.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.cusc.product.common.util.WaterMarkCommonUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.IOException;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Objects;
import java.util.Set;
import java.util.UUID;
import java.util.function.Supplier;

/**
 * Excel文件生成
 *
 * @author liubin
 */
@Slf4j
public class EasyExcelWriteCommonUtil {

    /**
     * 文件行数限制
     */
    private static final int EXCEL_LIMIT_NUM = 500000;


    /**
     * 数据写入Excel文件，多文件 自定义表头
     *
     * @param suppliers 自定义函数
     * @param listFiled 表头
     * @param dirName   期待的压缩文件的名称
     * @return zip压缩后的文件
     */
    public static File createMultiFileExcelZipFiles(List<Supplier<List>> suppliers, List<String> listFiled,
                                                    Class clzss, String dirName, String waterMarkUserName, String waterMarkUsePhone) {
        File zipFile;
        // 创建临时目录
        if (Objects.isNull(dirName)) {
            dirName = UUID.randomUUID().toString();
        }
        File localDir = createDir(FileBaseCommonUtil.TEMPORARY_DIRECTORY_PATH + "/" + dirName);
        log.info("临时目录: " + localDir.getPath());
        String sheet = "Sheet1";
        // 文件批次编号
        int fileId = 1;
        // 单个文件累积写入数据行数
        int number = 0;
        ExcelWriter excelWriter = null;
        WriteSheet writeSheet = null;
        //只导出这几列
        Set<String> includeColumnFiledNames = new LinkedHashSet<>();
        for (int j = 0; j < listFiled.size(); j++) {
            includeColumnFiledNames.add(listFiled.get(j));
        }
        try {
            for (int i = 0; i < suppliers.size(); i++) {
                List dataList = suppliers.get(i).get();
                if (dataList.size() > EXCEL_LIMIT_NUM) {
                    log.error("Supplier返回数据量最大不可超过{}", EXCEL_LIMIT_NUM);
                    throw new RuntimeException(String.format("Supplier返回数据量最大不可超过%s", EXCEL_LIMIT_NUM));
                }
                number += dataList.size();
                if (number > EXCEL_LIMIT_NUM) {
                    // 数据量累积超出了， 需要新建文件记录
                    int comNum = number - EXCEL_LIMIT_NUM;
                    List compensate = dataList.subList(dataList.size() - comNum, dataList.size());
                    List oldDataList = dataList.subList(0, dataList.size() - comNum);

                    // 旧文件追加数据
                    File oldFile = new File(localDir, dirName + "_" + fileId + ".xlsx");
                    if (oldFile.exists()) {
                        excelWriter.write(oldDataList, writeSheet);
                        // 添加水印
                        Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
                        WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
                        excelWriter.finish();
                    }
                    if (CollectionUtils.isNotEmpty(compensate)) {
                        // 写入新的文件数据
                        File file = new File(localDir, dirName + "_" + (++fileId) + ".xlsx");
                        excelWriter = EasyExcelFactory.write(file.getAbsolutePath()).build();
                        writeSheet = EasyExcel.writerSheet(i, sheet).head(clzss).includeColumnFieldNames(includeColumnFiledNames).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
//                        writeSheet = EasyExcelFactory.writerSheet(sheet).build();
                        excelWriter.write(dataList, writeSheet);
                        // 添加水印
                        Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
                        WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
                        // 重置记录数
                        number = compensate.size();
                    }
                    if (CollectionUtils.isNotEmpty(oldDataList)) {
                        oldDataList.clear();
                    }
                    if (CollectionUtils.isNotEmpty(compensate)) {
                        compensate.clear();
                    }
                    if (CollectionUtils.isNotEmpty(dataList)) {
                        dataList.clear();
                    }
                    continue;
                }
                File file = new File(localDir, dirName + "_" + fileId + ".xlsx");
                if (file.exists()) {
                    excelWriter.write(dataList, writeSheet);
                    // 添加水印
                    Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
                    WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
                } else {
                    excelWriter = EasyExcelFactory.write(file.getAbsolutePath()).build();
                    writeSheet = EasyExcel.writerSheet(i, sheet).head(clzss).includeColumnFieldNames(includeColumnFiledNames).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                    excelWriter.write(dataList, writeSheet);
                    // 添加水印
                    Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
                    WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
                }
                if (CollectionUtils.isNotEmpty(dataList)) {
                    dataList.clear();
                }
            }
        } catch (RuntimeException e) {
            log.error("数据导出失败", e);
            throw new RuntimeException(e);
        } catch (IOException e) {
            log.error("数据导出失败", e);
            throw new RuntimeException(e);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

        String zipPath = Zip4jCommonUtil.zip(localDir.getPath());
        deleteFile(localDir);

        if (StringUtils.isEmpty(zipPath)) {
            log.error("文件压缩失败");
            throw new RuntimeException("文件压缩失败");
        }
        zipFile = new File(zipPath);
        log.info("临时目录压缩后路径: " + zipPath);
        return zipFile;
    }

    /**
     * 删除文件目录
     *
     * @param file
     * @return
     */
    private static Boolean deleteFile(File file) {
        // 判断文件不为null或文件目录存在
        if (file == null || !file.exists()) {
            log.error("文件删除失败,请检查文件是否存在以及文件路径是否正确");
            return false;
        }
        // 获取目录下子文件
        File[] files = file.listFiles();
        // 遍历该目录下的文件对象
        for (File f : files) {
            // 判断子目录是否存在子目录,如果是文件则删除
            if (f.isDirectory()) {
                // 递归删除目录下的文件
                deleteFile(f);
            } else {
                // 文件删除
                f.delete();
            }
        }
        // 文件夹删除
        file.delete();
        return true;
    }

    /**
     * 创建目录
     *
     * @param localDirPath 目录路径
     * @return File对象
     */
    private static File createDir(String localDirPath) {
        File localDir = new File(localDirPath);
        if (!localDir.exists()) {
            localDir.mkdirs();
        }
        return localDir;
    }

}

package com.cusc.product.common.util;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ReflectUtil;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;

public class WaterMarkCommonUtil {


    /**
     * Excel 导出添加水印
     *
     * @param workbook ExcelWorkbook
     * @param userName 导出人姓名
     */
    public static void insertWaterMarkNamePhoneToXlsx(Workbook workbook, String userName, String phone) throws IOException {
        String date = DateUtil.now();
        String waterMarkText = userName + "\n" + phone;
        if (workbook instanceof SXSSFWorkbook) {
            insertWaterMarkTextToXlsx((SXSSFWorkbook) workbook, waterMarkText);
        } else if (workbook instanceof XSSFWorkbook) {
            insertWaterMarkTextToXlsx((XSSFWorkbook) workbook, waterMarkText);
        }
        //throw new RemoteException("HSSFWorkbook 模式不支持 Excel 水印");
    }

    /**
     * Excel 导出添加水印
     *
     * @param workbook ExcelWorkbook
     * @param userName 导出人姓名
     */
    public static void insertWaterMarkTextToXlsx(Workbook workbook, String userName) throws IOException {
        String date = DateUtil.now();
        String waterMarkText = userName + "\n" + date;
        if (workbook instanceof SXSSFWorkbook) {
            insertWaterMarkTextToXlsx((SXSSFWorkbook) workbook, waterMarkText);
        } else if (workbook instanceof XSSFWorkbook) {
            insertWaterMarkTextToXlsx((XSSFWorkbook) workbook, waterMarkText);
        }
        //throw new RemoteException("HSSFWorkbook 模式不支持 Excel 水印");
    }


    /**
     * 给 Excel 添加水印
     *
     * @param workbook      SXSSFWorkbook
     * @param waterMarkText 水印文字内容
     */
    private static void insertWaterMarkTextToXlsx(SXSSFWorkbook workbook, String waterMarkText) throws IOException {
        BufferedImage image = createWatermarkImage(waterMarkText);
        ByteArrayOutputStream imageOs = new ByteArrayOutputStream();
        ImageIO.write(image, "png", imageOs);
        int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
        XSSFPictureData pictureData = (XSSFPictureData) workbook.getAllPictures().get(pictureIdx);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
            SXSSFSheet sheet = workbook.getSheetAt(i);
            //这里由于 SXSSFSheet 没有 getCTWorksheet() 方法，通过反射取出 _sh 属性
            XSSFSheet shReflect = (XSSFSheet) ReflectUtil.getFieldValue(sheet, "_sh");
            PackagePartName ppn = pictureData.getPackagePart().getPartName();
            String relType = XSSFRelation.IMAGES.getRelation();
            PackageRelationship pr = shReflect.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
            shReflect.getCTWorksheet().addNewPicture().setId(pr.getId());
        }
        imageOs.close();
    }


    /**
     * 给 Excel 添加水印
     *
     * @param workbook      XSSFWorkbook
     * @param waterMarkText 水印文字内容
     */
    private static void insertWaterMarkTextToXlsx(XSSFWorkbook workbook, String waterMarkText) throws IOException {
        BufferedImage image = createWatermarkImage(waterMarkText);
        ByteArrayOutputStream imageOs = new ByteArrayOutputStream();
        ImageIO.write(image, "png", imageOs);
        int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
        XSSFPictureData pictureData = workbook.getAllPictures().get(pictureIdx);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
            XSSFSheet sheet = workbook.getSheetAt(i);
            PackagePartName ppn = pictureData.getPackagePart().getPartName();
            String relType = XSSFRelation.IMAGES.getRelation();
            PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
            sheet.getCTWorksheet().addNewPicture().setId(pr.getId());
        }
        imageOs.close();
    }


    /**
     * 创建水印图片
     *
     * @param waterMark 水印文字
     */
    private static BufferedImage createWatermarkImage(String waterMark) {
        String[] textArray = waterMark.split("\n");
        Font font = new Font("microsoft-yahei", Font.PLAIN, 24);
//        int width = 500;
//        int height = 400;
        int width = 300;
        int height = 200;

        BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
        // 背景透明 开始
        Graphics2D g = image.createGraphics();
        image = g.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
        g.dispose();
        // 背景透明 结束
        g = image.createGraphics();
        g.setColor(new Color(237, 235, 233));// 设定画笔颜色
        g.setFont(font);// 设置画笔字体
        //   g.shear(0.1, -0.26);// 设定倾斜度

//        设置字体平滑
        g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);

        //文字从中心开始输入，算出文字宽度，左移动一半的宽度，即居中
        FontMetrics fontMetrics = g.getFontMetrics(font);

        // 水印位置
        int x = width / 2;
        int y = height / 2;
        // 设置水印旋转
        g.rotate(Math.toRadians(-40), x, y);
        for (String s : textArray) {
            // 文字宽度
            int textWidth = fontMetrics.stringWidth(s);
            g.drawString(s, x - (textWidth / 2), y);// 画出字符串
            y = y + font.getSize();
        }

        g.dispose();// 释放画笔
        return image;
    }


}