how to use "bestfit" attribute to autofit column width


  • Open excel and type  the  text "This is a test" in A1, it width is bigger than default cell width . When I click format in the Ribbon and click Autofit column width, the "This is a test" will fit the cell  width. When I check the underlying sheet1.xml, I found the following information was added.
       <col min="1" max="1" width="15.28515625" bestFit="1" customWidth="1"/>
    But when I modify sheet1.xml to delete the  width="15.28515625", this cell will be hidden. It look likes you must set the width when apply  bestFit attibute,
     So my questions If programmaticly set the autofit column width,  I donot know the width of the text.  I just want to generate the sheet1.xml  like
        <col min="1" max="1"  bestFit="1" customWidth="1"/>, but it does not work.  I try and try, still donot know how make the bestFit work without set the
    width. Another question is what do the min="1" max="1" customWidth="1"  mean?

    Friday, May 22, 2009 5:43 AM

All replies

  • you may use OpenXmlClassesExplorer tool to check the ECMA spec of the element 'col' and its attributes, as well as the Office 2007 Notes with the tool (there possibly are slight differences betweeen the ECMA spec and Ofiice 2007 behavoirs, if you don'y follow the deviation descriptions, the document may not be opened by Ofiice 2007 client.)
    Besides, why you need to adjust the width automatically? the documents are for viewing? do you need to export the worksheet? Thanks!

    Shuangshuang Liao (MS)

    Friday, May 22, 2009 7:54 AM
  • The following description is from the ECMA spec. My purpose is programmaticaly generate the sheet1.xml, and want the autofit the data with the column width. But I found it must be  something like  <col min="1" max="1"  width="15.28515625" bestFit="1" customWidth="1"/>. The width  width="15.28515625"  must be set and fit with the text width, but how can I set the width, do you need parse the text length to know the width? what does it  'Best fit' is set to true under these conditions "The column width has never been manually set by the user, AND The column width is not the default width"  mean? I really donot want to set the width and just want to set bestFit="1"  and excel should handle it, but <col min="1" max="1"  bestFit="1" customWidth="1"/> never work.

    Flag indicating if the specified column(s) is set to 'best fit'. 'Best fit' is set to


    true under these conditions:

    The column width has never been manually set by the user, AND The column width is not the default width

    'Best fit' means that when numbers are typed into a cell contained in a 'best fit' column, the column width should automatically resize to display the number.[


    Note: In best fit cases, column width must not be made smaller, only larger. endnote]

    Friday, May 22, 2009 6:17 PM
  • After i tried, i think "autofit" behavior is sort of layout functionality. everytime you click the "autofit" option in the menu, Excel will recalculate the width again, simultaneously update the value of attribute "width". it's kind of runtime feature of Excel, instead of document centric. so SDK has no way to do the same thing as Excel does.
    Wednesday, June 03, 2009 9:12 AM