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: [email protected]
For additional commands, e-mail: [email protected]
