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

Question

I follow the sample and add pictures as watermarks 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());
    }

} 

The strategy generally functions fairly well. To the Excel is inserted a picture. However, the appearance is distinct.

• When editing a picture in Excel, the image is displayed in the background (of the sheets). However, when I print the sheet, it is NOT visible.
• In LibreOffice (7.1), the image is printed rather than displayed while the sheet is being edited.

Is it possible to fix it so that it works in both Spreadsheets?

Answer 1

There is nothing that apache poi could do to alter this behavior, which is a feature of all spreadsheet programs.

The code example you provided does not produce watermarks. Microsoft Excel does not include a watermark feature. Instead, it embellishes sheets with backdrop images.

Microsoft itself states: You cannot print a background graphic for an Excel worksheet. So using Microsoft Excel the sheet's 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.

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.