Apache POI - watermark in Excel - different appearence in Excel and LibreOffice

Question

I follow the sample and add pictures as watermark like the following:

private void addWaterMark4AllSheets() {
    final BufferedImage image = FontImage.createWatermarkImage();
    // Export to byte stream B
    final ByteArrayOutputStream os = new ByteArrayOutputStream();
    try {
        ImageIO.write(image, "png", os);
    } catch (final IOException e) {
        e.printStackTrace();
    }

    final XSSFWorkbook wb = (XSSFWorkbook) workBook;
    final int pictureIdx = wb.addPicture(os.toByteArray(), Workbook.PICTURE_TYPE_PNG);
    final POIXMLDocumentPart poixmlDocumentPart = wb.getAllPictures().get(pictureIdx);
    // ((XSSFSheet )(schreiben.getSheet()).
    for (int i = 0; i < workBook.getNumberOfSheets(); i++) {// Get each Sheet
        final XSSFSheet sheet = wb.getSheetAt(i);
        final PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();
        final String relType = XSSFRelation.IMAGES.getRelation();
        // add relation from sheet to the picture data
        final PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType,
                null);
        // set background picture to sheet
        sheet.getCTWorksheet().addNewPicture().setId(pr.getId());
    }

} 

In general the approach works quite fine. A picture is added into the Excel. But the appearance is different.

1. In Excel: the image is displayed in the background during the editing (of the sheets). But it is NOT displayed when I print the sheet.
2. In LibreOffice (7.1): the image is NOT displayed during the editing (of the sheet) - but is printed.

Is there a chance to fix it for working in both Spreadsheets?

Answer 1

There is nothing what apache poi could change as this behavior is by design in the different spreadsheet softwares.

Your linked code example does not creating watermarks. Watermark functionality is not available in Microsoft Excel. Instead it adds background pictures to sheets.

Microsoft itself states: You cannot print a background graphic for a Excel worksheet. So using Microsoft Excel the sheets background graphics only are visible in Excel GUI but not in prints.

In LibreOffice those background graphics are called watermarks but Libreoffice states in Defining Graphics or Colors in the Background of Pages (Watermark):

In spreadsheets this background appears only in the print behind the cells not formatted elsewhere.

So using Libreoffice the sheets background graphics are visible in prints only.

So what you had observed is by design.

In Add or remove a sheet background Microsoft describes methods to mimic a watermark in Excel. The usage of a picture in a header or footer to mimic a watermark using apache poi is described in apache POI adding watermark in Excel workbook. But this also means a print watermark. So the watermark is visible in print preview and print only. It is not visible in sheet's GUI. And the option to add picture in a header or footer to mimic a watermark is Excel only. LibreOffice does not provide that feature.

Conclusion:

There is not a possibility to have a functionality similar to a watermark which works in Excel and LibreOffice the same way.