java在Excel中添加png图片作为页眉(已解决)

1.背景

Excel在打印的时候需要附带水印打出，水印需要在下载Excel文件时就设置好
需要在打印的时候打印出水印，使用添加背景添加的水印在打印的时候不会出现，只有设置页眉，并将页眉设置为一长与打印纸张一样大小的图片，才可以在每一页中打印出水印

2.出现的问题

需要在打印的时候打印出水印，使用添加背景添加的水印在打印的时候不会出现，所以只有设置页眉，并将页眉设置为一长与打印纸张一样大小的图片，才可以在每一页中打印出水印，因为poi默认只支持字符串页眉设置，具体原因可以看下面参考文章。最终找到下面两种实现方法

参考：
poi源码详解默认只支持字符串页眉原因

3.实现方法

1 使用poi修改xlsx里面的xml文件来实现我们自己的页眉
2 使用Spire.XLS为Excel添加图片到页眉

有两种实现方法,推荐第一种，因为第一种是基于poi的另外扩展实现图片页眉，只要能拿到XSSFSheet就可以无缝切换到这种方式，能实现平滑迁移。
第二种方式jar包本身比较大，只用一个设置页眉的功能，依赖比较重。而且第二种只能在Excel处理完成后使用本地读取或者使用流读取在重新设置页眉，需要大幅修改原来代码输入输出流方式，整体调整会比较大。

4.使用poi修改xlsx里面的xml文件来实现我们自己的页眉

4.1 原理说明：

Excel本质上来说是一种xml描述文件，可以反向找到页眉设置的位置，然后改动xlsx里面的xml文件添加vmlDrawing1.vml页眉文件实现我们自己的页眉。

4.2 重写POIXMLDocumentPart 中的commit方法，写入我们自定义的vml文件

导入依赖

<!--poi导入导出组件-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>ooxml-schemas</artifactId>
	<version>1.0</version>
</dependency>

```java
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.xmlbeans.XmlObject;

import static org.apache.poi.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;
import java.io.IOException;
import java.io.OutputStream;

public 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 {
            // WPS测试通过版本
            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();
        }
    }

}

4.3 使用PackagePartName将图片打包进xml，然后使用重写的VmlDrawing写入vml页眉到对应文件目录位置

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackagingURIHelper;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.ImageUtils;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;

   /**
     * 为Excel设置图片页眉,大体思路为改动xlsx里面的xml文件来实现我们自己的页眉
     * @param sheet
     * @param waterRemarkPath
     * @param position
     * @throws Exception
     */
    public static void putWaterRemarkToExcel(XSSFSheet sheet, String waterRemarkPath, String position)throws Exception {
        InputStream inputStream = WaterMarkHandler.class.getResourceAsStream("/jpg/平台水印.png");
        byte[] byteData = FileUtil.readInputStream(inputStream);
        int pictureIdx = sheet.getWorkbook().addPicture(byteData, 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 IllegalArgumentException("输入的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);
    }

参考：
poi 版本_poi的excel图片水印生成
easypoi代码git地址

5.使用Spire.XLS为Excel添加图片到页眉

5.1 下载jar包后放入工程/resources/lib本地，pom引入

		<dependency>
			<groupId>e-iceblue</groupId>
			<artifactId>spire.xls.free</artifactId>
			<version>5.1.0</version>
			<scope>system</scope>
			<systemPath>${project.basedir}/src/main/resources/lib/spire.xls.free-5.1.0.jar</systemPath>
		</dependency>
		
		<!--includeSystemScope 修改pom打包,将本地libjar打入-->
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<version>2.1.4.RELEASE</version>
				<configuration>
					<includeSystemScope>true</includeSystemScope>
				</configuration>
				<executions>
					<execution>
						<goals>
							<goal>repackage</goal>
						</goals>
					</execution>
				</executions>
			</plugin>

5.2 设置页眉为图片示例代码

public class ImageHeader {
    public static void main(String[] args) throws IOException {
        String imageFile = "C:\\Users\\Test1\\Desktop\\Image.png";

        //加载Excel示例文档
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //获取第一个工作表
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //加载图片
        BufferedImage image = ImageIO.read( new File(imageFile));

        //设置图片页眉
        worksheet.getPageSetup().setLeftHeaderImage(image);
        worksheet.getPageSetup().setLeftHeader("&G");

        //设置显示样式
        worksheet.setViewMode(ViewMode.Layout);

        //保存文档
        workbook.saveToFile("output/ImageHeader.xlsx", ExcelVersion.Version2010);
    }
}

参考：
使用Spire.XLS为Excel添加图片到页眉

6.扩展，EsayExcel设置图片页眉

6.1 实现SheetWriteHandler的afterSheetCreate方法，调用putWaterRemarkToExcel为页眉添加图片水印

import com.alibaba.excel.write.handler.SheetWriteHandler;
/**
 * excel添加水印，只支持XSSFWorkbook，其余类别：SXSSFWorkbook、SXSSFWorkbook请另寻他法
 * easyExcel使用时需要设置inMemory(true)，否者默认使用的是SXSSFWorkbook，会报错！
 */
@Slf4j
@RequiredArgsConstructor
public class WaterMarkHandler implements SheetWriteHandler {
 
    private final String WATER_MARK;

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }
 
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 获取sheet表
        XSSFSheet sheet = (XSSFSheet) writeSheetHolder.getSheet();
        try {
            putWaterRemarkToExcel(sheet, WATER_MARK, "LEFT");
        } catch (Exception e) {
            log.info("添加水印失败, Message : {}", e.getMessage(), e);
        }
    }
}

6.2 导出示例，inMemory一定要配置

@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("导出测试", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), DemoData.class)
                .inMemory(true) // 注意，此项配置不能少
                .registerWriteHandler(new WaterMarkHandler("我是水印"))
                .sheet("模板")
                .doWrite(data());
}

参考：
EasyExcel导出添加水印(设置背景，非插入图片的方式)