# Calculating auto fit with for text

• ### Question

• Hi all

This page shows how to calculate the best fit with for a column under the width attribute

If however the cells in a column contain text is it really still the same formula using max digit width ?

Thanks

Monday, August 15, 2016 12:04 PM

### All replies

• Hi Minieggs1999,

first of all I want to confirm with you that to get the max digit width you want to use user interface or you want to use code?

the description mentioned in the link will also work for text because the calculations are performed on the characters. so it doesn't matter that if character is numeric or text.

you can test it on your side too.

if you have any further questions let me know.

Regards

Deepak

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Tuesday, August 16, 2016 1:44 AM
• Thanks for the reply Yes I create the font and measure each 0-9 charcter to find out the widest and get the sane answer as in hhe example But surely W and M's etc are widers than 1's etc ??
Tuesday, August 16, 2016 6:29 AM

http://polymathprogrammer.com/2012/11/18/calculate-excel-column-width-pixel-interval/

Tuesday, August 16, 2016 8:29 AM
• Hi Minieggs1999,

if you are using the code to calculate this then can you provide that code so that I can also test with that code and we can try to look in to that.

Regards

Deepak

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Tuesday, August 16, 2016 8:53 AM
• I can do but it is not in a Microsoft language. It is in a 4GL called Dataflex

I can tell you it does calc the same value as in the example formula on the msdn page to the last decimal place

Let me know if you still want it

Tuesday, August 16, 2016 9:10 AM
• I don't understand the 4GL language.

but without code I am also not able to make a test with that. so I can't tell you the exact results.

here I can recommend you to also check the result of pixel to character and match that result with character to pixel.

we can know the difference in the result.

Regards

Deepak

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Wednesday, August 17, 2016 5:49 AM
• OK here is my code ...

```    Function PixelWidthOfScreenText String sText Boolean bBold Integer iPointSize String sTypeface Returns Integer
Integer iFontSize iLogPixelsY
Number nFontSize
Boolean bVoid
Handle hDC hFont hOldFont
Pointer lpLogFont lptm
tWinTextMetric tm
tWinLogFont ALogFont
Integer cx bOK
Pointer lpsText lpsPoint
String sPoint

// Convert Font point size to logical pixels....
Move (GetDC(0)) to hDC
Move (GetDeviceCaps(hDC, LOGPIXELSY)) to iLogPixelsY
Move ((iPointSize * iLogPixelsY / 72.0) + 0.5) to nFontSize
Move nFontSize to iFontSize    // nFontSize is rounded up, then we truncate it into iFontSize - this reduces rounding error due to integer truncation

// Convert the TypeFace name into a Char Array....
Move (ToANSI(sTypeface)) to sTypeFace    // convert OEM typeface name into an ANSI string.
Move sTypeFace to pTypeFace

If (bBold) Begin
Move 700 to ALogFont.lfWeight
End

// Convert Logical Pixels into physical pixels....
Move (0-iFontSize) to ALogFont.lfHeight  // use -ve value to instruct system to scale font to device

Move (CreateFontIndirect(lpLogFont)) to hFont   // Here we create the font
Move (SelectObject(hDC, hFont)) to hOldFont     // Now we select it to the device context

ZeroType tPoint to sPoint

Move (GetTextExtent(hDC, lpsText, length(sText), lpsPoint)) to bOk

GetBuff from sPoint At tPoint.x to cx

Move (SelectObject(hDC, holdFont)) to hOldFont
Move (DeleteObject(hFont)) to bVoid
Move (ReleaseDC(0, hDC)) to hDC

Function_Return cx
End_Function

Function CalcMaxDigitWidth Boolean bBold Integer iPointSize String sTypeface Returns Integer
Integer iMaxDigit iCurrentDigit iLoop

For iLoop from 0 to 9
Get PixelWidthOfScreenText iLoop bBold  iPointSize sTypeface to iCurrentDigit
Move (iMaxDigit max iCurrentDigit) to iMaxDigit
Loop

Function_Return iMaxDigit
End_Function

Function CreateSheetData String sFilename String[][] saValues Returns Integer
Handle hoSheetDataDoc hoSheetData hoRow hoC hoSheet1 hoNode hoNodeParent hoCols hoCol
String sXML sNS sText sNSDefault sNSx14ac sColLetter sCell
String[] saTextValues
Boolean bOK
Integer iStringWidth iMaxDigit iLoopRow iLoopRowEnd iLoopCol iLoopColEnd iTextPos iMaxCol iError
Number nBestFitSize
Integer[] iaMaxCharsPerCol

Get CreateDocumentElementNS of hoSheetDataDoc sNSDefault "sheetData" to hoSheetData

// limit to xl max rows
Move (SizeOfArray(saValues)-1 min 1048575) to iLoopRowEnd

Get psaTextValues to saTextValues

For iLoopRow from 0 to iLoopRowEnd
Get  AddElementNS     of hoSheetData sNSDefault "row" "" to hoRow
Send AddAttributeNS   of hoRow sNSDefault "r" (iLoopRow+1)
Send AddAttributeNS   of hoRow sNSx14ac   "x14ac:dyDescent" "0.25"

// limit to xl max cols
Move (SizeOfArray(saValues[iLoopRow])-1  min 16383) to iLoopColEnd

Move (iMaxCol max iLoopColEnd) to iMaxCol

For iLoopCol from 0 to iLoopColEnd
// limit to xl max cell width
Move (Left(Trim(saValues[iLoopRow][iLoopCol]),32767)) to sCell
If (Length(sCell)>0) Begin
Move (iaMaxCharsPerCol[iLoopCol] max Length(sCell)) to iaMaxCharsPerCol[iLoopCol]
Get  AddElementNS of hoRow sNSDefault "c" "" to hoC
Get ColumnNumberToLetter iLoopCol to sColLetter
Send AddAttributeNS of hoC sNSDefault "r" (sColLetter+String(iLoopRow))
Move (BinarySearchArray(sCell,saTextValues)) to iTextPos
If (iTextPos>-1) Begin
Send AddAttributeNS of hoC sNSDefault "t" "s"
Send AddElementNS   of hoC sNSDefault "v" iTextPos
End
Else Begin
Send AddAttributeNS of hoC sNSDefault "s" "2"
Send AddElementNS   of hoC sNSDefault "v" sCell
End
Send Destroy of hoC
End
Loop
Send Destroy of hoRow
Loop

Get TextResourceAsString "sheet1" to sXML

Get Create (RefClass(cXMLDOMDocument)) to hoSheet1

Set pbAsync of hoSheet1 to False
Set pbValidateOnParse of hoSheet1 to True

Get LoadXml of hoSheet1 sXML to bOK

Set psSelectionNamespaces of hoSheet1 to sNS
Get FindNode of hoSheet1 "//default:worksheet/default:sheetData" to hoNode
Get FindNode of hoNode ".." to hoNodeParent
Get RemoveNode of hoNodeParent hoNode to hoNode
Send Destroy of hoNode
Send Destroy of hoNodeParent

Get FindNode of hoSheet1 "//default:worksheet/default:pageMargins" to hoNode
Get FindNode of hoNode ".." to hoNodeParent
Get InsertBeforeNode of hoNodeParent hoSheetData hoNode to hoSheetData
Send Destroy of hoNode
Send Destroy of hoNodeParent

Get FindNode of hoSheet1 "//default:worksheet/default:dimension" to hoNode
Send RemoveAttributeNS  of hoNode "" "ref"
Get ColumnNumberToLetter iMaxCol to sColLetter
Send AddAttributeNS     of hoNode sNS "ref" ("A1:"+sColLetter+String(iLoopRowEnd+1))

Get CreateDocumentElementNS of hoSheetDataDoc sNSDefault "cols" to hoCols
Get CalcMaxDigitWidth True 11 "Calibri" to iMaxDigit

For iLoopCol from 0 to iMaxCol
Get  AddElementNS     of hoCols sNSDefault "col" "" to hoCol
Send AddAttributeNS   of hoCol  sNSDefault "min" (iLoopCol+1)
Send AddAttributeNS   of hoCol  sNSDefault "max" (iLoopCol+1)
Move ((Integer((iaMaxCharsPerCol[iLoopCol]*iMaxDigit+5)/iMaxDigit*256.0))/256.0) to nBestFitSize
Send AddAttributeNS   of hoCol  sNSDefault "width" nBestFitSize
Send AddAttributeNS   of hoCol  sNSDefault "bestFit" "1"
Send AddAttributeNS   of hoCol  sNSDefault "customWidth" "1"
Send Destroy of hoCol
Loop

Get FindNode of hoSheetData ".." to hoNodeParent
Get InsertBeforeNode of hoNodeParent hoCols hoSheetData to hoCols
Send Destroy of hoNodeParent
Send Destroy of hoCols

Send Destroy of hoSheetData

Set psDocumentName of hoSheet1 to sFilename
Get SaveXMLDocument of hoSheet1 to iError
Send Destroy of hoSheet1

Function_Return (iError=0)
End_Function```

• Edited by Wednesday, August 17, 2016 8:27 AM missing code
Wednesday, August 17, 2016 8:26 AM
• Hi Minieggs1999,

as I already told you I am not able to understand 4th Gen Language.

but I want to check the result so I try to test in VBA code.

I totally understand that you had mentioned in your earlier reply,"But surely W and M's etc are widers than 1's etc ??" and I also agree with you.

with my test I get result below.

`Sub demo()Debug.Print "Cell A1 Character width in pixel:" & ActiveWindow.PointsToScreenPixelsX(Range("A1").Width)Debug.Print "Cell A2 Character width in pixel:" & ActiveWindow.PointsToScreenPixelsX(Range("A2").Width)Debug.Print Range("A1").WidthDebug.Print Range("A1").ColumnWidthEnd Sub`

Result:

As you can see that I got the same number of pixels for "M" and "1". so here I think that it is not actually mapping the width of character as it considers the same width for any character whether it is a Text or Numeric.

Regards

Deepak

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Thursday, August 18, 2016 1:48 AM
• OK using the default Calibri 11

Put the word "Andrew" in the cell

Using the formula gives 6.7109375 which is not quite wide enough for the text

If you double click on the column separator and resave the spreadsheet and then inspect the sheet1.xml you will see the width is exactly 8 using whatever calculation Excel really uses

• Edited by Thursday, August 18, 2016 8:30 AM added font
Thursday, August 18, 2016 8:29 AM
• Hi Miniegges1999,

did you used a VBA code to find the width of cell?

look what results I find when I run that code.

you can see that column width is 8.43.

Regards

Deepak

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Friday, August 19, 2016 4:36 AM
• No, you are answering a different question to the one I am asking

If you open a new sheet and type 1 and M in the first two cells

Now double click on the column separators to auto-fit them

If you check the column widths in Excel they come out at  1.29 and 2  clearly different to each other. This is not the value I need to set but they prove M is wider than 1

If you now save it as test.xlsx

Rename it as test.xlsx.zip

Open it and open xl\worksheets\sheet1.xml

You will see

```<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dimension ref="A1:B1"/>
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0">
<selection activeCell="D3" sqref="D3"/>
</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
<cols>
<col min="1" max="1" width="2" bestFit="1" customWidth="1"/>
<col min="2" max="2" width="2.7109375" bestFit="1" customWidth="1"/>
</cols>
<sheetData>
<row r="1" spans="1:2" x14ac:dyDescent="0.25">
<c r="A1">
<v>1</v>
</c><c r="B1" t="s">
<v>0</v>
</c>
</row>
</sheetData>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

```

Notice the cols element and within it two col elements containing a width. The first being 2 and the second 2.7109375

RIGHT !

These are the values I am trying to calculate using the formula on this page

Now part of the formula is the number of characters in the string which in this in both cases is one.

If you use the formula on Calibri 11 you get the answer 1.7109375 which obviously is neither 2 or 2.7109375

So by any measure the formula against the width attribute on that page cannot be the one Excel is using

So my question I need answering is what is the formula that gives the answers 2 and 2.7109375 for these columns

Friday, August 19, 2016 9:02 AM
• Thanks for the reply Yes I create the font and measure each 0-9 charcter to find out the widest and get the sane answer as in hhe example But surely W and M's etc are widers than 1's etc ??
With digits, all characters have the same width in a given font & point size. For a Word macro that calculates character widths for a designated font & point size, see: http://www.msofficeforums.com/word/13894-character-spacing-conversion-inches.html#post38158

Cheers
Paul Edstein
[MS MVP - Word]

Saturday, August 20, 2016 2:49 AM
• Hi Minieggs1999,

i try to find the formula or a way to get your desired result.

but I did not find any formula for that.

I will try to search again and if I find something that can help you to solve your issue I will provide you.

Regards

Deepak

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Monday, August 22, 2016 5:41 AM
• It's not clear what your ultimate objective is but usually the simplest way in Excel to determine the width of mixed text, and/or height with multiline, is with the range object's AutoFit or Shape/Textbox's AutoSize (even with mixed fonts).
Monday, August 22, 2016 12:20 PM
• The ultimate objective is to create an xlsx file outside of excel without the need for Excel using the SpeadsheetML standards

This is because I need to create xlsx files from scheduled tasks on a server and you cannot automate Excel for this purpose as reco0mmended by Microsoft

Monday, August 22, 2016 12:25 PM
• So you're saying you effectively Excel is not to hand at all, even locally as a helper?

Have you looked at GetTextExtentPoint32, plenty of examples out there. Make sure you get a complete example as there are various objects both to create and cleanly destroy in the process.

Although pixels to points is the same for most users it might not be for some. Also keep in mind Excel cells include some padding when 'autofit'. There was a way of calculating it but forget now and no doubt it's changed with newer versions.

Monday, August 22, 2016 3:31 PM
• Correct, Excel is not involved

Because of the recommendations here (now old doc obviously)
https://support.microsoft.com/en-gb/kb/257757

Yes I am aware GetTextExtentPoint32 but the ultimate value that goes in the width attribute of the column must be some sort of relative value and therefore there MUST be a formula somewhere for calculating it

Monday, August 22, 2016 4:03 PM
• I'm not aware of any Excel related formula or exposed method, though if you know the given Font specifications and metrics no doubt the text width with mixed characters could be calculated.

IIRC GetTextExtentPoint32 works well with most of what's thrown at it, and consistent with results returned from AutoFit and AutoSize, albeit without the 'padding'. Although there are a few API calls involved I think a lot less code than the long example you posted above, why not go with that?

Keep in mind column widths for sizing are based on a standard character (like numerals) in the Font as defined in the sheet's "Normal" style, not sure if that'll be an extra factor you'll need to handle.

Monday, August 22, 2016 4:44 PM