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";
String outputDirectory = "D:/test/out";
String picName = "image1.png";
String picFolderPath = "D:/test/watermark/";
String excelOutFilePath = "D:/test/output.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);
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)) {
DefaultHandler defaultHandler = new DefaultHandler() {
private 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);
}
}
@Override
public void characters(char[] ch, int start, int length) {
}
};
XMLReader reader = XMLReaderFactory.createXMLReader();
reader.setContentHandler(defaultHandler);
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();
int worksheetXMLNSRPositionToInsert = addNodeToNodeLocation.getWorksheetxmlns().getColumnNumber();
int sheetDataLineNumberToInsert = addNodeToNodeLocation.getSheetData().getLineNumber();
int sheetDataCharPositionToInsert = addNodeToNodeLocation.getSheetData().getColumnNumber();
int workSheetLineNumberToInsert = addNodeToNodeLocation.getWorksheet().getLineNumber();
int workSheetCharPositionToInsert = addNodeToNodeLocation.getWorksheet().getColumnNumber();
if (workSheetLineNumberToInsert == sheetDataLineNumberToInsert) {
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 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;
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;
}
! 转载请标准来源 !