Here are two scripts that I have had to build for a class that I teach. You
will either need to write the constants explicitly, or I can email neet the
constans module that I have built, for the second one to work. I will copy it
to the very end. If you have any questions, just let me know. Here is a simple
one that allows me to interface with my grade book spreadsheet though the
command line for taking attendance:
from win32com.client import Dispatch
import time
xl = Dispatch('Excel.Application')
wb = xl.Workbooks.Open('C:\\Documents and Settings\\My
Documents\\discrete\\GradeBook.xls')
ws = wb.Worksheets.Item(1)
ws.Activate()
i = 1
while True:
if ws.Cells(1,i).Text == '':
break
i += 1
t = time.localtime()
d = '%s/%s/%s'%(t.tm_mon,t.tm_mday,t.tm_year)
ws.Cells(1,i).FormulaR1C1 = d
ws.Cells(2,i).Select()
j = 2
while True:
name = ws.Cells(j,1).Text
if name == '':
break
name = name.split()[1]
here = int(raw_input('%s here? '%name))
ws.Cells(j,i).FormulaR1C1 = here
j += 1
wb.Save()
The next one is a kluge (spaghetti code), sorry but it is complete. It builds
a truth table for evalutating logic expressions:
from win32com.client import Dispatch
>From officecon.xlcon import *
xl = Dispatch('Excel.Application')
binary = False # set this to true to use 0 and 1 instead of False and True
vars = raw_input("Comma seperate the variables:")
headers = raw_input("What other headers do you want, comma seperated?")
headers = headers.split(',')
vars = vars.split(',')
numToAlpha = 'abcdefghijklmnopqrstuvwxyz'
if not xl.Visible:
xl.Workbooks.Add()
xl.Worksheets.Add()
for i in range(3):
sheet = xl.Worksheets.Item(2)
sheet.Delete()
xl.ActiveWindow.SelectedSheets.Delete
else:
xl.Worksheets.Add()
sheet = xl.Worksheets.Item(1)
if len(headers[-1]) > 30:
sheet.name = headers[-1][:27]+'...'
elif headers[-1] == '':
sheet.name = 'Example'
else:
sheet.name = headers[-1]
xl.Visible = True
for i in range(len(vars)):
sheet.Range(numToAlpha[i]+'1').FormulaR1C1 = vars[i]
sheet.Rows("1:1").Select()
xlBottom = -4107
xlCenter = -4108
xl.Selection.NumberFormat = "General"
xl.Selection.HorizontalAlignment = xlCenter
xl.Selection.VerticalAlignment = xlBottom
xl.Selection.Font.name = "Areial"
xl.Selection.Font.FontStyle = "Bold"
xl.Selection.Font.Size = 12
rows = []
number = 2**len(vars)
for i in range(number):
row = []
for j in range(len(vars)):
row.append((i%(2**(len(vars)-j)))/(2**(len(vars)-j-1)))
rows.append(row)
for row in range(len(rows)):
for column in range(len(rows[row])):
if not rows[row][column]:
if binary:
value = 0
else:
value = 'TRUE'
else:
if binary:
value = 1
else:
value = 'FALSE'
sheet.Range(numToAlpha[column]+`row+2`).FormulaR1C1 = value
for i in range(len(headers)):
sheet.Range(numToAlpha[i+len(vars)]+'1').FormulaR1C1 = headers[i]
sheet.Range(numToAlpha[i+len(vars)]+'1').Select()
sheet.Range("A1:"+numToAlpha[len(vars)+len(headers)-1]+`number+1`).Select()
xl.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
xl.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
xl.Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
xl.Selection.Borders(xlEdgeLeft).Weight = xlThick
xl.Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
xl.Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
xl.Selection.Borders(xlEdgeTop).Weight = xlThick
xl.Selection.Borders(xlEdgeTop).ColorIndex = xlAutomatic
xl.Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
xl.Selection.Borders(xlEdgeBottom).Weight = xlThick
xl.Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
xl.Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
xl.Selection.Borders(xlEdgeRight).Weight = xlThick
xl.Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic
xl.Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
xl.Selection.Borders(xlInsideVertical).Weight = xlThin
xl.Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic
xl.Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
xl.Selection.Borders(xlInsideHorizontal).Weight = xlThin
xl.Selection.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
xl.Range("A1:"+numToAlpha[len(vars)+len(headers)-1]+'2').Select()
xl.Selection.Borders(xlInsideHorizontal).Weight = xlThick
xl.Range(numToAlpha[len(vars)-1]+'1:'+numToAlpha[len(vars)]+`number+1`).Select()
xl.Selection.Borders(xlInsideVertical).LineStyle = xlDouble
xl.Range(numToAlpha[len(vars)+len(headers)-1]+'1:'+numToAlpha[len(vars)+len(headers)-1]+`number+1`).Select()
xl.Selection.Borders(xlEdgeLeft).LineStyle = xlDashDotDot
xl.Selection.Interior.ColorIndex = 20
xl.Selection.Interior.Pattern = xlSolid
#xl.Selection.Borders(xlEdgeLeft).Weight = xlThick
#xl.Selection.Borders(xlEdgeTop).Weight = xlThick
#xl.Selection.Borders(xlEdgeBottom).Weight = xlThick
xl.Selection.Borders(xlEdgeRight).Weight = xlThick
#xl.Selection.Borders(xlInsideVertical).Weight = xlThin
#xl.Selection.Borders(xlInsideHorizontal).Weight = xlThin
xl.Range("A1:"+numToAlpha[len(vars)+len(headers)-1]+'2').Select()
xl.Selection.Borders(xlInsideHorizontal).Weight = xlThick
xl.Range("A2:"+numToAlpha[len(vars)-1]+`number+1`).Select()
xl.Selection.Interior.ColorIndex = 35
xl.Selection.Interior.Pattern = xlSolid
xl.Selection.Interior.PatternColorIndex = xlAutomatic
#gray every other row
for i in range(number):
if i%2:
xl.Range('A'+`i+1`+':'+numToAlpha[len(vars)+len(headers)-2]+`i+1`).Select()
xl.Selection.Interior.ColorIndex = 2
xl.Selection.Interior.Pattern = xlLightUp
xl.Selection.Interior.PatternColorIndex = xlAutomatic
#gray every other row of answers only dark grey
for i in range(number):
if i%2:
xl.Range(numToAlpha[len(vars)+len(headers)-1]+`i+1`+':'+numToAlpha[len(vars)+len(headers)-1]+`i+1`).Select()
xl.Selection.Interior.ColorIndex = 20
xl.Selection.Interior.Pattern = xlLightUp
xl.Selection.Interior.PatternColorIndex = xlAutomatic
#gray every other row of vars only dark grey
for i in range(number):
if i%2:
xl.Range('A'+`i+1`+':'+numToAlpha[len(vars)-1]+`i+1`).Select()
xl.Selection.Interior.ColorIndex = 35
xl.Selection.Interior.Pattern = xlLightUp
xl.Selection.Interior.PatternColorIndex = xlAutomatic
xl.Range(numToAlpha[len(vars)]+'2').Select()
xl.Columns("A:ZZ").EntireColumn.AutoFit()
xl.ActiveWindow.Zoom = 200
xl.WindowState = xlMaximized
xl.Visible = True
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of alex23
Sent: Friday, June 10, 2005 12:04 AM
To: python-list@python.org
Subject: Re: how to operate the excel by python?
ÒÊÃÉɽÈË wrote:
> i want to compare the content in excel,but i don't know whick module
> to use! can you help me?
I noticed a package on PyPi today that might be useful to you:
http://www.python.org/pypi/xlrd/0.3a1
The homepage is a little brief, so I clipped their example from the
README:
import xlrd
book = xlrd.open_workbook("myfile.xls")
print "The number of worksheets is", book.nsheets
print "Worksheet name(s):", book.sheet_names()
sh = book.sheet_by_index(0)
print sh.name, sh.nrows, sh.ncols
print "Cell D30 is", sh.cell_value(rowx=29, colx=3)
for rx in range(sh.nrows):
print sh.row(rx)
I haven't had cause to use it myself, however.
-alex23
--
http://mail.python.org/mailman/listinfo/python-list
--
http://mail.python.org/mailman/listinfo/python-list
