java excel 逐行读取,从Excel表单逐行读取图像

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 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.

解决方案

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.