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 in Others by Kithuzzz
• 20,660 points
126 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 by narikkadan
• 37,660 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 in Others by narikkadan
• 37,660 points
65 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 in Others by narikkadan
• 37,660 points
62 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 in Others by narikkadan
• 37,660 points
50 views
0 votes
1 answer

What are the RGB and HEX codes of the 3 color conditional format in Excel?

In Excel 2016 at least the colors ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
356 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,170 points
2,768 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,120 points
6,554 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 in Others by narikkadan
• 37,660 points
169 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 in Others by narikkadan
• 37,660 points
62 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