I wrote a custom resizer which attempts to take into account the actual default font in the workbook. See the code
below. It works, although the result can be 'off' by about 10% - depends on the font.
Play with it, may be you will figure out how to tweak it to produce the exact
result.
Patches are always welcome.
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.util.IOUtils;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.awt.*;
/**
* @author Yegor Kozlov
*/
public class ResizeImage {
public static void main(String[] args) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.createSheet();
//change the default font
for(short i=0; i < wb.getNumberOfFonts(); i++){
HSSFFont f = wb.getFontAt(i);
f.setFontHeight((short)(f.getFontHeight()*2));
f.setFontName("Verdana");
}
HSSFPatriarch p = sh.createDrawingPatriarch();
for (int i = 0; i < args.length; i++) {
byte[] bytes = IOUtils.toByteArray(new FileInputStream(args[i]));
int idx = wb.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
HSSFPicture picture = p.createPicture(new HSSFClientAnchor(), idx);
new PictureResizer(sh, picture).resize();
}
FileOutputStream out = new FileOutputStream("workbook.xls");
wb.write(out);
out.close();
}
public static class PictureResizer {
final HSSFPicture _picture;
final HSSFSheet _sheet;
final float _pw;
final float _ph;
public PictureResizer(HSSFSheet sheet, HSSFPicture picture){
_picture = picture;
_sheet = sheet;
HSSFFont defaultFont = sheet.getWorkbook().getFontAt((short)0);
Font font = new Font(defaultFont.getFontName(), Font.PLAIN,
defaultFont.getFontHeightInPoints());
FontMetrics fontMetrics = new Label().getFontMetrics(font);
//width of the default character in pixels at 96 dpi
_pw = (float)fontMetrics.charWidth('0')*96/72;
// height of the default character in pixels at 96 dpi
// (if the row has a medium or thick top border, or if any cell in
the row directly above
// the current row has a thick bottom border then the row height
has been adjusted higher by .75 points )
_ph = (float)(fontMetrics.getMaxAscent() +
fontMetrics.getMaxDescent() + 0.75)*96/72;
}
private float getColumnWidthInPixels(int column){
int cw = _sheet.getColumnWidth(column);
return _pw*cw/256;
}
private float getRowHeightInPixels(int i){
HSSFRow row = _sheet.getRow(i);
float height;
if(row != null) height = row.getHeight();
else height = _sheet.getDefaultRowHeight();
return height/255*_ph;
}
public HSSFClientAnchor getPreferredSize(){
HSSFClientAnchor anchor = (HSSFClientAnchor)_picture.getAnchor();
Dimension size = _picture.getImageDimension();
double scaledWidth = size.getWidth();
double scaledHeight = size.getHeight();
float w = 0;
//space in the leftmost cell
w += getColumnWidthInPixels(anchor.getCol1())*(1 -
(float)anchor.getDx1()/1024);
short col2 = (short)(anchor.getCol1() + 1);
int dx2 = 0;
while(w < scaledWidth){
w += getColumnWidthInPixels(col2++);
}
if(w > scaledWidth) {
//calculate dx2, offset in the rightmost cell
col2--;
double cw = getColumnWidthInPixels(col2);
double delta = w - scaledWidth;
dx2 = (int)((cw-delta)/cw*1024);
}
anchor.setCol2(col2);
anchor.setDx2(dx2);
float h = 0;
h += (1 - (float)anchor.getDy1()/256)*
getRowHeightInPixels(anchor.getRow1());
int row2 = anchor.getRow1() + 1;
int dy2 = 0;
while(h < scaledHeight){
h += getRowHeightInPixels(row2++);
}
if(h > scaledHeight) {
row2--;
double ch = getRowHeightInPixels(row2);
double delta = h - scaledHeight;
dy2 = (int)((ch-delta)/ch*256);
}
anchor.setRow2(row2);
anchor.setDy2(dy2);
return anchor;
}
public void resize(){
HSSFClientAnchor anchor = (HSSFClientAnchor)_picture.getAnchor();
anchor.setAnchorType(2);
HSSFClientAnchor pref = getPreferredSize();
int row2 = anchor.getRow1() + (pref.getRow2() - pref.getRow1());
int col2 = anchor.getCol1() + (pref.getCol2() - pref.getCol1());
anchor.setCol2((short)col2);
anchor.setDx1(0);
anchor.setDx2(pref.getDx2());
anchor.setRow2(row2);
anchor.setDy1(0);
anchor.setDy2(pref.getDy2());
}
}
}
Yegor
I have to insert images precisely enough, so I try to use java.awt.* to
calculate the column width. The dpi is fixed at 96 on Windows, but the
default font may be changed. So I wrote the code as follows:
public final class HssfHelper
{
private final static int DPI_WINDOWS_NORMAL = 96;
private final static int DPI_INDUSTRY_STANDARD = 72;
/**
* Get default font width in points
*
*/
public static int getDefaultFontWidth(HSSFWorkbook workbook) {
//get default workbook font. HSSFWorkbook.getFontAt(0) should always
return the default one.
HSSFFont defFont = workbook.getFontAt((short)0);
//cast workbook font to Java font
Font font = new Font(defFont.getFontName(), Font.PLAIN,
defFont.getFontHeightInPoints());
//get font metrics
FontMetrics fontMetrics = new Label().getFontMetrics(font);
//get char width
fontWidth = fontMetrics.charWidth('0');
fontWidth = fontWidth * DPI_WINDOWS_NORMAL / DPI_INDUSTRY_STANDARD;
//Normally, Windows font is larger than industry standard
return fontWidth;
}
/**
* Get column width in points
*
*/
public static int getColumnWidthInPoints(HSSFWorkbook workbook, HSSFSheet
sheet, int colIndex) {
return (int)(sheet.getColumnWidth(colIndex) *
getDefaultFontWidth(workbook) + 255) / 256;
}
}
But this still only works with default font settings. Is there something
wrong of my code? Thanks for any help.
George
Yegor Kozlov wrote:
I hope my belated answer is of help
HSSFPicture.resize() as well as XSSFPicture.resize() indeed work only for
the default font which is Arial 10pt for .xls
and Calibri 11pt for .xlsx.
Excel uses a funny coordinate system to position graphic objects. X and Y
axes are measured not in pixels but in units
proportional to a character width of the default font. To properly
position and size an image POI needs to translate the
actual dimensions measured in pixels to columns and rows. This is the key
of the problem. At the moment POI performs
this translation using hardcoded constants obtained empirically for Arial
10pt and Calibri 11pt. It works OK in most
cases and also explains why HSSFPicture.resize() is 'off' if the default
font is changed.
Unfortunately it can not be easily fixed. To measure a custom font POI
needs to load it (i.e. the font must be
physically available) and get the font metrics using java.awt.* utilities.
It will make the result machine dependent and
impossible to test.
I'm going to update javadocs on HSSFPicture.resize() and close Bug 48415
as 'wontfix'
Yegor
Dear all,
As my test result, this function only works when the default font size of
workbook not changed. If I modified its size (For example, from 12 to 22)
and then call this function to insert an image, it stretchs horizontally.
Image 1 (Default font size set to 12):
http://old.nabble.com/file/p26738706/1.jpg
Image 2 (Default font size set to 22):
http://old.nabble.com/file/p26738706/2.jpg
I've checked the source code of HSSFPicture.java, function resize() calls
another function getPixelWidth() to calculate the column width in pixels,
but the later function only works correctly when the default font size of
workbook not changed.
Can somebody give me some suggestions to correct this problem? Thanks.
George
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
