Java 给 excel 大文件 添加水印 和 锁定单元格，非poi方式

Java 给 excel 大文件 添加水印 和 锁定单元格，非poi方式

! 转载请标准来源 !

https://blog.youkuaiyun.com/ImAdrian/article/details/133313827

为什么不用poi？

poi依赖冲突，被折磨得痛不欲生，poi太慢了，嫌弃

话不多说，直接上代码

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.19</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.12.0</version>
</dependency>
<dependency>
    <groupId>org.dom4j</groupId>
    <artifactId>dom4j</artifactId>
    <version>2.1.4</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.28</version>
</dependency>

public class Constant {

    public static final String addNodeToSheetDataEndAfter = "sheetData";

    public static final String addNodeToWorksheetEndBefore = "worksheet";
}

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ZipUtil;

import java.nio.charset.StandardCharsets;

public class ExcelAddWaterMarketPipeline {

    public static void main(String[] args) {
        long start = System.currentTimeMillis();
        String xlsxFilePath= "D:/test/test.xlsx"; // xlsx文件路径
        String outputDirectory = "D:/test/out"; // 解压缩后的目录
        String picName = "image1.png"; // 水印文件名称
        String picFolderPath = "D:/test/watermark/"; // 水印文件所在文件夹
        String excelOutFilePath = "D:/test/output.xlsx"; // 指定要创建的 xlsx 压缩文件的路径

        FileUtil.del(outputDirectory);
        FileUtil.del(excelOutFilePath);

        ZipUtil.unzip(xlsxFilePath, outputDirectory, StandardCharsets.UTF_8);
        System.out.println("文件已解压缩到目录：" + outputDirectory);
        WaterMarket waterMarket = new WaterMarket(outputDirectory, picFolderPath, picName);
        waterMarket.addWatermark("1234");

        long zipStart = System.currentTimeMillis();
        ZipUtil.zip(outputDirectory, excelOutFilePath);
//        ZipUtils.zipFileChannel(outputDirectory, excelOutFilePath);
        System.out.println("文件夹已添加到压缩文件。");
        long zipEnd = System.currentTimeMillis();
        System.out.println("压缩文件耗时：" + (zipEnd - zipStart) / 1000 + "秒");

        FileUtil.del(outputDirectory);

        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start) / 1000 + "秒");
    }
}

public class IgnoreException extends RuntimeException {

    public IgnoreException(Throwable throwable) {
        super(throwable);
    }

    public IgnoreException() {
    }

    public IgnoreException(String s) {
        super(s);
    }
}

import org.apache.commons.lang3.StringUtils;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.Locator;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.FileInputStream;

public class LargeXMLProcessor {

    public static SheetLocation getAddNodeToNodeLocation(String sheetXmlFilePath) {

        SheetLocation sheetLocation = new SheetLocation();

        try (FileInputStream fileInputStream = new FileInputStream(sheetXmlFilePath)) {
            // 创建SAX解析器
            DefaultHandler defaultHandler = new DefaultHandler() {
                private Locator locator;

                // 设置Locator对象以获取字符位置信息
                @Override
                public void setDocumentLocator(Locator locator) {
                    this.locator = locator;
                }

                // 处理元素开始事件
                @Override
                public void startElement(String uri, String localName, String qName, Attributes attributes) {
                    // 获取节点的起始位置信息
                    int lineNumber = locator.getLineNumber();
                    int columnNumber = locator.getColumnNumber();
                    if (StringUtils.equals(qName, Constant.addNodeToWorksheetEndBefore)) {
                        WorksheetLocation location = new WorksheetLocation();
                        location.setLineNumber(lineNumber);
                        location.setColumnNumber(columnNumber);
                        sheetLocation.setWorksheetxmlns(location);
                        System.out.println("Element '" + qName + "' starts at line " + lineNumber + ", column " + columnNumber);
                    }
                }

                // 处理元素结束事件
                @Override
                public void endElement(String uri, String localName, String qName) {
                    // 获取节点的起始位置信息
                    int lineNumber = locator.getLineNumber();
                    int columnNumber = locator.getColumnNumber();
                    if (StringUtils.equals(qName, Constant.addNodeToSheetDataEndAfter)) {
                        WorksheetLocation location = new WorksheetLocation();
                        location.setLineNumber(lineNumber);
                        location.setColumnNumber(columnNumber);
                        sheetLocation.setSheetData(location);
                        System.out.println("Element '" + qName + "' starts at line " + lineNumber + ", column " + columnNumber);
                    }
                    if (StringUtils.equals(qName, Constant.addNodeToWorksheetEndBefore)) {
                        WorksheetLocation location = new WorksheetLocation();
                        location.setLineNumber(lineNumber);
                        location.setColumnNumber(columnNumber  - ("<>".length() + "/".length() + localName.length()));
                        sheetLocation.setWorksheet(location);
                        System.out.println("Element '" + qName + "' starts at line " + lineNumber + ", column " + columnNumber);
                    }
//                    if (!ObjectUtils.isEmpty(location.getLineNumber()) && !ObjectUtils.isEmpty(location.getColumnNumber())) {
//                        throw new IgnoreException("Actively throwing an exception to end, please ignore after capturing.");
//                    }
                }

                // 处理元素内的字符数据
                @Override
                public void characters(char[] ch, int start, int length) {
                    // 在这里处理元素内的字符数据
                }
            };
            XMLReader reader = XMLReaderFactory.createXMLReader();
            reader.setContentHandler(defaultHandler);

            // 开始解析XML文件
            reader.parse(new InputSource(fileInputStream));
        } catch (IgnoreException e) {
            System.out.println("主动抛出异常，忽略");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        System.out.println(sheetLocation);
        return sheetLocation;
    }
}

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.IdUtil;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;

public class OptimizedInsertStringInLargeFile {
    public static void addNodeToLocation(String filePath, SheetLocation addNodeToNodeLocation, String readOnlyAndPasswordNode, String pictureNode) {
        long start = System.currentTimeMillis();
        String parentFolderPath = new File(filePath).getParentFile().getAbsolutePath();
        String tempFilePath = parentFolderPath + "/" + IdUtil.fastSimpleUUID() + ".xml";
        String xmlnsr = "xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\"";

        int worksheetXMLNSRLineNumberToInsert = addNodeToNodeLocation.getWorksheetxmlns().getLineNumber(); // 要插入的行号（从1开始）
        int worksheetXMLNSRPositionToInsert = addNodeToNodeLocation.getWorksheetxmlns().getColumnNumber(); // 要插入的位置（从1开始）

        int sheetDataLineNumberToInsert = addNodeToNodeLocation.getSheetData().getLineNumber(); // 要插入的行号（从1开始）
        int sheetDataCharPositionToInsert = addNodeToNodeLocation.getSheetData().getColumnNumber(); // 要插入的位置（从1开始）

        int workSheetLineNumberToInsert = addNodeToNodeLocation.getWorksheet().getLineNumber(); // 要插入的行号（从1开始）
        int workSheetCharPositionToInsert = addNodeToNodeLocation.getWorksheet().getColumnNumber(); // 要插入的位置（从1开始）

        if (workSheetLineNumberToInsert == sheetDataLineNumberToInsert) {
            // 因为sheetData在前 所以 worksheet需要往后移
            workSheetCharPositionToInsert += readOnlyAndPasswordNode.length();
        }
        File file = new File(filePath);
        File tempFile = new File(tempFilePath);
        try (
                // 打开文件输入流和输出流，使用缓冲区
                InputStreamReader inputStreamReader = new InputStreamReader(Files.newInputStream(file.toPath()), StandardCharsets.UTF_8);
                OutputStreamWriter outputStreamWriter = new OutputStreamWriter(Files.newOutputStream(tempFile.toPath()), StandardCharsets.UTF_8);
                BufferedReader reader = new BufferedReader(inputStreamReader);
                BufferedWriter writer = new BufferedWriter(outputStreamWriter)
        ) {

            String line;
            int currentLineNumber = 1;

            // 用StringBuilder来缓存修改后的内容
            StringBuilder modifiedContent = new StringBuilder();
            writer.write(modifiedContent.toString());


            // 逐行处理文件内容
            while ((line = reader.readLine()) != null) {
                modifiedContent = new StringBuilder();
                if (currentLineNumber == worksheetXMLNSRLineNumberToInsert) {
                    if (!line.contains(xmlnsr)) {
                        String xmlnsrInsertStr = " " + xmlnsr;
                        // 在指定行插入字符串 第一个 -1 是往前移一位  第二个 -1 是substring 下表应该往前移动一位
                        int insertPosition = Math.min(Math.max(worksheetXMLNSRPositionToInsert - 1 - 1, 0), line.length()); // 考虑位置超出行末的情况
                        line = line.substring(0, insertPosition) + xmlnsrInsertStr + line.substring(insertPosition);
                        if (worksheetXMLNSRLineNumberToInsert == sheetDataLineNumberToInsert) {
                            sheetDataCharPositionToInsert += xmlnsrInsertStr.length();
                        }
                        if (worksheetXMLNSRLineNumberToInsert == workSheetLineNumberToInsert) {
                            workSheetCharPositionToInsert += xmlnsrInsertStr.length();
                        }
                    }
                }
                if (currentLineNumber == sheetDataLineNumberToInsert) {
                    // 在指定行插入字符串
                    int insertPosition = Math.min(Math.max((sheetDataCharPositionToInsert - 1), 0), line.length()); // 考虑位置超出行末的情况
                    line = line.substring(0, insertPosition) + readOnlyAndPasswordNode + line.substring(insertPosition);
                }
                if (currentLineNumber == workSheetLineNumberToInsert) {
                    // 在指定行插入字符串
                    int insertPosition = Math.min(Math.max((workSheetCharPositionToInsert - 1), 0), line.length()); // 考虑位置超出行末的情况
                    line = line.substring(0, insertPosition) + pictureNode + line.substring(insertPosition);
                }
                modifiedContent.append(line).append(System.lineSeparator());
                currentLineNumber++;
                writer.append(modifiedContent.toString());
            }
            long end = System.currentTimeMillis();
            System.out.println("写文件文件耗时：" + (end - start) / 1000 + "s");
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 删除原始文件并重命名临时文件为原始文件名
        if (FileUtil.del(filePath)) {
            if (!tempFile.renameTo(file)) {
                System.err.println("Failed to rename temp file.");
            }
        } else {
            System.err.println("Failed to delete source file.");
        }
        System.out.println("String inserted successfully.");
    }
}

import lombok.Data;

@Data
public class SheetLocation {

    private WorksheetLocation sheetData;

    private WorksheetLocation worksheet;

    private WorksheetLocation worksheetxmlns;
}

import cn.hutool.core.io.FileUtil;
import org.apache.commons.lang3.StringUtils;

import java.util.List;
import java.util.Locale;
import java.util.stream.Collectors;

public class WaterMarket {

    private String excelUnzipFolder;
    private String picFolder;

    private String waterMarketFileName;

    private String xlFolder;

    private String worksheetsFolder;

    private String worksheetsRelsFolder;

    private String mediaFolder;

    private String relsFileSuffixName;

    private String relsFileContent;


    public WaterMarket(String excelUnzipFolder, String picFolder, String waterMarketFileName) {
        this.excelUnzipFolder = excelUnzipFolder;
        this.picFolder = picFolder;
        this.waterMarketFileName = waterMarketFileName;
        xlFolder = excelUnzipFolder + "/xl";
        worksheetsFolder = xlFolder + "/worksheets";
        worksheetsRelsFolder = worksheetsFolder + "/_rels";
        mediaFolder = xlFolder + "/media";
        relsFileSuffixName = ".rels";
        relsFileContent = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n" +
                "<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">\n" +
                "<Relationship Id=\"rId" + Integer.MAX_VALUE + "\" Target=\"../media/" + waterMarketFileName + "\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/image\"/>\n" +
                "</Relationships>\n";

    }

    public void addWatermark() {
        addWatermark(null);
    }

    public void addWatermark(String password) {
        if (!FileUtil.exist(mediaFolder)) {
            FileUtil.mkdir(mediaFolder);
        }
        FileUtil.copy(picFolder + "/" + waterMarketFileName, mediaFolder, true);
        List<String> collect = FileUtil.listFileNames(worksheetsFolder).stream().filter(fileName -> StringUtils.endsWith(fileName, ".xml")).collect(Collectors.toList());
        String pictureNode = "<picture r:id=\"rId" + Integer.MAX_VALUE + "\"/>";
        String readOnlyAndPasswordNode = "";
        if (StringUtils.isNotBlank(password)) {
            int hash = createXorVerifier1(password);
            String encodePassword = String.format(Locale.ROOT, "%04X", hash);
            readOnlyAndPasswordNode = "<sheetProtection password=\"" + encodePassword + "\" sheet=\"true\" scenarios=\"true\" objects=\"true\" selectLockedCells=\"true\"/>";
        }
        for (String sheetFileName : collect) {
            String relsPath = worksheetsRelsFolder + "/" + sheetFileName + relsFileSuffixName;
            FileUtil.writeUtf8String(relsFileContent, relsPath);
            String sheetXmlFilePath = worksheetsFolder + "/" + sheetFileName;
            SheetLocation addNodeToNodeLocation = LargeXMLProcessor.getAddNodeToNodeLocation(sheetXmlFilePath);
            OptimizedInsertStringInLargeFile.addNodeToLocation(sheetXmlFilePath, addNodeToNodeLocation, readOnlyAndPasswordNode, pictureNode);
        }
    }

    public static int createXorVerifier1(String password) {
        byte[] arrByteChars = toAnsiPassword(password);
        short verifier = 0;
        if (!"".equals(password)) {
            for (int i = arrByteChars.length - 1; i >= 0; --i) {
                verifier = rotateLeftBase15Bit(verifier);
                verifier = (short) (verifier ^ arrByteChars[i]);
            }

            verifier = rotateLeftBase15Bit(verifier);
            verifier = (short) (verifier ^ arrByteChars.length);
            verifier = (short) (verifier ^ '칋');
        }

        return verifier & '\uffff';
    }

    private static byte[] toAnsiPassword(String password) {
        byte[] arrByteChars = new byte[password.length()];

        for (int i = 0; i < password.length(); ++i) {
            int intTemp = password.charAt(i);
            byte lowByte = (byte) (intTemp & 255);
            byte highByte = (byte) (intTemp >>> 8 & 255);
            arrByteChars[i] = lowByte != 0 ? lowByte : highByte;
        }

        return arrByteChars;
    }

    private static short rotateLeftBase15Bit(short verifier) {
        short intermediate1 = (short) ((verifier & 16384) == 0 ? 0 : 1);
        short intermediate2 = (short) (verifier << 1 & 32767);
        short intermediate3 = (short) (intermediate1 | intermediate2);
        return intermediate3;
    }

}

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class WorksheetLocation {

    private Integer lineNumber;

    private Integer columnNumber;
}

! 转载请标准来源 !