none
[Excel column width]How to calculate maximum digit width?(I am a C++ programmer) RRS feed

  • Question

  • Hello everyone,

    This question might be related to the post

    https://social.msdn.microsoft.com/Forums/en-US/9a6a9785-66ad-4b6b-bb9f-74429381bd72/margin-padding-in-cell-excel?forum=os_binaryfile&forum=os_binaryfile

    The problem is that I am using a 4k monitor, which is 3840*2160 resolution, the Excel default  column width is 9.09(96 Pixcels,) according to Microsoft's calculation formula:

    width = Truncate ([{Number of Characters}*{Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256

    the thing is that maximum digit width will be a large, unrealistic number. Does this mean there is a problem with formula? and why/how?

    Thanks,
    Frost
    Thursday, November 30, 2017 4:18 PM

All replies

  • Hello everyone,

    This question might be related to the post

    https://social.msdn.microsoft.com/Forums/en-US/9a6a9785-66ad-4b6b-bb9f-74429381bd72/margin-padding-in-cell-excel?forum=os_binaryfile&forum=os_binaryfile

    The problem is that I am using a 4k monitor, which is 3840*2160 resolution, the Excel default  column width is 9.09(96 Pixcels,) according to Microsoft's calculation formula:

    width = Truncate ([{Number of Characters}*{Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256

    the thing is that maximum digit width will be a large, unrealistic number. Does this mean there is a problem with formula? and why/how?

    Thanks,

    Frost


    • Edited by Frostge95 Thursday, November 30, 2017 4:14 PM
    • Merged by Chenchen Li Friday, December 1, 2017 1:49 AM duplicated
    Thursday, November 30, 2017 4:06 PM
  • You can't use such a simple formula to get the width of a text, resp. the optimal width of a column.

    https://msdn.microsoft.com/en-us/library/windows/desktop/dd144938(v=vs.85).aspx

    I've used that in the past for a special project to draw a frame around a text, but the width of a column in Excel is more,because you also have to consider that a cell has a frame with thin or thick lines ...

    And the view can havea zoom state... icons...

    The formula above is just a simple approximation, which only works under special conditions...

    Andreas.




    Thursday, November 30, 2017 6:34 PM
  • Hi Frost,

    Thank you for your Office spec question. One of our engineers will respond soon.

    Thanks,


    Jeff McCashland | Microsoft Protocols Open Specifications Team

    Thursday, November 30, 2017 7:08 PM
    Moderator
  • Hello Frost -

    I'm researching this for you. I assume that you are facing this issue only with 4k monitors; non-4k monitors are giving you correct width. Is this understanding correct?

    Thanks.


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Thursday, November 30, 2017 8:18 PM
  • Hello Frost,

    This forum (Excel for Developers) is for development issues when using Excel Object Model. According to your description, you want to know how to calculate the column width. As this issue is related to Open Specifications and i notice you have posted a same thread in Office XML, ODF, and Binary File Formats forum, i would merge this thread to

    https://social.msdn.microsoft.com/Forums/en-US/d02ef91f-e89e-41c5-9c18-b9564abd9f3f/excel-column-widthhow-to-calculate-maximum-digit-widthi-am-a-c-programmer?forum=os_binaryfile 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 1, 2017 1:47 AM
  • Hi Andreas Thank you for replying me, and yes, I have tried thay way as well, but the result is not very accurate. For example Calibri 11, Microsoft gave me 7 pixel but using that way gave me 5.
    • Edited by Frostge95 Friday, December 1, 2017 2:48 AM
    Friday, December 1, 2017 2:46 AM
  • Hello Frost - 

    Reg "For example Calibri 11, Microsoft gave me 7 pixel but using that way gave me 5."

    Seems you are getting Maximum Digit Width(MDW) as 5 on 4k monitor for Calibri Font? You also mentioned that you are getting an unrealistic number as MDW? What is this unrealistic number and how are you getting it?

    If I understand correctly; you would like to know which API you can leverage to get MDW as 7; which excel is using. Is this understanding correct?

    Thanks


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Saturday, December 2, 2017 12:44 AM
  • Hi Tarun, 

    thank you for replying this question, and yes, on 4k monitor, I used GetTextExtentpoint32 function to get 5 pixel width. if this result is not correct, what should I do? 

    the unrealistic number means that I used Excel 2016 and default column width is 8.09, then divide by the width of 8 characters, we will have 0.09 character width. If apply the formula Microsoft gives me, and suppose the character width is w, and 5 pixel padding, then 5/w = 0.09, which gives me w =55.6, and I don't think this is realistic.

    Saturday, December 2, 2017 10:27 PM
  • I used GetTextExtentpoint32 function to get 5 pixel width.

    In Excel we have points, not pixel.

    I calculate the width of the chars with GetTextExtentpoint32 in points and add 5 points to get the "optimal" width.

    After that I calculate for Range R:
      R.EntireColumn.ColumnWidth = X * R.EntireColumn.ColumnWidth / R.EntireColumn.Width

    That works... if I run the process a few times.

    I'm interested how that works on your PC:

    https://www.dropbox.com/s/cxco6a746693da1/TextSize.xls?dl=1

    The code works only in 32-bit (because it's old code, I have no time to update to 64-bit).

    If you open the file, select a cell with text and click the button a few times.

    The red rectangle is a shape, I resize it to the same width/height and move to the cell for testing purpose.

    But the width and hight is not the same if you use AutoFit, it is smaller.

    Andreas.


    Sunday, December 3, 2017 8:35 AM
  • @Andreas: Thank you for your help.

    @Frostge95: Does the solution shared by Andreas helps?  


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team


    Tuesday, December 5, 2017 7:49 PM
  • Hello Frostge95:

    Can you please let us know if you still need any further assistance ?

    Thanks.


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team


    Monday, December 11, 2017 4:16 PM
  • Hello Fostge95:

    We are closing our Analysis since we haven't heard back form you. If want to re-engage us; please open a new thread.

    Thanks.


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Wednesday, December 13, 2017 6:48 PM