Managed to make a bit of a breakthrough this evening. Now, I am able to
expose the name of the image file and it's dimensions through the
ShapeInformation class - very bad name really, it should be
PictureInformation I guess.
Anayway, the 'new' code is below and if you take a look in the while loop at
the end of the getPictures() method, you will see how the classes can be
utilised. As always, there are ways to improve this code; as far as I am
aware, the Escher Layer also manages all drawings, shapes and images in the
workbook and the method I have taken does not distinguish between a picture
and and other sort of shape that could be placed into or onto the worksheet.
That may be enough for your immediate needs - I hope it is - but if not, I
am going to guess that different subclasses of EscherRecord are probably the
way to distinguish the different drawing objects.
Secondly, I am not too sure that I have interpreted the offsets correctly.
The values retunred by the getCol1() and getRow1() methods will tell you
what cell is at the top left hand corner of the image whilst those returned
by getCol2() and getRow2() which cell is at the bottom right hand corner.
Exactly how the offsets work, I do not know but I am certain you can find
that out by experimentation.
Thridly, there is lots of other information you will likely need to expose;
the type of the anchor being just one of these bits. It is all - at least I
think it is all - exposed by the two objects we have captured - the opt and
anchor records. If you read the javadoc for these, I am confident that will
explain what is available and you can then modify 'my' code accordingly.
Finally, the actual copying of the images could be accomplished in two ways
I guess, by using the path information exposed by the ShapeInformation class
or by using the data stream available through the HSSFPictureData class. If
you need to go with the latter approach that is where another problem may
raise it's head; how to tie together the HSSFPictureData object with the
positional information exposed by the ShapeInformation class.
Good luck and if you need to ask any questions fell free to send me an
e-mail.
*****************************************************************
import java.io.FileInputStream;
import java.io.BufferedInputStream;
import java.io.File;
import java.util.List;
import java.util.Iterator;
import java.util.HashMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.record.EscherAggregate;
import org.apache.poi.ddf.EscherContainerRecord;
/**
*
* @author MB
*/
public class ExtractPictures {
private HashMap<String, ShapeInformation> shapes = new HashMap<String,
ShapeInformation>();
/**
* @param args the command line arguments
*/
public void findPictures(String inputFilename) {
File inputFile = null;
FileInputStream fileIStream = null;
BufferedInputStream bufIStream = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
EscherAggregate escherAggregate = null;
EscherContainerRecord escherContainer = null;
List childContainers = null;
List childRecords = null;
Iterator listIterator = null;
try{
inputFile = new File(inputFilename);
fileIStream = new FileInputStream(inputFile);
bufIStream = new BufferedInputStream(fileIStream);
workbook = new HSSFWorkbook(bufIStream);
sheet = workbook.getSheetAt(0);
escherAggregate = sheet.getDrawingEscherAggregate();
if(escherAggregate != null) {
escherContainer = escherAggregate.getEscherContainer();
this.iterateContainer(escherContainer, 1);
}
java.util.Set<String> keys = shapes.keySet();
Iterator<String> keyIterator = keys.iterator();
while(keyIterator.hasNext()) {
String key = keyIterator.next();
ShapeInformation shape = shapes.get(key);
System.out.println("The picture file name is: " +
shape.getPictureFilename());
System.out.println("The co-ordinates of the top left hand
corner of the image are: " +
shape.getCol1() +
", " +
shape.getRow1());
System.out.println("The co-ordinates of the bottom hand
corner of the image are: " +
shape.getCol2() +
", " +
shape.getRow2());
}
}
catch(Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows:...............");
ex.printStackTrace(System.out);
}
finally {
if(bufIStream != null) {
try {
bufIStream.close();
}
catch(Exception ex) {
// I G NO R E //
}
}
}
}
private void iterateContainer(EscherContainerRecord escherContainer, int
level) {
List childRecords = escherContainer.getChildRecords();
Iterator listIterator = null;
org.apache.poi.ddf.EscherSpgrRecord sprgRecord = null;
org.apache.poi.ddf.EscherSpRecord spRecord = null;
org.apache.poi.ddf.EscherOptRecord optRecord = null;
org.apache.poi.ddf.EscherClientAnchorRecord anchrRecord = null;
org.apache.poi.ddf.EscherClientDataRecord dataRecord = null;
org.apache.poi.ddf.EscherDgRecord dgRecord = null;
Object next = null;
listIterator = childRecords.iterator();
while(listIterator.hasNext()) {
next = listIterator.next();
if(next instanceof org.apache.poi.ddf.EscherContainerRecord) {
this.iterateContainer((org.apache.poi.ddf.EscherContainerRecord)next,
++level);
}
else {
if(next instanceof org.apache.poi.ddf.EscherSpgrRecord) {
sprgRecord = (org.apache.poi.ddf.EscherSpgrRecord)next;
}
else if(next instanceof org.apache.poi.ddf.EscherSpRecord) {
spRecord = (org.apache.poi.ddf.EscherSpRecord)next;
}
else if(next instanceof org.apache.poi.ddf.EscherOptRecord)
{
optRecord = (org.apache.poi.ddf.EscherOptRecord)next;
String key = String.valueOf(level);
if(this.shapes.containsKey(key)) {
this.shapes.get(key).setOptRecord(optRecord);
}
else {
ShapeInformation shape = new
ShapeInformation(level);
shape.setOptRecord(optRecord);
this.shapes.put(key, shape);
}
}
else if(next instanceof
org.apache.poi.ddf.EscherClientAnchorRecord) {
anchrRecord =
(org.apache.poi.ddf.EscherClientAnchorRecord)next;
String key = String.valueOf(level);
if(this.shapes.containsKey(key)) {
this.shapes.get(key).setAnchorRecord(anchrRecord);
}
else {
ShapeInformation shape = new
ShapeInformation(level);
shape.setAnchorRecord(anchrRecord);
this.shapes.put(key, shape);
}
}
else if(next instanceof
org.apache.poi.ddf.EscherClientDataRecord) {
dataRecord =
(org.apache.poi.ddf.EscherClientDataRecord)next;
}
else if(next instanceof org.apache.poi.ddf.EscherDgRecord) {
dgRecord = (org.apache.poi.ddf.EscherDgRecord)next;
}
}
}
}
public static final void main(String[] args) {
new ExtractPictures().findPictures("C:\\temp\\picture.xls");
}
}
import org.apache.poi.ddf.EscherOptRecord;
import org.apache.poi.ddf.EscherClientAnchorRecord;
import org.apache.poi.ddf.EscherProperty;
import org.apache.poi.ddf.EscherComplexProperty;
import org.apache.poi.ddf.EscherProperties;
import java.util.List;
import java.util.Iterator;
/**
* Encapsulate the following information about a picture on an Excel
* spreadsheet;
*
* The name of the picture.
* The numbers of rows and columns that together identify the cells at the
top
* left and bottom right hand corners of the picture.
* Any offset within those cells. Apparantly, these offset will always be in
the
* range from 0 to 1023.
*
* @author MB
*/
public class ShapeInformation {
private EscherOptRecord optRecord = null;
private EscherClientAnchorRecord anchrRecord = null;
private EscherProperty fileProperty = null;
private String encoding = null;
private String pictureFilename = null;
private int level = 0;
public ShapeInformation(int level) {
this(level, "UTF-16LE");
}
public ShapeInformation(int level, String encoding) {
this.level = level;
this.encoding = encoding;
}
public void setOptRecord(EscherOptRecord optRecord) {
this.optRecord = optRecord;
List propList = this.optRecord.getEscherProperties();
Iterator iter = propList.iterator();
while(iter.hasNext()) {
EscherProperty property = (EscherProperty)iter.next();
if(property.getPropertyNumber() ==
EscherProperties.BLIP__BLIPFILENAME) {
if(property.isComplex()) {
EscherComplexProperty complexProp =
(EscherComplexProperty)property;
try {
this.pictureFilename = new
String(complexProp.getComplexData(), "UTF-16LE").trim();
}
catch(java.io.UnsupportedEncodingException ueEx) {
this.pictureFilename = null;
}
}
}
}
}
public void setAnchorRecord(EscherClientAnchorRecord anchrRecord) {
this.anchrRecord = anchrRecord;
}
public String getPictureFilename() {
return(this.pictureFilename);
}
/**
* Get the row number for the top left hand corner of the picture.
*
* @return Get the row number for the top left hand corner of the
picture.
*/
public short getRow1() {
return(this.anchrRecord.getRow1());
}
/**
* Get the row number for the bottom right hand corner of the picture.
*
* @return Get the row number for the bottom right hand corner of the
* picture.
*/
public short getRow2() {
return(this.anchrRecord.getRow2());
}
/**
* Get the column number for the top left hand corner of the picture.
*
* @return Get the column number for the top left hand corner of the
* picture.
*/
public short getCol1() {
return(this.anchrRecord.getCol1());
}
/**
* Get the column number for the bottom right hand corner of the
picture.
*
* @return Get the column number for the bottom right hand corner of the
* picture.
*/
public short getCol2() {
return(this.anchrRecord.getCol2());
}
/**
* Get the amount the image is offset from the top edge of the cell at
* the top left hand corner of the image. This number will always be
* between 0 and 1023.
*
* @return Get the offset form the top of the cell at the top left hand
* corner of the image.
*/
public short getOffsetRow1() {
return(this.anchrRecord.getDy1());
}
/**
* Get the amount the image is offset from the bottom of the cell at
* the bottom right hand corner of the image. This number will always be
* between 0 and 1023.
*
* @return Get the offset from the bottom of the cell at the bottom
right
* hand corner of the image.
*/
public short getOffsetRow2() {
return(this.anchrRecord.getDy2());
}
/**
* Get the amount the image is offset from the right hand edge of the
cell
* at the top left hand corner of the image.
*
* @return Get the offset from the right hand edge of the cell at the
top
* left hand corner of the image.
*/
public short getOffsetCol1() {
return(this.anchrRecord.getDx1());
}
/**
* Get the amount the image is offset from the right hand edge of the
cell
* at the bottom right hand corner of the image.
*
* @return Get the offset from the top of the cell at the bottom right
hand
* corner of the image.
*/
public short getOffsetCol2() {
return(this.anchrRecord.getDx2());
}
}
matthewxb wrote:
>
> I have a workbook contains two pictures. I want to copy the pictures from
> a source workbook to a target workbook. How can I do that?
>
> I used HSSFWorkbook.getAllPictures() to retrieve all the pictures, but it
> only allow me to read the pictures' binary data. I have to know the size
> and the location of each picture.
> I write a loop to read each worksheet and call
> HSSFSheet.getDrawingPatriarch(). It contains four methods in
> HSSFPatriarch, suppose they represent the location of picture.
>
> HSSFPatriarch.getX1(),HSSFPatriarch.getX2(), HSSFPatriarch.getY1(),
> HSSFPatriarch.getY2()
>
> But above methods return 0, which is incorrect.
>
> And the size of picture I couldn't find a way to know, since the actual
> picture size and the size shown in worksheet are different.
>
> I want to copy the pictures from a source workbook to a target workbook.
> How can I do that? Please advice, thank you very much!
>
--
View this message in context:
http://www.nabble.com/Copy-images-from-Workbook-to-Workbook-tp23032425p23162091.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
