python类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)