Autofit column formatting
bfentem
been on my todo list for quite a while. So far everything is working
out well. Thanks for everyone's efforts on it.
Part of my final cleanup, I would like to have the column widths
"autofit" to the maximum cell sizes. When using excel interactively,
highlighting the whole sheet, and then double click on the column
separators will auto adjust the column widths to view all of the
cells' contents.
I've search the group list and the documentation. The closest thing I
found is the alignment.shrink_to_fit, but I'm not getting it to work.
At least as I'm expecting it to work.
Is the a quick function that is available to perform this step? Or a
short example someone could provide.
Thanks
John Machin
Hi bfentem ... Welcome to the group!
> Part of my final cleanup, I would like to have the column widths
> "autofit" to the maximum cell sizes. When using excel interactively,
> highlighting the whole sheet, and then double click on the column
> separators will auto adjust the column widths to view all of the
> cells' contents.
True.
> I've search the group list and the documentation. The closest thing I
> found is the alignment.shrink_to_fit, but I'm not getting it to work.
> At least as I'm expecting it to work.
>
> Is the a quick function that is available to perform this step? Or a
> short example someone could provide.
The functionality you describe above is a response from Excel's display
rendering engine. Pigs will fly before xlwt acquires one of those.
The XLS file format has no way of specifying for each sheet whether it
is required to "autofit" the sheet when first displayed (and turn the
flag off when the file is saved) -- that would require MS to be
extremely supportive of third parties creating Excel-compatible files,
another case of porcine aviation.
The only time I've been asked by a user about this, the conversation
went something like this:
Me: Sorry, no, you'll have to select the columns or the whole sheet then
do Format/Column/Autofit Selection.
User: Having the computer create the spreadsheet has saved us hours, a
few clicks don't matter. BTW, it's easier to double-click on a column
separator.
Me: Thanks for the tip!
HTH,
John
John Yeung
>
> On 1/09/2009 7:29 AM, bfentem wrote:
>
>> The closest thing I found is the alignment.shrink_to_fit,
>> but I'm not getting it to work.
I don't have Excel running in front of me (I use it at work), so I
can't say for sure, but I imagine the shrink-to-fit you are referring
to is specifically meant to leave column widths alone and try making
the font smaller. (I find this is usually ugly, but I actually know
some people at work who use this.)
>> Is the a quick function that is available to perform this step? Or a
>> short example someone could provide.
>
> The functionality you describe above is a response from Excel's display
> rendering engine. Pigs will fly before xlwt acquires one of those.
Well, there isn't a "quick" function to do this, but because the
demand for this feature (combined with my own interest) was high
enough, I took the time to actually come up with a dictionary that
maps common characters (roughly speaking, string.letters +
string.digits + string.punctuation + ' ') to approximate column widths
needed by Arial 10, which is my default Excel font. When I'm writing
out a sheet that needs a column autofitted, I keep track of the
maximum width of the data going into that column, according to this
dictionary. (For characters not in the dictionary, I use the width of
'0'. I didn't have the patience to do this again for bold, so I just
multiply by 1.1 if it's bold.)
In my experiments, I even managed to accidentally stumble upon how
Excel's "fudge factor" works (sort of), and as a result, my autofit is
actually quite close to Excel's.
The safer and faster-performing way to do autofitting is to pick a
monospaced font (like Courier New or whatever), find out what the
column width is for one of those characters, and multiply by the
number of characters (plus fudge factor if you wish). I don't know
how *much* faster this is than adding up the (varying) widths of
characters in a cell, but if the customer likes Arial a lot more than
any monospaced font you can find, then it may be worth the hit in
performance.
And yes, I know my solutions are kludgy and use brute force, and
crucially are not applicable to any fonts other than the ones I've
bothered to map out, but I am not clever enough to come up with
anything better.
If there's demand for this here, I can see about retrieving my code
and posting it.
John Y.
bfentem
Understand the issues with the pigs, thus using this type of solution
rather than futzing with the multiple variations of excel. I've
normally used tab-delimited or csv files for plain outputting
purposes.
Since my output is only spanning 7 columns and it's output is
consistent, I'm going with a fixed column size approach. If I need to
expand the solution, I will probably use a similar approach to yours
mentioned below. Right now, the users are happy to just get the
information.
Brent...
On Aug 31, 7:28 pm, John Yeung <gallium.arsen...@gmail.com> wrote:
John Yeung
> If there's demand for this here, I can see about retrieving
> my code and posting it.
Someone e-mailed me that they were interested in my Arial 10 character
width dictionary, so I've posted arial10.py to the files section of
this group. It also contains some commentary and simple column width
functions.
John Y.
Ethan Furman
~Ethan~