You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "C. Benson Manica" <cb...@gmail.com> on 2010/07/07 22:14:19 UTC
Set column width in sensible units
I feel like I have got to be missing something obvious - all I want to do is
set column width in twips, same as you can do with the rows, rather than in
whatever the heck 1/256th of a character width is. It turns out that
public void setColumnWidth( HSSFSheet sheet, int colIdx, double widthInTwips
) {
sheet.setColumnWidth( colIdx,(int)(441.3793d+256d*(width-1d)) );
}
seems to work perfectly with the default font (10pt Arial), but presumably
that magic constant is different for different fonts. Is there some way to
get the library to do this nonsense for me that I've missed despite hours of
searching?
--
C. Benson Manica
cbmanica@gmail.com
Re: Set column width in sensible units
Posted by w1a5r <w1...@yahoo.com>.
I missed the whole problem of setting the column width after data was
inserting in the cell. You are correct that there would have to be a formula
based on the font being used.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Set-column-width-in-sensible-units-tp2304417p4258977.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: Set column width in sensible units
Posted by David Fisher <df...@jmlafferty.com>.
> Let me correct my math errors.
How embarrassing. Getting old.
>> Let's see.
>>
>> 1 point = 20 twips
>> 72 points = 1 inch
>>
>> Font metric for the "0" zero character is 556/1000 x 10 points = 5.56 pts per character.
>>
>
> 5.56 x 20 = 112 twips per default character.
111.2 twips per char.
>
>> 1 character = 256 width units
>>
>> How many twips per width unit?
>>
>
> 112 / 256 = 0.4375 twips per width unit
111.2 / 256 = 0.434375
>
>>
>> How many width units per twip?
>>
>
> 256 / 112 = 2.5871429 width units per twip
256 / 111.2 = 2.30215827
You get the idea, if we can find a rational basis for a conversion then we can proceed.
> Have you compared your results on a Mac version of Excel?
There is a good chance that we will need an function to set a default font in order to set this conversion in such a way that it works well for the users of your spreadsheet.
>>> Is there some way to
>>> get the library to do this nonsense for me that I've missed despite hours of
>>> searching?
>>
>> If we can confirm the formulas above then there can be a twip based version of setColumnWidth.
I agree that it would be a good thing.
Regards,
Dave
>>
>> Regards,
>> Dave
>>
>>
>>>
>>> --
>>> C. Benson Manica
>>> cbmanica@gmail.com
>>
>>
>>
>> ---------------------------------------------------------------------
>> 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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Set column width in sensible units
Posted by David Fisher <df...@jmlafferty.com>.
Let me correct my math errors.
> Let's see.
>
> 1 point = 20 twips
> 72 points = 1 inch
>
> Font metric for the "0" zero character is 556/1000 x 10 points = 5.56 pts per character.
>
5.56 x 20 = 112 twips per default character.
> 1 character = 256 width units
>
> How many twips per width unit?
>
112 / 256 = 0.4375 twips per width unit
>
> How many width units per twip?
>
256 / 112 = 2.5871429 width units per twip
> Have you compared your results on a Mac version of Excel?
>
>> Is there some way to
>> get the library to do this nonsense for me that I've missed despite hours of
>> searching?
>
> If we can confirm the formulas above then there can be a twip based version of setColumnWidth.
>
> Regards,
> Dave
>
>
>>
>> --
>> C. Benson Manica
>> cbmanica@gmail.com
>
>
>
> ---------------------------------------------------------------------
> 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: Set column width in sensible units
Posted by David Fisher <df...@jmlafferty.com>.
Hi,
> I feel like I have got to be missing something obvious - all I want to do is
> set column width in twips, same as you can do with the rows, rather than in
> whatever the heck 1/256th of a character width is. It turns out that
>
> public void setColumnWidth( HSSFSheet sheet, int colIdx, double widthInTwips
> ) {
> sheet.setColumnWidth( colIdx,(int)(441.3793d+256d*(width-1d)) );
> }
>
> seems to work perfectly with the default font (10pt Arial), but presumably
> that magic constant is different for different fonts.
How did you come up with 441.3793? Which character are you measuring?
Let's see.
1 point = 20 twips
72 points = 1 inch
Font metric for the "0" zero character is 556/1000 x 10 points = 5.56 pts per character.
5.56 x 20 = 11.2 twips per character.
1 character = 256 width units
How many twips per width unit?
11.2 / 256 = 0.04375
How many width units per twip?
256 / 11.2 = 25.8571429
Have you compared your results on a Mac version of Excel?
> Is there some way to
> get the library to do this nonsense for me that I've missed despite hours of
> searching?
If we can confirm the formulas above then there can be a twip based version of setColumnWidth.
Regards,
Dave
>
> --
> C. Benson Manica
> cbmanica@gmail.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Set column width in sensible units
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
You should be able to use the following to set the width as
double-value, however make sure to divide the original int-value by
256 here, and this is only available for XSSF, not for HSSF as there
it is stored as int-value in the binary format:
XSSFSheet.getColumnHelper().setColWidth(columnIndex, widthDivBy256);
XSSFSheet.getColumnHelper().setCustomWidth(columnIndex, true);
Dominik.
On Fri, Sep 4, 2015 at 4:55 PM, atish shimpi <at...@gmail.com> wrote:
> *void org.apache.poi.ss.usermodel.Sheet.setColumnWidth(int arg0, int arg1)*
> api takes width only in /*integer */form but as per my requirement it should
> accept float value as well, could you help me to resolve this?
>
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Set-column-width-in-sensible-units-tp2304417p5720046.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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Set column width in sensible units
Posted by atish shimpi <at...@gmail.com>.
*void org.apache.poi.ss.usermodel.Sheet.setColumnWidth(int arg0, int arg1)*
api takes width only in /*integer */form but as per my requirement it should
accept float value as well, could you help me to resolve this?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Set-column-width-in-sensible-units-tp2304417p5720046.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: Set column width in sensible units
Posted by w1a5r <w1...@yahoo.com>.
Hey I have a magic method I use for this, not positive it will work for what
you need but worth a try.
public static int calculateColWidth(int width){
if(width > 254)
return 65280; // Maximum allowed column width.
if(width > 1){
int floor = (int)(Math.floor(((double)width)/5));
int factor = (30*floor);
int value = 450 + factor + ((width-1) * 250);
return value;
}
else
return 450; // default to column size 1 if zero, one or negative number
is passed.
}
Good Luck!
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Set-column-width-in-sensible-units-tp2304417p4257949.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