Java导出Excel添加自定义水印

目录

pom中导入ooxml-schemas依赖

新增水印类ExcelWaterMark.java

ExcelUtil.java中重写setResponseHeader方法

---

pom中导入ooxml-schemas依赖

<!-- excel操作 -->
        <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.0</version>
		</dependency>

新增水印类ExcelWaterMark.java

public class ExcelWaterMark
{
    private static final Logger log = LoggerFactory.getLogger(ExcelWaterMark.class);

    /**
     * 给 Excel 添加水印
     *
     * @param workbook XSSFWorkbook
     * @param waterMarkText 水印文字内容
     */
    public static void insertWaterMarkText(XSSFWorkbook workbook, String waterMarkText)
    {
        BufferedImage image = createWatermarkImage(waterMarkText);
        // 导出到字节流B
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try
        {
            ImageIO.write(image, "png", os);
        }
        catch (IOException e)
        {
            log.error("添加水印失败");
        }
        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());
        }
    }

    /**
     * 创建水印图片 excel
     *
     * @param waterMark 水印内容
     */
    public static BufferedImage createWatermarkImage(String waterMark)
    {
        String[] textArray = waterMark.split("\n");
        java.awt.Font font = new java.awt.Font("microsoft-yahei", java.awt.Font.PLAIN, 20);
        Integer width = 500;
        Integer 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 (int i = 0; i < textArray.length; i++)
        {
            g.drawString(textArray[i], 0, y); // 画出字符串
            y = y + font.getSize();
        }
        g.dispose(); // 释放画笔
        return image;
    }
}

ExcelUtil.java中重写setResponseHeader方法

 // 发送响应流方法
    public void setResponseHeader(XSSFWorkbook workbook, HttpServletResponse response, String fileName, String fileType) {
        try {
            try {
                ExcelWaterMark.insertWaterMarkText(workbook, "内部资料 请勿外传");

                // fileName = new String(fileName.getBytes(),"UTF-8");
                //fileName = new String(fileName.getBytes("gbk"), "iso8859-1") + ".xls";
                fileName = new String(fileName.getBytes("gbk"), "iso8859-1") + "." + fileType;
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/x-download");// 下面三行是关键代码，处理乱码问题
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

            /*
             * response.setContentType("application/octet-stream;charset=UTF-8");
             * response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
             */
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }

水印添加只支持XSSF，不支持HSSF。ooxml-schemas和poi版本是有可能冲突的，调试一番poi3.9和ooxml-schemas1.0版本不存在冲突jar