reading column from excel and doing specific math
Question: I have an excel file that has 27 columns. I want to write a python code which
reads column by column and stores the final 5 values in the coloum which will
have math equations done on them.

I have this so far:

    
    
    from math import tan
    
    #Write Header
    #outFile.write('Test Name,X+ avg,X+ std,X+ count,X- avg,X- std,X- count,X angle,Y+ avg,Y+ std,Y+ count,Y- avg,Y- std,Y- count,Y angle\n')
    
    #for line in inFile:
    
    
    if 1==1:
    
        line = [1,2,38.702,37.867,35.821, 44, 49,55,65,20,25,28,89.]
    
        line0= len(line)
        print "the list size"
        print line0
    
        line1 = len(line) -5  #takes the overall line and subtracts 5. 
        print "the is the start of the 5 #'s we need"
        print line1 #prints the line
    
        d= line[line1:line0]       #pops all the values from in the line (..)
        print "these are the 5 #'s we need"
        print d
    
        lo=min(d)
        hi=max(d)
    
    
        print "the lowest value is"
        print lo
    
    
        print "the highest value is"
        print hi
    
        average1 = float (sum(d))/ len(d)   #sum
        print "the average of the values is"
        print average1
    

the "line = [1,2,38.702,37.867,35.821, 44, 49,55,65,20,25,28,89.]" part I want
python to automatically read in the coloumn, store the 5 last vales and do the
above math analysis.


Answer: You'll probably want to use a library like
[openpyxl](http://packages.python.org/openpyxl/), here's an example from the
docs on how to access certain cells.

    
    
    columnOffset = 2 # column offset is (the letter as a number - 1), a = (1-1), b = (2-1)
    rowOffset = 35 # row offset is row number - 1
    
    wb = load_workbook('test.xlsx')
    ws = wb.get_active_sheet()
    for column in ws.columns[columnOffset:27]: #because you have 27 columns to parse through
        for rowIndex, cell in enumerate(column[-5:]):
            if rowIndex >= rowOffset:
                print cell.value
    

Unfortunately openpyxl doesn't have really good support for columns so you'll
have to use indexes to get your columns.

**Based on xlrd**

    
    
    import xlrd
    
    wb = xlrd.open_workbook("C:\Users\Asus\Desktop\herp.xls")
    sh = wb.sheet_by_name(u'A Snazzy Title')
    
    for index in xrange(2, 4):
        lastFive = sh.col_values(index, start_rowx=59, end_rowx=64)
        print lastFive
    

It looks like xlrd treats the entire spreadsheet like a 2D list, so remember
to start your indexes at 0. Yes xlrd is based entirely in python.

_credit to[alexis](http://stackoverflow.com/users/699305/alexis) for pointing
out this module._

