导出生成有水印的excel文件

liuhm~

已于 2024-07-29 11:45:00 修改

阅读量1.2w

点赞数 1

分类专栏： java0基础文章标签： java

于 2020-08-24 18:03:02 首次发布

本文链接：https://blog.csdn.net/qq_36120342/article/details/108204483

版权

java0基础专栏收录该内容

52 篇文章 2 订阅

订阅专栏

上代码跑一下即可
pom

 	 <properties>
        <poi.ooxml.version>4.1.2</poi.ooxml.version>
        <ooxml.schemas.version>1.4</ooxml.schemas.version>
        <java.version>1.8</java.version>
    </properties>

   <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.ooxml.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>ooxml-schemas</artifactId>
            <version>${ooxml.schemas.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.ooxml.version}</version>
        </dependency>

utils

package com.liuhm.utils;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ooxml.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.*;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * excel操作类
 *
 * @author liuhaomin
 * @date 2020-8-8 11:36:09
 */
@Slf4j
public class ExcelUtils {
    /**
     * 字体图片
     * @author liuhaomin
     * @date 2020-8-8 11:36:09
     */
    static class FontImage {
        final static String[] TEXT = new String[]{"xxx系统"};
        @Data
        @AllArgsConstructor
        @NoArgsConstructor
        static class Watermark {
            private Boolean enable;
            private String[] text;
            private String dateFormat;
            private String color;
        }
        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, 12);
            Integer width = 300;
            Integer height = 70 * watermark.getText().length;

            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.1, -0.26);
            // 消除文字锯齿
            g.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING,RenderingHints.VALUE_TEXT_ANTIALIAS_ON);
            //设置字体平滑
            g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);

            int y =60;
            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;
        }
    }

    /**
     * 添加水印
     * @param wb
     * @param sheet
     * @param waterMark
     */
    public static void addWaterMark(XSSFWorkbook wb, XSSFSheet sheet, String waterMark){
        //是否添加水印
        if(StringUtils.isNotBlank(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) {
                log.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());
        }
    }

    /**
     * 添加水印
     * @param wb
     * @param sheet
     * @param waterMarks
     */
    public static void addWaterMark(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) {
                log.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());

        }
    }

    public static void main(String[] args) {
        // 指定创建的excel文件名称
        try {
            BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream("/data123.xlsx"));
            // 定义一个工作薄（所有要写入excel的数据，都将保存在workbook中）
            XSSFWorkbook workbook = new XSSFWorkbook();

            // 创建一个sheet
            XSSFSheet sheet = workbook.createSheet("my-sheet-1");
            XSSFSheet sheet2 = workbook.createSheet("my-sheet-2");

            // 开始写入数据流程，2大步：1、定位到单元格，2、写入数据；定位单元格，需要通过行、列配合指定。
            // step1: 先选择第几行（0表示第一行），下面表示在第6行
            XSSFRow row = sheet.createRow(5);
            // step2：选择第几列（0表示第一列），注意，这里的第几列，是在上面指定的row基础上，也就是第6行，第3列
            XSSFCell cell = row.createCell(2);
            // step3：设置单元格的数据（写入数据）
            cell.setCellValue("hello world");
            XSSFWorkbook xssfWorkbook = workbook;
            String strDateFormat = "yyyy-MM-dd HH:mm:ss";
            SimpleDateFormat sdf = new SimpleDateFormat(strDateFormat);
            String[] ss=new String[]{"xxxx系统","超级管理员", sdf.format(new Date())};
            for(int i=0;i<workbook.getNumberOfSheets();i++){
                ExcelUtils.addWaterMark(xssfWorkbook, xssfWorkbook.getSheetAt(i),ss);
            }
            // 执行写入操作
            workbook.write(outputStream);
            workbook.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

TestController

package com.liuhm.controller;

import com.liuhm.utils.ExcelUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author liuhaomin
 * @date 2020/9/23
 */
@RestController
@RequestMapping("/")
public class TestController {
    @GetMapping
    public void test(HttpServletResponse response){
        // 指定创建的excel文件名称
        try {
            String path="/data123.xlsx";
            BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(path));
            // 定义一个工作薄（所有要写入excel的数据，都将保存在workbook中）
            XSSFWorkbook workbook = new XSSFWorkbook();

            // 创建一个sheet
            XSSFSheet sheet = workbook.createSheet("my-sheet-1");
            XSSFSheet sheet2 = workbook.createSheet("my-sheet-2");

            // 开始写入数据流程，2大步：1、定位到单元格，2、写入数据；定位单元格，需要通过行、列配合指定。
            // step1: 先选择第几行（0表示第一行），下面表示在第6行
            XSSFRow row = sheet.createRow(5);
            // step2：选择第几列（0表示第一列），注意，这里的第几列，是在上面指定的row基础上，也就是第6行，第3列
            XSSFCell cell = row.createCell(2);
            // step3：设置单元格的数据（写入数据）
            cell.setCellValue("hello world");
            XSSFWorkbook xssfWorkbook = workbook;
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String[] ss=new String[]{"开始xxxx系统xxxx系统xxxx系统xxxx系统xxxx系统结束","超级管理员", sdf.format(new Date())};
            for(int i=0;i<workbook.getNumberOfSheets();i++){
                ExcelUtils.addWaterMark(xssfWorkbook, xssfWorkbook.getSheetAt(i),ss);
            }
            // 执行写入操作
            workbook.write(outputStream);
            workbook.close();
            outputStream.flush();
            outputStream.close();

            download(path,response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public HttpServletResponse download(String path, HttpServletResponse response) {
        try {
            // path是指欲下载的文件的路径。
            File file = new File(path);
            // 取得文件名。
            String filename = file.getName();
            // 取得文件的后缀名。
            String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();

            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/octet-stream");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return response;
    }
}