CODEWITHSUNDEEP
javaexcelapache-poi
SANKET
SANKET

Reputation: 15

Read images from Excel sheet row by row

I have stuck up in reading images from an Excel sheet.

I have one Excel sheet which includes employee information like name, address and photo of an employee. I want to read it using Java and store it into some user management system.

Following is my code:

int idColumn = ...;
POIFSFileSystem poifs = ...;
HSSFWorkbook workbook = new HSSFWorkbook(poifs);
HSSFSheet sheet = workbook.getSheetAt(0);

List<HSSFPictureData> pictures = workbook.getAllPictures();
for (int i = 0; i < pictures.size(); i++) {
    HSSFPictureData picture = pictures.get(i);

    // This does not map to the row from the picture:
    HSSFRowrow = sheet.getRow(i);

    HSSFCell idCell = row.getCell(idColumn);
    long employeeId = idCell != null ? (long) idCell.getNumericCellValue() : 0;

    myUserService.updatePortrait(employeeId, picture.getData());
}

The problem is it's not mapping to the exact user as on Excel sheet: Suppose user A has image A on excel sheet. But it's not mapping to the user A. So I would like to know the read images row wise.

Upvotes: 0

Views: 4052

Answers (2)

praveen
praveen

Reputation: 81

You can use the following code to read images from an Excel sheet row by row:

POIFSFileSystem poifs = new POIFSFileSystem(fis) ;
          HSSFWorkbook workbook = new HSSFWorkbook(poifs);
          HSSFSheet sheet = workbook.getSheetAt(0);
          int idColumn1 = 14;
          int idColumn2 = 16;
          int pictureColumn = 0;
          //HSSFSheet sheet = null;

          for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
              if (shape instanceof HSSFPicture) {
                  HSSFPicture picture = (HSSFPicture) shape;
                  HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor();

                  // Ensure to use only relevant pictures
                  if (anchor.getCol1() == pictureColumn) {

                      // Use the row from the anchor
                      HSSFRow pictureRow = sheet.getRow(anchor.getRow1());
                      if (pictureRow != null) {
                          HSSFCell idCell14 = pictureRow.getCell(17);
                          HSSFCell idCell16 = pictureRow.getCell(19);
                         System.out.println(idCell14);
                              int age  =  (int)idCell14.getNumericCellValue();
                              int year  =  (int)idCell16.getNumericCellValue();

                              System.out.println(age+":"+year);
                              HSSFPictureData data = picture.getPictureData();
                             byte data1[] = data.getData();
                         FileOutputStream out = new FileOutputStream(age+"."+year+".png");
                              out.write(data1);
                              out.close();
                              pcount++;
                         }
                  }
              }

Upvotes: -1

Tobias Liefke
Tobias Liefke

Reputation: 9022

As you might have noticed: a cell in Excel does never contain a Picture (or any other Shape). Instead there is a separate layer that contains all Shape objects for a sheet. Thats why you can place an image across multiple cells.

But you could use the anchor of a shape to determine to which cell it was attached during positioning:

int idColumn = ...;
int pictureColumn = ...;
HSSFSheet sheet = ...;

for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
    if (shape instanceof HSSFPicture) {
        HSSFPicture picture = (HSSFPicture) shape;
        HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor();

        // Ensure to use only relevant pictures
        if (anchor.getCol1() == pictureColumn) {

            // Use the row from the anchor
            HSSFRow pictureRow = sheet.getRow(anchor.getRow1());
            if (pictureRow != null) {
                HSSFCell idCell = pictureRow.getCell(idColumn);
                if (idCell != null) {
                    long employeeId = (long) idCell.getNumericCellValue();
                    myUserService.updatePortrait(employeeId, picture.getData());
                }
            }
        }
    }
}

I'm using the HSSFPatriarch in my example, as this makes it possible to determine the pictures per sheet (if you've got more than one sheet in the file).

It's important to notice that the anchor of a shape doesn't need to be in the cell where the picture is visually positioned - although usually it is. In that case you could extract the position from the dx1 and dy1 attributes of the anchor.

Upvotes: 3

Related Questions