Resize Image on workbook sheet does not work in apache poi

Question

i am using newest apache poi dependency and want to resize image (to not have it so so big after workbook opened):

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
    </dependencies>

1. I created new workbook as .xlsm and didnt change default font (it is Calibri 11 px) according to documentation.
2. downloaded image from pexels: Link To Pexels Image
3. used code like here:

import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

public class ImageResizer {

    public static  void main (String[] args) throws IOException {
        ImageResizer resizer = new ImageResizer();
    }
    public ImageResizer() throws IOException {
        InputStream inputStream;
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        Cell cell = null;
        XSSFRow sheetRow = null;

        try {
            File myFile = new File("C:\\Users\\Luke\\Downloads\\TestWorkbook_AddingImage.xlsm");
            FileInputStream fis = new FileInputStream(myFile);
            workbook = new XSSFWorkbook (fis);
            fis.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        }

        sheet = workbook.getSheetAt(0);

        FileInputStream stream = null;
        try {
            stream = new FileInputStream( "C:\\Users\\Luke\\Downloads\\pexels-yura-forrat-12850802.jpg" );
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        }
        CreationHelper helper = workbook.getCreationHelper();
        Drawing drawing = sheet.createDrawingPatriarch();

        ClientAnchor anchor = helper.createClientAnchor();
        final int pictureIndex = workbook.addPicture( stream, Workbook.PICTURE_TYPE_JPEG);
        stream.close();

        anchor.setCol1( 0 );
        anchor.setRow1( 0);
        final Picture pict = drawing.createPicture( anchor, pictureIndex );
        pict.resize(0.1,0.1);

        FileOutputStream os = new FileOutputStream("C:\\Users\\Luke\\Downloads\\TestWorkbook_AddingImage_Saved.xlsm");
        workbook.write(os);
        os.close();
        System.out.println("Done!");

        }

    }

And pict.resize(0.1, 0.1) method is making my picture very, very small (inside cell is only barely seen dot). I tried also with 0.5, 0.9 and even 2.0 but result is all the same. Why? What can i do to resize picture?

Best, Michal

Answer 1

This seems to be a bug in Picture.resize(double scaleX, double scaleY) and Picture.resize(double scale) when either ClientAnchor.Col1 or ClientAnchor.Row1 or both are 0. It works when both of the top left anchor cell indexes are greater than 0.

A workaround is to use Picture.resize() first, before resizing with scale. Picture.resize() first calculates the bottom right anchor position to fit the full native picture size. After that Picture.resize(double scaleX, double scaleY) works properly.

...
        anchor.setCol1(0);
        anchor.setRow1(0);
        final Picture pict = drawing.createPicture( anchor, pictureIndex );
        pict.resize();
        pict.resize(0.1, 0.1);
...