Python xlrd 模块，open_workbook() 实例源码

def read_workbook(logger, input_filename):
    """
    Read the contents of input_filename and return
    :param logger:         The logger
    :param input_filename: Filepath of the spreadsheet to read
    :return:  Dict of response sets
    """
    wb_response_sets = {}
    if os.path.isfile(input_filename):
        wb = open_workbook(input_filename)
        for sheet in wb.sheets():
            name = sheet.name
            wb_response_sets[name] = []

            number_of_rows = sheet.nrows
            for row in range(1, number_of_rows):
                if sheet.cell(row, 0).value != "":
                    label_object = {
                        'label': sheet.cell(row, 0).value,
                    }
                    wb_response_sets[name].append(label_object)
        return wb_response_sets
    else:
        logger.error('{0} does not appear to be a valid file'.format(input_filename))

def _update_excel(self,parent_path):
        '''??????????'''
        rb=xlrd.open_workbook(parent_path+"/test.xls",formatting_info=True)
        wb =copy(rb)
        summary = wb.get_sheet(11)#???
        basemessage=wb.get_sheet(1)#????
        protection=xlwt.Protection()
        protection.cell_locked=0
        cellStyle1=xlwt.easyxf("protection:cell_locked false;")
        cellStyle2=xlwt.XFStyle()

        librarylogger.info(u"?????????????")
        try:
            summary.write(2, 2, u'?????????'+self._rnumber+u'?')
            librarylogger.info(u"?????????????")
            basemessage.write(7,1,self._rnumber,cellStyle1)
            librarylogger.info(u"???????????????")
        except Exception, e:
            print traceback.format_exc()
            librarylogger.info(u"?????????????")

        wb.save(parent_path+"/test.xls")

def _get_cis(xls_filename, sheet_index=1):
    '''Return dictionary of cis title's and their corresponding cis tag'''
    tag_col = 1
    title_col = 2
    score_col = 4

    workbook = xlrd.open_workbook(xls_filename)
    worksheet = workbook.sheet_by_index(sheet_index)

    ret = {}

    for row_num in range(1,worksheet.nrows):
        scoring_status = worksheet.cell(row_num, score_col).value
        if scoring_status != 'scored':
            continue

        title = str(worksheet.cell(row_num, title_col).value).lower()
        rec_num = worksheet.cell(row_num, tag_col).value
        if isinstance(rec_num, float):
            rec_num = str(rec_num) + '0'
        rec_num = 'CIS-' + str(rec_num)
        ret[title] = rec_num
    return ret

def __init__(self):
        self.workbook = xlrd.open_workbook(ANSWER_KEY_PATH)

        self.sheets = {}
        self.sheets['Sim Period'] = self.workbook.sheet_by_name('Sim Period')
        self.sheets['Sim Cumulative'] = self.workbook.sheet_by_name(
            'Sim Cumulative')
        self.sheets['s_p'] = self.workbook.sheet_by_name('s_p')

        for name, index in self.INDEXES.items():
            if isinstance(index, dict):
                subvalues = {}
                for subkey, subindex in index.items():
                    subvalues[subkey] = self.get_values(subindex)
                setattr(self, name, subvalues)
            else:
                setattr(self, name, self.get_values(index))

def xlrd_xls2array(infilename):
    """ Returns a list of sheets; each sheet is a dict containing
    * sheet_name: unicode string naming that sheet
    * sheet_data: 2-D table holding the converted cells of that sheet
    """    
    book       = xlrd.open_workbook(infilename)
    sheets     = []
    formatter  = lambda(t,v): format_excelval(book,t,v,False)

    for sheet_name in book.sheet_names():
        raw_sheet = book.sheet_by_name(sheet_name)
        data      = []
        for row in range(raw_sheet.nrows):
            (types, values) = (raw_sheet.row_types(row), raw_sheet.row_values(row))
            data.append(map(formatter, zip(types, values)))
        sheets.append({ 'sheet_name': sheet_name, 'sheet_data': data })
    return sheets

def load_hud_crosswalk(fn):
    book = xlrd.open_workbook(fn)
    sheet = book.sheet_by_index(0)

    zccd = []
    for row_i in xrange(1, sheet.nrows):
        z = sheet.cell(row_i, 0).value
        stcd = sheet.cell(row_i, 1).value # formatted like STCD (FIPS, CD)
        st = stcd[:2]
        cd = stcd[2:]
        try:
            zccd.append({
                'zip': z,
                'state_fips': st,
                'state_abbr': FIPS_TO_STATE[st],
                'cd': str(int(cd)) # string conversion to drop leading zero
            })
        except Exception,e:
            msg = 'unable to convert CD for %s: %s' % (z, stcd)
            log.error(msg)
            continue
    return zccd

def data(self):
        if not self._data:
            workbook = open_workbook(self.excel)
            if type(self.sheet) not in [int, str]:
                raise SheetTypeError('Please pass in <type int> or <type str>, not {0}'.format(type(self.sheet)))
            elif type(self.sheet) == int:
                s = workbook.sheet_by_index(self.sheet)
            else:
                s = workbook.sheet_by_name(self.sheet)

            if self.title_line:
                title = s.row_values(0)  # ???title
                for col in range(1, s.nrows):
                    # ?????????????dict???self._data?
                    self._data.append(dict(zip(title, s.row_values(col))))
            else:
                for col in range(0, s.nrows):
                    # ????????self._data?
                    self._data.append(s.row_values(col))
        return self._data

def read_excel(filepath):
    """Get excel source

    Args:
        filepath: The full path of excel file. excel???????

    Returns:
        data: Data of excel. excel???
    """
    is_valid = False
    try:
        if os.path.isfile(filepath):
            filename = os.path.basename(filepath)
            if filename.split('.')[1] == 'xls':
                is_valid = True
        data = None
        if is_valid:
            data = xlrd.open_workbook(filepath)
    except Exception as xls_error:
        raise TypeError("Can't get data from excel!") from xls_error
    return data

def load_information(self,widget,event):
        book=xlrd.open_workbook("information.xls")
        sh=book.sheet_by_index(0)
        servers = sh.col_values(2)
        target = sh.col_values(12)

        num = 0
        print servers
        print target
        print self.serverInfo
        for server in servers:
            if self.serverInfo.has_key(server):
                self.serverInfo[server]["information"] = target[num]
            num = num + 1;

        for server in self.serverInfo:
            self.serverInfo[server]["vTerminal"].feed_child(self.serverInfo[server]["information"])

def main():
    weekday_sheets_file = xlrd.open_workbook(WEEKDAY_FILE_NAME).sheets()
    weekend_sheets_file = xlrd.open_workbook(WEEKEND_FILE_NAME).sheets()

    raw_weekday_sheets = extract_valid_sheets(weekday_sheets_file)
    raw_weekend_sheets = extract_valid_sheets(weekend_sheets_file)

    shape_file = open('../raw/trip_shape.csv', 'r').readlines()
    shapes = [s[:-1].split(',') for s in shape_file]

    weekday_trips = create_trips(raw_weekday_sheets, 'weekday', shapes)
    weekend_trips = create_trips(raw_weekend_sheets, 'weekend', shapes)

    print(HEADER)

    for t in weekday_trips:
        print(t)

    for t in weekend_trips:
        print(t)

def main():
    weekday_sheets_file = xlrd.open_workbook(WEEKDAY_FILE_NAME).sheets()
    weekend_sheets_file = xlrd.open_workbook(WEEKEND_FILE_NAME).sheets()

    raw_weekday_sheets = extract_valid_sheets(weekday_sheets_file)
    raw_weekend_sheets = extract_valid_sheets(weekend_sheets_file)

    weekday_sheet_names = extract_sheet_names(raw_weekday_sheets)
    weekend_sheet_names = extract_sheet_names(raw_weekend_sheets)

    names = weekday_sheet_names + weekend_sheet_names
    without_duplicates = sorted(set(names))
    routes = create_routes(without_duplicates)

    print(HEADER)

    for r in routes:
        print(r)

def main():
    weekday_workbook = xlrd.open_workbook(WEEKDAY_FILE_NAME)
    weekend_workbook = xlrd.open_workbook(WEEKEND_FILE_NAME)

    weekday_sheets_file = weekday_workbook.sheets()
    weekend_sheets_file = weekend_workbook.sheets()

    weekday_stop_times = create_stop_times(weekday_sheets_file, weekday_workbook)
    weekend_stop_times = create_stop_times(weekend_sheets_file, weekend_workbook)

    print(HEADER)

    for s in weekday_stop_times:
        print(s)

    for s in weekend_stop_times:
        print(s)

def main():
    weekday_sheets_file = xlrd.open_workbook(WEEKDAY_FILE_NAME).sheets()
    weekend_sheets_file = xlrd.open_workbook(WEEKEND_FILE_NAME).sheets()

    raw_weekday_sheets = extract_valid_sheets(weekday_sheets_file)
    raw_weekend_sheets = extract_valid_sheets(weekend_sheets_file)

    weekday_sheet = extract_names_and_stops(raw_weekday_sheets)
    weekend_sheet = extract_names_and_stops(raw_weekend_sheets)

    names = weekday_sheet + weekend_sheet
    routes = sorted(set(create_route_jps(names)))

    print(HEADER)

    for r in routes:
        print(r)

def handle(self, *args, **options):
        verbosity = options.get("verbosity", NORMAL)
        file_path = options["file_path"][0]

        wb = xlrd.open_workbook(file_path)
        sh = wb.sheet_by_index(0)

        if verbosity >= NORMAL:
            self.stdout.write("=== Movies imported ===")
        for rownum in range(sh.nrows):
            if rownum == 0:
                # let's skip the column captions
                continue
            (title, url, release_year) = sh.row_values(rownum)
            movie, created = Movie.objects.get_or_create(
                title=title,
                url=url,
                release_year=release_year,
            )
            if verbosity >= NORMAL:
                self.stdout.write("{}. {}".format(
                    rownum, movie.title
                ))

def read_xl(self, row_number=0, column_number=0,
                sheetname=None, sheetnum=0):
        workbook = xlrd.open_workbook(self.path)

        if sheetname:
            sheet = workbook.sheet_by_name(sheetname)
        else:
            sheet = workbook.sheet_by_index(sheetnum)

        for i, row in enumerate(sheet.get_rows()):
            if i >= row_number:
                data = row[column_number:column_number + self.cols_to_display]
                data = [point.value for point in data]
                self.entry_grid.add_row(data=data)

            if i >= (self.rows_to_display + row_number):
                break

def GetExcelContent(name):
    #return {sheetindex: (sheetname, sheet UsedRange value) dict
    ctx = {}
    book = xlrd.open_workbook(name)
    if book == None:
        raise "Open Excel(%s) failed!" % name
    for i in range(book.nsheets):
        s = book.sheet_by_index(i)
        sname = s.name
        svalue = list()
        for r in range(s.nrows):
            svalue.append( s.row_values(r) )
        ctx[i] = (sname, svalue)
    return ctx

#Read Excel file content

def GetExcelContent(self, name):
        """ ?? Excel ???? """
        #return {sheetindex: (sheetname, sheet UsedRange value) dict
        ctx = {}
        #if type(name) != unicode:
        #raise TypeError("name must be unicode")
        try:
            book = xlrd.open_workbook(name)
            #print "open excel, ", name.encode('gbk')
        except:
            raise ValueError("Open name[%s] failed." % name.encode('utf-8') )
        if book == None:
            raise "Open Excel(%s) failed!" % name.encode('utf-8')
        for i in range(book.nsheets):
            s = book.sheet_by_index(i)
            sname = s.name
            svalue = list()
            for r in range(s.nrows):
                svalue.append( s.row_values(r) )
            ctx[i] = (sname, svalue)
        return ctx

    #Read Excel file content

def xls_as_xlsx(xls_file):
    # first open using xlrd
    source_workbook = xlrd.open_workbook(file_contents=xls_file.read())

    # Create the destination workbook, deleting and auto-generated worksheets.
    destination_workbook = openpyxl.Workbook() # TODO: Would like to figure out how to make appends work with a "write_only" workbook.
    for wksht_nm in destination_workbook.get_sheet_names():
        worksheet= destination_workbook.get_sheet_by_name(wksht_nm)
        destination_workbook.remove_sheet(worksheet)

    worksheet_names= ['survey', 'choices']
    for wksht_nm in source_workbook.sheet_names():
        source_worksheet= source_workbook.sheet_by_name(wksht_nm)
        destination_worksheet= destination_workbook.create_sheet(title=wksht_nm)

        for row in xrange(source_worksheet.nrows):
            destination_worksheet.append( [source_worksheet.cell_value(row, col) for col in xrange(source_worksheet.ncols)] )

    return io.BytesIO(save_virtual_workbook(destination_workbook))

def ingest(self, file_path):
        self.extract_ole_metadata(file_path)
        try:
            book = xlrd.open_workbook(file_path, formatting_info=False)
        except Exception as err:
            raise ProcessingException('Invalid Excel file: %s' % err)

        self.result.flag(self.result.FLAG_WORKBOOK)
        try:
            for sheet in book.sheets():
                rows = self.generate_csv(sheet)
                self.csv_child_iter(rows, sheet.name)
        except XLRDError as err:
            raise ProcessingException('Invalid Excel file: %s' % err)
        finally:
            book.release_resources()

def excel_to_csv(path):
    wb = xlrd.open_workbook(path)
    sh = wb.sheet_by_index(0)
    csv_path = convert_path_to_csv(path)
    csv_file = open(csv_path, 'wb')
    wr = csv.writer(csv_file)
    date_tuple = xlrd.xldate_as_tuple(sh.row_values(0)[-1], wb.datemode)
    date = datetime(*date_tuple).strftime('%m/%d/%Y')
    date_fields = [date for i in range(sh.nrows-1)]
    date_fields = ['Date'] + date_fields
    for rownum in xrange(sh.nrows):
        if rownum == 0:
            wr.writerow([date_fields[rownum]] + sh.row_values(rownum)[:-1] + ['Value'])
        else:
            wr.writerow([date_fields[rownum]] + sh.row_values(rownum))
    csv_file.close()

def datacel():
    try:
        filepath='.\\test_case\\case.xlsx'
        file=xlrd.open_workbook(filepath)
        me=file.sheets()[0]
        nrows=me.nrows
        listid=[]
        listkey=[]
        listconeent=[]
        listurl=[]
        listfangshi=[]
        listqiwang=[]
        listrelut=[]
        listname=[]
        for i in range(1,nrows):
            listid.append(me.cell(i,0).value)
            listkey.append(me.cell(i,2).value)
            listconeent.append(me.cell(i,3).value)
            listurl.append(me.cell(i,4).value)
            listname.append(me.cell(i,1).value)
            listfangshi.append((me.cell(i,5).value))
            listqiwang.append((me.cell(i,6).value))
        return listid,listkey,listconeent,listurl,listfangshi,listqiwang,listname
    except:LOG.info('????????????:%s'%Exception)

def saveToExcel(content):
    """
    save the content from createBsObj(content) into excel.
    :param content:
    :return:
    """
    wbk = xlwt.Workbook(encoding='utf-8', style_compression=0)
    sheet = wbk.add_sheet("sheet1",cell_overwrite_ok=True)
    wbk.save('data.xls')

    xlsfile=r'./data.xls'
    book = xlrd.open_workbook(xlsfile)
    sheet_name = book.sheet_names()[0]
    sheet1 = book.sheet_by_name(sheet_name)

    nrows = sheet1.nrows
    ncols = sheet1.ncols
    # sheet.write(nrows+i,ncols,bookTitle)
    wbk.save('data.xls')


# main call function

def import_xlsx(file_loc):
  #file_loc = '/Users/harry/Dropbox (OpenDesk)/06_Production/06_Software/CADLine Plugin/excel files/LAYERCOLOURS - new.xlsx'
  wb = open_workbook(file_loc)

  sheet = wb.sheet_by_index(0)

  #row = sheet.row(4)

  sheetdict = {}
  for colnum in range(1, sheet.ncols):
    col_values_list = []
    for rownum in range(1, sheet.nrows):  
      #TO DO loop through each row and append in to a []
      col_values_list.append(eval(sheet.cell_value(rownum, colnum)))

    #print(col_values_list)
    sheetdict[sheet.cell_value(0, colnum)] = col_values_list
  #print(sheetdict.keys())
  #print(sheetdict)

  return sheetdict

def Read_Good_Name(self,xpath,col_index=None,sheet_index=None):
        sheet_index = int(sheet_index)
        col_index = int(col_index)
        #??xls???????????????
        rb = xlrd.open_workbook(xpath,formatting_info=True)
        #????sheet?
        r_sheet = rb.sheet_by_index(sheet_index)
        #?????
        table_row_nums = r_sheet.nrows
        list = []
        #??????
        for i in range(1,table_row_nums):
            #??????
            cvalue = r_sheet.cell(i,col_index).value
            if type(cvalue).__name__ == 'unicode':
                cvalue = cvalue.encode('utf-8')
            elif type(cvalue).__name__ == 'float':
                cvalue = str(int(cvalue))
            #???list?
            list.append(cvalue)
        return list

def read_ff_parameters(excel_file_path=ff_par, ff_selection='uff'):
    """
    Read force field parameters from an excel file according to force field selection
    """
    # Read Excel File
    force_field_data = xlrd.open_workbook(excel_file_path)
    # Read columns to acquire force field parameters
    atom_names = force_field_data.sheets()[0].col_values(0)[2:]
    uff_sigma = force_field_data.sheets()[0].col_values(1)[2:]
    uff_epsilon = force_field_data.sheets()[0].col_values(2)[2:]
    dre_sigma = force_field_data.sheets()[0].col_values(3)[2:]
    dre_epsilon = force_field_data.sheets()[0].col_values(4)[2:]

    uff = {'atom': atom_names, 'sigma': uff_sigma, 'epsilon': uff_epsilon}
    dre = {'atom': atom_names, 'sigma': dre_sigma, 'epsilon': dre_epsilon}

    if ff_selection == 'uff':
        return uff
    if ff_selection == 'dre':
        return dre
    else:
        print('No such force field')

def makeMasterDict(start, stop):
    print("*****************************")
    print("Assembling master dictionary")
    print("*****************************")
    # Set up dictionary CSV
    with open('data/dictionary.csv', 'w') as f:
        c = csv.writer(f)
        c.writerow(['year', 'dictname', 'dictfile', 'varnumber', 'varname', 'datatype' ,'fieldwidth', 'format', 'imputationvar', 'vartitle'])
        f.close()

        # For each Excel dictionary, take the contents and file name and add to master dictionary csv
    for i in range(start,stop):
        for file in os.listdir('dict/' + str(i) + '/'):
            if file.endswith((".xls", ".xlsx")):
                print("Adding " + str(i) + " " + file + " to dictionary")
                dictname = file.split(".", 1)[0]
                rowstart = [i, dictname, file]
                workbook = xlrd.open_workbook('dict/' + str(i) +'/' + file, on_demand = True)
                worksheet = workbook.sheet_by_name('varlist')
                with open('data/dictionary.csv', 'a') as f:
                    c = csv.writer(f)
                    for r in range(2,worksheet.nrows):
                        varrow = worksheet.row_values(r)
                        row = rowstart + varrow
                        c.writerow(row)

def validate_stop_test_run(self):
        """Make a binary comparison.

        Raises:
            AssertionError. the result file differs from the expected file.
        """
        expected_sheet = \
            xlrd.open_workbook(self.EXPECTED_FILE_PATH).sheet_by_index(0)
        actual_sheet = \
            xlrd.open_workbook(self.handler.output_file_path).sheet_by_index(0)

        self.assertEqual(actual_sheet.nrows, expected_sheet.nrows)
        self.assertEqual(actual_sheet.ncols, expected_sheet.ncols)

        for row, col in itertools.product(range(actual_sheet.nrows,
                                                actual_sheet.ncols)):
            actual_cell = actual_sheet.cell(row, col)
            expected_cell = expected_sheet.cell(row, col)
            self.assertEqual(actual_cell, expected_cell)

def extract_excel(filename):
    ret_string = ''
    book = xlrd.open_workbook(filename)
    sheets = book.sheet_names()

    for sheet in sheets:
        worksheet = book.sheet_by_name(sheet)
        num_rows = worksheet.nrows - 1
        num_cells = worksheet.ncols - 1
        curr_row = -1
        while curr_row < num_rows:
            curr_row += 1
            row = worksheet.row(curr_row)
            curr_cell = -1
            while curr_cell < num_cells:
                curr_cell += 1
                # Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank
                cell_type = worksheet.cell_type(curr_row, curr_cell)
                cell_value = worksheet.cell_value(curr_row, curr_cell)
                ret_string += cell_value+'\n'
    return ret_string   

# Given a big long string of text, split it by newline and try matching stuff in it.

def read_excel(self, path):
        '''
        ???????????
        :param path: 
        :return: dict content
        '''
        excel = xlrd.open_workbook(path)
        # ???
        table_server = excel.sheet_by_name(u'server')
        table_network = excel.sheet_by_name(u'network')
        data = {}
        # ????
        data.update(self.read_data(table_server))
        data.update(self.read_data(table_network))
        os.remove(path)  # ??????
        if not data:
            return False

        return data

def test_merged_cells_xlsx(self):
        book = xlrd.open_workbook(from_this_dir('merged_cells.xlsx'))

        sheet1 = book.sheet_by_name('Sheet1')
        expected = []
        got = sheet1.merged_cells
        self.assertEqual(expected, got)

        sheet2 = book.sheet_by_name('Sheet2')
        expected = [(0, 1, 0, 2)]
        got = sheet2.merged_cells
        self.assertEqual(expected, got)

        sheet3 = book.sheet_by_name('Sheet3')
        expected = [(0, 1, 0, 2), (0, 1, 2, 4), (1, 4, 0, 2), (1, 9, 2, 4)]
        got = sheet3.merged_cells
        self.assertEqual(expected, got)

        sheet4 = book.sheet_by_name('Sheet4')
        expected = [(0, 1, 0, 2), (2, 20, 0, 1), (1, 6, 2, 5)]
        got = sheet4.merged_cells
        self.assertEqual(expected, got)

def test_merged_cells_xlsx(self):
        book = xlrd.open_workbook(from_this_dir('merged_cells.xlsx'))

        sheet1 = book.sheet_by_name('Sheet1')
        expected = []
        got = sheet1.merged_cells
        self.assertEqual(expected, got)

        sheet2 = book.sheet_by_name('Sheet2')
        expected = [(0, 1, 0, 2)]
        got = sheet2.merged_cells
        self.assertEqual(expected, got)

        sheet3 = book.sheet_by_name('Sheet3')
        expected = [(0, 1, 0, 2), (0, 1, 2, 4), (1, 4, 0, 2), (1, 9, 2, 4)]
        got = sheet3.merged_cells
        self.assertEqual(expected, got)

        sheet4 = book.sheet_by_name('Sheet4')
        expected = [(0, 1, 0, 2), (2, 20, 0, 1), (1, 6, 2, 5)]
        got = sheet4.merged_cells
        self.assertEqual(expected, got)

def parse_dof_file(file_path):
    """ Parse dof rolling sales xls file"""
    book = xlrd.open_workbook(file_path)
    sheet = book.sheet_by_index(0)
    rows = sheet.get_rows()

    # remove first 4 row
    [next(rows) for x in range(4)]
    # 5th row is the headers
    headers = to_headers(next(rows))

    for row in rows:
        _row = list(map(cell_converter, row))

        if len(list(filter(item_exists, _row))):
            yield dict(zip(headers, _row))

def setup(startDatabase, endDatabase, maxElement):
    global generation
    global genSize
    global data
    global database
    database = []
    generation = []
    Individual.bestChromosome = None
    Individual.bestFitness = 0
    for i in range (0, maxElement * maxElement):
        database.append([])
    #open the database file
    data = xlrd.open_workbook("Complex Final database.xlsx").sheet_by_index(0)
    createDatabase (startDatabase, endDatabase, maxElement)
    #DEBUG ONLY
    temp(maxElement)
    #print(database)
    #generation 0: all random lists of chromosomes
    for i in range (0, genSize):
        generation.append(Individual(randomChrom(maxElement), maxElement))
    #print(database)

def extract(self, filename, **kwargs):
        workbook = xlrd.open_workbook(filename)
        sheets_name = workbook.sheet_names()
        output = "\n"
        for names in sheets_name:
            worksheet = workbook.sheet_by_name(names)
            num_rows = worksheet.nrows
            num_cells = worksheet.ncols

            for curr_row in range(num_rows):
                row = worksheet.row(curr_row)
                new_output = []
                for index_col in xrange(num_cells):
                    value = worksheet.cell_value(curr_row, index_col)
                    if value:
                        if isinstance(value, (int, float)):
                            value = unicode(value)
                        new_output.append(value)
                if new_output:
                    output += u' '.join(new_output) + u'\n'
        return output

def convert(self, xls_filename):
        xls_filename = _unicode_anyway(xls_filename);
        try:
            self._workbook = xlrd.open_workbook(xls_filename);
            self._xls_filetime = os.path.getmtime(xls_filename);
            self._xls_filename = xls_filename;
        except:
            raise Exception("Failed to load workbook: %s" % xls_filename);

        self._sheet_names = self._workbook.sheet_names();
        self._meta_tables = list();

        if self._meta in self._sheet_names:
            self._load_meta_sheet();
        else:
            self._load_meta_header();

        for sheet_desc in self._meta_tables:
            self._convert_sheet(sheet_desc);
            self._tables.append(sheet_desc.table_name);

def get_workbooks(self):
        """
        If the data to be processed is not stored in files or if
        special parameters need to be passed to xlrd.open_workbook
        then this method must be overriden.
        Any implementation must return an iterable sequence of tuples.
        The first element of which must be an xlrd.Book object and the
        second must be the filename of the file from which the book
        object came.
        """
        for path in self.get_filepaths():
            yield (
                xlrd.open_workbook(
                    path,
                    pickleable=0,
                    formatting_info=1,
                    on_demand=True),
                os.path.split(path)[1]
                )

def excel():
    fname = "..\\Case\\test.xlsx"
    bk = xlrd.open_workbook(fname)
    try:
        sh = bk.sheet_by_name('Sheet1')
    except:
        print "%?????Sheet1?" % fname
    # ????
    nrows = sh.nrows
    # ????
    ncols = sh.ncols
    print '?????%s  ?????%s' % (nrows, ncols)
    a = []
    for i in range(0, nrows):
        c = sh.row_values(i)
        # if i == 0:
        #     continue
        # else:
        #     c[0] = int(c[0])
        #     c[6] = int(c[6])
        a.append(c)
    return a
#     print a

def readexcel(path):
    workbook = xlrd.open_workbook(path)
    sheets =  workbook.sheet_names()
    # ??????
    for sname in sheets:
        print(sname)
    #?????sheet
    worksheet = workbook.sheet_by_name(sheets[0])
    #??sheet???????
    print(worksheet.nrows)
    print(worksheet.ncols)
    #?????cell?
    print(worksheet.cell(0,1).value)
    #??????
    print(worksheet.row(0)[0].value)
    #??cell???
    print(worksheet.row(0)[0].ctype)
    #???????
    for cell in worksheet.row(0):
        print(cell.value)
    #???????
    for cell in worksheet.col(0):
        if cell.value is not None and cell.value !='':
            print(cell.value)

def read_sheets_from_xls(file_path):
    workbook = xlrd.open_workbook(file_path)
    sheets = []
    for sheet in workbook.sheets():
        if sheet.ncols <= 0:
            continue
        cells = []
        for y in range(0, sheet.nrows):
            # ??????
            all_empty = True
            for v in sheet.row_values(y):
                if v != '':
                    all_empty = False
                    break
            if all_empty:
                continue
            text = sheet.cell_value(y, 0)
            # ?????
            if isinstance(text, unicode) and text.startswith('//'):
                continue
            cells.append(sheet.row(y))
        if len(cells) > 0:
            sheets.append((sheet.name, cells))
    return sheets

def readExcel(self, filename):
        self.positions = []
        wb = open_workbook(filename)
        for sheet in wb.sheets():
            number_of_rows = sheet.nrows
            number_of_columns = sheet.ncols
            rows = []
            for row in range(0, number_of_rows):
                values = []
                for col in range(number_of_columns):
                    value  = sheet.cell(row, col).value
                    try:
                        value.replace(' ', '')
                    except ValueError:
                        pass
                    finally:
                        values.append(value)
                position = Position(*values)
                self.positions.append(position)

    #-----------------------------------------------------------------
    # Read US stocks from text file (as saved in Outlook) -- OBSOLETE
    #-----------------------------------------------------------------

def readExcel(self, filename):
        self.positions = []
        wb = open_workbook(filename)
        for sheet in wb.sheets():
            number_of_rows = sheet.nrows
            number_of_columns = sheet.ncols
            rows = []
            for row in range(0, number_of_rows):
                values = []
                for col in range(number_of_columns):
                    value  = sheet.cell(row, col).value
                    try:
                        value.replace(' ', '')
                    except ValueError:
                        pass
                    finally:
                        values.append(value)
                position = Position(*values)
                self.positions.append(position)

    #-----------------------------------------------------------------
    # Read US stocks from text file (as saved in Outlook) -- OBSOLETE
    #-----------------------------------------------------------------

def importData(fileName):
  """
    Main entry point - opens import data Excel sheet (assumed to be first 
    sheet) and processes each record, one at a time.
  """
  global importSheet, importRow, headers
  book = xlrd.open_workbook(fileName)
  importSheet = book.sheet_by_index(0)

  print "importing from %s" % importSheet.name

  # get cell value types from first-non header data row (2nd), since headers are all of type text  
  for colnum in range(0, importSheet.ncols):
    headers.append((importSheet.cell(0, colnum).value, importSheet.cell(1, colnum).ctype))

  configErrorReporting(headers)

  for importRow in range(1, importSheet.nrows):
    record = {}
    for colnum in range(0, importSheet.ncols):
      if headers[colnum][1] == xlrd.XL_CELL_DATE:
        dateTuple = xlrd.xldate_as_tuple(importSheet.cell(rowx=importRow, colx=colnum).value, book.datemode)
        date = datetime.date(dateTuple[0], dateTuple[1], dateTuple[2])
        # required format for xsd:Date type, for web service call
        record[headers[colnum][0]] = date.isoformat()
      else:
        value = importSheet.cell_value(rowx=importRow, colx=colnum)
        if isinstance(value, basestring):
          record[headers[colnum][0]] = value.strip()
        else:
          record[headers[colnum][0]] = value
      #print "%s: %s" % (type(record[headers[colnum]]), record[headers[colnum]])

    record = handleAccountContact(record)

  book.unload_sheet(importSheet.name)

def excel_to_db(excel_file):
    """
    Asset add batch function
    """
    try:
        data = xlrd.open_workbook(filename=None, file_contents=excel_file.read())
    except Exception, e:
        return False
    else:
        table = data.sheets()[0]
        rows = table.nrows
        for row_num in range(1, rows):
            row = table.row_values(row_num)
            if row:
                group_instance = []
                ip, port, hostname, use_default_auth, username, password, group = row
                if get_object(Asset, hostname=hostname):
                    continue
                if isinstance(password, int) or isinstance(password, float):
                    password = unicode(int(password))
                use_default_auth = 1 if use_default_auth == u'??' else 0
                password_encode = CRYPTOR.encrypt(password) if password else ''
                if hostname:
                    asset = Asset(ip=ip,
                                  port=port,
                                  hostname=hostname,
                                  use_default_auth=use_default_auth,
                                  username=username,
                                  password=password_encode
                                  )
                    asset.save()
                    group_list = group.split('/')
                    for group_name in group_list:
                        group = get_object(AssetGroup, name=group_name)
                        if group:
                            group_instance.append(group)
                    if group_instance:
                        asset.group = group_instance
                    asset.save()
        return True

def execute(self, context, filepath, *sheet_readers):
        workbook = xlrd.open_workbook(filepath)
        return [reader(context, workbook) for reader in sheet_readers]

def load_from_excel(self, file, click_type='callback_data', default_click='default_blank_callback'):
        buttons = []
        wb = open_workbook(file, formatting_info=True)
        sheet = wb.sheet_by_name("Sheet1")
        print('Reading keyboard from:', file)
        for col in range(sheet.ncols):
            text = data = ''
            buttons.append([])
            for row in range(sheet.nrows):
                cell = sheet.cell(row, col)
                fmt = wb.xf_list[cell.xf_index]
                border = fmt.border
                has_bottom = bool(border.bottom_line_style)
                if not has_bottom:
                    text = str(cell.value)
                else:
                    data = str(cell.value)
                    if data and text:
                        buttons[col].append({'text': text, click_type: data})
                    else:
                        buttons[col].append({'text': data, click_type: default_click})
                    text = ''
                    data = ''

            if not has_bottom and text:
                raise ExcelNoBottomException('Cell({0},{1}) has no bottom border.'.format(row, col))

        # Flip columns and rows
        buttons = list(map(list, itertools.zip_longest(*buttons)))
        buttons = [[button for button in row if button is not None] for row in buttons]
        self.keyboard['inline_keyboard'] = buttons

def setWb(self):
        sheet_names = list()
        if os.path.isfile(self.reportName):
            wb = copy(xlrd.open_workbook(self.reportName, formatting_info=1))
            sheets = wb._Workbook__worksheets
            for s in sheets:
                sheet_names.append(s.get_name()) 
            return wb, sheet_names
        else:
            return xlwt.Workbook(encoding='utf-8'), sheet_names

def read_excel_sheet(file=None, file_content=None, sheet_num=0, sheet_name=None, title_row=0, titles_list=None):
    '''
    ??Excel?????Sheet???

    :param file: Excel????
    :param file_content: Excel????, ????????file or file_content???
    :param sheet_num: ?????
    :param sheet_name: ?????(???)
    :param title_row: ??????????(?0??), ??????????
    :param titles_list: ??????, ????????title_row??, ?????????
    :return: list(dict) ??????????dict, ?title_row?titles_list???key
    '''
    if titles_list is None:
        titles_list = []
    try:
        wb = xlrd.open_workbook(filename=file, file_contents=file_content)
    except Exception as e:
        raise e

    sheet = wb.sheets()[sheet_num]
    nrows = sheet.nrows  # ??
    ncols = sheet.ncols  # ??
    _console.debug('rows: %s, cols: %s' %(nrows, ncols))
    if titles_list and len(titles_list) > 1:
        titles = titles_list
    else:
        titles = sheet.row_values(title_row)  # ?????
    _console.debug(titles)

    # ?????????
    list = []
    for row_num in range(title_row + 1, nrows):
        _console.debug('row: %s' %(row_num,))
        row = sheet.row_values(row_num)
        if row:
            obj = {}
            for i, title in enumerate(titles):
                obj[title] = row[i]
            list.append(obj)
    return list

def main():
    weekday_sheets_file = xlrd.open_workbook(WEEKDAY_FILE_NAME).sheets()
    weekend_sheets_file = xlrd.open_workbook(WEEKEND_FILE_NAME).sheets()

    raw_weekday_sheets = extract_valid_sheets(weekday_sheets_file)
    raw_weekend_sheets = extract_valid_sheets(weekend_sheets_file)

    new_weekday_sheets = xlwt.Workbook()
    new_weekend_sheets = xlwt.Workbook()

    pole_file = open(POLE_COMPLETION)
    poles = [p[:-1].split(',') for p in pole_file.readlines()]

    create_sheet(raw_weekday_sheets, new_weekday_sheets, 'weekday', poles)
    create_sheet(raw_weekend_sheets, new_weekend_sheets, 'weekend', poles)

    new_weekday_sheets.save(NORM_WEEKDAY_FILE)
    new_weekend_sheets.save(NORM_WEEKEND_FILE)

def do(self):
        #app = wx.PySimpleApp(0)


        wcd="Excel Files(*.xls)|*.xls|"
        dir = "/home"
        save_dlg = wx.FileDialog(self.parent, message='Choose File to be Imported', defaultDir=dir, defaultFile= '', wildcard=wcd, style=wx.OPEN)
        if save_dlg.ShowModal() == wx.ID_OK:
            path = save_dlg.GetPath()


            self.book = open_workbook(path)
            self.current_sheet=self.book.sheet_by_index(0)
            self.rown=self.current_sheet.nrows
            self.DB=db_operations()
            self.write_to_db()
        save_dlg.Destroy()
        print "extd"

def __init__(self, filepath):
        # ????excel???
        if filepath.endswith('.xlsx') or filepath.endswith('.xls'):
            if os.path.exists(filepath):
                try:
                    # ??xlrd book??
                    self.fileHandle = xlrd.open_workbook(filepath)
                except Exception as err:
                    logging.error("{} error: ????excel????! ????: {} ????: {}".format(
                        Operator.get_current_func(), filepath, str(err)))
        if not self.fileHandle:
            raise Exception('????excel????! ??????')