how to operate the excel by python?

[Hughes, Chad O]

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: python-list-bounces+chad.hughes=pnl.gov at python.org [mailto:python-list-bounces+chad.hughes=pnl.gov at python.org] On Behalf Of alex23
Sent: Friday, June 10, 2005 12:04 AM
To: python-list at 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