问题描述
我是apache POI java开发的新手，我正在尝试使用下面的代码为excel添加水印。但水印ID覆盖了它背后的内容。我想在后台添加水印。
I'm new to apache POI java development, I'm trying to add watermark to excel using below code. But the watermark id overridding the contents behind it. I want to add watermark in background.
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标题中添加图片
Can you please assist me and tell me how can i add watermark in excel (XSSF or in HSSF workbook) or add picture in exel header
谢谢
Mudassir
ThanksMudassir
推荐答案
Microsoft Excel没有内置水印功能。 ..但遗憾的是，没有一种方法可以直接由apache poi支持。
Microsoft Excel doesn’t come with a built-in watermark feature. However, there are a couple of ways that you can simulate the look of a watermark.. But unfortunately none of those are directly supported by apache poi.
如果要求只是 XSSF ，那么可以使用底层低电平编程标题中的图片 XSSF 的对象。
If the requirement would be XSSF only, then one could programming a picture in the header using the underlaying low level objects of XSSF.
A * .xlsx 文件只是 ZIP 存档。所以我们可以解压缩并查看内部结构。那么创建一个 * .xlsx 文件，在标题中有一张图片，然后查看 * .xlsx ZIP 存档。
A *.xlsx file simply is a ZIP archive. So we can unzip it and having a look at the internals. So do creating a *.xlsx file having a picture in header and then look into the *.xlsx ZIP archive.
/xl/worksheets/sheet1.xml 这是工作表 XML ，我们发现如下：
There in /xl/worksheets/sheet1.xmlwhich is the sheets XML, we find something like:
...
<headerFooter>
<oddHeader>&C&G</oddHeader>
</headerFooter>
<legacyDrawingHF r:id="rId1"/>
...
所以我们有& G 指向中的图形& C 输入标题。我们有一个指向传统绘图的关系ID。
So we have &G which points to a Graphic in &Center header. And we have a relation Id which points to a legacy drawing.
我们在 /xl/drawings/vmlDrawing1.vml中找到的这个传统绘图。在此 * .vml 文件中，还与 / xl / media / 中的图像有关。
This legacy drawing we find in /xl/drawings/vmlDrawing1.vml. In this *.vml file also is a relation to a image in /xl/media/.
所以我们必须做的是
- 将图像添加到工作簿。这实际上是由
apache poi提供的。 -
将& G放入中心标题。这实际上也是由
apache poi提供的。
创建 / xl /drawings/vmlDrawing1.vml 作为 PackagePart 并创建一个 POIXMLDocumentPart ，它提供 commit（）在写出文件时将它的 XML 保存到包中的方法。
Creating /xl/drawings/vmlDrawing1.vml as a PackagePart and creating a POIXMLDocumentPart which provides commit() method for saving it's XML into the package while writing out the file.
以下代码是工作草案，其中显示了原理。正如我从关联的Microsoft支持页面下载的图片。
Following code is working draft which shows the principle. As the picture I have downloaded AF101880439_en-us_draft.png from the linked Microsoft support page.
代码已完成，可以生成并生成结果 *。xlsx 文件，其中间有DRAFT图片第一张标题。
The code is complete and works and creates a result *.xlsx file having the DRAFT-picture in center header of first sheet.
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*/);
workbook.write(new FileOutputStream("CreateExcelPictureInHeaderAKAWatermark.xlsx"));
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在Excel工作簿中添加水印的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！