Member Avatar for subhraakasuny

Hi Team,

I have an excel which has multiple rows and each row can have multiple images associated with that , which are postioned on a specific cells of that particular row.I know while we are positioning a image on top of a cell, that is not associated with the cell and it is actually floting the sheet of the workbook.My target is to read those images and at the same time mapping that image with particular cell using apche POI. I have two questions actually:

  1. Is there any way to associate an image with a excel cell so that I can read the image using POI as a cell property?
  2. If above point is not feasible, then is there any way that I can read the image from the excel and map that images with particular rows?

**I have attached the sample data.

I have already treid cell.getSheet().getWorkbook().getAllPictures().. But that is returning all the images, but I don't understand which image is positioned on top of which cell.

Any help will be highly appreciated. I am waiting for your reply :).

  • 2 Contributors
  • 1 Reply
  • 1K Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by orichisonic

Dani AI

Generated

Short answer: Excel pictures are drawing shapes that float above the grid — they are not cell properties — so calling workbook.getAllPictures() only returns image bytes, not where they sit on the sheet. Use the sheet drawing API to inspect picture shapes and their anchors (row/column + dx/dy offsets) to map images back to rows or cells. (poi.apache.org)

A practical POI approach (for .xlsx): get the sheet drawing, iterate its shapes, pick picture shapes, then read each picture's ClientAnchor (use getRow1()/getCol1() or the offsets to decide which row/column it belongs to). Example (XSSF-only sketch):

Workbook wb = WorkbookFactory.create(new File("input.xlsx"));
XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(0);
XSSFDrawing drawing = sheet.getDrawingPatriarch();
if (drawing != null) {
    for (XSSFShape s : drawing.getShapes()) {
        if (s instanceof XSSFPicture) {
            XSSFPicture pic = (XSSFPicture) s;
            XSSFClientAnchor a = pic.getClientAnchor();
            int row = a.getRow1();   // top-left anchor (0-based)
            int col = a.getCol1();
            byte[] bytes = pic.getPictureData().getData();
            // save bytes and map to (row,col) or compute center using dx/dy
        }
    }
}

This is the standard pattern; see the drawing/shape APIs and community examples for details. (poi.apache.org)

Troubleshooting & tips: .getCol1()/.getRow1() can return -1 for absolute anchors, pictures can span multiple cells, and XSSF uses EMU units for offsets (convert with Units.EMU_PER_PIXEL) so convert dx/dy when calculating the image center. For .xls use HSSFPatriarch / HSSFPicture similarly. If mapping must be exact, compute the pixel position from anchor + column widths / row heights and then pick the cell whose rectangle the image center falls into. (poi.apache.org)

Notes: this expands on 's original attempt and complements 's alternative suggestion (third-party libraries can reposition images, but POI itself exposes anchors for mapping).

Member Avatar for orichisonic

Hi, it is not possible to read an image from a cell as a cell property. If you only want to read the images from a worksheet and reposition them on a specific cell, refer to the following code snippet. This requires another free Excel API instead of POI.

import com.spire.xls.*;

public class ChangeImagePosition {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook wb= new Workbook();

        //Load an Excel file
        wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\images.xlsx");

        //Get the first image
        ExcelPicture excelPicture = wb.getWorksheets().get(0).getPictures().get(0);

        //Place the image on a specific row and column
        excelPicture.setTopRow(1);
        excelPicture.setLeftColumn(2);

        //Save to file
        wb.saveToFile("output/ChangePicPosition.xlsx",ExcelVersion.Version2016);
    }
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.