python Help with calculating the average and std on excel files

im trying to calculate the average and std for x, y, and z column for about 50 excel files that i saved in a folder. each excel files has x values in the first column, y in the second, and z in the third column. im using this script, but it kept giving me error. All the files are saved as ".xls". please help and if you guys know any other way i can do this that would be very helpful. Here is the Script and the error:

    import xlrd
    import numpy
    import os

    path = "E:\\hello\\Patient"
    dirList=os.listdir(path)
    f = open('E:\\hello\\try.xls', 'w')
    f.write('Patient_ID, Xavg, xstd, yavg, ystd, zavg, ystd')
    f.write("\n")

    ##print dirList
    ##i = 0
    Col_values=[]
    for file in dirList:
        fullpath = os.path.join(path,file)
    ##    print fullpath
        if os.path.isfile(fullpath) == 1:
            wb = xlrd.open_workbook(fullpath)
            sh = wb.sheet_by_index(0)
            f.write(str(file))
            f.write(", ")
            for i in range(0,3):
                for j in range(sh.nr开发者_高级运维ows):
                    Col_values.append(sh.cell(j,i).value)
                a = numpy.average(Col_values)
                b = numpy.std(Col_values)
                f.write(str(a))
                f.write(", ")
                f.write(str(b))
                f.write(", ")
            f.write("\n")

    f.close()

Do it step by step to verify that you are able to read something from the xls-files. Use the hardcoded absolute path to the file. BTW, code looks ok (as far as I can tell) Error is probably in the xls-file...

Something like:

import xlrd
wb = xlrd.open_workbook('myworkbook.xls')

#Get the first sheet either by index or by name

sh = wb.sheet_by_index(0)
sh = wb.sheet_by_name(u'Sheet1')

#Index individual cells:

cell_A1 = sh.cell(0,0).value

Besides reading Excel files, your script is trying to write one as well. It's not going to work. You need to use the xlwt package to write .xls files. Your approach to writing right now looks more like CSV. If all your so-called Excel files are really CSV files (despite the name ending in .xls), then xlrd is not going to read them properly.

If you really want to work with Excel files, you should use both xlrd (to read) and xlwt (to write). If you can get by working with CSV files, you shouldn't be using xlrd at all. You should be using Python's included csv module instead (for both reading and writing .csv files).