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

0 votes

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?

Oct 9, 2022 in Others by Kithuzzz
• 38,000 points
2,238 views

1 answer to this question.

0 votes

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. 

answered Oct 9, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Apache POI. Setup data filters in Excel

It's already enabled in Apache POI 3.7. ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 63,600 points
2,102 views
0 votes
1 answer

Formatting Excel Spreadsheet using Apache POI in JAVA

Try this: CellRangeAddress range= new CellRangeAddress(firstrow, lastrow, firstcol, ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,600 points
2,264 views
0 votes
1 answer

Excel formula gives error when write using Apache-poi library in Java

I tested some sample code. The cell ...READ MORE

answered Nov 5, 2022 in Others by narikkadan
• 63,600 points
1,980 views
0 votes
1 answer

How to create page borders using Apache POI in excel files with Java?

Microsoft Excel cannot do this. Libreoffice Calc ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 63,600 points
1,068 views
0 votes
1 answer
0 votes
1 answer

Can anyone show how to modify an existing excel sheet using Apache POI?

Hello Prakash, to modify an existing excel ...READ MORE

answered Jul 17, 2019 in Selenium by Anvi
• 14,150 points
3,848 views
0 votes
1 answer

How can I read numeric data from an Excel sheet using Selenium Webdriver?

Hey Jignesh, for reading numeric data from ...READ MORE

answered Jul 17, 2019 in Selenium by Abha
• 28,140 points
8,230 views
0 votes
1 answer

What is the better API to Reading Excel sheets in java - JXL or Apache POI

Here are the things where both APIs ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,600 points
3,436 views
0 votes
1 answer

Comparing two Excel files in R and returning different titles

Solution: library(tidyverse) dat <- read_xlsx("Book1.xlsx") dat2 <- read_xlsx("Book2.xlsx") book1_output <- anti_join(dat,dat2, ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,600 points
2,025 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP