Home Original page

Problem with COM and Excel

Tim Golden tim.golden at iname.com
Wed Oct 3 02:20:34 EDT 2001 
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
More information about the Python-list mailing list