JimRobinson has asked for the wisdom of the Perl Monks concerning the following question:

I have recently been given a programming task which requires a little Perl code. I have created an xls file from a csv file, bolded and underlined...all is well. Isn't there column "autofit" functionality in Perl? Thanks guys !!

2006-07-21 Retitled by GrandFather, as per Monastery guidelines
Original title: 'AutoFit'

  • Comment on How to set an Excel column to 'AutoFit'

Replies are listed 'Best First'.
Re: How to set an Excel column to 'AutoFit'
by marto (Cardinal) on Jul 21, 2006 at 14:17 UTC
    Hi JimRobinson,

    From the Spreadsheet::WriteExcel documentation, under set_column method:

    'The width corresponds to the column width value that is specified in Excel. It is approximately equal to the length of a string in the default font of Arial 10. Unfortunately, there is no way to specify "AutoFit" for a column in the Excel file format. This feature is only available at runtime from within Excel.'

    I guess your best bet is to drive Excel to do this via Win32::OLE.

    Hope this helps.

    Martin
[reply]
[d/l]
Re: How to set an Excel column to 'AutoFit'
by jmcnamara (Monsignor) on Jul 21, 2006 at 15:09 UTC

    As pointed out above, autofit isn't possible using Spreadsheet::WriteExcel (since it isn't controlled by the file format).

    However, here is a workaround that I posted to the Spreadsheet::WriteExcel Google-Group.

    --
    John.

[reply]
Re: How to set an Excel column to 'AutoFit'
by davorg (Chancellor) on Jul 21, 2006 at 14:19 UTC

    You don't say what you have used to create the Excel file, so I'll assume that it's Spreadsheet::WriteExcel. The documentation for that module's set_column method says this:

    Unfortunately, there is no way to specify "AutoFit" for a column in the Excel file format. This feature is only available at runtime from within Excel.

    So it looks like you're out of luck.

    --
    <http://dave.org.uk>

    "The first rule of Perl club is you do not talk about Perl club."
    -- Chip Salzenberg

[reply]
      Thanks guys...I was affraid of that but it doesn't make sense. If I can alter data for the cell at runtime, seems like I could alter the cell width as well. Thanks for the quick response nevertheless.
[reply]

        Jim,

        You may have misread the documentation. The "set_column" method does allow you to alter the cell width. But you have to give it a "real" width; you can't just ask for the "best fit" width (which is what AutoFit does).

        I see that John has posted a link to a workaround that allows you to calculate a width that is approximately what AutoFit would give you.

        --
        <http://dave.org.uk>

        "The first rule of Perl club is you do not talk about Perl club."
        -- Chip Salzenberg

[reply]

Back to Seekers of Perl Wisdom