Python xlrd.open_workbook方法代码示例

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def test_read_xlrd_book(self, ext):
        import xlrd
        df = self.frame

        engine = "xlrd"
        sheet_name = "SheetA"

        with ensure_clean(ext) as pth:
            df.to_excel(pth, sheet_name)
            book = xlrd.open_workbook(pth)

            with ExcelFile(book, engine=engine) as xl:
                result = read_excel(xl, sheet_name, index_col=0)
                tm.assert_frame_equal(df, result)

            result = read_excel(book, sheet_name=sheet_name,
                                engine=engine, index_col=0)
            tm.assert_frame_equal(df, result) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def translate_to():
	post = []

	book = open_workbook(sys.argv[1])
	sheet = book.sheet_by_index(0)
	ctr = 0
	for row in sheet.col(1):
		if ctr!=0:
			post.append(row.value.encode('utf-8'))
		ctr+=1
	for i in range(0,len(post)):
		str(post[i])


	translations=[] 
	xbook = xlsxwriter.Workbook('hinglish.xlsx')
	xsheet = xbook.add_worksheet('Test')
	for i in range(0,len(post)):
		translation = translator.translate(post[i],src='en', dest='hi')
		xsheet.write(i,1,''.join((translation.text))) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
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)) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def test_read_xlrd_Book(self):
        _skip_if_no_xlrd()
        _skip_if_no_xlwt()

        import xlrd

        df = self.frame

        with ensure_clean('.xls') as pth:
            df.to_excel(pth, "SheetA")
            book = xlrd.open_workbook(pth)

            with ExcelFile(book, engine="xlrd") as xl:
                result = xl.parse("SheetA")
                tm.assert_frame_equal(df, result)

            result = read_excel(book, sheetname="SheetA", engine="xlrd")
            tm.assert_frame_equal(df, result) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def convert_xls_to_xlsx(src_file_path, dst_file_path):

    print (src_file_path, dst_file_path)
    book_xls = xlrd.open_workbook(src_file_path)
    book_xlsx = Workbook()

    sheet_names = book_xls.sheet_names()
    for sheet_index in range(0,len(sheet_names)):
        sheet_xls = book_xls.sheet_by_name(sheet_names[sheet_index])
        if sheet_index == 0:
            sheet_xlsx = book_xlsx.get_active_sheet()
            sheet_xlsx.title = sheet_names[sheet_index]
        else:
            sheet_xlsx = book_xlsx.create_sheet(title=sheet_names[sheet_index])

        for row in range(0, sheet_xls.nrows):
            for col in range(0, sheet_xls.ncols):
                sheet_xlsx.cell(row = row+1 , column = col+1).value = sheet_xls.cell_value(row, col)

    book_xlsx.save(dst_file_path)
    return dst_file_path 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def read_context_from_excel(filename):
    if not os.path.exists(filename):
        return []
    
    arr = []
    workbook = xlrd.open_workbook(filename=filename)
    for sheetname in workbook.sheet_names():
        sheet = workbook.sheet_by_name(sheetname)
        cur_sheet = []
        for i in range(sheet.nrows):
            rows = []
            for j in range(sheet.ncols):
                rows.append(get_cell_val(sheet, i, j, datemode=workbook.datemode))
            cur_sheet.append(rows)
        arr.append(cur_sheet)
    return arr 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def process():
    book = xlrd.open_workbook(file_contents=requests.get(URL).content)
    sheet = book.sheet_by_index(0)

    return [
        {
            "country_code": "FI",
            "primary": True,
            "bic": bic.value.upper().strip(),
            "bank_code": bank_code.value,
            "name": name.value,
            "short_name": name.value,
        }
        for bank_code, bic, name in list(sheet.get_rows())[2:]
        if bank_code.value != ""
    ] 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def process():
    registry = []
    skip_names = ["NAV", "VRIJ", "NAP", "NYA", "VRIJ - LIBRE", "-"]

    book = xlrd.open_workbook(file_contents=requests.get(URL).content)
    sheet = book.sheet_by_index(0)

    for row in list(sheet.get_rows())[2:]:
        bank_code, bic, name, second_name = row[:4]
        if bic.value.upper() in skip_names:
            continue
        registry.append(
            {
                "country_code": "BE",
                "primary": True,
                "bic": bic.value.upper().replace(" ", ""),
                "bank_code": bank_code.value,
                "name": name.value or second_name.value,
                "short_name": name.value or second_name.value,
            }
        )
    return registry 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def read_xls(_file):
    agent_config_vars['data_format'] = 'CSV' # treat as CSV from here out
    agent_config_vars['timestamp_format'] = ['epoch']
    # open workbook
    with xlrd.open_workbook(_file) as wb:
        # for each sheet in the workbook
        for sheet in wb.sheets():
            # for each row in the sheet
            for row in sheet.get_rows():
                # build dict of <field name: value>
                d = label_message(list(map(lambda x: x.value, row)))
                # turn datetime into epoch
                timestamp = ''
                while timestamp == '' and len(agent_config_vars['timestamp_field']) != 0:
                    timestamp_field = agent_config_vars['timestamp_field'].pop(0)
                    try:
                        timestamp_xlrd = d[timestamp_field]
                    except KeyError:
                        continue
                    timestamp = get_timestamp_from_datetime(datetime(
                        *xlrd.xldate_as_tuple(
                            timestamp_xlrd,
                            sheet.book.datemode)))
                d[timestamp_field] = timestamp
                agent_config_vars['timestamp_field'] = [timestamp_field]
                yield d 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def census_parser(filename, verbose):
    # Create the named tuple
    CensusTuple = namedtuple('Census', 'name, rank, count, prop100k, cum_prop100k, pctwhite, pctblack, pctapi, pctaian, pct2prace, pcthispanic')

    # Define the location of the file and worksheet till arguments are developed
    worksheet_name = "top1000"

    #Define work book and work sheet variables
    workbook = xlrd.open_workbook(filename)
    spreadsheet = workbook.sheet_by_name(worksheet_name)
    total_rows = spreadsheet.nrows - 1
    current_row = -1

    # Define holder for details
    username_dict = {}
    surname_dict = {}
    alphabet = list(string.ascii_lowercase)

    while current_row < total_rows:
        row = spreadsheet.row(current_row)
        current_row += 1
        entry = CensusTuple(*tuple(row)) #Passing the values of the row as a tuple into the namedtuple
        surname_dict[entry.rank] = entry
        cellname = entry.name
        cellrank = entry.rank
        for letter in alphabet:
            if "." not in str(cellrank.value):
                if verbose > 1:
                    print("[-] Eliminating table headers")
                break
            username = letter + str(cellname.value.lower())
            rank = str(cellrank.value)
            username_dict[username] = rank
    username_list = sorted(username_dict, key=lambda key: username_dict[key])

    return(surname_dict, username_dict, username_list) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def main():
    """Run the program."""
    args = parse_arguments()

    ext = os.path.splitext(args.input_file)[-1].lower()
    with gzip.open(args.output_file, mode="wt") as outfile:
        csvwriter = csv.writer(outfile, delimiter=str("\t"), lineterminator="\n")

        try:
            if ext in (".tab", ".txt", ".tsv"):
                with open(args.input_file) as infile:
                    for line in infile:
                        outline = line.strip().split("\t")
                        csvwriter.writerow(outline)
            elif ext == ".csv":
                with open(args.input_file) as infile:
                    for line in infile:
                        outline = line.strip().split(",")
                        csvwriter.writerow(outline)
            elif ext in (".xls", ".xlsx"):
                workbook = xlrd.open_workbook(args.input_file)
                worksheet = workbook.sheets()[0]
                for rownum in range(worksheet.nrows):
                    csvwriter.writerow(worksheet.row_values(rownum))
            else:
                print('{"proc.error":"File extension not recognized."}')
        except Exception:
            print('{"proc.error":"Corrupt or unrecognized file."}')
            raise 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def pasre_inter(filename):#导入接口
	file=xlrd.open_workbook(filename)
	me=file.sheets()[0]
	nrows=me.nrows
	ncol=me.ncols
	project_name=[]
	model_name=[]
	interface_name=[]
	interface_url=[]
	interface_meth=[]
	interface_par=[]
	interface_header=[]
	interface_bas=[]
	jiekou_bianhao=[]
	interface_type=[]
	for i in range(2,nrows):
		jiekou_bianhao.append(me.cell(i,0).value)
		project_name.append(me.cell(i,2).value)
		model_name.append(me.cell(i,3).value)
		interface_name.append(me.cell(i,1).value)
		interface_url.append(me.cell(i,4).value)
		interface_type.append(me.cell(i,5).value)
		interface_header.append(me.cell(i,6).value)
		interface_meth.append(me.cell(i,7).value)
		interface_par.append(me.cell(i,8).value)
		interface_bas.append(me.cell(i,9).value)
		i+=1
	return jiekou_bianhao,interface_name,project_name,model_name,interface_url,\
		   interface_header,interface_meth,interface_par,interface_bas,interface_type
#导入测试用例 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def _check_xls_export(self):
        xls_export_url = reverse(
            'xls_export', kwargs={'username': self.user.username,
                                  'id_string': self.xform.id_string})
        response = self.client.get(xls_export_url)
        expected_xls = open_workbook(os.path.join(
            self.this_directory, "fixtures", "transportation",
            "transportation_export.xls"))
        content = self._get_response_content(response)
        actual_xls = open_workbook(file_contents=content)
        actual_sheet = actual_xls.sheet_by_index(0)
        expected_sheet = expected_xls.sheet_by_index(0)

        # check headers
        self.assertEqual(actual_sheet.row_values(0),
                         expected_sheet.row_values(0))

        # check cell data
        self.assertEqual(actual_sheet.ncols, expected_sheet.ncols)
        self.assertEqual(actual_sheet.nrows, expected_sheet.nrows)
        for i in range(1, actual_sheet.nrows):
            actual_row = actual_sheet.row_values(i)
            expected_row = expected_sheet.row_values(i)

            # remove _id from result set, varies depending on the database
            del actual_row[22]
            del expected_row[22]
            self.assertEqual(actual_row, expected_row) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def _num_rows(self, content, export_format):
        def xls_rows(f):
            return open_workbook(file_contents=f).sheets()[0].nrows

        def csv_rows(f):
            with tempfile.TemporaryFile() as tmp:
                tmp.write(f.encode('utf-8'))
                tmp.seek(0)
                return len([line for line in csv.reader(tmp)])
        num_rows_fn = {
            'xls': xls_rows,
            'csv': csv_rows,
        }
        return num_rows_fn[export_format](content) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import open_workbook [as 别名]
def _get_xls_data(self, filepath):
        storage = get_storage_class()()
        with storage.open(filepath) as f:
            workbook = open_workbook(file_contents=f.read())
        transportation_sheet = workbook.sheet_by_name("transportation")
        self.assertTrue(transportation_sheet.nrows > 1)
        headers = transportation_sheet.row_values(0)
        column1 = transportation_sheet.row_values(1)
        return dict(zip(headers, column1))