Results 1 to 5 of 5

Thread: Excel Exporter - autoFitWidth

  1. #1
    Sencha Premium User
    Join Date
    Sep 2012
    Posts
    28
    Answers
    1

    Default Excel Exporter - autoFitWidth

    I'm attempting to use the autoFitWidth config property to auto size the width of columns for an excel export. However, I can't figure out where to set this property to make it take effect. Most other style configs are set on the config object on the saveDocumentAs(config) call...Has anyone figured out how to use this property? Thanks.

  2. #2
    Sencha User
    Join Date
    Feb 2013
    Location
    California
    Posts
    11,985
    Answers
    506

    Default

    Have you tried setting it on each column?

  3. #3
    Sencha Premium User
    Join Date
    Sep 2012
    Posts
    28
    Answers
    1

    Default

    Yes, I've tried setting autoFitWidth and autoSize on the column definition directly and they have no effect.

  4. #4
    Sencha Premium User
    Join Date
    Dec 2009
    Location
    Iasi, Romania
    Posts
    159
    Answers
    9

    Default

    Quote Originally Posted by gabe.galperin View Post
    Yes, I've tried setting autoFitWidth and autoSize on the column definition directly and they have no effect.
    Hi Gabe,

    autoFitWidth doesn't really work as expected. I played around with Excel to check what it does in the xlsx when you double click a column to set autoFitWidth. Basically Excel calculates the width of that column and sets that column as bestFit. Here is what bestFit means according to the xlsx specification:
    Flag indicating if the specified column(s) is set to 'best fit'. 'Best fit' is set to true underthese 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]


    The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
    And this is the specification for width:
    Column width measured as the number of characters of the maximum digit width of thenumbers 0, 1, 2, ..., 9 as rendered in the normal style's font. There are 4 pixels of marginpadding (two on each side), plus 1 pixel padding for the gridlines.
    width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixelpadding}]/{Maximum Digit Width}*256)/256
    [Example: Using the Calibri font as an example, the maximum digit width of 11 point fontsize is 7 pixels (at 96 dpi). In fact, each digit is the same width for this font. Therefore, ifthe cell width is 8 characters wide, the value of this attribute must beTruncate([8*7+5]/7*256)/256 = 8.7109375. end example]


    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)*{MaximumDigit Width})
    [Example: Using the same example as above, the calculation would beTruncate(((256*8.7109375+Truncate(128/7))/256)*7) = 61 pixels. end example]
    To translate from pixels to character width, use this calculation:=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100
    [Example: Using the example above, the calculation would be Truncate((61-5)/7*100+0.5)/100 = 8 characters. end example]
    [Note: when wide borders are applied, part of the left/right border must overlap with the2 pixel padding on each side. Wide borders do not affect the width calculation of thecolumn. end note]
    [Note: When the sheet is in the mode to view formulas instead of values, the pixel width of the column is doubled. end note]
    The possible values for this attribute are defined by the W3C XML Schema double datatype.
    So, it's not enough to set bestFit into the xml we also need to calculate the width of that column. You can see above the dependencies to calculate the width of a cell. Can't really do this in javascript since you may not have access to the chosen excel font

    What we support in the xlsx exporter is to have that flag set on a column (check class Ext.exporter.file.ooxml.excel.Column). This means that when you edit your file in Excel and one of the cells gets bigger then that whole column will autoresize. Be careful that this is available only for cells that have numbers and not for text cells.

    Hope this clarifies this issue for you.

    Best,
    Adrian

  5. #5
    Sencha Premium User
    Join Date
    Dec 2009
    Location
    Iasi, Romania
    Posts
    159
    Answers
    9

    Default

    Gabe, just saw that you were talking about the Excel xml format. My answer above applies to both xml and xlsx formats and you need to define autoFitWidth on the exportStyle of that grid column:
    Code:
    exportStyle: {    
        format: 'Standard',
       autoFitWidth: true,
       alignment: {
            horizontal: 'Right'
        }
    }
    

Similar Threads

  1. Replies: 5
    Last Post: 29 Sep 2016, 8:02 AM
  2. Replies: 1
    Last Post: 17 May 2013, 4:44 AM
  3. Replies: 1
    Last Post: 10 May 2012, 3:04 AM
  4. Ext.ux.Exporter and IE8 ?????
    By Spongerusher in forum Ext 3.x: Help & Discussion
    Replies: 7
    Last Post: 18 Jan 2010, 4:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •