如何使用Apache POI从xls文件中获取带有名称的图片 [英] How to get pictures with names from an xls file using Apache POI

问题描述

使用 workbook.getAllPictures() 我可以获得图片数据，但不幸的是，它只是数据，而这些对象没有访问图片名称或任何其他相关信息的方法.

Using workbook.getAllPictures() I can get an array of picture data but unfortunately it is only the data and those objects have no methods for accessing the name of the picture or any other related information.

有一个 HSSFPicture类其中将包含图片的所有详细信息，但是如何从xls获取例如这些对象的数组?

There is a HSSFPicture class which would contain all the details of the picture but how to get for example an array of those objects from the xls?

更新:

发现了这样的问题如何找到一个在apache poi中包含图片的单元格，该单元格具有用于遍历工作表中所有图片的方法.那行得通.

Found SO question How can I find a cell, which contain a picture in apache poi which has a method for looping through all the pictures in the worksheet. That works.

现在，我可以尝试使用HSSFPicture类，我发现getFileName()方法正在返回不带扩展名的文件名.我可以使用getPictureData().suggestFileExtension()获得建议的文件扩展名，但我确实需要获取将图片添加到xls文件中后的扩展名.有办法吗?

Now that I was able to try the HSSFPicture class I found out that the getFileName() method is returning the file name without the extension. I can use the getPictureData().suggestFileExtension() to get a suggested file extension but I really would need to get the extension the picture had when it was added into the xls file. Would there be a way to get it?

更新2:

通过宏将图片添加到xls中.这是宏的一部分，它将图像添加到工作表中. fname是完整路径，imageName是文件名，都包括扩展名.

The pictures are added into the xls with a macro. This is the part of macro that is adding the images into the sheet. fname is the full path and imageName is the file name, both are including the extension.

Set img = Sheets("Receipt images").Pictures.Insert(fname)
img.Left = 10
img.top = top + 10
img.Name = imageName
Set img = Nothing

检查图片在Excel文件中是否已经存在的例程.

The routine to check if the picture already exists in the Excel file.

For Each img In Sheets("Receipt images").Shapes
    If img.Name = imageName Then
        Set foundImage = img
        Exit For
    End If
Next

这可以识别"image.jpg"与"image.gif"不同，因此img.Name包含扩展名.

This recognizes that "image.jpg" is different from "image.gif", so the img.Name includes the extension.

推荐答案

形状名称不在默认的POI对象中.因此，如果我们需要它们，我们必须处理基础对象.那是因为HSSF中的形状主要是EscherAggregate( http://poi.apache.org/apidocs/org/apache/poi/hssf/record/EscherAggregate.html )，我们可以从工作表中获取.从其父类AbstractEscherHolderRecord中，我们可以获得包含形状选项的所有EscherOptRecords.在这些选项中，还可以找到groupshape.shapenames.

The shape names are not in the default POI objects. So if we need them we have to deal with the underlying objects. That is for the shapes in HSSF mainly the EscherAggregate (http://poi.apache.org/apidocs/org/apache/poi/hssf/record/EscherAggregate.html) which we can get from the sheet. From its parent class AbstractEscherHolderRecord we can get all EscherOptRecords which contains the options of the shapes. In those options are also to find the groupshape.shapenames.

我的例子不是完整的解决方案.它仅用于显示可以使用哪些对象来实现此目的.

My example is not the complete solution. It is only provided to show which objects could be used to achieve this.

示例:

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.FileInputStream;
import java.io.InputStream;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.hssf.record.*;
import org.apache.poi.ddf.*;

import java.util.List;
import java.util.ArrayList;

class ShapeNameTestHSSF {

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("workbook1.xls");
   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);

   EscherAggregate escherAggregate = ((HSSFSheet)sheet).getDrawingEscherAggregate();

   EscherContainerRecord escherContainer = escherAggregate.getEscherContainer().getChildContainers().get(0); 
   //throws java.lang.NullPointerException if no Container present

   List<EscherRecord> escherOptRecords = new ArrayList<EscherRecord>();

   escherContainer.getRecordsById(EscherOptRecord.RECORD_ID, escherOptRecords);

   for (EscherRecord escherOptRecord : escherOptRecords) {
    for (EscherProperty escherProperty : ((EscherOptRecord)escherOptRecord).getEscherProperties()) {
     System.out.println(escherProperty.getName());
     if (escherProperty.isComplex()) {
      System.out.println(new String(((EscherComplexProperty)escherProperty).getComplexData(), "UTF-16LE"));
     } else {
      if (escherProperty.isBlipId()) System.out.print("BlipId = ImageId = ");
      System.out.println(((EscherSimpleProperty)escherProperty).getPropertyValue());
     }
     System.out.println("=============================");
    }
    System.out.println(":::::::::::::::::::::::::::::");
   }


   FileOutputStream fileOut = new FileOutputStream("workbook1.xls");
   wb.write(fileOut);
   fileOut.flush();
   fileOut.close();

  } catch (InvalidFormatException ifex) {
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

再次:这不是立即可用的解决方案.由于EscherRecords的复杂性，此处无法提供即用型解决方案.也许是为了获取正确的图像形状及其相关的EscherOptRecords EscherRecords，您可以递归地遍历EscherAggregate中的所有EscherRecords，以检查它们是否为ContainerRecords以及是否遍历其子级，等等.

Again: This is not a ready to use solution. A ready to use solution cannot be provided here, because of the complexity of the EscherRecords. Maybe to get the correct EscherRecords for the image shapes and their related EscherOptRecords, you have recursive to loop through all EscherRecords in the EscherAggregate checking whether they are ContainerRecords and if so loop through its children and so on.

这篇关于如何使用Apache POI从xls文件中获取带有名称的图片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！