使用java代码给Excel加水印，保真，新鲜出炉

首先，往表格里贴透明图片，这个很智障，会严重干扰正常阅读和操作
设置文件背景图；
其次，其实就是给excel加一个背景图，但是问题就麻烦在java中基本没有这么干过的，可用方案很少，有spire公司提供的方案，但是这是一个授权付费方案，团队应该是不愿为这个小需求付钱的。最终还是考虑使用poi原生支持
但是poi原生也只能是XSSFWorkbook这种实现能够支持，如果项目是用别的实现生成excel的话，需要改写组装excel文件内容这部分代码，使用xssfworkbook, xssfsheet。
最后，我们看一下效果

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>Test0614</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.xmlbeans</groupId>
                    <artifactId>xmlbeans</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>ooxml-schemas</artifactId>
            <version>1.3</version>
        </dependency>

    </dependencies>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

</project>

下面是java具体实现代码

package com.msl;

import org.apache.poi.POIXMLDocumentPart;
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.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.*;

import static java.awt.image.BufferedImage.TYPE_INT_ARGB;

public class ExcelWatermark0903 {
    public static void main(String[] args) {
        // 输入Excel文件路径

      

          String inputFile = "/Users/navyliu/Downloads/input/1.xlsx";

        // 输出Excel文件夹路径
        String outputFolder = "/Users/navyliu/Downloads/output";
        // 水印文字
        String watermarkText = "我是水印文字";

        try {
            FileInputStream fis = new FileInputStream(inputFile);
            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            //设置文本和字体大小
            Font font = new Font("仿宋", Font.PLAIN, 20);

                int height = 467;// sheet.getPageSetup().getPageHeight() ;
                int width = 987;//sheet.getPageSetup().getPageWidth()
                BufferedImage watermarkImage = drawText(watermarkText, font, Color.pink, Color.white, height, width);
                ByteArrayOutputStream imageOutputStream = new ByteArrayOutputStream();
                ImageIO.write(watermarkImage, "png", imageOutputStream);
                byte[] imageBytes = imageOutputStream.toByteArray();

                int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);

                //add relation from sheet to the picture data
                POIXMLDocumentPart poixmlDocumentPart = (POIXMLDocumentPart) workbook.getAllPictures().get(pictureIdx);
                for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                    XSSFSheet  xssfSheet =  workbook.getSheetAt(i);
                    PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();
                    String relType = XSSFRelation.IMAGES.getRelation();
                    PackageRelationship pr = xssfSheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
                    xssfSheet.getCTWorksheet().addNewPicture().setId(pr.getId());
                }

            // 保存带水印的Excel到输出文件夹
            File outputDir = new File(outputFolder);
            if (!outputDir.exists()) {
                outputDir.mkdirs();
            }
            String outputFilePath = outputFolder + "/output2.xlsx";
            FileOutputStream fos = new FileOutputStream(outputFilePath);
            workbook.write(fos);
            fos.close();

            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    private static BufferedImage drawText (String text, Font font, Color textColor, Color backColor,double height, double width)
    {
        //定义图片宽度和高度
        BufferedImage img = new BufferedImage((int) width, (int) height, TYPE_INT_ARGB);

        Graphics2D loGraphic = img.createGraphics();

        //获取文本size
        FontMetrics loFontMetrics = loGraphic.getFontMetrics(font);
        int liStrWidth = loFontMetrics.stringWidth(text);
        int liStrHeight = loFontMetrics.getHeight();

        //文本显示样式及位置
        loGraphic.setColor(backColor);
        loGraphic.fillRect(0, 0, (int) width, (int) height);
        loGraphic.translate(((int) width - liStrWidth) / 2, ((int) height - liStrHeight) / 2);
        loGraphic.rotate(Math.toRadians(-45));

        loGraphic.translate(-((int) width - liStrWidth) / 4, -((int) height - liStrHeight) / 4);
        loGraphic.setFont(font);
        loGraphic.setColor(textColor);
        loGraphic.drawString(text, ((int) width - liStrWidth) /4 , ((int) height - liStrHeight) /2);
        loGraphic.drawString(text,((int) width - liStrWidth) /2, ((int) height - liStrHeight) / 4);
        loGraphic.drawString(text,((int) width - liStrWidth) /6, ((int) height - liStrHeight) /20);
        loGraphic.dispose();
        return img;
    }

}

实现的效果 如图所示：