RE: how to operate the excel by python?

Discussion in 'Python' started by Hughes, Chad O, Jun 10, 2005.

  1. 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+'1').FormulaR1C1 = vars
    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
    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= [mailto:python-list-bounces+chad.hughes=] On Behalf Of alex23
    Sent: Friday, June 10, 2005 12:04 AM
    To:
    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
     
    Hughes, Chad O, Jun 10, 2005
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. benn
    Replies:
    2
    Views:
    601
  2. ÒÊÃÉɽÈË

    how to operate the excel by python?

    ÒÊÃÉɽÈË, Jun 10, 2005, in forum: Python
    Replies:
    6
    Views:
    10,293
    McBooCzech
    Jun 13, 2005
  3. Hughes, Chad O

    RE: how to operate the excel by python?

    Hughes, Chad O, Jun 10, 2005, in forum: Python
    Replies:
    0
    Views:
    1,379
    Hughes, Chad O
    Jun 10, 2005
  4. alexeyomux
    Replies:
    3
    Views:
    166
    gregarican
    Aug 9, 2007
  5. Replies:
    2
    Views:
    56
    Mark H Harris
    May 13, 2014
Loading...

Share This Page