Column width value

[Ray Cassick \(Home\)]

I am trying to write a library in VB.net that can be used to write Excel
spread sheets directly form an application. My library will be writing the
sheets as XML files.

I need to be bale to give the user of the library a way to control column
width and am running into a bit of an odd quandary with regards to the value
used.

The Excel GUI seems to let you enter the column width as a factor of
characters (between 0 and 255) but when that number is stored in the sheet
it is not stored as that number but rather it is stored as (according to the
xmlss spec) points.

My library is allowing the user to enter the column width explicitly so I
have to decide how I want them to enter it in (as characters or points). I
would like to keep it as characters since this is what most Excel users are
already using in the GUI but then I need to convert it to points myself when
I store it to the file. This is where I am having a difficult time. I can't
seem to come up with any specific conversion formula that makes the numbers
work out.

Here is a small table showing my issue. The 'char' column is the value that
the user enters into the GUI and the 'points' column is how the value is
actually stored. These are REAL values taken from some tests I ran in my
effort to find the formula used to convert these numbers.

char points
-----------------
..1 .75
..2 1.5
..3 3.0
..4 3.75
..5 4.5
..6 5.25
..7 6
..8 7.5
..9 8.25
1 9
2 14.25
3 19.5
4 24.75
5 30
6 35.25
7 40.5
8 45.75
9 51
10 56.25

Can anyone here lend a hand as to the real conversion formula used here? I
am starting to think that I should just use a lookup table but I really
would like to not go in that direction if I don't have to. There HAS to be a
formula for this that I am just not able to find.

Thanks to all in advance...

PS. I am going to keep searching myself so if I happen to finid it I will be
sure to reply here

--
Raymond R Cassick
CEO / CSA
Enterprocity Inc.
www.enterprocity.com
3380 Sheridan Drive, #143
Amherst, NY 14226
V: 716-316-5973
Blog: http://spaces.msn.com/members/rcassick/

 

[Pete_UK]

If you just look at the integer values, you can see a relationship
centred on 5:

1 9 = n * 6 + 4 * 0.75
2 14.25 = n * 6 + 3 * 0.75
3 19.5 = n * 6 + 2 * 0.75
4 24.75 = n * 6 + 1 * 0.75
5 30 = n * 6
6 35.25 = n * 6 - 1 * 0.75
7 40.5 = n * 6 - 2 * 0.75
8 45.75 = n * 6 - 3 * 0.75
9 51 = n * 6 - 4 * 0.75
10 56.25 = n * 6 - 5 * 0.75

where n is the number of characters. You could continue this beyond 10
to see if the correlation holds, but for these integer values:

points = 5.25 * characters + 3.75

Hope this helps - I've not looked at the fractional values.

Pete

 

[Ray Cassick \(Home\)]

I LOVE the internet

You are the man!

I ran this through a quick test with a .1 increments between .1 and 255 and
it seems to work for all but a few things:

1) The fractions below 1 (.1 - .9) are way off, but I think I can deal
with that in my code.

2) It seems that excel throws another wrinkle into the works a bit. I
tried to test this by predicting the resulting values and then putting those
actual values into a sheet and looking at the resulting XML. I was a bit
confused because the numbers from that formula were close but did not match.
For example I enter in the value of 4.2 into excel and then looked at the
resulting value in the saved XML and it came back as 25.5 where the formula
was returning 25.8. Hmmm I then opened Excel again and looked at the value
and found that even though I entered in 4.2 it stored 4.14 instead. Running
4.14 through the formula results in 25.49. Much better. So it seems that
Excel is forcing the entered numbers into a specific range of allowed
values.

This little slip doe snot bother me because my tests have shown that even if
I use the value 25.8 in the raw XML that I am generating excel will do the
conversion for me when it opens the document and take care of the little bit
of difference.

THANKS for the quick and accurate help.

 

[Pete_UK]

Ray,

Thanks for feeding back - I'm glad you were able to make use of it.

I have discovered in the past that Excel must have some internal
rounding of its own (maybe to do with pixels?). I didn't look at the
fractional values in your table as I felt that a User is more likely to
enter an integer number of characters - perhaps your code could
restrict them to this?

Regards,

Pete

 

[Ray Cassick \(Home\)]

Yes, I have decided to do some restricting since this component is really
going to be mostly for reporting like sheet generation. No column widths
less than 1 seems to fit the bill fine.

Thanks again for all the help.