POI - Fixed width of image in Excel cell

Question

I followed the approach to add a new image with the POI.

    cell.getRow().setHeight(img.getHeightForExcel());
    sheet.setColumnWidth(cell.getColumnIndex(), img.getWidthForExcel());

    final int picID = workBook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
    /* Create the drawing container */
    final XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();

    // ========adding image START
    final XSSFClientAnchor myAnchor = new XSSFClientAnchor();
    myAnchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    /* Define top left corner, and we can resize picture suitable from there */
    myAnchor.setCol1(cell.getColumnIndex()); // Column start
    myAnchor.setRow1(rowNum - 1); // Row start
    myAnchor.setCol2(cell.getColumnIndex() + 2); // Column end (covers two columns)
    myAnchor.setRow2(rowNum); // Row end

    /* Invoke createPicture and pass the anchor point and ID */
    final XSSFPicture myPicture = drawing.createPicture(myAnchor, picID);

In principal this works quite well. I specify the width of the column at the beginning with the width of the image. (The height as well).

The major problem I'm facing is that as soon as I run autoadjust like

    for (; i < max; i++) {
        xlsWorkbook.getSheet().autoSizeColumn(i);
    }

I get the problem that the first two columns are resized as well. But with this the width of the image is resized as well. Since the width might be quite long (or quite narrow) I don't want to affect the image size.

Is there a way to set the width of the image despite the column width?

score 2 · Accepted Answer · answered Jun 01 '21 at 17:20

If you don't want resizing the image when column widths are changing, then you cannot using that approach. This approach explicitly tells that the image shall be sized as the cell size it is anchored to. So if that cell size changes, the pictures size changes too.

You might think that ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE should protect the image from resizing. But this only is valuable when opened in Excel GUI. Apache poi does not respect ClientAnchor.AnchorType while auto sizing the columns. May be this will change in later versions. But in current version apache poi 5.0.0 it does not.

So to fulfill your requirement you set only a one cell anchor. That is only anchor.setCol1 and anchor.setRow1 as the upper left position of the picture. Then you need resizing the picture later to set the bottom right position. You must do that resizing after all column widths and row heights are set. So after auto sizing the columns. Else the resizing the columns will resizing the picture again.

Complete example:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.util.IOUtils;

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;


class ImageTest {

 public static void main(String[] args) throws Exception {

  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet("My Sample Excel");
  //FileInputStream obtains input bytes from the image file
  InputStream inputStream = new FileInputStream("./logo.png");
  //Get the contents of an InputStream as a byte[].
  byte[] bytes = IOUtils.toByteArray(inputStream);
  //Adds a picture to the workbook
  int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
  //close the input stream
  inputStream.close();
  //Returns an object that handles instantiating concrete classes
  CreationHelper helper = wb.getCreationHelper();
  //Creates the top-level drawing patriarch.
  Drawing drawing = sheet.createDrawingPatriarch();

  //Create an anchor that is attached to the worksheet
  ClientAnchor anchor = helper.createClientAnchor();
  
  //Set anchor type; only valuable in Excel GUI  
  anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);

  //Create an anchor with upper left cell only
  anchor.setCol1(1); //Column B
  anchor.setRow1(2); //Row 3

  //Create a picture
  Picture pict = drawing.createPicture(anchor, pictureIdx);
   
  //Reset the image to the original size
  //pict.resize(); // don't do this before autosize column

  //Create cell in column B to auto sizing that column
  Cell cell = sheet.createRow(0).createCell(1);
  cell.setCellValue("12345678901234567890");

  sheet.autoSizeColumn(1);
  
  //Reset the image to the original size
  //pict.resize();
  //Reset the image to half the original size
  pict.resize(0.5);

  //Write the Excel file
  FileOutputStream fileOut = null;
  fileOut = new FileOutputStream("./myFile.xlsx");
  wb.write(fileOut);
  fileOut.close();

 }
}

POI - Fixed width of image in Excel cell

1 Answers1