python读取EXCEL

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

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

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

# -*- coding: utf-8 -*-
'''
Created on 2014/09/05

@author: puma004
'''

import xlrd

def PrnError(eromsg):
    print 'error:%s'%eromsg

def 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 samerowlst

def 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