none
What is the width of an Excel column in twips? RRS feed

  • Question

  • Hi all;

    We're hitting a problem where our calculation of a column width is clearly a little different from Excel. Looking at 3.3.1.12, it gives a very detailed explanation except for two issues:

    First, what font & font size should we use? It says the normal style font. Is that /styleSheet/cellStyles/cellStyle/@name='Normal'? Or is it something else?

    Second, the calculation gives the width in pixels. What is the conversion to twips? Do we assume 96 dpi for the pixels?

    And is there anything else I need to do outside of the described calculation?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Saturday, June 8, 2019 1:50 PM

All replies

  • Hi Dave, 

    Thanks for the question. I'll check into it and get back to you.

    Best regards,
    Tom Jebo
    Sr Escalation Engineer
    Microsoft Open Specifications

    Saturday, June 8, 2019 8:31 PM
    Moderator
  • Hi Dave, 

    Thanks for patience and sorry for delay in responding. I've gone through the code on this and done some testing and it appears that Excel is following the guide in the standard at least with respect to Calibri font. In those tests I did find that the assumption would be 96 dpi and the font can be found in the path you identified, i.e.: 

      <cellStyles count="1">
        <cellStyle name="Normal" xfId="0" builtinId="0"/>
      </cellStyles>

    which points to:

      <fonts count="1" x14ac:knownFonts="1">
        <font>
          <sz val="11"/>
          <color theme="1"/>
          <name val="Calibri"/>
          <family val="2"/>
          <scheme val="minor"/>
        </font>
      </fonts>

    In my sample file tests. 

    And if I specify a column width (in the UI) of 10 characters, I see that Excel correctly inserts the following:

        <col min="7" max="7" width="10.7109375" customWidth="1"/>

    Which agrees with my own calculations.

    I have not tested much more extensively, therefore, if you see something that is standing out as deviating, perhaps there is something I've missed. One thing to remember is the truncation that is used in the formulas in 18.3.1.13 "col (Column Width & Formatting). 

    Can you share what you're seeing differently in your calculations?

    Tom


    Tuesday, June 18, 2019 12:10 AM
    Moderator
  • Ok, here we go. Open the file ATE-2451.xlsx. The width unit for a cell is the character "0" in Calibri 12 point. Reading the true type font file, that's 121.44001 twips. Looking at column C in the worksheet TEST it has a width of 23.

    To turn this into pixels it's (121.44001 * 96) / 1440 = 8.096 pixels for a character.

    So the width of the column in pixels is truncate (((23 * 8.096 + 5) / 8.096) * 256)) / 256 = 23.6172 pixels

    At 96 DPI, that's 23.6172 / 96 = 354.2578 twips.

    Now if you look at D47 you see a bitmap. But that bitmap is actually anchored in C47 with an offset of: 680357 EMUs or 1,071.4283 twips. That's basically 3 times the width of column C.

    What am I missing in these calculations?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Thursday, June 20, 2019 5:41 PM
  • Dave, 

    The first problem I see here is that you're saying column C is width 23, I'm assuming based on this in the file format: 

      <cols>
        <col min="1" max="1" width="5.75" customWidth="1"/>
        <col min="2" max="2" width="23" customWidth="1"/>
        <col min="3" max="6" width="22.125" customWidth="1"/>
      </cols>
    

    The problem is that <col min="2" max="2"... is actually column B. 

    Column C is the last col entry and is 22.125 in width after the calculations.

    I actually got 8 pixels in width for the Calibri font with 12 points (for all the numbers 0-9, they're the same) when using System.Windows.Forms' TextRenderer.MeasureText() method. But pretty close. 

    In the UI, it says column C is 21.5 chars in width. When I plug in the numbers to the formula in 18.3.1.13 "col", I get: 

    Truncate((21.5 chars * 8 pixels + 5 padding)/8 * 256)/256 = 22.125

    Which agrees with the file format col entry. And that's 331.875 twips.

    Let me take a little more time to check out your bitmap offset calculations so I understand that part. 

    Tom


    Thursday, June 20, 2019 7:15 PM
    Moderator
  • The difference here is that the cols/col specification in the WorkSheet part is using the underlying logical worksheet columns, i.e. A, B, C, ... But the when the drawing part specifies a twoCellAnchor to anchor a picture in a cell, it uses merged cell indexes.

    I measured the EMU values in inches for the from and to elements and they come to about 1.84 inches (1682750 EMUs) offset from the left side of the second merged cell (C):



    and 0.744 inches (680357 EMUs) offset from the left side of the third merged cell (D) which agrees with where the extents of the picture object:

    (The measuring tool didn't let me get as precise as I wanted but it's close enough.)

    It makes sense that the twoCellAnchor would use merged cells as these are the visual cells with which to anchor objects. But the cols/col elements need to allow for all cells in the sheet to be described. Using merged cells wouldn't allow that.

    I hope this helps.

    Tom


    Friday, June 21, 2019 9:15 PM
    Moderator
  • >> And that's 331.875 twips.

    >> to about 1.84 inches (1682750 EMUs) offset from the left side of the second merged cell (C)

    Tom - what I'm struggling with is that as you said, the offset is 1.84" from the left edge of the cell, but the cell is only 332 twips wide. I think I'm not understanding something about the picture offset measure - but I don't see what I'm missing.

    ??? - thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Monday, June 24, 2019 2:39 PM
  • Hi Tom;

    Ok, I'm even more confused. In ISO/IEC 29500-1:2012(E) (that's the latest - correct?) 18.3.1.13 it has an example of:

    [Example: Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi). In fact, each digit is the same width for this font. Therefore, if the cell width is 8 characters wide, the value of this attribute must be Truncate([8*7+5]/7*256)/256 = 8.7109375. end example]

    Correct me if I'm wrong. This means if I want to display 8 characters in a column, I need to set width='8.71" - correct? Since I start with the column width attribute, I don't care about the above formula. Instead, what I want is:

    To translate the value of width in the file into the column width value at runtime 
    (expressed in terms of pixels), use this calculation:

    =Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})

    [Example: Using the same example as above, the calculation would be Truncate(((256*8.7109375+Truncate(128/7))/256)*7) = 61 pixels. end example]

    Is that correct?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Monday, June 24, 2019 4:24 PM
  • Sorry, my math was wrong on the twip conversion (331.875). 

    Based on the 18.3.1.13 formulas, 22.125 character width in your example file should work out to 177 pixels which should be 2655 twips. That is also about 1.84 inches which agrees with the measurement for the picture anchor's "from" offset when converted from EMUs.

    Truncate(((256*22.125+Truncate(128/8))/256)*8) = 177 
    Check me on this but I think that works out.

    Tom



    Monday, June 24, 2019 5:29 PM
    Moderator
  • And yes, this is correct. BTW, the latest edition is ISO 29500-1:2016:

    https://www.iso.org/standard/71691.html

    Tom

    Monday, June 24, 2019 5:38 PM
    Moderator