You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by George Wei <gw...@hotmail.com> on 2009/12/11 05:52:58 UTC
Bug of HSSFPicture.resize()
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
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26738706.html
Sent from the POI - User mailing list archive at Nabble.com.
Re: Bug of HSSFPicture.resize()
Posted by MSB <ma...@tiscali.co.uk>.
I think that your only recourse now would be to raise this as a bug using
bugzilla George.
If you can. list the version of POI you are using, the version of Java and
the operating system. Include an Excel file that demonstrates the problem
and the code required to reproduce if. Also, it would be worth explaining
again the preliminary investigations you undertook. That way the developers
should have all of the ammunition they need to address the issue.
Sorry I could not help.
Yours
Mark B
PS Looks like I will need to re-visit that example again.
George Wei wrote:
>
> Mark,
>
> Thanks for your reply, but I don't mean what you said.
>
> I re-explain my test steps here:
>
> 1.1 Use Excel to new an Excel workbook
> 1.2 Use POI to open it and insert an image
> 1.3 Call HSSFPicture.resize()
> 1.4 Save and close it
>
> 2.1 Use Excel to new an Excel workbook
> 2.2 Use Excel to modify the default font size (For example, from 10 to 20)
> 2.3 Save and close it
> 2.4 Use POI to oepn it and insert the same image used in the previous test
> 2.5 Call HSSFPicture.resize()
> 2.6 Save and close it
>
> Now, use Excel to open these two workbooks, as illustated in prevous
> threads, you can see the same image in these workbooks do NOT have the
> same size! The later one stretchs horizontally!
>
> BTW, I tried the code you mentioned, but it still only works when the
> default font size not changed yet.
>
> George
>
>
> MSB wrote:
>>
>> Sorry to say George that I have not encountered this specific problem but
>> do wonder if it is related to the type of client anchor that HSSF uses.
>> These anchors seem to tie an image to a specific cell whereas using Excel
>> itself, it is possible to identify the coordinates of the images corners
>> along with inset values also. Tying the image directly to the cell means
>> that when the cell is re-sized then so is the image and I think that this
>> is what is happening in your case - changing the size of the default font
>> causes Excel to increase the width and height of the sheets cells when it
>> opens the worksheet. Sorry to say that I cannot think of an easy answer
>> because Excel is resizing the columns rather than POI but if it helps you
>> could take a look at some code I wrote quite some time ago. It allows you
>> to add an image onto a sheet and to specify the location of the image and
>> its' dimensions. you can find it all here;
>> http://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi/hssf/usermodel/examples/AddDimensionedImage.java?revision=805284&view=markup
>>
>> Do not know if it will work for your requirement and it certainly does
>> not answer your question but you may be able to use this class to get you
>> over the current problem.
>>
>> Yours
>>
>> Mark B
>>
>>
>> George Wei wrote:
>>>
>>> There are no comments from neither developers nor users for several
>>> days. Hasn't anybody encountered this problem except me?
>>>
>>>
>>> George Wei wrote:
>>>>
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26861591.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by George Wei <gw...@hotmail.com>.
Mark,
Thanks for your reply, but I don't mean what you said.
I re-explain my test steps here:
1.1 Use Excel to new an Excel workbook
1.2 Use POI to open it and insert an image
1.3 Call HSSFPicture.resize()
1.4 Save and close it
2.1 Use Excel to new an Excel workbook
2.2 Use Excel to modify the default font size (For example, from 10 to 20)
2.3 Save and close it
2.4 Use POI to oepn it and insert the same image used in the previous test
2.5 Call HSSFPicture.resize()
2.6 Save and close it
Now, use Excel to open these two workbooks, as illustated in prevous
threads, you can see the same image in these workbooks do NOT have the same
size! The later one stretchs horizontally!
BTW, I tried the code you mentioned, but it still only works when the
default font size not changed yet.
George
MSB wrote:
>
> Sorry to say George that I have not encountered this specific problem but
> do wonder if it is related to the type of client anchor that HSSF uses.
> These anchors seem to tie an image to a specific cell whereas using Excel
> itself, it is possible to identify the coordinates of the images corners
> along with inset values also. Tying the image directly to the cell means
> that when the cell is re-sized then so is the image and I think that this
> is what is happening in your case - changing the size of the default font
> causes Excel to increase the width and height of the sheets cells when it
> opens the worksheet. Sorry to say that I cannot think of an easy answer
> because Excel is resizing the columns rather than POI but if it helps you
> could take a look at some code I wrote quite some time ago. It allows you
> to add an image onto a sheet and to specify the location of the image and
> its' dimensions. you can find it all here;
> http://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi/hssf/usermodel/examples/AddDimensionedImage.java?revision=805284&view=markup
>
> Do not know if it will work for your requirement and it certainly does not
> answer your question but you may be able to use this class to get you over
> the current problem.
>
> Yours
>
> Mark B
>
>
> George Wei wrote:
>>
>> There are no comments from neither developers nor users for several days.
>> Hasn't anybody encountered this problem except me?
>>
>>
>> George Wei wrote:
>>>
>>> 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
>>>
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26860999.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by MSB <ma...@tiscali.co.uk>.
Sorry to say George that I have not encountered this specific problem but do
wonder if it is related to the type of client anchor that HSSF uses. These
anchors seem to tie an image to a specific cell whereas using Excel itself,
it is possible to identify the coordinates of the images corners along with
inset values also. Tying the image directly to the cell means that when the
cell is re-sized then so is the image and I think that this is what is
happening in your case - changing the size of the default font causes Excel
to increase the width and height of the sheets cells when it opens the
worksheet. Sorry to say that I cannot think of an easy answer because Excel
is resizing the columns rather than POI but if it helps you could take a
look at some code I wrote quite some time ago. It allows you to add an image
onto a sheet and to specify the location of the image and its' dimensions.
you can find it all here;
http://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi/hssf/usermodel/examples/AddDimensionedImage.java?revision=805284&view=markup
Do not know if it will work for your requirement and it certainly does not
answer your question but you may be able to use this class to get you over
the current problem.
Yours
Mark B
George Wei wrote:
>
> There are no comments from neither developers nor users for several days.
> Hasn't anybody encountered this problem except me?
>
>
> George Wei wrote:
>>
>> 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
>>
>
>
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26855152.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by George Wei <gw...@hotmail.com>.
There are no comments from neither developers nor users for several days.
Hasn't anybody encountered this problem except me?
George Wei wrote:
>
> 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
>
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26852875.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by MSB <ma...@tiscali.co.uk>.
Thanks for this George. Sadly, I cannot advise you on how to proceed but I am
confident that Yegor, Jash or Nick will notice your reply and will be more
than happy to help.
Yours
Mark B
George Wei wrote:
>
> It's my pleasure if I can contribute something to POI.
>
> I've submitted a bug to ASF Bugzilla, but I can't find it now. It was
> deleted by someone?
>
> As I don't know how to submit source code to POI's subversion, I can only
> attach the code here. Maybe somebody can help me do that.
>
> The attachment contains 3 files:
> ExcelUtil.java - Excel utilities. Useful methods are
> getColumnWidthInPixels(), getRowHeightInPixels(), resizePicture()
> ResizeImage.java - A test case of ExcelUtil.resizePicture()
> TestResizeIamge.bat - A batch file to compile & run the test
>
> As my newest test result, the maximum error of ExcelUtil.resizePicture()
> is reduced to 3%.
>
> George http://old.nabble.com/file/p26985684/ExelUtil.zip ExelUtil.zip
>
>
> MSB wrote:
>>
>> By gum George (that's an old English expression expressing surprise by
>> the way), that's some coding you did there, and I thought that it would
>> be relatively straightforward. Have not had the chance to look through it
>> all yet but I will tonight hopefully. If you are willing to do so and if
>> you have the time once your work on this project has concluded, it could
>> well be worth working this into an example that could then be added to
>> the POI site - like the AddDimensionedImage example - as I am certain
>> there will be other people facing similar problems. Of course, this will
>> all hinge on your employer's policies with regard to the work you do; my
>> last employer would have regarded anything I did as their property and
>> would not have been willing to allow me to share it with anyone outside
>> the company.
>>
>> Yours
>>
>> Mark B
>>
>
>
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26991167.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by George Wei <gw...@hotmail.com>.
It's my pleasure if I can contribute something to POI.
I've submitted a bug to ASF Bugzilla, but I can't find it now. It was
deleted by someone?
As I don't know how to submit source code to POI's subversion, I can only
attach the code here. Maybe somebody can help me do that.
The attachment contains 3 files:
ExcelUtil.java - Excel utilities. Useful methods are
getColumnWidthInPixels(), getRowHeightInPixels(), resizePicture()
ResizeImage.java - A test case of ExcelUtil.resizePicture()
TestResizeIamge.bat - A batch file to compile & run the test
As my newest test result, the maximum error of ExcelUtil.resizePicture() is
reduced to 3%.
George http://old.nabble.com/file/p26985684/ExelUtil.zip ExelUtil.zip
MSB wrote:
>
> By gum George (that's an old English expression expressing surprise by the
> way), that's some coding you did there, and I thought that it would be
> relatively straightforward. Have not had the chance to look through it all
> yet but I will tonight hopefully. If you are willing to do so and if you
> have the time once your work on this project has concluded, it could well
> be worth working this into an example that could then be added to the POI
> site - like the AddDimensionedImage example - as I am certain there will
> be other people facing similar problems. Of course, this will all hinge on
> your employer's policies with regard to the work you do; my last employer
> would have regarded anything I did as their property and would not have
> been willing to allow me to share it with anyone outside the company.
>
> Yours
>
> Mark B
>
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26985684.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by MSB <ma...@tiscali.co.uk>.
By gum George (that's an old English expression expressing surprise by the
way), that's some coding you did there, and I thought that it would be
relatively straightforward. Have not had the chance to look through it all
yet but I will tonight hopefully. If you are willing to do so and if you
have the time once your work on this project has concluded, it could well be
worth working this into an example that could then be added to the POI site
- like the AddDimensionedImage example - as I am certain there will be other
people facing similar problems. Of course, this will all hinge on your
employer's policies with regard to the work you do; my last employer would
have regarded anything I did as their property and would not have been
willing to allow me to share it with anyone outside the company.
Yours
Mark B
Mark,
Thanks for your tips and I tried what you suggested. The code is as follows:
/**
* Excel utilities
* @author George Wei
*
*/
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFFont;
public final class ExcelUtil {
/**
* width of 1px in columns with default width in units of 1/256 of a
character width
* provied by Yegor
*/
private static final float PX_DEFAULT = 32.00f;
/**
* width of 1px in columns with overridden width in units of 1/256 of a
character width
* provied by Yegor
*/
private static final float PX_MODIFIED = 36.56f;
private static final int DEFAULT_COLUMN_WIDTH = 8;
private static final int FONT_TABLE_INDEX_DEF = 0;
private static final int FONT_TABLE_INDEX_CHN = 1;
private static final int FONT_TABLE_INDEX_FONT_SIZE = 0;
private static final int FONT_TABLE_INDEX_COL_WIDTH = 1;
private static final int FONT_TABLE_INDEX_COL_WIDTH_PIXEL = 2;
private static final int[][][] fontTable = new int[][][]{
{{ 6, 925, 40}, { 6, 925, 40}},
{{ 8, 850, 56}, { 8, 850, 56}},
{{ 9, 843, 64}, { 9, 850, 56}},
{{10, 843, 64}, {10, 843, 64}},
{{11, 838, 72}, {11, 838, 72}},
{{12, 811, 80}, {12, 838, 72}},
{{14, 809, 96}, {14, 810, 88}},
{{16, 808, 104}, {16, 809, 96}},
{{18, 854, 120}, {18, 808, 104}},
{{20, 847, 136}, {20, 850, 128}},
{{22, 844, 144}, {22, 847, 136}},
{{24, 828, 160}, {24, 844, 144}},
{{26, 826, 168}, {26, 828, 160}},
{{28, 814, 184}, {28, 826, 168}}
};
/**
* get default font of Excel workbook
*/
private static final HSSFFont getDefaultWorkbookFont(HSSFWorkbook wb) {
return wb.getFontAt((short)0);
}
/**
* get language index in font table
*/
private static final int getLanguageIndex(String fontName) {
char c = fontName.charAt(0);
//check if font name is Simplified Chinese
if (c >= '\u4e00' && c <= '\u9fa5')
return FONT_TABLE_INDEX_CHN;
//TODO: other languages can be added here
//default language: English
return FONT_TABLE_INDEX_DEF;
}
/**
* get nearest font size in font table for given fon size and language
*/
private static final int getMatchedFontIndex(int fontSize, int
languageIndex) {
//if font size is too small or too large, throw an exception
if ((fontSize < fontTable[0][languageIndex][FONT_TABLE_INDEX_FONT_SIZE] -
2) ||
(fontSize > fontTable[fontTable.length -
1][languageIndex][FONT_TABLE_INDEX_FONT_SIZE] + 2))
throw new IllegalArgumentException("font size out of bounds");
for (int i = 0; i < fontTable.length; i++) {
if (fontTable[i][languageIndex][FONT_TABLE_INDEX_FONT_SIZE] >= fontSize)
return i;
}
return fontTable.length - 1;
}
/**
* get default column width (in units of a character width) for given font
size & language
*/
private static final float getDefaultColumnWidth(int fontIndex, int
languageIndex) {
return fontTable[fontIndex][languageIndex][FONT_TABLE_INDEX_COL_WIDTH] /
100;
}
/**
* get default column width (in pixels) for given font size & language
*/
private static final int getDefaultColumnWidthInPixels(int fontIndex, int
languageIndex) {
return
fontTable[fontIndex][languageIndex][FONT_TABLE_INDEX_COL_WIDTH_PIXEL];
}
/**
* check if column has default width
*/
private static final boolean defaultWidth(HSSFSheet sheet, int column) {
int cw = sheet.getColumnWidth(column);
int def = sheet.getDefaultColumnWidth()*256;
return cw == def;
}
/**
* get convert factor of units of 1/256 of a character width to pixels
*/
private static final float getConvertFactor(int fontIndex, int
languageIndex) {
return getDefaultColumnWidthInPixels(fontIndex, languageIndex) /
getDefaultColumnWidth(fontIndex, languageIndex) * (PX_DEFAULT /
PX_MODIFIED);
}
/**
* get Excel column width in pixels
*/
public static final int getColumnWidthInPixels(HSSFWorkbook wb, HSSFSheet
sheet, int column) {
//get default font
HSSFFont defFont = getDefaultWorkbookFont(wb);
//get language index
int languageIndex = getLanguageIndex(defFont.getFontName());
//get nearest font index
int fontIndex = getMatchedFontIndex(defFont.getFontHeightInPoints(),
languageIndex);
//if column has default width, return mapped width in pixels in font table
if (defaultWidth(sheet, column))
return getDefaultColumnWidthInPixels(fontIndex, languageIndex);
//calculate width in pixels
return (int)((sheet.getColumnWidth(column) * getConvertFactor(fontIndex,
languageIndex) + 255) / 256);
}
}
As my test result, the maximum error of getColumnWidthInPixels() is about
5%, much better than Yegor's sample (As Yegor said, it's about 10%). The
shortage may be lack of flexibility: It use a font table to calculate column
width in pixels. The table must be preset, and font size smaller than the
smallest one in the table or bigger than the biggest one is not supported.
George
MSB wrote:
>
> 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
>
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26955976.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by George Wei <gw...@hotmail.com>.
Mark,
Thanks for your tips and I tried what you suggested. The code is as follows:
/**
* Excel utilities
* @author George Wei
*
*/
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFFont;
public final class ExcelUtil {
/**
* width of 1px in columns with default width in units of 1/256 of a
character width
* provied by Yegor
*/
private static final float PX_DEFAULT = 32.00f;
/**
* width of 1px in columns with overridden width in units of 1/256 of a
character width
* provied by Yegor
*/
private static final float PX_MODIFIED = 36.56f;
private static final int DEFAULT_COLUMN_WIDTH = 8;
private static final int FONT_TABLE_INDEX_DEF = 0;
private static final int FONT_TABLE_INDEX_CHN = 1;
private static final int FONT_TABLE_INDEX_FONT_SIZE = 0;
private static final int FONT_TABLE_INDEX_COL_WIDTH = 1;
private static final int FONT_TABLE_INDEX_COL_WIDTH_PIXEL = 2;
private static final int[][][] fontTable = new int[][][]{
{{ 6, 925, 40}, { 6, 925, 40}},
{{ 8, 850, 56}, { 8, 850, 56}},
{{ 9, 843, 64}, { 9, 850, 56}},
{{10, 843, 64}, {10, 843, 64}},
{{11, 838, 72}, {11, 838, 72}},
{{12, 811, 80}, {12, 838, 72}},
{{14, 809, 96}, {14, 810, 88}},
{{16, 808, 104}, {16, 809, 96}},
{{18, 854, 120}, {18, 808, 104}},
{{20, 847, 136}, {20, 850, 128}},
{{22, 844, 144}, {22, 847, 136}},
{{24, 828, 160}, {24, 844, 144}},
{{26, 826, 168}, {26, 828, 160}},
{{28, 814, 184}, {28, 826, 168}}
};
/**
* get default font of Excel workbook
*/
private static final HSSFFont getDefaultWorkbookFont(HSSFWorkbook wb) {
return wb.getFontAt((short)0);
}
/**
* get language index in font table
*/
private static final int getLanguageIndex(String fontName) {
char c = fontName.charAt(0);
//check if font name is Simplified Chinese
if (c >= '\u4e00' && c <= '\u9fa5')
return FONT_TABLE_INDEX_CHN;
//TODO: other languages can be added here
//default language: English
return FONT_TABLE_INDEX_DEF;
}
/**
* get nearest font size in font table for given fon size and language
*/
private static final int getMatchedFontIndex(int fontSize, int
languageIndex) {
//if font size is too small or too large, throw an exception
if ((fontSize < fontTable[0][languageIndex][FONT_TABLE_INDEX_FONT_SIZE] -
2) ||
(fontSize > fontTable[fontTable.length -
1][languageIndex][FONT_TABLE_INDEX_FONT_SIZE] + 2))
throw new IllegalArgumentException("font size out of bounds");
for (int i = 0; i < fontTable.length; i++) {
if (fontTable[i][languageIndex][FONT_TABLE_INDEX_FONT_SIZE] >= fontSize)
return i;
}
return fontTable.length - 1;
}
/**
* get default column width (in units of a character width) for given font
size & language
*/
private static final float getDefaultColumnWidth(int fontIndex, int
languageIndex) {
return fontTable[fontIndex][languageIndex][FONT_TABLE_INDEX_COL_WIDTH] /
100;
}
/**
* get default column width (in pixels) for given font size & language
*/
private static final int getDefaultColumnWidthInPixels(int fontIndex, int
languageIndex) {
return
fontTable[fontIndex][languageIndex][FONT_TABLE_INDEX_COL_WIDTH_PIXEL];
}
/**
* check if column has default width
*/
private static final boolean defaultWidth(HSSFSheet sheet, int column) {
int cw = sheet.getColumnWidth(column);
int def = sheet.getDefaultColumnWidth()*256;
return cw == def;
}
/**
* get convert factor of units of 1/256 of a character width to pixels
*/
private static final float getConvertFactor(int fontIndex, int
languageIndex) {
return getDefaultColumnWidthInPixels(fontIndex, languageIndex) /
getDefaultColumnWidth(fontIndex, languageIndex) * (PX_DEFAULT /
PX_MODIFIED);
}
/**
* get Excel column width in pixels
*/
public static final int getColumnWidthInPixels(HSSFWorkbook wb, HSSFSheet
sheet, int column) {
//get default font
HSSFFont defFont = getDefaultWorkbookFont(wb);
//get language index
int languageIndex = getLanguageIndex(defFont.getFontName());
//get nearest font index
int fontIndex = getMatchedFontIndex(defFont.getFontHeightInPoints(),
languageIndex);
//if column has default width, return mapped width in pixels in font table
if (defaultWidth(sheet, column))
return getDefaultColumnWidthInPixels(fontIndex, languageIndex);
//calculate width in pixels
return (int)((sheet.getColumnWidth(column) * getConvertFactor(fontIndex,
languageIndex) + 255) / 256);
}
}
As my test result, the maximum error of getColumnWidthInPixels() is about
5%, much better than Yegor's sample (As Yegor said, it's about 10%). The
shortage may be lack of flexibility: It use a font table to calculate column
width in pixels. The table must be preset, and font size smaller than the
smallest one in the table or bigger than the biggest one is not supported.
George
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
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26955024.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by MSB <ma...@tiscali.co.uk>.
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: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by George Wei <gw...@hotmail.com>.
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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by Yegor Kozlov <ye...@dinom.ru>.
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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by George Wei <gw...@hotmail.com>.
Thanks 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Bug-of-HSSFPicture.resize%28%29-tp26738706p26909776.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug of HSSFPicture.resize()
Posted by Yegor Kozlov <ye...@dinom.ru>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org