目录

poi导出excel加水印，单元格可修改（只支持XSSFWorkbook）

引入的jar包：

操作水印的工具类：

最终效果

 小提示

poi导出excel加水印，单元格可修改（只支持XSSFWorkbook）

引入的jar包：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
 
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
 
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.4</version>
</dependency>

操作水印的工具类：

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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
 
/**
 * <p>Title: 水印设置 </p>
 * <p>Description: 水印设置 </p>
 */
public class WatermarkUtils {
 
    private final static Logger logger = LoggerFactory.getLogger(WatermarkUtils.class);
 
    static class FontImage {
        final static String[] TEXT = new String[]{"XXXX系统"};
        final static Integer fontSize = 12;
 
        /**
         * <p>Title: 设置水印相关信息 </p>
         * <p>Description: 水印信息 </p>
         */
        static class Watermark {
            private Boolean enable;
            private String[] text;
            private String dateFormat;
            private String color;
 
            public Watermark() {}
 
            public Watermark(Boolean enable, String[] text, String dateFormat, String color) {
                this.enable = enable;
                this.text = text;
                this.dateFormat = dateFormat;
                this.color = color;
            }
 
            public Boolean getEnable() {
                return enable;
            }
 
            public void setEnable(Boolean enable) {
                this.enable = enable;
            }
 
            public String[] getText() {
                return text;
            }
 
            public void setText(String[] text) {
                this.text = text;
            }
 
            public String getDateFormat() {
                return dateFormat;
            }
 
            public void setDateFormat(String dateFormat) {
                this.dateFormat = dateFormat;
            }
 
            public String getColor() {
                return color;
            }
 
            public void setColor(String color) {
                this.color = color;
            }
        }
 
 
        /**
         * <p>Title: 生成水印图片信息 </p>
         * <p>Description: 生成水印图片信息 </p>
         */
 
        public static BufferedImage createWatermarkImage(Watermark watermark) {
            if (watermark == null) {
                watermark = new Watermark();
                watermark.setEnable(true);
                watermark.setText(TEXT);
                watermark.setColor("#C5CBCF");
                watermark.setDateFormat("yyyy-MM-dd HH:mm");
            } else {
                if (StringUtils.isEmpty(watermark.getDateFormat())) {
                    watermark.setDateFormat("yyyy-MM-dd HH:mm");
                } else if (watermark.getDateFormat().length() == 16) {
                    watermark.setDateFormat("yyyy-MM-dd HH:mm");
                } else if (watermark.getDateFormat().length() == 10) {
                    watermark.setDateFormat("yyyy-MM-dd");
                }
                if (watermark.getText().length==0) {
                    watermark.setText(TEXT);
                }
                if (StringUtils.isEmpty(watermark.getColor())) {
                    watermark.setColor("#C5CBCF");
                }
            }
            Font font = new Font("微软雅黑", Font.BOLD, fontSize);
            Integer width = 300;
            Integer height = 100 * watermark.getText().length;
            // 设置最大的宽度
            for (int j=0;j<watermark.getText().length;j++){
                int textWidth = fontSize*watermark.getText()[j].length();
                if(textWidth>width){
                    width=textWidth;
                }
            }
            height=(width/2);
            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(Integer.parseInt(watermark.getColor().substring(1), 16)));
            // 设置画笔字体
            g.setFont(font);
            // 设定倾斜度
            g.shear(0, -0.3);
            // 消除文字锯齿
            g.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING,RenderingHints.VALUE_TEXT_ANTIALIAS_ON);
            //设置字体平滑
            g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
 
            int y =(height/2)+font.getSize()*2;
            int x =0;
            for (int j=0;j<watermark.getText().length;j++){
                String[] textArray = watermark.getText()[j].split("\n");
                y+= 1*(j+1);
                // 计算文字长度，计算居中的x点坐标
                FontMetrics fm = g.getFontMetrics(font);
                int textWidth = fm.stringWidth(watermark.getText()[j]);
                x = (width - textWidth) / 2;
 
                for (int i = 0; i < textArray.length; i++) {
                    // 画出字符串
                    g.drawString(textArray[i], x, y);
                    y = y + font.getSize();
                }
 
            }
            g.dispose();// 释放画笔
            return image;
        }
    }
 
   /**
    * <p>Title: 添加水印 </p>
    * <p>Description: 添加水印信息 </p>
    * @param wb 工作薄
    * @param sheet 工作表
    * @param waterMark 水印信息
    */
    public static void addWaterMark(XSSFWorkbook wb, XSSFSheet sheet, String waterMark){
        //是否添加水印
        if(StringUtils.isEmpty(waterMark)){
            FontImage.Watermark watermark = new FontImage.Watermark();
            watermark.setText(new String[]{waterMark});
            watermark.setEnable(true);
            BufferedImage image = FontImage.createWatermarkImage(watermark);
            // 导出到字节流B
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                ImageIO.write(image, "png", os);
            } catch (IOException e) {
                logger.error("add watermark error: {}", e.getMessage());
            }
 
            int pictureIdx = wb.addPicture(os.toByteArray(), Workbook.PICTURE_TYPE_PNG);
            POIXMLDocumentPart poixmlDocumentPart = wb.getAllPictures().get(pictureIdx);
 
            PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();
            String relType = XSSFRelation.IMAGES.getRelation();
            //add relation from sheet to the picture data
            PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
            //set background picture to sheet
            sheet.getCTWorksheet().addNewPicture().setId(pr.getId());
        }
    }
 
    /**
     * <p>Title: 添加水印 </p>
     * <p>Description: 添加水印信息 </p>
     * @param wb 工作薄
     * @param sheet 工作表
     * @param waterMarks 水印信息
     */
    public static void addWaterMarks(XSSFWorkbook wb, XSSFSheet sheet, String[] waterMarks){
        // 是否添加水印
        if(waterMarks!=null && waterMarks.length>0){
            FontImage.Watermark watermark = new FontImage.Watermark();
            watermark.setText(waterMarks);
            watermark.setEnable(true);
            BufferedImage image = FontImage.createWatermarkImage(watermark);
            // 导出到字节流B
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                ImageIO.write(image, "png", os);
            } catch (IOException e) {
                logger.error("add watermark error: {}", e.getMessage());
            }
 
            int pictureIdx = wb.addPicture(os.toByteArray(), Workbook.PICTURE_TYPE_PNG);
            POIXMLDocumentPart poixmlDocumentPart = wb.getAllPictures().get(pictureIdx);
 
            PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();
            String relType = XSSFRelation.IMAGES.getRelation();
            //add relation from sheet to the picture data
            PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
            //set background picture to sheet
            sheet.getCTWorksheet().addNewPicture().setId(pr.getId());
 
        }
 
    }
 
    /**
     * <p>Title: 添加水印 </p>
     * <p>Description: 添加水印 </p>
     * @param workbook 工作薄
     * @param waterMark 水印信息
     */
    public static void addWorkbookWaterMark(Workbook workbook, String waterMark){
        if (workbook != null){
            XSSFWorkbook xssfWorkbook = (XSSFWorkbook)workbook;
            for(int i = 0; i < xssfWorkbook.getNumberOfSheets(); i++){
                addWaterMark(xssfWorkbook, xssfWorkbook.getSheetAt(i),waterMark);
            }
        }
    }
 
    /**
     * <p>Title: 添加水印 </p>
     * <p>Description: 添加水印 </p>
     * @param workbook 工作薄
     * @param waterMarks 水印信息
     */
    public static void addWorkbookWaterMarks(Workbook workbook, String[] waterMarks){
        if (workbook != null){
            XSSFWorkbook xssfWorkbook = (XSSFWorkbook)workbook;
            for(int i = 0; i < xssfWorkbook.getNumberOfSheets(); i++){
                addWaterMarks(xssfWorkbook, xssfWorkbook.getSheetAt(i), waterMarks);
            }
        }
    }
 
    public static void main(String[] args){
        try {
            XSSFWorkbook workbook =  new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("0");
            Sheet sheet1 = workbook.createSheet("1");
            // 水印信息
            String[] waterMarks = {"XXX系统", DateUtils.getDate("yyyy-MM-dd HH:ss")};
            WatermarkUtils.addWorkbookWaterMarks(workbook, waterMarks);
            FileOutputStream fileOut = new FileOutputStream("C:\\Users\\rong\\Desktop\\123.xlsx");
            workbook.write(fileOut);
            fileOut.close();
            fileOut.flush();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
    
 
}

最终效果

 小提示

1、如果需要导出.xls文件，建议创建XSSFWorkbook，添加水印后，输出时修改文件后缀。

2、大批量数据情况下注意HSSFWorkbook数据区域，防止修改后缀导致数据丢失。

3、此方法弊端，打印无法显示水印信息，如需要打印显示水印，请选择图片方式进行添加水印。

本栏目推荐文章
• C++实现文件内查找字符串
• IDEA项目名称后面出现中括号，模块Modules的名子和文件夹名称不同，可以右键修改名称也可以在File->Project Structure 修改Modules的Name(快捷键ctrl+Shift+Alt+s)
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 类模板分文件编写
• Linux文件IO之二 [补档-2023-07-21]
• Linux文件IO之一 [补偿-2023-07-21]
• 十行python代码实现文件去重，去除重复文件的脚本
• vim编辑器实现左边目录右边是文件内容
• MakeFile文件的使用 [补档-2023-07-13]
• C语言文件操作函数

水印 单元 文件 excel Excel水印 单元 文件excel 单元 文件 数据excel 水印excel 页眉 水印excel 单元excel 单元 颜色excel 字符 单元 次数excel 单元 交点excel 单元sheetjs格式excel 表格 单元excel word