目前4.0版本poi对水印支持的不友好,包括office和wps对水印支持的也不有好,本文通过页眉来实现水印的开发.
第一步确定页眉是否可以使用
如果,页眉是可以插入图片,但是poi目前只支持字符串类型:
Header header = sheet.getHeader();
header.setLeft("我是页眉");
代码生成出来的页眉也只是在打印预览的时候可以看到,xlsx本身是可以解压的,既然可以插入字符串的页眉,我们可以修改xlsx本身来判断页眉的定位,xlsx解压后,这个drawings就是,页眉的配置,这个大家可以解压一个插入页眉的图片试一下
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><xml xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:v="urn:schemas-microsoft-com:vml">
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1"/>
</o:shapelayout>
<v:shape id="RH" o:spid="_x0000_s1026" o:spt="75" alt="微信图片_20200425154944" type="#_x0000_t75" style="position:absolute;left:0pt;top:0pt;margin-left:0pt;margin-top:0pt;height:534.6pt;width:538.2pt;" filled="f" o:preferrelative="t" stroked="f" coordsize="21600,21600">
<v:path/>
<v:fill on="f" focussize="0,0"/>
<v:stroke on="f"/>
<v:imagedata o:relid="rId1" o:title="微信图片_20200425154944"/>
<o:lock v:ext="edit" rotation="t" aspectratio="t"/>
</v:shape>
</xml>
这个vmlDrawing1.vml就是上面的内容也就是图片的描述,其中id="RH" 是页眉不的左中右,这个是wsp的配置,office还可以这个有点不一样不过大体都是一个概念
这样我们就可以通过改动xlsx里面的xml文件来实现我们自己的页眉了
首先,header设置为图片格式
Header header = sheet.getHeader();
header.setLeft("&G"); // wps显示为&[图片]
然后我们定义我们自己的vmlDrawing
@Overrideprotected void commit() throws IOException {
PackagePart part = getPackagePart();
OutputStream out = part.getOutputStream();
try {
"<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();
}
}
下一步就是把这个对象插入到workbook
public static void putWaterRemarkToExcel(Sheet sheet, String waterRemarkPath, String position) throws Exception {
int pictureIdx = sheet.getWorkbook().addPicture(IOUtils.toByteArray(POICacheManager.getFile(waterRemarkPath)), Workbook.PICTURE_TYPE_PNG);
OPCPackage opcpackage = ((XSSFWorkbook) sheet.getWorkbook()).getPackage();
PackagePartName partname = PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing" + pictureIdx + ".vml");
PackagePart part = opcpackage.createPart(partname, "application/vnd.openxmlformats-officedocument.vmlDrawing");
VmlDrawing vmldrawing = new VmlDrawing(part);
//创建页眉,位置LEFT,下面headerPos填写对应的 Header header = sheet.getHeader();
switch (position){
case "LEFT":
header.setLeft("&G");
vmldrawing.setHeaderPos("LH");
break;
case "CENTER":
header.setCenter("&G");
vmldrawing.setHeaderPos("CH");
break;
case "RIGHT":
header.setRight("&G");
vmldrawing.setHeaderPos("RH");
break;
default:
throw new ExcelExportException("输入的position参数不合法");
}
XSSFPictureData picData = (XSSFPictureData) sheet.getWorkbook().getAllPictures().get(pictureIdx);
String rIdPic = vmldrawing.addRelation(null, XSSFRelation.IMAGES, picData).getRelationship().getId();
ByteArrayInputStream is = new ByteArrayInputStream(picData.getData());
java.awt.Dimension imageDimension = ImageUtils.getImageDimension(is, picData.getPictureType());
IOUtils.closeQuietly(is);
vmldrawing.setRIdPic(rIdPic);
vmldrawing.setPictureTitle(waterRemarkPath);
vmldrawing.setImageDimension(imageDimension);
String rIdExtLink = ((XSSFSheet) sheet).addRelation(null, XSSFRelation.VML_DRAWINGS, vmldrawing).getRelationship().getId();
((XSSFSheet) sheet).getCTWorksheet().addNewLegacyDrawingHF().setId(rIdExtLink);
}
通过上面的办法我们就可以给excel插入页眉继而实现水印的效果,最终效果,可以通过调整图片的大小实现不同的水印效果
具体代码参考 https://gitee.com/lemur/easypoi/里面的水印代码