Excel添加水印

POI中使用水印

package cn.richinfo.mc.admin.utils;

import org.apache.commons.lang3.StringUtils;
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.Sheet;
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.FileOutputStream;
import java.io.IOException;
import java.time.LocalDate;

/**
 * <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 = 20;

        /**
         * <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 = 200;
            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系统", LocalDate.now().toString()};
            WatermarkUtils.addWorkbookWaterMarks(workbook, waterMarks);
            FileOutputStream fileOut = new FileOutputStream("E:\\123.xlsx");
            workbook.write(fileOut);
            fileOut.close();
            fileOut.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}

EasyExcel

package cn.richinfo.mc.admin.config;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
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.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;

@RequiredArgsConstructor
public class WaterMarkHandler implements SheetWriteHandler {

    private final String WATER_MARK;

    public static ByteArrayOutputStream createWaterMark(String content) throws IOException {
        int width = 200;
        int height = 150;
        BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象
        String fontType = "微软雅黑";
        int fontStyle = Font.BOLD;
        int fontSize = 20;
        Font font = new Font(fontType, fontStyle, fontSize);
        Graphics2D g2d = image.createGraphics(); // 获取Graphics2d对象
        image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
        g2d.dispose();
        g2d = image.createGraphics();
        g2d.setColor(new Color(0, 0, 0, 20)); //设置字体颜色和透明度，最后一个参数为透明度
        g2d.setStroke(new BasicStroke(1)); // 设置字体
        g2d.setFont(font); // 设置字体类型  加粗 大小
        g2d.rotate(-0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);//设置倾斜度
        FontRenderContext context = g2d.getFontRenderContext();
        Rectangle2D bounds = font.getStringBounds(content, context);
        double x = (width - bounds.getWidth()) / 2;
        double y = (height - bounds.getHeight()) / 2;
        double ascent = -bounds.getY();
        double baseY = y + ascent;
        // 写入水印文字原定高度过小，所以累计写水印，增加高度
        g2d.drawString(content, (int) x, (int) baseY);
        // 设置透明度
        g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
        // 释放对象
        g2d.dispose();
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        ImageIO.write(image, "png", os);
        return os;
    }


    /**
     * 为Excel打上水印工具函数
     *
     * @param sheet excel sheet
     * @param bytes 水印图片字节数组
     */
    public static void putWaterRemarkToExcel(XSSFSheet sheet, byte[] bytes) {
        //add relation from sheet to the picture data
        XSSFWorkbook workbook = sheet.getWorkbook();
        int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
        String rID = sheet.addRelation(null, XSSFRelation.IMAGES, workbook.getAllPictures().get(pictureIdx)).getRelationship().getId();
        //set background picture to sheet
        sheet.getCTWorksheet().addNewPicture().setId(rID);
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    @SneakyThrows
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        try (ByteArrayOutputStream waterMark = createWaterMark(WATER_MARK)) {
            XSSFSheet sheet = (XSSFSheet) writeSheetHolder.getSheet();
            putWaterRemarkToExcel(sheet, waterMark.toByteArray());
        }
    }
}

  EasyExcel.write(response.getOutputStream(), TMcPnsBind.class).useDefaultStyle(false).inMemory(true) // 注意，此项配置不能少
                    .registerWriteHandler(new WaterMarkHandler(watermark)).sheet("城市对应业代数-照片数-门店数").doWrite(pnsBinds);