Solved: Problem with COM and Excel
Pekka Niiranen
krissepu at vip.fi
Wed Oct 3 05:32:12 EDT 2001
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
More information about the Python-list
mailing list