从Excel表单逐行读取图像 [英] Read images from Excel sheet row by row

问题描述

我有一张Excel表格，其中包含员工信息，如员工的姓名，地址和照片。我想使用Java阅读它并将其存储到某些用户管理系统中。

以下是我的代码：

  int idColumn = ...; 
 POIFSFileSystem poifs = ...; 
 HSSFWorkbook工作簿=新HSSFWorkbook（poifs）; 
 HSSFSheet sheet = workbook.getSheetAt（0）; 
 
列表< HSSFPictureData>图片= workbook.getAllPictures（）; （int i = 0; i< pictures.size（）; i ++）{
 HSSFPictureData picture = pictures.get（i）; 
 
 
 //这不映射到图片中的行：
 HSSFRowrow = sheet.getRow（i）; 
 
 HSSFCell idCell = row.getCell（idColumn）; 
 long employeeId = idCell！= null？ （long）idCell.getNumericCellValue（）：0; 
 
 myUserService.updatePortrait（employeeId，picture.getData（））; 
} 
  

问题是它没有在Excel工作表上映射到确切的用户：
假设用户 A 在Excel表上具有图像 A 。但它并不映射到用户 A 。所以我想知道读取的图像是行的。

解决方案

正如你可能已经注意到的：Excel中的单元格永远不会包含 a 图片（或任何其他 Shape ）。相反，有一个单独的图层包含表格的所有 Shape 对象。这就是为什么你可以跨多个单元格放置图像。

但是，您可以使用形状的锚点来确定在定位期间连接到哪个单元格：

  int idColumn = ...; 
 int pictureColumn = ...; 
 HSSFSheet sheet = ...; 
 
（HSSFShape shape：sheet.getDrawingPatriarch（）。getChildren（））{
 if（shape instanceof HSSFPicture）{
 HSSFPicture picture =（HSSFPicture）shape; 
 HSSFClientAnchor anchor =（HSSFClientAnchor）picture.getAnchor（）; 
 
 //确保只使用相关图片
 if（anchor.getCol1（）== pictureColumn）{
 
 //使用锚点
 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（））; 
} 
} 
} 
} 
} 
  

我在我的例子中使用 HSSFPatriarch ，因为这样可以确定每张图片 （如果你有在文件中有多个表格）。

重要的是要注意，形状的锚点不需要位于图片被视觉定位的单元格中 - 虽然通常是在这种情况下，您可以从锚点的 dx1 和 dy1 属性中提取位置。

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.

解决方案

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.

这篇关于从Excel表单逐行读取图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！