I've tested your code, it still only works with default font settings.
Actually, the method you use to calculate column width is as the same as I
written.
George
Yegor Kozlov wrote:
>
> 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]
>
>
>
--
View this message in context:
http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26925251.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
