Just a thought George and I am not at all sure that it adds anything to the
discussion but could the whole problem be simply a question of proportion?
>From what Yegor has said, and from what I have found out whilst having a bit
of a dig around, Excel uses character units to express the width of the
columns. So, if the deafult font is set to size 12, the default column width
will be 8.4 units and it will still be 8.4 units if the default font size is
increased to 22. However, whilst the number of units will have remained the
same, the apparant width of the column will have increased.
Following this line of reasoning along, it is necessary to reduce the number
of units as the size of the foint is increased if you want to fix the width
of the column or, in your case, adjust the size of the image. Could you not
therefore, simply take the existing dimensions, divide that value by the new
value for the fint size and then multiply by the original default size?
Talking of column widths again for example, (8.4 / 22) * 12.
Do not have any idea if it would or even could work but it is another avenue
you might explore and would be very easy to test, in a workbook, increase
the deafult font size to 22 and set the column width to 4.6 and look at the
results, now, whether that will work for an image or not, that is a
question. If it did though, you could use Java code to create a newly
resized image and then add that to the sheet.
Yours
Mark B
George Wei wrote:
>
> 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-tp26738706p26925930.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]
