Java - 导出Excel添加水印、密码

需求：
导出excel时，添加操作人的用户名、手机号水印、文件密码。基于若依框架的excel导出工具类改造，有三种添加水印方式，此处仅做一个记录。

一、 添加依赖

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

二、工具类

1.水印处理类

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 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.ByteArrayOutputStream;
import java.io.IOException;

/**
 * 水印处理类
**/
@RequiredArgsConstructor
public class WaterMarkHandler implements SheetWriteHandler {

    private final String WATER_MARK;

    public static ByteArrayOutputStream createWaterMark(String content) throws IOException, FontFormatException {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        BufferedImage watermarkImage = ExcelWaterMark.createWatermarkImage(content);
        ImageIO.write(watermarkImage, "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) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        try (ByteArrayOutputStream waterMark = createWaterMark(WATER_MARK)){
            XSSFSheet sheet = (XSSFSheet) writeSheetHolder.getSheet();
            putWaterRemarkToExcel(sheet, waterMark.toByteArray());
        } catch (IOException | FontFormatException e) {
            e.printStackTrace();
        }
    }
}

2.生成水印方法类

import cn.hutool.core.util.ReflectUtil;
import lombok.extern.slf4j.Slf4j;
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.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;

/**
 * Excel 生成水印
 **/
@Slf4j
public class ExcelWaterMark {


    /**
     * 给 Excel 添加水印
     *
     * @param workbook XSSFWorkbook
     * @param waterMarkText 水印文字内容
     */
    public static void insertWaterMarkText(XSSFWorkbook workbook, String waterMarkText) {
        try {
            BufferedImage image = createWatermarkImage(waterMarkText);
            // 导出到字节流B
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ImageIO.write(image, "png", os);
            int pictureIdx = workbook.addPicture(os.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
            XSSFPictureData pictureData = workbook.getAllPictures().get(pictureIdx);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                // 获取每个Sheet表
                XSSFSheet sheet = workbook.getSheetAt(i);
                PackagePartName ppn = pictureData.getPackagePart().getPartName();
                String relType = XSSFRelation.IMAGES.getRelation();
                PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
                sheet.getCTWorksheet().addNewPicture().setId(pr.getId());
            }
        } catch (IOException e) {
            log.error("异常：", e);
        } catch (FontFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * 给 Excel 添加水印
     *
     * SXSSFWorkbook 模式导出用反射
     * 因为 SXSSFWorkbook 通过
     * SXSSFSheet sheet = workbook.getSheetAt(i);
     * 获取SXSSFSheet，SXSSFSheet中没有XSSFSheet的方法，
     * 而且SXSSFSheet中的XSSFSheet是被final修饰的final XSSFSheet _sh;
     * 所以可以用个取到反射从SXSSFSheet中取到XSSFSheet，这样就解决了没有XSSFSheet中类中方法的问题了
     *
     * @param workbook      SXSSFWorkbook
     * @param waterMarkText 水印文字内容
     */
    public static void insertWaterMarkTextToXlsx(SXSSFWorkbook workbook, String waterMarkText) {
        try {
            BufferedImage image = createWatermarkImage(waterMarkText);
            ByteArrayOutputStream imageOs = new ByteArrayOutputStream();
            ImageIO.write(image, "png", imageOs);
            int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
            XSSFPictureData pictureData = (XSSFPictureData) workbook.getAllPictures().get(pictureIdx);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
                SXSSFSheet sheet = workbook.getSheetAt(i);
                //这里由于 SXSSFSheet 没有 getCTWorksheet() 方法，通过反射取出 _sh 属性
                XSSFSheet shReflect = (XSSFSheet) ReflectUtil.getFieldValue(sheet, "_sh");
                PackagePartName ppn = pictureData.getPackagePart().getPartName();
                String relType = XSSFRelation.IMAGES.getRelation();
                PackageRelationship pr = shReflect.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
                shReflect.getCTWorksheet().addNewPicture().setId(pr.getId());
            }
        } catch (IOException | FontFormatException e) {
            log.error("添加水印失败");
        }
    }

    /**
     * 创建水印图片 excel
     *
     * @param waterMark 水印内容
     */
    public static BufferedImage createWatermarkImage(String waterMark) throws IOException, FontFormatException {
        String[] textArray = waterMark.split("\n");
        // 添加中文字体 加载下载的中文字体,这样就不会出现中文乱码或中文丢失的情况
        ClassPathResource resource = new ClassPathResource("font/msyh.ttf");
        InputStream fi = resource.getInputStream();
        BufferedInputStream fb = new BufferedInputStream(fi);
        int fontStyle = Font.PLAIN;
        int fontSize = 20;
        Font font = Font.createFont(fontStyle,fb);
        font = font.deriveFont(fontStyle,fontSize);
        int width = 500;
        int height = 200;
        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("#C5CBCF".substring(1), 16))); // 设定画笔颜色
        g.setFont(font); // 设置画笔字体
        g.shear(0.1, -0.26); // 设定倾斜度
        // 设置字体平滑
        g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
        int y = 150;
        for (String s : textArray) {
            g.drawString(s, 0, y); // 画出字符串
            y = y + font.getSize();
        }
        g.dispose(); // 释放画笔
        return image;
    }
}

三、导出带水印、密码的Excel

1.使用easyExcel的导出数据

    public void easyExcelExport(@RequestBody ParamVO param, HttpServletResponse response) {
        try (InputStream inputStream = new ClassPathResource("excel.template/export_template.xlsx").getInputStream();
                OutputStream outputStream = response.getOutputStream();) {

            List<EntityPO> exportList = xxxService.getExportList(param);

            //读模板流
            String fileName = "easyExcel导出带水印文件";
            String fileName3 = URLEncoder.encode(fileName, "utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''"+fileName3+".xlsx");
            response.setContentType("application/msexcel");// 定义输出类型
            response.setCharacterEncoding("UTF-8");

            ExcelWriter excelWriter = EasyExcel.write(outputStream)
                    .withTemplate(inputStream)
                    .password("123456") 	// 添加密码
                    .inMemory(true)
                    .build();
                    
            // 添加水印        
            WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new WaterMarkHandler(SecurityUtils.getLoginUser().getUser().getNickName() + " " + SecurityUtils.getLoginUser())).build();

            excelWriter.fill(orderStatisticsByTypeList, writeSheet);
            excelWriter.finish();
            outputStream.flush();

        } catch (Exception e){
            log.error("异常:", e);
        }
    }

2.使用poi导出数据

    public void poiExcelExport(@RequestBody ClassOrderStatisticListVO param, HttpServletResponse response, HttpServletRequest request) {
        try{
            List<EntityPO> list = new Array()<>;
            ExcelUtil<EntityPO> util = new ExcelUtil<>(SchoolClassStatisticPO.class);
            String exportPwdCode = "123456";	// 添加密码
            util.exportEncryptExcel(response, list, "导出带水印报表",exportPwdCode);
        } catch (Exception e){
            log.error("导出，方法异常 >>>>>>>>>>> :", e);
        }
    }

poi处理类

/**
 * poi Excel相关处理
 * 
 * @author ruoyi
 */
public class ExcelUtil<T>
{
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int sheetSize = 65536;

    /**
     * 工作表名称
     */
    private String sheetName;

    /**
     * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
     */
    private Excel.Type type;

    /**
     * 工作薄对象
     */
    private Workbook wb;

    public Workbook getWb() {
        return wb;
    }

    public void setWb(Workbook wb) {
        this.wb = wb;
    }

    /**
     * 工作表对象
     */
    private Sheet sheet;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;

    /**
     * 导入导出数据列表
     */
    private List<T> list;

    /**
     * 注解列表
     */
    private List<Object[]> fields;

    /**
     * 当前行号
     */
    private int rownum;
    
    /**
     * 标题
     */
    private String title;

    /**
     * 最大高度
     */
    private short maxHeight;

    /**
     * 统计列表
     */
    private Map<Integer, Double> statistics = new HashMap<Integer, Double>();

    /**
     * 数字格式
     */
    private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");

    /**
     * 实体对象
     */
    public Class<T> clazz;

    public ExcelUtil(Class<T> clazz)
    {
        this.clazz = clazz;
    }

    /**
     * 文件密码
     */
    public String password;


    /**
     * 带密码的初始化
     * @param list
     * @param sheetName
     * @param title
     * @param type
     * @param password excel文件密码
     */
    public void init2(List<T> list, String sheetName, String title, Excel.Type type, String password)
    {
        if (list == null){
            list = new ArrayList<T>();
        }
        this.list = list;
        this.sheetName = sheetName;
        this.type = type;
        this.title = title;
        this.password = password;
        createExcelField();
        createWorkbook();
        log.info("poi 水印：{}", SecurityUtils.getLoginUser().getUser().getNickName() + " " + SecurityUtils.getLoginUser().getUser().getPhonenumber());
        // 添加水印
        ExcelWaterMark.insertWaterMarkTextToXlsx((SXSSFWorkbook) this.wb, SecurityUtils.getLoginUser().getUser().getNickName() + " " + SecurityUtils.getLoginUser().getUser().getPhonenumber());
        createTitle();
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     * 加密文件
     * @param response 返回数据
     * @param list 导出数据集合
     * @param sheetName 工作表的名称
     */
    public void exportEncryptExcel(HttpServletResponse response, List<T> list, String sheetName, String password)
    {
        exportEncryptExcel(response, list, sheetName, StringUtils.EMPTY, password);
    }


    /**
     * 对list数据源将其里面的数据导入到excel表单
     * 加密文件
     * @param response 返回数据
     * @param list 导出数据集合
     * @param sheetName 工作表的名称
     * @param title 标题
     */
    public void exportEncryptExcel(HttpServletResponse response, List<T> list, String sheetName, String title, String password)
    {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        this.init2(list, sheetName, title, Excel.Type.EXPORT, password);
        exportExcel(response);
    }


    /**
     * 对list数据源将其里面的数据导入到excel表单
     * 
     * @return 结果
     */
    public void exportExcel(HttpServletResponse response)
    {
        try
        {
            writeSheet();
            if (StringUtils.isNotBlank(password)) {
                exportEncryptExcel(this.wb, title, response, password);
            } else {
                wb.write(response.getOutputStream());
            }
        }
        catch (Exception e) {
            log.error("poi-导出Excel异常:", e);
        } finally {
            IOUtils.closeQuietly(wb);
        }
    }

    /**
     * poi 导出一个需要密码打开的Excel
     *
     * @param workbook workbook流
     * @param fileName 文件名
     * @param response 响应
     * @param password 密码
     */
    public static void exportEncryptExcel(Workbook workbook, String fileName, HttpServletResponse response, String password) throws Exception {
        //
        if (password != null && !"".equals(password)) {
            // 文件名
            fileName = fileName + ".xlsx";

            // 创建一个字节数组输出流
            ByteArrayOutputStream workbookOutput = new ByteArrayOutputStream();
            workbook.write(workbookOutput);
            workbookOutput.flush();

            // 创建一个字节数组输入流
            ByteArrayInputStream workbookInput = new ByteArrayInputStream(workbookOutput.toByteArray());

            // 加密
            EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
            Encryptor enc = info.getEncryptor();
            enc.confirmPassword(password);

            // 创建一个POIFS 文件系统
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem();
            OPCPackage opc = OPCPackage.open(workbookInput);
            OutputStream outputStream = enc.getDataStream(poifsFileSystem);
            opc.save(outputStream);
            outputStream.close();
            opc.close();
            workbookOutput = new ByteArrayOutputStream();
            poifsFileSystem.writeFilesystem(workbookOutput);
            workbookOutput.flush();

            // 获取文件名并转码
            String name = URLEncoder.encode(fileName, "UTF-8");
            // 编码
            response.setCharacterEncoding("UTF-8");
            // 设置强制下载不打开
            response.setContentType("application/force-download");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + name);
            byte[] buff = new byte[1024];
            BufferedInputStream bufferedInputStream = null;

            try {
                OutputStream responseOutputStream = response.getOutputStream();
                bufferedInputStream = new BufferedInputStream(new ByteArrayInputStream(workbookOutput.toByteArray()));

                for (int i = bufferedInputStream.read(buff); i != -1; i = bufferedInputStream.read(buff)) {
                    responseOutputStream.write(buff, 0, buff.length);
                    responseOutputStream.flush();
                }
            } catch (IOException e) {
                log.error("poi 文件导出失败：{}", e.getMessage());
            } finally {
                if (bufferedInputStream != null) {
                    try {
                        bufferedInputStream.close();
                    } catch (IOException e) {
                        log.error("poi 文件导出关闭输出流失败:{}", e);
                    }
                }
            }
        }
    }
}

3.ruoyi工具类导出(poi)

public void export(HttpServletResponse response, SysUser user) throws Exception{

        List<SysUser> list = userService.selectUserList(user);
        List<UserExportPo> poList = list.stream().map(sysUser -> {
            UserExportPo exportPo = new UserExportPo();
            BeanUtils.copyProperties(sysUser, exportPo);
            if(sysUser.getDept()!=null){
                exportPo.setDeptName(sysUser.getDept().getDeptName());
            }
            return exportPo;
        }).collect(Collectors.toList());
        ExportParams params = new ExportParams("导出列表", "导出列表", ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(params, UserExportPo.class, poList);

        // 添加水印、密码
        ExcelUtils.downLoadExcel("列表", response, workbook, true, "123456");
}

excel下载工具类

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

public class ExcelUtils {

    /**
     * excel下载
     *
     * @param fileName 下载时的文件名称
     * @param response
     * @param workbook excel数据
     * @param flag 是否添加水印标识
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook, boolean flag) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            if (flag) {
            	// 添加水印
                ExcelWaterMark.insertWaterMarkText((XSSFWorkbook) workbook, SecurityUtils.getLoginUser().getUser().getNickName() + " " + SecurityUtils.getLoginUser().getUser().getPhonenumber());
            }

            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }
}