setting excel cells to a specific width and height in pixel

[Thorsten Bux]

Hi,

does anybody know how to set the width and height of an excel cell to a specific size in pixel?

I'm trying to place a picture directly over a single cell. Therefore I have to scale the cell to the same width and height as the width and height from the picture.

also i would like to know how to set a cell to the max. possible height and width.

thanks for your answer
Thorsten

[Nick Burch]

Post by Thorsten Bux
does anybody know how to set the width and height of an excel cell to a
specific size in pixel?

I think the two methods you'll want are:
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFRow.html#setHeightInPoints(float)
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setColumnWidth(short,%20short)

Post by Thorsten Bux
also i would like to know how to set a cell to the max. possible height and width.

Try giving those two methods really large values? (Not sure, haven't
tried)

Nick

[Pierre Lavignotte]

Hi,

I've found this piece of code in my test project.

I can't remember why some code is commented out, but I know that 1023
and 255 values mean that you fill the cell with the image (ie 100%).

private void drawImage(short col, int row, InputStream in,
HSSFWorkbook workbook,
HSSFPatriarch patriarch) throws IOException, NullPointerException {
if (in == null) {
throw new NullPointerException("Fichier image null");
}

//Conversion de l'image au format png(format de JEXCEL).
ByteArrayOutputStream out = new ByteArrayOutputStream();
BufferedImage input = ImageIO.read(in);
ImageIO.write(input, "PNG", out);

double imgWidth = input.getWidth();
double imgHeight = input.getHeight();
// double ratio = imgWidth / imgHeight;
// double ratioXl = 1; //sizeColum/sizeRow;
// double SIZE_LIMIT_WIDTH = 0.9;
// double SIZE_LIMIT_HEIGHT = SIZE_LIMIT_WIDTH * ratioXl;
//
// if (imgHeight <= imgWidth) {
// imgWidth = SIZE_LIMIT_WIDTH;
// imgHeight = (SIZE_LIMIT_HEIGHT / ratio);
// } else {
// imgHeight = SIZE_LIMIT_HEIGHT;
// imgWidth = SIZE_LIMIT_WIDTH * ratio;
// }

HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(0, 0, 1023, 255,
col, row, col, row);

anchor.setAnchorType(2);

int pictureIndex = workbook.addPicture(out.toByteArray(),
HSSFWorkbook.PICTURE_TYPE_PNG);

patriarch.createPicture(anchor, pictureIndex);
}

Hope it helps,

Pierre

Post by Nick Burch

Post by Thorsten Bux
does anybody know how to set the width and height of an excel cell to a
specific size in pixel?

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFRow.html#setHeightInPoints(float)
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setColumnWidth(short,%20short)

Post by Thorsten Bux
also i would like to know how to set a cell to the max. possible height and width.

Try giving those two methods really large values? (Not sure, haven't tried)
Nick
---------------------------------------------------------------------

[Thorsten Bux]

Hi,

thanks for your reply.

I tried something like this too.

The problem with this algorithm is, that a picture in the next row and same column may be smaller than the width of the column. If that is the case it should keep its size.


-------- Original-Nachricht --------

Datum: Thu, 11 Sep 2008 17:38:48 +0200
Betreff: Re: setting excel cells to a specific width and height in pixel
Hi,
I've found this piece of code in my test project.
I can't remember why some code is commented out, but I know that 1023
and 255 values mean that you fill the cell with the image (ie 100%).
private void drawImage(short col, int row, InputStream in,
HSSFWorkbook workbook,
HSSFPatriarch patriarch) throws IOException, NullPointerException {
if (in == null) {
throw new NullPointerException("Fichier image null");
}
//Conversion de l'image au format png(format de JEXCEL).
ByteArrayOutputStream out = new ByteArrayOutputStream();
BufferedImage input = ImageIO.read(in);
ImageIO.write(input, "PNG", out);
double imgWidth = input.getWidth();
double imgHeight = input.getHeight();
// double ratio = imgWidth / imgHeight;
// double ratioXl = 1; //sizeColum/sizeRow;
// double SIZE_LIMIT_WIDTH = 0.9;
// double SIZE_LIMIT_HEIGHT = SIZE_LIMIT_WIDTH * ratioXl;
//
// if (imgHeight <= imgWidth) {
// imgWidth = SIZE_LIMIT_WIDTH;
// imgHeight = (SIZE_LIMIT_HEIGHT / ratio);
// } else {
// imgHeight = SIZE_LIMIT_HEIGHT;
// imgWidth = SIZE_LIMIT_WIDTH * ratio;
// }
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(0, 0, 1023, 255,
col, row, col, row);
anchor.setAnchorType(2);
int pictureIndex = workbook.addPicture(out.toByteArray(),
HSSFWorkbook.PICTURE_TYPE_PNG);
patriarch.createPicture(anchor, pictureIndex);
}
Hope it helps,
Pierre

Post by Nick Burch

Post by Thorsten Bux
does anybody know how to set the width and height of an excel cell to a
specific size in pixel?

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFRow.html#setHeightInPoints(float)
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setColumnWidth(short,%20short)

Post by Nick Burch

Post by Thorsten Bux
also i would like to know how to set a cell to the max. possible height and width.

Try giving those two methods really large values? (Not sure, haven't

tried)

Post by Nick Burch
Nick
---------------------------------------------------------------------

--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------

[Thorsten Bux]

Hi,

thanks for your answer.
I already knew this methods the question was which values will set them to the max height and width.

I tried several values and if the value is to big the cell shrinks suddenly.



-------- Original-Nachricht --------

Datum: Thu, 11 Sep 2008 16:34:39 +0100 (BST)
Betreff: Re: setting excel cells to a specific width and height in pixel

Post by Thorsten Bux
does anybody know how to set the width and height of an excel cell to a
specific size in pixel?

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFRow.html#setHeightInPoints(float)
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setColumnWidth(short,%20short)

Post by Thorsten Bux
also i would like to know how to set a cell to the max. possible height and width.

Try giving those two methods really large values? (Not sure, haven't
tried)
Nick
---------------------------------------------------------------------

[Anthony Andrews]

According to Bill's Bunch;

The maximum row height for an individual row is 409 points (1 point equals approximately 1/72 inch). The maximum column width for an individual cell is 255 characters. This value represents the number of characters that can be displayed in a cell that is formatted with the standard font.

Have a look here for more info;

http://office.microsoft.com/en-us/excel/HA101375451033.aspx


--- On Thu, 9/11/08, Thorsten Bux <***@gmx.de> wrote:
From: Thorsten Bux <***@gmx.de>
Subject: setting excel cells to a specific width and height in pixel
To: ***@poi.apache.org
Date: Thursday, September 11, 2008, 8:26 AM

Hi,

does anybody know how to set the width and height of an excel cell to a
specific size in pixel?

I'm trying to place a picture directly over a single cell. Therefore I have
to scale the cell to the same width and height as the width and height from the
picture.

also i would like to know how to set a cell to the max. possible height and
width.

thanks for your answer
Thorsten