poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Beardsley <markbrd...@tiscali.co.uk>
Subject Re: Strange effects with images in XLSX file
Date Sun, 09 Sep 2012 10:57:55 GMT
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


Mime
View raw message