java - apache POI 在 Excel 工作簿中添加水印

Question

我是 apache POI java 开发的新手，我正在尝试使用下面的代码向 Excel 添加水印。但是水印id覆盖了它后面的内容。我想在背景中添加水印。

public class xlWatermark {
    public static void main(String[] args) {
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream("Test.xls");
            HSSFSheet ws = wb.createSheet("testSheet");
            HSSFPatriarch dp = ws.createDrawingPatriarch();
            HSSFClientAnchor anchor = new HSSFClientAnchor
                (0, 0, 1023, 255, (short) 2, 4, (short) 13, 26);
            HSSFTextbox txtbox = dp.createTextbox(anchor);
            HSSFRichTextString rtxt = new HSSFRichTextString("test");
            HSSFFont font = wb.createFont();
            font.setColor((short) 27);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints((short) 192);
            font.setFontName("Verdana");
            rtxt.applyFont(font);
            txtbox.setString(rtxt);
            txtbox.setLineStyle(HSSFShape.LINESTYLE_NONE);
            txtbox.setNoFill(true);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } 
    }

您能否帮助我并告诉我如何在 Excel(XSSF 或 HSSF 工作簿)中添加水印或在 Exel 标题中添加图片

谢谢穆达西尔

Answer 1

Microsoft Excel 没有内置水印功能。 However, there are a couple of ways that you can simulate the look of a watermark .. 但不幸的是，这些都不是 apache poi 直接支持的。

如果需求仅为XSSF，则可以使用XSSF 的底层低级对象在 header 中编写图片。

*.xlsx 文件只是一个ZIP 存档。所以我们可以解压它并看看内部结构。因此，请创建一个在标题中包含图片的 *.xlsx 文件，然后查看 *.xlsx ZIP 存档。

在/xl/worksheets/sheet1.xml(即工作表XML)中，我们发现类似以下内容:

...
<headerFooter>
 <oddHeader>&C&G</oddHeader>
</headerFooter>
<legacyDrawingHF r:id="rId1"/>
...

所以我们有&G，它指向&C输入标题中的图形。我们有一个关系 ID，它指向旧绘图。

我们在/xl/drawings/vmlDrawing1.vml中找到了这个旧绘图。此 *.vml 文件还与 /xl/media/ 中的图像相关。

所以我们必须做的是

1. 将图像添加到工作簿。这实际上已经由 apache poi 提供了。

2. 将“&G”放入中心标题中。这实际上也已经由 apache poi 提供了。

3. 创建 /xl/drawings/vmlDrawing1.vml 作为 PackagePart 并创建提供 commit( ) 方法，用于在写出文件时将其 XML 保存到包中。

4. 创建所有需要的关系。

以下代码是显示原理的工作草案。如我下载的图片AF101880439_en-us_draft.png从链接的 Microsoft 支持页面。

代码已完成并可以运行，并创建一个结果 *.xlsx 文件，该文件在第一张纸的中心标题中包含 DRAFT 图片。

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.util.IOUtils;
import org.apache.poi.ss.util.ImageUtils;

import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.POIXMLDocumentPart;

import org.apache.xmlbeans.XmlObject;

import static org.apache.poi.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;

public class CreateExcelPictureInHeaderAKAWatermark {

 static void createPictureForHeader(XSSFSheet sheet, int pictureIdx, String pictureTitle, int vmlIdx, String headerPos) throws Exception {
  OPCPackage opcpackage = sheet.getWorkbook().getPackage();

  //creating /xl/drawings/vmlDrawing1.vml
  PackagePartName partname = PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing" + vmlIdx+ ".vml");
  PackagePart part = opcpackage.createPart(partname, "application/vnd.openxmlformats-officedocument.vmlDrawing");
  //creating new VmlDrawing
  VmlDrawing vmldrawing = new VmlDrawing(part);

  //creating the relation to the picture in /xl/drawings/_rels/vmlDrawing1.vml.rels
  XSSFPictureData picData = sheet.getWorkbook().getAllPictures().get(pictureIdx);
  String rIdPic = vmldrawing.addRelation(null, XSSFRelation.IMAGES, picData).getRelationship().getId();

  //get image dimension
  ByteArrayInputStream is = new ByteArrayInputStream(picData.getData());
  java.awt.Dimension imageDimension = ImageUtils.getImageDimension(is, picData.getPictureType());
  is.close();

  //updating the VmlDrawing
  vmldrawing.setRIdPic(rIdPic);
  vmldrawing.setPictureTitle(pictureTitle);
  vmldrawing.setImageDimension(imageDimension);
  vmldrawing.setHeaderPos(headerPos);

  //creating the relation to /xl/drawings/vmlDrawing1.xml in /xl/worksheets/_rels/sheet1.xml.rels
  String rIdExtLink = sheet.addRelation(null, XSSFRelation.VML_DRAWINGS, vmldrawing).getRelationship().getId();

  //creating the <legacyDrawingHF r:id="..."/> in /xl/worksheets/sheetN.xml
  sheet.getCTWorksheet().addNewLegacyDrawingHF().setId(rIdExtLink);

 }

 public static void main(String[] args) throws Exception {

  Workbook workbook = new XSSFWorkbook();

  Sheet sheet;
  Header header;
  InputStream is;
  byte[] bytes;

  int pictureIdx; //we need it later

  sheet = workbook.createSheet();

  header = sheet.getHeader();
  header.setCenter("&G"); // &G means Graphic

  //add picture data to this workbook
  is = new FileInputStream("AF101880439_en-us_draft.png");
  bytes = IOUtils.toByteArray(is);
  pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
  is.close();

  //create header picture from picture data of this workbook
  createPictureForHeader((XSSFSheet)sheet, pictureIdx, "AF101880439_en-us_draft", 1, "CH"/*CenterHeader*/);

  FileOutputStream out = new FileOutputStream("CreateExcelPictureInHeader.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();    
 }

 //class for VmlDrawing
 static class VmlDrawing extends POIXMLDocumentPart {

  String rIdPic = "";
  String pictureTitle = "";
  java.awt.Dimension imageDimension = null;
  String headerPos = "";

  VmlDrawing(PackagePart part) {
   super(part);
  }

  void setRIdPic(String rIdPic) {
   this.rIdPic = rIdPic;
  }

  void setPictureTitle(String pictureTitle) {
   this.pictureTitle = pictureTitle;
  }

  void setHeaderPos(String headerPos) {
   this.headerPos = headerPos;
  }

  void setImageDimension(java.awt.Dimension imageDimension) {
   this.imageDimension = imageDimension;
  }

  @Override
  protected void commit() throws IOException {
   PackagePart part = getPackagePart();
   OutputStream out = part.getOutputStream();
   try {
    XmlObject doc = XmlObject.Factory.parse(

      "<xml xmlns:v=\"urn:schemas-microsoft-com:vml\""
     +" xmlns:o=\"urn:schemas-microsoft-com:office:office\""
     +" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"
     +" <o:shapelayout v:ext=\"edit\">"
     +"  <o:idmap v:ext=\"edit\" data=\"1\"/>"
     +" </o:shapelayout><v:shapetype id=\"_x0000_t75\" coordsize=\"21600,21600\" o:spt=\"75\""
     +"  o:preferrelative=\"t\" path=\"m@4@5l@4@11@9@11@9@5xe\" filled=\"f\" stroked=\"f\">"
     +"  <v:stroke joinstyle=\"miter\"/>"
     +"  <v:formulas>"
     +"   <v:f eqn=\"if lineDrawn pixelLineWidth 0\"/>"
     +"   <v:f eqn=\"sum @0 1 0\"/>"
     +"   <v:f eqn=\"sum 0 0 @1\"/>"
     +"   <v:f eqn=\"prod @2 1 2\"/>"
     +"   <v:f eqn=\"prod @3 21600 pixelWidth\"/>"
     +"   <v:f eqn=\"prod @3 21600 pixelHeight\"/>"
     +"   <v:f eqn=\"sum @0 0 1\"/>"
     +"   <v:f eqn=\"prod @6 1 2\"/>"
     +"   <v:f eqn=\"prod @7 21600 pixelWidth\"/>"
     +"   <v:f eqn=\"sum @8 21600 0\"/>"
     +"   <v:f eqn=\"prod @7 21600 pixelHeight\"/>"
     +"   <v:f eqn=\"sum @10 21600 0\"/>"
     +"  </v:formulas>"
     +"  <v:path o:extrusionok=\"f\" gradientshapeok=\"t\" o:connecttype=\"rect\"/>"
     +"  <o:lock v:ext=\"edit\" aspectratio=\"t\"/>"
     +" </v:shapetype><v:shape id=\"" + headerPos + "\" o:spid=\"_x0000_s1025\" type=\"#_x0000_t75\""
     +"  style='position:absolute;margin-left:0;margin-top:0;"
     +"width:" + (int)imageDimension.getWidth() + "px;height:" + (int)imageDimension.getHeight() + "px;"
     +"z-index:1'>"
     +"  <v:imagedata o:relid=\""+ rIdPic + "\" o:title=\"" + pictureTitle + "\"/>"
     +"  <o:lock v:ext=\"edit\" rotation=\"t\"/>"
     +" </v:shape></xml>"

    );
    doc.save(out, DEFAULT_XML_OPTIONS);
    out.close();
   } catch (Exception ex) {
    ex.printStackTrace();
   }
  }

 }

}

<小时/>

需要对导入进行更改才能使用当前的 apache poi 4.0.1:

...
//import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.ooxml.POIXMLDocumentPart;

import org.apache.xmlbeans.XmlObject;

//import static org.apache.poi.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;
import static org.apache.poi.ooxml.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;
...