Solved: Problem with COM and Excel

[Pekka Niiranen]

Thank you Tim Golden and Cliff Wells.

  I do not need to provide a range if I use this:

>>> import win32com.client
>>> import win32com.client.dynamic
>>> xlApp = win32com.client.dynamic.Dispatch('Excel.Application')
>>> xlBook = xlApp.Workbooks.Add()
>>> sht = xlBook.Worksheets("Sheet1")
>>> xlApp.Visible = 1

And now the fixed line:

>>> sht.Columns.AutoFit()

-pekka-


Tim Golden wrote:

> Pekka Niiranen <krissepu at vip.fi> wrote in message news:<3BBA1BD5.8E833283 at vip.fi>...
> > How can I run AutoFit to all columns of my Excel sheet from python ?
> >
> > I have tried:
> >
> > >>> xlApp = win32com.client.dynamic.Dispatch('Excel.Application')
> > >>> xlBook = xlApp.Workbooks.Add()
> > >>> sht = xlBook.Worksheets("Sheet1")
> > >>> xlApp.Visible = 1
> >
> > So far so good, but at this stage I added some values to Excel Sheet
> > (manually) and then run:
> >
> > >>> sht.Columns().AutoFit
> >
> > The whole PC is jamming with harddisk running madly. I have to use task
> > manager to kill Idle and Excel.
> >
> > -pekka-
>
> The following works for me:
>
> def _2xl (headers, rowset, spreadsheet):
>   """_2xl - put a cursor output set to a spreadsheet, optionally
> applying traffic light formatting
>
>   Parameters:
>     headers - sequence of sequences containing column names & types
> (typically from cursor.description)
>     rowset - sequence containing each row as a sequence
>     spreadsheet - full path to a spreadsheet
>   """
>   n_cols = len (headers)
>   n_rows = len (rowset)
>   xl = Dispatch ("Excel.Application")
>   xl.DisplayAlerts = 0
>   try:
>     xl.Workbooks.Add ()
>     xl.ActiveSheet.Range (xl.Cells (1, 1), xl.Cells (1, n_cols)).Value
> = [header[0] for header in headers]
>     xl.ActiveSheet.Range (xl.Cells (1, 1), xl.Cells (1,
> n_cols)).Font.Bold = 1
>     xl.ActiveSheet.Range (xl.Cells (2, 1), xl.Cells (1 + n_rows,
> n_cols)).Value = rowset
>     xl.ActiveSheet.Range (xl.Cells (1, 1), xl.Cells (1 + n_rows,
> n_cols)).EntireColumn.AutoFit ()
>
>     xl.ActiveSheet.SaveAs (Filename=spreadsheet)
>     xl.Quit ()
>   finally:
>     xl = None
>     del xl
>
> I suspect the bit you need is the EntireColumn property of the range.
> HTH. Tim