Protect/freeze the position and size of image in xslx sheet using apache POI

Question

I am creating an xlsx file using Apache POI in which a logo is inserted at the top left side. The issue is that, when ever the size of the cell is increased, the image also expands. How do I freeze or protect the image's size and position so that when the cell size increases, the image remains the same (doesn't expand).

Following is the code to insert the image.

rowNo = 1;
row = sheet.createRow(rowNo);
//For Adding sample Logo
addImage(1, 1, 3, 4, appPath + "/images/sample_logo.png"); 

public boolean addImage(int col1, int row1, int col2, int row2, String FileName) {
        try {
            FileInputStream fileInputStream = null;
            try {
                fileInputStream = new FileInputStream(FileName);
            } catch (FileNotFoundException e) {
                return false;
            }
            ByteArrayOutputStream imgBytes = new ByteArrayOutputStream();
            int b;

            try {
                while ((b = fileInputStream.read()) != -1) {
                    imgBytes.write(b);
                }
                fileInputStream.close();
            } catch (IOException e) {
                return false;
            }
        sheet.addMergedRegion(new CellRangeAddress(row1, row2 - 1, col1, col2 - 1)); 
       // sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 2));
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
            int index = workBook.addPicture(imgBytes.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG); //index = 4
            XSSFDrawing patriarch = sheet.createDrawingPatriarch();
            patriarch.createPicture(anchor, index);
            anchor.setAnchorType(2);
        } catch (Exception ex) {
            LOGGER.error(ex);
        }
        return true;
    }

Kindly help me out.

score 3 · Accepted Answer · answered Jun 28 '19 at 14:11

The setting Format Picture - Size and Properties - Don't move or size with cells will be set in apache poi using XSSFClientAnchor.setAnchorType set to ClientAnchor.AnchorType DONT_MOVE_AND_RESIZE.

Example:

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.util.IOUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.xssf.usermodel.*;

class CreateExcelPictures {

 static String excelPath = "./ExcelWithLogo.xlsx";
 static String appPath = "./";
 static XSSFWorkbook workbook;
 static XSSFSheet sheet;

 static void addImage(int col1, int row1, int col2, int row2, String imageFileName, ClientAnchor.AnchorType anchorType) throws Exception {

  InputStream imageInputStream = new FileInputStream(imageFileName);
  byte[] bytes = IOUtils.toByteArray(imageInputStream);
  int pictureId = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
  imageInputStream .close();

  XSSFClientAnchor anchor = workbook.getCreationHelper().createClientAnchor();
  anchor.setAnchorType(anchorType);
  anchor.setCol1(col1);
  anchor.setRow1(row1);
  anchor.setCol2(col2);
  anchor.setRow2(row2);

  XSSFDrawing drawing = sheet.createDrawingPatriarch();

  XSSFPicture picture = drawing.createPicture(anchor, pictureId);

 }

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

  workbook = new XSSFWorkbook();
  sheet = workbook.createSheet();

  addImage(1, 1, 3, 4, appPath + "sample_logo.png", ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE); 

  FileOutputStream fos = new FileOutputStream(excelPath);
  workbook.write(fos);
  fos.close();
  workbook.close();

 }
}

After that the picture sample_logo.png anchored in B4:C4 will not be moved and/or resized through changing cell sizes.

Protect/freeze the position and size of image in xslx sheet using apache POI

1 Answers1