none
Calculating auto fit with for text RRS feed

  • Question

  • Hi all

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

    https://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.column.aspx

    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.
    Click HERE to participate the survey.

    Tuesday, August 16, 2016 1:44 AM
    Moderator
  • 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
  • Also this article seems to dispute the calculation example

    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.
    Click HERE to participate the survey.

    Tuesday, August 16, 2016 8:53 AM
    Moderator
  • 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
  • Hi Minieggs1999,

    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.
    Click HERE to participate the survey.

    Wednesday, August 17, 2016 5:49 AM
    Moderator
  • 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 
            Address pTypeFace
            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 (AddressOf(ALogFont.lfFaceName)) to pTypeFace
            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 (AddressOf(ALogFont)) to lpLogFont 
        
            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
            GetAddress of sPoint to lpsPoint
            GetAddress of sText  to lpsText
    
            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 Create (RefClass(cXMLDOMDocument)) to hoSheetDataDoc
    
            Move "http://schemas.openxmlformats.org/spreadsheetml/2006/main" to sNSDefault
            Move "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" to sNSx14ac
            
            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
            
            Move 'xmlns:default="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"' to sNS
            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
            Send Destroy of hoSheetDataDoc
    
            Set psDocumentName of hoSheet1 to sFilename
            Get SaveXMLDocument of hoSheet1 to iError
            Send Destroy of hoSheet1
    
            Function_Return (iError=0)
        End_Function

    • Edited by Minieggs1999 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").Width
    Debug.Print Range("A1").ColumnWidth
    End 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.
    Click HERE to participate the survey.


    Thursday, August 18, 2016 1:48 AM
    Moderator
  • 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 Minieggs1999 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.
    Click HERE to participate the survey.

    Friday, August 19, 2016 4:36 AM
    Moderator
  • 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"?>
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
     <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
    https://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.column.aspx

    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.
    Click HERE to participate the survey.

    Monday, August 22, 2016 5:41 AM
    Moderator
  • 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
    Moderator
  • 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
    Moderator
  • 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
    Moderator