"Epplus" Column width discrepancy

Descriptionhi Jan,

I has noticed that, I set a column width there is a
Discrepancy between the width that I set in code and what
Excel reports.

From the code below, Excel reports the width of be 11.27.

Excel reports the height to being exactly what I set.

Osheet.column (1). Width = 12.0d;
Osheet.row (1). Height = 27.75d;

Duardoclosed Nov at 5:00pm by
This is the OOXML value. You can has a look at it if you rename your document *.zip, extract it and look in the \xl\worksheets\sheetx.xml file. I think Excel recalculates it in some-on startup.
Anyway, I can ' t change it since a IoT of people use it.comments

Tomdierickx wrote APR 9 at 8:32 PM

For anybody else who runs to this, by some lengthy trial-and-error experimentation, I believe whatever Column width you Think you is setting, it narrows it by approx 0.667 and then rounds to the nearest 1/7th and if you is trying to set a V Alue lower than 1.667, it starts rounding down on 50% to the nearest 12th.

So, for X >= 1.667, you'll probably see a true column width of [ROUND (7 * (x-1/256))-5]/7 rounding this to Decimal places. For example, let's say you try setting the column width to ten (i.e. x=10), then you'll likely really see a value of "ROU ND (7 * (10-1/256)) "= 70-5 = 65/7 = 9.29

One workaround that seems to work for a variety of sample widths are to create a custom function to ' adjust up ' the value y ou think you ' re setting by an amount that would result in a net value to get as close as possible (to the nearest 1/7th If > 1.6667 or to the nearest 1/2th if < 1.6667):

Osheet.column (1). Width = Ten ' really ends up being 9.29
Osheet.column (2). Width = Settruecolumnwidth (Ten) ' Really ends up being 10.00

Where the following function takes your desired column width and modifies it by enough such that the net result of setting The column width would actually end up being what do you want it to be:

Private Function settruecolumnwidth (ByVal dblwidth as double) as double

' Deduce what's the COLUMN WIDTH would really GET SET to
Dim Z as Double = 1
If (Dblwidth >= (1 + 2/3)) Then

  z = Math.Round((Math.Round(7 * (dblWidth - 1 / 256), 0) - 5) / 7, 2)

Else

  z = Math.Round((Math.Round(12 * (dblWidth - 1 / 256), 0) - Math.Round(5 * dblWidth, 0)) / 12, 2)

End If

' How far OFF? (would be less THAN 1)
Dim Erroramt as Double = 0
Erroramt = Dblwidth-z

' CALCULATE AMOUNT to TACK ONTO the ORIGINAL AMOUNT to RESULT in the CLOSEST POSSIBLE SETTING
Dim Adjamt as Double = 0
If (Dblwidth >= (1 + 2/3)) Then

  adjAmt = (Math.Round(7 * errorAmt - 7 / 256, 0)) / 7

Else

  adjAmt = ((Math.Round(12 * errorAmt - 12 / 256, 0)) / 12) + (2 / 12)

End If

' RETURN A scaled-value that should RESULT in the NEAREST POSSIBLE VALUE to the TRUE desired SETTING
If (Z > 0) Then

  Return dblWidth + adjAmt

Else

  Return 0

End If

End Function

Grimmdp wrote APR 2 at 1:42 PM

Hi Tom,
Thanks a bunch for posting this, it helped me a ton. Here it's in C # for anyone who need it:

Dean

        public static double Gettruecolumnwidth (double dblwidth) {//deduce, what's the COLUMN WIDTH would            Really GET SET to double z = 1d;            if (Dblwidth >= (1 + 2/3)) Z = Math.Round ((Math.Round (7 * (DBLWIDTH-1/256), 0)-5)/7, 2); else Z = Math.Round ((math.round (dblWidth-1/0)-Math.Round (5 * dblwidth, 0)/12,             2); How far OFF?             (would be less THAN 1) Double erroramt = dblwidth-z; CALCULATE AMOUNT to TACK ONTO the ORIGINAL AMOUNT to RESULT in the CLOSEST POSSIBLE SETTING double Adja            Mt = 0d;             if (Dblwidth >= (1 + 2/3)) Adjamt = (Math.Round (7 * errorAmt-7/256, 0))/7;             else Adjamt = ((Math.Round (erroramt-12/256, 0)/12) + (2/12); RETURN A Scaled-value that should RESULT in the NEAREST POSSIBLE VALUE to the TRUE desired SETTING if (Z > 0) return dblwidth + Adjamt;         return 0d; }

　　

Redaxe wrote Jul at 8:38 AM

Saved my day thanks a lot,
Only one issue 3.5 are being calculated as 3.57 but their fine for me. :)

Christophe_ wrote Jan 7 at 9:07 AM

Also saved some time on our project, thanks!

"Epplus" Column width discrepancy