python读取EXCEL

因为经常需要读取excel文件，所以把常用的几个功能写在了一起，以后可以直接调用。

但是有些函数是有缺陷的，比如：函数match_excel_bycol()和函数match_excel_bycrow()，希望能通过匹配文字来返回指定行/列的值，但是目前只能匹配文字，如果excel中有日期或者数字类型，就会有错误无法匹配。先这样吧，有时间再继续研究。

使用时，只要导入该模块即可使用这些函数。

# -*- coding: utf-8 -*-'''Created on 2014/09/05@author: puma004'''import xlrddef PrnError(eromsg):    print 'error:%s'%eromsgdef Open_excel(fname):    '''        param fname:        The path to the spreadsheet file to be opened.    Returns:        A dict include a class['book'] and a list['sheetnames']:           An instance of the Book class.           A list of the names of all the worksheets in the workbook file. This information is available even when no sheets have yet been loaded.    '''    try:        book = xlrd.open_workbook(fname)    except Exception,e:        PrnError(e)    else:        sheetnames = book.sheet_names()        bookdict={'book':book,'sheetnames':sheetnames}        return bookdict    def read_excel_byrow(book=None,shtname=None,*rowxs):    '''    Contains the data of rows requird for one worksheet.    :param book:         An object of the book required    :param shtname:         Name of sheet required    :param *rowxs:         Index of rows required.If None,then read all the rows.    Returns:         A list of the data for rows required    '''    rowlist = []    try:        Work_Sheet = book.sheet_by_name(shtname)        rowsnum = Work_Sheet.nrows        if rowxs:            rindex = rowxs        else:            rindex = tuple(xrange(rowsnum))        for r in rindex:            rowvalue = Work_Sheet.row_values(r)            rowlist.append(rowvalue)    except Exception,e:        PrnError(e)    else:        return rowlist    def read_excel_bycol(book=None,shtname=None,*colxs):    '''    Contains the data of cols requird for one worksheet.    :param book:         An object of the book required    :param shtname:         Name of sheet required    :param *cols:         Index of cols required.If None,then read all the cols.    Returns:         A list of the data for colws required    '''    collist = []    try:        Work_Sheet = book.sheet_by_name(shtname)        colsnum = Work_Sheet.ncols        if colxs:            cindex = colxs        else:            cindex = tuple(xrange(colsnum))        for c in cindex:            colvalue = Work_Sheet.col_values(c)            collist.append(colvalue)    except Exception,e:        PrnError(e)    else:        return collist    def read_excel_table(book=None,shtname=None,indexs=[],rowmode=True):    '''        :param book:        An object of the book required    :param shtname:        Name of sheet required    :param indexs:        A list for index of row/col required    :param rowmode:        index data by_row default.If rowmode=False, index date by_col.    Returns:         A list of the data for rows/cols required    '''    listset = []    try:        Work_Sheet = book.sheet_by_name(shtname)        rowsnum = Work_Sheet.nrows        colsnum = Work_Sheet.ncols        if rowmode:            indxmax = rowsnum            readline = Work_Sheet.row_values        else:            indxmax = colsnum            readline = Work_Sheet.col_values        if indexs:            index = indexs        else:            index = xrange(indxmax)        for indx in index:            value = readline(indx)            listset.append(value)    except Exception,e:        print e    else:        return listset    def read_excel_bycell(book=None,shtname=None,rowxs=0,colxs=0):    '''    Contains the data of cell requird for one worksheet.    :param book:        An object of the book required    :param shtname:        Name of sheet required    :param rowxs:       Index of row required    :param colxs:       Index of col required    Returns:         A data of cell required    '''    try:        Work_Sheet = book.sheet_by_name(shtname)        cellvalue = Work_Sheet.cell_value(rowxs,colxs)    except Exception,e:        print e    else:        return cellvalue    def match_excel_bycol(book=None,shtname=None,scolx=0,keywd='',*tcolxs):    '''        :param book:        An object of the book required    :param shtname:        Name of sheet required    :param scolx:        A index of col to refer    :param keywd:        The keyword to search    Returns:         A list of list the data in (scolx[,tcolxs])    '''    rowlist = []    try:        Work_Sheet = book.sheet_by_name(shtname)        rowsnum = Work_Sheet.nrows        for rowx in xrange(rowsnum):            colvalue = Work_Sheet.cell_value(rowx,scolx)            if keywd in colvalue:                if tcolxs:                    tcollst = list(tcolxs)                    rowvalue = getdate_samerow(Work_Sheet,rowx,tcollst)                else:                    rowvalue = Work_Sheet.row_values(rowx)                rowlist.append(rowvalue)    except Exception,e:        PrnError(e)    else:        return rowlist    def match_excel_byrow(book=None,shtname=None,srowx=0,keywd='',*trowxs):    '''        :param book:        An object of the book required    :param shtname:        Name of sheet required    :param scolx:        A index of row to refer    :param keywd:        The keyword to search    Returns:         A list of list the data in (srowx[,trowxs])    '''    collist = []    try:        Work_Sheet = book.sheet_by_name(shtname)        colsnum = Work_Sheet.ncols        for colx in xrange(colsnum):            colvalue = Work_Sheet.cell_value(srowx,colx)            if keywd in colvalue:                if trowxs:                    trowlst = list(trowxs)                    colvalue = getdate_samecol(Work_Sheet,colx,trowlst)                else:                    colvalue = Work_Sheet.col_values(colx)                collist.append(colvalue)    except Exception,e:        PrnError(e)    else:        return collist    def getdate_samerow(sht,row,tcolxlst):    '''        :param sht:         An object of the Sheet class    :param row:         A index of row to refer    :param tcolxlst:        A list of index for cols to search    Returns:         A list of data in row    '''    samerowlst = []    for tcol in tcolxlst:                try:            rowvalue = sht.cell_value(row,tcol)        except  Exception,e:            PrnError(e)        else:            samerowlst.append(rowvalue)    return samerowlstdef getdate_samecol(sht,colx,trowxlst):    '''        :param sht:         An object of the Sheet class    :param colx:         A index of col to refer    :param trowxlst:        A list of index for rows to search    Returns:         A list of data in colx    '''    samecollst = []    for trow in trowxlst:        try:            colvalue = sht.cell_value(trow,colx)        except  Exception,e:            PrnError(e)        else:            samecollst.append(colvalue)    return samecollst