You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Beuck, Torsten" <To...@ottogroup.com> on 2012/09/06 17:13:41 UTC
Strange effects with images in XLSX file
Hi,
I'm just creating a piece of code that exports some data - including one image per row - to an excel xlsx file with Apache POI. I'm using the AddDimensionedImage example class and it works well. But if I open the resulting file with excel and move one of the images for the first time, the first image will be moved instead. If I then move another image, the last image will be moved instead, but not the image I like to move.
If I save the file with excel before I move any image the above described behavior does not arise.
I wrote a little bit of code to reproduce the behavior (all you need in addition are three images A.png, B.png and C.png):
public class ImageExportTest {
private final URL[] pictureURL = new URL[3];
public ImageExportTest() throws MalformedURLException {
this.pictureURL[0] = new URL("file:///tmp/A.png");
this.pictureURL[1] = new URL("file:///tmp/B.png");
this.pictureURL[2] = new URL("file:///tmp/C.png");
}
public static void main(final String[] args) {
try {
final ImageExportTest exportTest = new ImageExportTest();
exportTest.export();
} catch (final Exception e) {
e.printStackTrace();
}
}
private void export() throws IOException {
final Workbook workbook = new XSSFWorkbook();
final Sheet sheet = workbook.createSheet("ImageExportTest");
for (int rowNo = 0; rowNo < 3; rowNo++) {
final Row row = sheet.createRow(rowNo);
row.setHeightInPoints(75);
// image cell
Cell cell = row.createCell(1);
cell.setCellValue("");
cell = row.createCell(2);
cell.setCellValue("image " + ("ABC".charAt(rowNo)));
}
sheet.setColumnWidth(1, 5000);
final Drawing drawing = sheet.createDrawingPatriarch();
final CreationHelper helper = workbook.getCreationHelper();
for (int rowNo = 0; rowNo < 3; rowNo++) {
final InputStream is = this.pictureURL[rowNo].openStream();
final byte[] bytes = IOUtils.toByteArray(is);
final int index = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
is.close();
final ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(1);
anchor.setRow1(rowNo);
final Picture pict = drawing.createPicture(anchor, index);
pict.resize();
}
final FileOutputStream fileOut = new FileOutputStream("/tmp/imageExportTest.xlsx");
workbook.write(fileOut);
fileOut.close();
}
}
Maybe anyone has any idea how to solve this issue?
Regards and thanx in advance,
Torsten
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Strange effects with images in XLSX file
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Far from extensive but I have tested with Excel now and did observe the same
behaviour. It does seem to be the case that with overlapping issues in a
sheet the position of the cursor when initiating the drag does determine
which image is moved.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Strange-effects-with-images-in-XLSX-file-tp5710866p5710897.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: Strange effects with images in XLSX file
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
It's never a waste of time addressing issues like these. Still, I have not
tried using Excel to replicate the problem. What I need to do is insert
three overlapping images and just check that the 'selection issue' remains.
Will post again when I do; should be later today.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Strange-effects-with-images-in-XLSX-file-tp5710866p5710896.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: Strange effects with images in XLSX file
Posted by "Beuck, Torsten" <To...@ottogroup.com>.
Hi Mark,
first I like to thank you very much for your commendable commitment to help me with this issue. This kind of dedication has made open source software that successful.
I just copied your code and tried it. After opening the resulting excel file I only saw the final three overlapping images. The three images in a row and the three images in a column were missing.
Only then I realised I was using POI 3.7 all the time. I'm feeling really terrible about that. After switching to 3.8 everything works fine. Even the image sizes are proper now.
I then tried your code again and can confirm the behaviour you described - it is repeatable.
Thanks again and sorry for wasting your time.
Yours,
Torsten
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Strange effects with images in XLSX file
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Now I have seen what you were describing but feel that it is nothing to do
with POI but rather Excel itself.
This morning, I was playing about with some code and created a workbook with
lits of images in it. I placed three images on a row and tried to replicate
the problem you described but I could not. Next, three images in a column
and again no problem. Finally, I arranged three image in a single column
heavilly overlapping and this time I did see the problem. The set up was
something like this, Image 1 was overlapped by image two which was
overlapped by image 3. I clicked on image 1 and then tried to drag it. Even
though the 'box' still appeared to indicate that I had selected image 1,
image 3 was the one that moved. Still though, I do not think it is the fault
of POI because I found that if I selected image 1 again and was then careful
to place the cursor in the small piece of image 1 that was visible from
behind images 2 and 3, then image 1 was dragged. It seems as if whichever
image the cursor lies within when the drag operation starts then that is the
image that will be dragged irrespective of which one is selected previously.
I still have to try repeating the operation with a workbook created entirely
with Excel but am going to do this now.
Can I also ask you to do something similar to make sure the results are
repeatable - be sure to place the cursor within the image you want to drag.
Also, I have not been able to see any problem with the resize() method at
all with the test code I have been using which is, as you can see, a
modification of the code you posted. This leads me to suspect some subtle
interaction that is going to take a little bit of tracking down.
import java.io.*;
import java.net.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.util.IOUtils;
/**
*
* @author Mark Beardsley
*/
public class ImageExportTest {
private final URL[] pictureURL = new URL[3];
private static final int EMU_PER_CM = 360000;
public ImageExportTest() throws MalformedURLException {
this.pictureURL[0] = new URL("file:///C:/temp/01.png");
this.pictureURL[1] = new URL("file:///C:/temp/02.png");
this.pictureURL[2] = new URL("file:///C:/temp/03.png");
}
public static void main(final String[] args) {
try {
ImageExportTest exportTest = new ImageExportTest();
exportTest.export();
} catch (final Exception e) {
e.printStackTrace();
}
}
private void export() throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("ImageExportTest");
sheet.setColumnWidth(1, 5000);
Drawing drawing = sheet.createDrawingPatriarch();
CreationHelper helper = workbook.getCreationHelper();
ClientAnchor anchor = null;
Picture pict = null;
Row row = null;
Cell cell = null;
InputStream is = null;
byte[] bytes = null;
int index = 0;
int imageWidth = 0;
int imageHeight = 0;
// Firstly, insert the images into a single row. There is plenty of
space
// for the image, no overlapping.
row = sheet.createRow(0);
row.setHeightInPoints(100);
for(int colNo = 0, imageNo = 0 ; colNo < 5; colNo += 2, imageNo++) {
cell = row.createCell(colNo);
sheet.setColumnWidth(colNo, 15000);
is = this.pictureURL[imageNo].openStream();
bytes = IOUtils.toByteArray(is);
is.close();
index = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
anchor = helper.createClientAnchor();
anchor.setCol1(colNo);
anchor.setRow1(0);
pict = drawing.createPicture(anchor, index);
pict.resize();
}
// Now use the insert the images into a single column. There is plenty
of
// space for the image, no overlapping.
for(int rowNo = 11, imageNo = 0; rowNo < 16; rowNo +=2, imageNo++) {
row = sheet.createRow(rowNo);
row.setHeightInPoints(300);
is = this.pictureURL[imageNo].openStream();
bytes = IOUtils.toByteArray(is);
is.close();
index = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
anchor = helper.createClientAnchor();
anchor.setCol1(0);
anchor.setRow1(rowNo);
pict = drawing.createPicture(anchor, index);
pict.resize();
}
// Now, all on a single row but this time, the image will be
overlapping.
row = sheet.createRow(17);
row.setHeightInPoints(100);
for(int colNo = 0; colNo < 3; colNo++) {
cell = row.createCell(colNo);
is = this.pictureURL[colNo].openStream();
bytes = IOUtils.toByteArray(is);
is.close();
index = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
anchor = helper.createClientAnchor();
anchor.setCol1(colNo);
anchor.setRow1(17);
pict = drawing.createPicture(anchor, index);
pict.resize();
}
// Finally, overlapping in a single column.
for(int rowNo = 28, imageNo = 0; rowNo < 31; rowNo++, imageNo++) {
row = sheet.createRow(rowNo);
is = this.pictureURL[imageNo].openStream();
bytes = IOUtils.toByteArray(is);
is.close();
index = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
anchor = helper.createClientAnchor();
anchor.setCol1(1);
anchor.setRow1(rowNo);
pict = drawing.createPicture(anchor, index);
pict.resize();
}
final FileOutputStream fileOut = new
FileOutputStream("C:/temp/imageExportTest.xlsx");
workbook.write(fileOut);
fileOut.close();
}
}
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Strange-effects-with-images-in-XLSX-file-tp5710866p5710885.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: Strange effects with images in XLSX file
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
I must have a look to see what the resize() method actually does. At first
glance, I expected it to set the images size either to the limits specified
by the anchors attributes or to mirror the original size of the image. Like
you, I am seeing inconsistent sizing behaviour with the images, the first
one inserted is huge, the second very small indeed and the third is
somewhere between then two extremes. This behaviour is consistent and so I
suspect it must have something to do with the detailed processing that
resize() undertakes.
You can always send the file to my apache.org email address - msb@apache.org
- and it should find its way. At this point in time, I still do not believe
that it is worthwhile raising this as a bug on Bugzilla until we have done a
little more digging around and can at least say with confidence what the
problem really is.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Strange-effects-with-images-in-XLSX-file-tp5710866p5710879.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: Strange effects with images in XLSX file
Posted by "Beuck, Torsten" <To...@ottogroup.com>.
The image sizes I see are a bit strange too, whereas my first two images have the same size (but not their original size - 100% height and 14% width) and the third is much smaller (6% height and 14% width).
I try to append the xslx file as attachment. If the mailing list does not accept attachments, where could I post the workbook copy?
Re: Strange effects with images in XLSX file
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Sorry to say this but I cannot reproduce this problem.
I copied your test code, compiled and then ran it and did not see the
problem you describe. What I did see is that the resize() method had a very
strange behavior that I could not explain. The first image was inserted into
the workbook but it was very much larger than the other two. In like manner,
the second image I inserted into the workbook was tiny and the third image
somewhere in-between in terms of size. All could be dragged independently of
one another (selecting image one led to me moving image one and so on). If I
commented out the resize() method and set all of the anchor's parameters
myself, then I saw three, equally sized images inserted into the workbook
and, again, all of the images could be dragged independently of one another.
To be certain, I changed the sizes of the images so that they filled the
cells, fitted well within the cell's boundaries and even overlapped into
neighbouring rows/columns.
Could this be what you are seeing? The first image, as it is huge, would be
the only one you could select on my example? If not, can you post a copy of
a workbook that you have created with your test code that illustrates the
problem you describe please?
Yours
Mark B
PS I am running under Windows 7 and tested the output workbook with both
Excel 2007 and OpenOffice.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Strange-effects-with-images-in-XLSX-file-tp5710866p5710877.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: Strange effects with images in XLSX file
Posted by "Beuck, Torsten" <To...@ottogroup.com>.
Hi Mark,
my apologies for the double post, but I tried using Nabble and got a "Mail delivery failed" from the poi-user mailing list. I don't know if you are using Nabble or not. Therefore here my answer to your reply again:
> I have just had a quick scan of the code you posted and cannot see you
> using the class so can I just check please, you are observing this
> behaviour irrespective of whether or not you use the
> AddDimensionedImage class/approach?
I didn't mention it, but yes: I'm observing this behaviour with and without the AddDimensioned class. To confirm this I wrote the small code piece I put in my first post.
BTW: I'm using Excel 2007 on Windows XP SP3.
Best regards,
Torsten
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Strange effects with images in XLSX file
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
I have just had a quick scan of the code you posted and cannot see you using
the class so can I just check please, you are observing this behaviour
irrespective of whether or not you use the AddDimensionedImage
class/approach?
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Strange-effects-with-images-in-XLSX-file-tp5710866p5710869.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