Hi Mark,
As discussed previously, I encountered some problems with the anchor
type using the HSSFWorkbook ... I now tried the XSSFWorkbook with the
same code and, what a miracle, the anchor type works just as it should.
(I had a look at the XSSFPicture source after I wrote this e-mail, see
results below...)
But now, I am facing another problem concerning the resize() method of
the Picture object.
As long as the image is larger (i.e. higher) than the cell, the resize()
works fine. But if the image fits into one cell, the effect of resize()
is that the image is scaled differentyl in X and Y. Width is ok, but
height seems to be just the default height of a row.
I am attaching the java test code to this e-mail.
What I want to achieve is that one image nicely fits into one cell.
Therefore I am setting the cell width and estimating the cell height.
Is it a bug or did I miss anything?
######
After checking the sources of XSSFPicture, I think I found the cause of
this behaviour. There is a bug if an image fits into one cell, the
calculation of the dx2 and dy2 is wrong:
In the
public XSSFClientAnchor getPreferredSize(double scale)
method, the remaining X (dx2) in the next column is caluclated as follows:
if (w > scaledWidth) {
double cw = getColumnWidthInPixels(col2 + 1);
double delta = w - scaledWidth;
dx2 = (int) (EMU_PER_PIXEL * (cw - delta));
}
which will give wrong results, if the image does not touch the
neighbouring column. I have added a test as follows:
if (w > scaledWidth) {
double cw = getColumnWidthInPixels(col2 + 1);
double delta = w - scaledWidth;
dx2 = (int) (EMU_PER_PIXEL * (cw - delta));
// test single col:
if (col2 == anchor.getCol1()) {
dx2 = (int) (EMU_PER_PIXEL * scaledWidth);
}
}
which solves the problem. Same for the rows in the same method:
if (h > scaledHeight) {
double ch = getRowHeightInPixels(row2 + 1);
double delta = h - scaledHeight;
dy2 = (int) (EMU_PER_PIXEL * (ch - delta));
// again test single row:
if (row2 == anchor.getRow1()) {
dy2 = (int) (EMU_PER_PIXEL * scaledHeight);
}
}
I am going to build the poi-ooxml library myself, although there is one
import missing in the XWPFStyle class:
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STStyleType;
I checked the poi-ooxml-schemas-3.7-20101029.jar and indeed this class
is not there. Anyway, I am commenting out the two methods
public void setType(STStyleType.Enum type)
and
public STStyleType.Enum getType()
hoping it won't do too much harm.
Cheers,
Hannes
PS: I am sending this as CC to the dev-list as well.
Am 07.02.2011 18:00, schrieb Mark Beardsley:
Will have a look at the attached example when I can. Sadly, I am away from
home at the moment and stuck using a laptop that does not have POI, Java or
Excel on it unfortunately
--
GRID-IT Gesellschaft für angewandte Geoinformatik mbH
Dr. Hannes Kleindienst
Technikerstrasse 21a, A-6020 Innsbruck
web www.grid-it.at
tel +43-(0)512-507 4861
mobil +43-(0)676-4300399
fax +43-(0)512-5074869
mail [email protected]
import java.awt.Graphics2D;
import java.awt.RenderingHints;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import javax.imageio.ImageIO;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author Hannes Kleindienst
*/
public class POItest {
public static void main(String[] args) throws Exception {
/** XLS workbook */
Workbook wb = new XSSFWorkbook();
// create sheet
Sheet sheet = wb.createSheet("Liste");
// set width to 200px
sheet.setColumnWidth(0, (int) (200 * 36.5));
// add image
POItest.setCell(wb, sheet, 1, 0, 200, new File("img1.jpg"),
ClientAnchor.DONT_MOVE_AND_RESIZE);
POItest.setCell(wb, sheet, 2, 0, 200, new File("img1.jpg"),
ClientAnchor.MOVE_AND_RESIZE);
POItest.setCell(wb, sheet, 3, 0, 200, new File("img1.jpg"),
ClientAnchor.MOVE_DONT_RESIZE);
// write output
File outputfile = new File("poitest.xlsx");
FileOutputStream fos = new FileOutputStream(outputfile);
wb.write(fos);
fos.close();
}
public static void setCell(Workbook wb, Sheet sheet, int row, int col, int
width, File imagefile, int anchortype) {
// check if the image file exists
if (!imagefile.exists() || !imagefile.isFile()) {
return;
}
try {
// get the image
BufferedImage img = POItest.resizeImage(ImageIO.read(imagefile), width*2);
// calculate and set height (width is 200px)
int height = img.getHeight();
// get or create row
Row r = sheet.getRow(row);
if (r == null) {
r = sheet.createRow(row);
}
r.setHeightInPoints(0.5F * height * 0.75F + 1);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(img, "JPG", baos);
int pictureIdx = wb.addPicture(baos.toByteArray(),
Workbook.PICTURE_TYPE_JPEG);
// Create the drawing patriarch. This is the top level container for all
shapes.
Drawing drawing = sheet.createDrawingPatriarch();
CreationHelper helper = wb.getCreationHelper();
// create anchor
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(col);
anchor.setRow1(row);
anchor.setAnchorType(anchortype);
//subsequent call of Picture#resize() will operate relative to it
Picture pict = drawing.createPicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
// pict.resize(0.5);
pict.resize(0.51);
// pict.resize();
} catch (IOException ex) {
ex.printStackTrace();
}
}
public static BufferedImage resizeImage(BufferedImage img, int newW) {
// current dimensions
int w = img.getWidth();
int h = img.getHeight();
// calc new h
int newH = (int) (h * ((float) newW) / w);
int type = img.getType();
// render image
BufferedImage resizedImage = new BufferedImage(newW, newH,
BufferedImage.TYPE_INT_RGB);
Graphics2D g = resizedImage.createGraphics();
g.setRenderingHint(RenderingHints.KEY_INTERPOLATION,
RenderingHints.VALUE_INTERPOLATION_BILINEAR);
g.drawImage(img, 0, 0, newW, newH, 0, 0, w, h, null);
g.dispose();
// return result
return resizedImage;
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
