You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by matthewxb <ma...@gmail.com> on 2009/04/14 05:02:21 UTC
Copy images from Workbook to Workbook
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-tp23032425p23032425.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Copy images from Workbook to Workbook
Posted by MSB <ma...@tiscali.co.uk>.
Sorry I have not been able to make more progress on this problem but with the
good weather our clients are pressing us to complete work on a number of
contracts.
Anyway Matthew, I have made a little more progress - getting rid of all the
duplicate records so that now, the code only returns a single opt and anchor
record for each image. The change was very simple really once I understood
that containers were also child records. Have a look at the modified
iterateContainer() method to see what I mean;
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.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);
}
}
}
}
}
There are still a few challenges - chief amongst them being how to match the
opt/anchor record combination to an actual image though I am going to guess
this should be possible as one of the properties of the opt record returns
something that looks like the path to and name of the picture.
Hopefully, I will be able to work on this code a little more tonight and I
will post if there is any progress.
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-tp23032425p23156516.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Copy images from Workbook to Workbook
Posted by MSB <ma...@tiscali.co.uk>.
Hello Matthew,
Are you still trying to do this? If so, I may - that is a big may - have a
solution for you.
To get at the size of the pictures, you really need to get acess to the
client anchor object that is associated with each one. It may be possible to
do this if you open the workbook, get a reference to the sheet that
'contains' the pictures, get a reference to the patriarch from this and ask
it for a list of it's children. If I am correct, this will return to you a
list of all the shapes that the patriarch contains. HSSFPicture is a
subclass of HSSFSimpleShape which itself is a subclass of HSSFShape and so I
am hoping that this list will contain references to the pictures. What I am
hoping - and I have not tested this so forgive me please if I am incorrect -
is that the List of children recovered from the patriarch will contain
entries of type HSSFPicture - or that you can cast them to this type if all
else fails. That way, you can call the getImageDimension() or
getPreferredSize() methods defined on the HSSFPicture class.
As I said, this may work, it may equally fall down flat on it's face, but it
is probably worth trying.
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-tp23032425p23061964.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Copy images from Workbook to Workbook
Posted by MSB <ma...@tiscali.co.uk>.
Do not waste any time on this idea Matthew. I tried it out quickly and it did
not work at all. Sorry.
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-tp23032425p23078457.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Copy images from Workbook to Workbook
Posted by MSB <ma...@tiscali.co.uk>.
To get at the actual position of the image on the page, you will need to
interrogate the client anchor object. It has methods called getRow1(),
getRow2(), getCol1() and getCol2(). They each return an int that together
will give you the indices of the cells at the top left hand and bottom right
hand corners of the cell. I am not sure but I guess that this will allow you
to easilly construct an anchor object for positioning the images on the copy
worksheet.
One other problem that remains to be resolved is tying together images and
positional details. The opt record contains details about the image itself
including it's location - it's fully qualified name. Getting at that
information is looking to be a little complex however - I am guessing that
you will have to look into the properties - the List of EscherProperty
objects - the opt record object maintains and try to interrogate it for the
information.
The other problem is to prevent the code from returning multiple copies of
the information and I think that the key to this may be to use the recordID
value contained in both the opt and anchor records. Sadly, time is short for
me now - as it was a grand prix weekend, I booked time off even thugh this
is our busiest time of the year; not something I expect to do this week
sadly. Having said that, if I have the chance to work on the code, I will do
so.
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-tp23032425p23131185.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Copy images from Workbook to Workbook
Posted by matthewxb <ma...@gmail.com>.
Thanks MSB, I did try the method you suggested in the first post. Same as
you, it failed.
I have checked other post: "How does we know the position of the image?"
http://www.nabble.com/How-does-we-know-the-position-of-the-image--td21539675.html#a21709308
which concluded that we couldn't get the position of image by POI at the
moment.
I will check your codes but my project deadline is coming and I need to work
on other issues temporary :(
--
View this message in context: http://www.nabble.com/Copy-images-from-Workbook-to-Workbook-tp23032425p23129203.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Copy images from Workbook to Workbook
Posted by MSB <ma...@tiscali.co.uk>.
OK Matthew, I have no idea whether or not you still need to do this operation
but I have been able to figure out some more information and to get part of
the way to a solution - I say part as currently I am getting multiple copies
of the information and need to identify how to prevent this from happening.
To get at the size and location of the images, it is necessary to mess
around with objects in the Escher Layer; to be precise, we need to get at
the EscherOptRecord and the EscherClientAnchorRecord instances associated
with each picture. The way to get at these is to grab the EscherAggregate
object from the sheet, get a reference to the associated
EscherContainerRecord and to then iterate through the lower level children
of this container. I have put together some code that does this and included
it below.
As you will be able to see, there are two classes, the second of which is
just a convenient way to aggregate the two class instances together. Just
look into the main() method of the ExtractPictures class to see how to run
the code against a workbook. More work still needs to be done unfortunately,
but I hope this class points the way forward.
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(shape);
}
}
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 childContainers = escherContainer.getChildContainers();
List childRecords = escherContainer.getChildRecords();
Iterator listIterator = childContainers.iterator();
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;
while(listIterator.hasNext()) {
next = listIterator.next();
if(next instanceof org.apache.poi.ddf.EscherContainerRecord) {
this.iterateContainer((org.apache.poi.ddf.EscherContainerRecord)next,
++level);
}
else {
System.out.println("Found an instance of " +
next.getClass().getName() + " in the list of child containers.");
}
}
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;
/**
*
* @author MB
*/
public class ShapeInformation {
private EscherOptRecord optRecord = null;
private EscherClientAnchorRecord anchrRecord = null;
private int level = 0;
public ShapeInformation(int level) {
this.level = level;
}
public void setOptRecord(EscherOptRecord optRecord) {
this.optRecord = optRecord;
}
public void setAnchorRecord(EscherClientAnchorRecord anchrRecord) {
this.anchrRecord = anchrRecord;
}
public EscherOptRecord getOptRecord() {
return(this.optRecord);
}
public EscherClientAnchorRecord getAnchorRecord() {
return(this.anchrRecord);
}
public String toString() {
StringBuffer buffer = new StringBuffer();
buffer.append("**** Shape Information for level: " + this.level + "
****");
buffer.append("\n");
buffer.append("[org.apache.poi.ddf.EscherOptRecord]");
buffer.append("\n");
buffer.append(this.optRecord.toString());
buffer.append("\n");
buffer.append("[org.apache.poi.ddf.EscherClientAnchorRecord]");
buffer.append("\n");
buffer.append(this.anchrRecord.toString());
buffer.append("\n");
return(buffer.toString());
}
}
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-tp23032425p23124134.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Copy images from Workbook to Workbook
Posted by TimShiu <ti...@ssc-ltd.com>.
Thanks for all the efforts on this issue and I have another little problem.
According to MSB's coding, the picture information like filename & the cell
location can be extracted.
My question is can we get back the byte[] of those pictures accordingly?
e.g. I want to get the image with its top left corner cell is at (0, 0)
I discovered that matthewxb can do it successfully by using
shape.getPicIndex() function to get the pictureData with the index. But I
don't know when and where the picIndex is set to the shape object.
Can anybody give me a hint on this problem? Thanks.
--
View this message in context: http://www.nabble.com/Copy-images-from-Workbook-to-Workbook-tp23032425p24303124.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Copy images from Workbook to Workbook
Posted by matthewxb <ma...@gmail.com>.
Finally, I am able to copy images between Excel file (preserve size and
location), but for some Excel files it prompts data lost during open, I am
still investigating it.
My codes are not well designed, the modification to POI just only serve for
its purpose, it should have a better way to implement.
Really thanks for MSB helps.
--
Copy image function (reuse some MSB contributed codes):
public void copyImages(HSSFSheet sourceSheet, HSSFSheet targetSheet)
{
List lst = sourceSheet.getWorkbook().getAllPictures();
Hashtable<String, HSSFPicture> picTbl = new Hashtable<String,
HSSFPicture>();
findShapeInfo(sourceSheet);
CreationHelper helper =
targetSheet.getWorkbook().getCreationHelper();
HSSFPatriarch drawing = null;
try
{
drawing = targetSheet.getDrawingPatriarch();
}
catch (Exception ex)
{
logger.warn("targetSheet.getDrawingPatriarch()
failed. ", ex);
}
if (drawing == null)
drawing = targetSheet.createDrawingPatriarch();
for (ShapeInfo shape : shapes.values())
{
if (shape.getPictureFilename() == null)
continue;
HSSFPictureData picData = (HSSFPictureData)
lst.get(shape.getPicIndex());
shape.setPictureData(picData);
byte[] data = shape.getPictureData().getData();
HSSFClientAnchor anchor = (HSSFClientAnchor)
helper.createClientAnchor();
anchor.setCol1(shape.getCol1());
anchor.setCol2(shape.getCol2());
anchor.setRow1(shape.getRow1());
anchor.setRow2(shape.getRow2());
anchor.setDx1(shape.getOffsetCol1());
anchor.setDx2(shape.getOffsetCol2());
anchor.setDy1(shape.getOffsetRow1());
anchor.setDy2(shape.getOffsetRow2());
// The pictureIdx is wrong
int pictureIdx =
targetSheet.getWorkbook().addPicture(String.valueOf(anchor.hashCode()),
data, shape.getPictureData().getFormat());
HSSFPicture pic = drawing.createPicture(anchor,
pictureIdx);
picTbl.put(String.valueOf(anchor.hashCode()), pic);
}
int index = 1;
lst = targetSheet.getWorkbook().getAllPictures();
for (int i = 0; i < lst.size(); i++)
{
index++;
HSSFPictureData picData = (HSSFPictureData)
lst.get(i);
if (picData.getCustomId() == null)
continue;
HSSFPicture pic = picTbl.get(picData.getCustomId());
if (pic == null)
continue;
pic.setPictureIndex(index);
}
}
private void findShapeInfo(HSSFSheet sheet)
{
try
{
EscherAggregate escherAggregate = sheet.getDrawingEscherAggregate();
if (escherAggregate != null)
{
EscherContainerRecord escherContainer =
escherAggregate.getEscherContainer();
iterateContainer(escherContainer, 1);
}
}
catch (Exception ex)
{
logger.error(ex, ex);
}
}
private void iterateContainer(EscherContainerRecord escherContainer, int
level)
{
if (escherContainer == null)
return;
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();
// logger.debug("next: " + next);
if (next instanceof org.apache.poi.ddf.EscherContainerRecord)
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 (shapes.containsKey(key))
shapes.get(key).setOptRecord(optRecord);
else
{
ShapeInfo shape = new ShapeInfo(level);
shape.setOptRecord(optRecord);
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 (shapes.containsKey(key))
shapes.get(key).setAnchorRecord(anchrRecord);
else
{
ShapeInfo shape = new ShapeInfo(level);
shape.setAnchorRecord(anchrRecord);
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;
}
}
--
Modification of POI source codes (add CustomId field in HSSFPictureData to
link with HSSFPicture):
Index: src/java/org/apache/poi/ddf/EscherBitmapBlip.java
===================================================================
--- src/java/org/apache/poi/ddf/EscherBitmapBlip.java (revision 773443)
+++ src/java/org/apache/poi/ddf/EscherBitmapBlip.java (working copy)
@@ -141,5 +141,17 @@
" Marker: 0x" + HexDump.toHex( field_2_marker ) + nl +
" Extra Data:" + nl + extraData;
}
+
+ private String customId = null;
+ public void setCustomId(String value)
+ {
+ this.customId = value;
+ }
+
+ public String getCustomId()
+ {
+ return this.customId;
+ }
+
}
Index: src/java/org/apache/poi/hssf/model/DrawingManager2.java
===================================================================
--- src/java/org/apache/poi/hssf/model/DrawingManager2.java (revision
773443)
+++ src/java/org/apache/poi/hssf/model/DrawingManager2.java (working
copy)
@@ -122,7 +122,10 @@
EscherDgRecord getDrawingGroup(int drawingGroupId)
{
- return (EscherDgRecord) drawingGroups.get(drawingGroupId-1);
+ if(drawingGroupId > drawingGroups.size())
+ return (EscherDgRecord)
drawingGroups.get(drawingGroups.size() - 1);
+ else
+ return (EscherDgRecord) drawingGroups.get(drawingGroupId-1);
}
boolean drawingGroupExists( short dgId )
Index: src/java/org/apache/poi/hssf/usermodel/HSSFPictureData.java
===================================================================
--- src/java/org/apache/poi/hssf/usermodel/HSSFPictureData.java (revision
773443)
+++ src/java/org/apache/poi/hssf/usermodel/HSSFPictureData.java (working
copy)
@@ -100,5 +100,13 @@
return "";
}
}
+
+ public String getCustomId()
+ {
+ if(blip instanceof EscherBitmapBlip)
+ return ((EscherBitmapBlip)blip).getCustomId();
+ else
+ return null;
+ }
}
Index: src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
===================================================================
--- src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (revision
773443)
+++ src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (working
copy)
@@ -1562,7 +1562,54 @@
return workbook.addBSERecord( r );
}
+
+ public int addPicture(String customId, byte[] pictureData, int format)
+ {
+ byte[] uid = newUID();
+ EscherBitmapBlip blipRecord = new EscherBitmapBlip();
+ blipRecord.setRecordId( (short) ( EscherBitmapBlip.RECORD_ID_START
+ format ) );
+ switch (format)
+ {
+ case PICTURE_TYPE_EMF:
+ blipRecord.setOptions(HSSFPictureData.MSOBI_EMF);
+ break;
+ case PICTURE_TYPE_WMF:
+ blipRecord.setOptions(HSSFPictureData.MSOBI_WMF);
+ break;
+ case PICTURE_TYPE_PICT:
+ blipRecord.setOptions(HSSFPictureData.MSOBI_PICT);
+ break;
+ case PICTURE_TYPE_PNG:
+ blipRecord.setOptions(HSSFPictureData.MSOBI_PNG);
+ break;
+ case HSSFWorkbook.PICTURE_TYPE_JPEG:
+ blipRecord.setOptions(HSSFPictureData.MSOBI_JPEG);
+ break;
+ case HSSFWorkbook.PICTURE_TYPE_DIB:
+ blipRecord.setOptions(HSSFPictureData.MSOBI_DIB);
+ break;
+ }
+ blipRecord.setUID( uid );
+ blipRecord.setMarker( (byte) 0xFF );
+ blipRecord.setPictureData( pictureData );
+ blipRecord.setCustomId(customId);
+
+ EscherBSERecord r = new EscherBSERecord();
+ r.setRecordId( EscherBSERecord.RECORD_ID );
+ r.setOptions( (short) ( 0x0002 | ( format << 4 ) ) );
+ r.setBlipTypeMacOS( (byte) format );
+ r.setBlipTypeWin32( (byte) format );
+ r.setUid( uid );
+ r.setTag( (short) 0xFF );
+ r.setSize( pictureData.length + 25 );
+ r.setRef( 1 );
+ r.setOffset( 0 );
+ r.setBlipRecord( blipRecord );
+
+ return workbook.addBSERecord( r );
+ }
+
/**
* Gets all pictures from the Workbook.
*
--
--
View this message in context: http://www.nabble.com/Copy-images-from-Workbook-to-Workbook-tp23032425p23593295.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Copy images from Workbook to Workbook
Posted by MSB <ma...@tiscali.co.uk>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org