python win32com excel problem

[Ray]

Hi,

I'm working on something with mysql and excel.
I'm using python and win32com. All major function works, But I have two
problems:

1. the output need to do "auto fit" to make it readable.

I tried to call "xlApp.Columns.AutoFit=1" the whole program will crash,
but without xlApp.Columns.AutoFit=1, everything just fine.


2. How do I set a rows format? I need to set row "F" to "Text", "o","p"
to general, and
"Q", "R", to currency.

the data in mysql is stored as text. and it's looks like:

551423
107300.00
22415.90
22124.17

In excel, It will display:

107300 #it should be 107300.00
22415.9 #it should be 22415.90


Error Message when I use Columns.AutoFit=1:

Traceback (most recent call last):
File "C:\Documents and Settings\Desktop\python\5.1.07\vpi.py", line
317, in <module>
root.mainloop()
File "C:\Python25\lib\lib-tk\Tkinter.py", line 1023, in mainloop
self.tk.mainloop(n)
File "C:\Python25\lib\Pmw\Pmw_1_2\lib\PmwBase.py", line 1751, in __call__
_reporterror(self.func, args)
File "C:\Python25\lib\Pmw\Pmw_1_2\lib\PmwBase.py", line 1777, in
_reporterror
msg = exc_type + ' Exception in Tk callback\n'
TypeError: unsupported operand type(s) for +: 'type' and 'str'



Python code :
(this function is called by clicked on "Excel" button from main program)

#Begin Function Generate_Excel#
def generate_excel(desc):
xlApp=Dispatch("Excel.Application")
xlApp.Workbooks.Add()
xlApp.Worksheets[0]
header=['Company', 'Factory', 'PO Number', 'PO Date', 'Required
Date', 'Item Number',\
'Production Date', 'Actual ShipDate', 'Shipping Method',
'Cost', 'Quote', 'Order QTY', \
'Item Cost', 'Item Quote', 'Pcs Shipped', 'Pcs UnShipped',
'UnShipped Cost', \
'UnShipped Quote']
if desc==1:
header.append('Description')
column=1
for each in header:
xlApp.ActiveSheet.Cells(1, column).Value=each
column=column+1
conn=MySQLdb.connect(host='sql_server', user='t5sll9',
passwd='5514dh6', db='app')
curs=conn.cursor()
curs.execute('call rr_shipping()')
data=curs.fetchall()
curs.close()
conn.close()
data_len=len(data)+1
if desc==0:
range="A2:R"+str(data_len)
if desc==1:
range="A2:S"+str(data_len)
xlApp.ActiveSheet.Range(range).Value=data

#problem here, if I call Columns.AutoFit or ActiveSheet.Columns.AutoFit
#the program will crush!

#xlApp.Columns.AutoFit=1
#xlApp.ActiveSheet.Columns.AutoFit=1
xlApp.Visible=1
#End Function Generate_Excel#

 

[Bart Willems]

Hi,
I tried to call "xlApp.Columns.AutoFit=1" the whole program will crash,
but without xlApp.Columns.AutoFit=1, everything just fine.

Autofit is a method. Also, columns are a method of a worksheet - try:
xlApp.Worksheets.Columns("C:K").Autofit()
(or whatever columns you need of course)

2. How do I set a rows format? I need to set row "F" to "Text", "o","p"
to general, and
"Q", "R", to currency.

Same story: you will need to define the range first.
xlApp.Worksheets.Rows("10:200").Numberformat = "General"
I think that you actually mean columns, and not rows - columns have
character designators, rows have numbers. In that case, try something
like xlApp.Activesheet.Columns("F") = "@" (text format), or the other
appropiate codes for number formatting as required. I usually pick
"#,##0.00" to display numbers with two decimals and thousands seperators.

Cheers,
Bart

 

[Bart Willems]

Autofit is a method. Also, columns are a method of a worksheet - try:
xlApp.Worksheets.Columns("C:K").Autofit()

Silly me. That is of course xlApp.Activesheet.Columns("C:K").Autofit()

On a sidenote, you can refer to a worksheet with xlApp.Worksheets(Name)
as well.

 

[Guest]

Autofit is a method. Also, columns are a method of a worksheet - try:
xlApp.Worksheets.Columns("C:K").Autofit()
(or whatever columns you need of course)


Same story: you will need to define the range first.
xlApp.Worksheets.Rows("10:200").Numberformat = "General"
I think that you actually mean columns, and not rows - columns have
character designators, rows have numbers. In that case, try something
like xlApp.Activesheet.Columns("F") = "@" (text format), or the other
appropiate codes for number formatting as required. I usually pick
"#,##0.00" to display numbers with two decimals and thousands seperators.

Cheers,
Bart

Thanks a lot!!

Ray