Python xlrd.open_workbook() Examples

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

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) 

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 

def read_in(usr_path):
    try:
        wb_path = os.path.join(usr_path, ACI_DEPLOY_FILE)
        wb = xlrd.open_workbook(wb_path)
        print("Workbook Loaded.")
    except Exception as e:
        print("Something went wrong logging opening the workbook - ABORT!")
        sys.exit(e)
    return wb 

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
#导入测试用例 

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 

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 != ""
    ] 

def topology_import(self, file):
        book = open_workbook(file_contents=file.read())
        status = "Topology successfully imported."
        for obj_type in ("device", "link"):
            try:
                sheet = book.sheet_by_name(obj_type)
            except XLRDError:
                continue
            properties = sheet.row_values(0)
            for row_index in range(1, sheet.nrows):
                values = {"dont_update_pools": True}
                for index, property in enumerate(properties):
                    if not property:
                        continue
                    func = db.field_conversion[property_types.get(property, "str")]
                    values[property] = func(sheet.row_values(row_index)[index])
                try:
                    db.factory(obj_type, **values).serialized
                except Exception as exc:
                    info(f"{str(values)} could not be imported ({str(exc)})")
                    status = "Partial import (see logs)."
            db.session.commit()
        for pool in db.fetch_all("pool"):
            pool.compute_pool()
        self.log("info", status)
        return status 

def write_excel_xls_append(path, sheet_name, value):
    index = len(value)  # 获取需要写入数据的行数
    workbook = xlrd.open_workbook(path)  # 打开工作簿
    worksheet = workbook.sheet_by_name(sheet_name)  # 获取工作簿中所有表格中的的第一个表格
    rows_old = worksheet.nrows  # 获取表格中已存在的数据的行数
    new_workbook = copy(workbook)  # 将xlrd对象拷贝转化为xlwt对象
    new_worksheet = new_workbook.get_sheet(sheet_name)  # 获取转化后工作簿中的第一个表格
    for i in range(0, index):
        for j in range(0, len(value[i])):
            new_worksheet.write(i + rows_old, j, value[i][j])  # 追加写入数据，注意是从i+rows_old行开始写入
    new_workbook.save(path)  # 保存工作簿
    print("{}【追加】写入【{}】数据成功！".format(path, sheet_name)) 

def create_dataset(self, in_stream):
        """
        Create dataset from first sheet.
        """
        import xlrd
        xls_book = xlrd.open_workbook(file_contents=in_stream)
        dataset = tablib.Dataset()
        sheet = xls_book.sheets()[0]

        dataset.headers = sheet.row_values(0)
        for i in range(1, sheet.nrows):
            dataset.append(sheet.row_values(i))
        return dataset 

def getSheet(name):
    sheets = xlrd.open_workbook(name)
    sheet = sheets.sheet_by_index(0)
    return sheet 

def write_to_excel(sheet, x, y, value): #x->col, y->row
    rb = open_workbook('record.xls')
    rs = rb.sheet_by_index(sheet)
    wb = copy(rb)
    ws = wb.get_sheet(sheet)
    ws.write(y, x, value)
    wb.save('record.xls') 

def write_excel_xls_append(path, value):
    index = len(value)  # 获取需要写入数据的行数
    workbook = xlrd.open_workbook(path)  # 打开工作簿
    sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
    worksheet = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格
    rows_old = worksheet.nrows  # 获取表格中已存在的数据的行数
    new_workbook = copy(workbook)  # 将xlrd对象拷贝转化为xlwt对象
    new_worksheet = new_workbook.get_sheet(0)  # 获取转化后工作簿中的第一个表格
    for i in range(0, index):
        for j in range(0, len(value[i])):
            new_worksheet.write(i+rows_old, j, value[i][j])  # 追加写入数据，注意是从i+rows_old行开始写入
    new_workbook.save(path)  # 保存工作簿
    print("xls格式表格【追加】写入数据成功！") 

def read_excel():
    workbook = xlrd.open_workbook(excel_path)
    sheet1= workbook.sheet_by_name(u'Sheet1')
    # nrows = sheet1.nrows
    # ncols = sheet1.ncols
    global Pro_name,tjp_num,cus_num,ref_name,ref_ink,ref_sup,ref_texture,pcb_name,\
           pcb_sup,pin_name,pin_head,pin_head,film_name,film_sup,chip_name,easy_tape,\
           easy_sup,in_pack,out_pack
    tjp_num = sheet1.cell(1, 1).value
    Pro_name = sheet1.cell(2, 1).value
    cus_num = sheet1.cell(3, 1).value
    ref_name = sheet1.cell(4, 1).value
    ref_sup = sheet1.cell(4, 3).value
    ref_ink = sheet1.cell(5, 1).value
    ref_texture = sheet1.cell(5, 3).value
    pcb_name = sheet1.cell(6, 1).value
    pcb_sup = sheet1.cell(6, 3).value
    pin_name = sheet1.cell(7, 1).value
    pin_head = sheet1.cell(7, 3).value
    film_name = sheet1.cell(8, 1).value
    film_sup = sheet1.cell(8, 3).value
    chip_name = sheet1.cell(9, 1).value
    easy_tape = sheet1.cell(10, 1).value
    easy_sup = sheet1.cell(10, 3).value
    in_pack = sheet1.cell(11, 1).value
    out_pack = sheet1.cell(11, 1).value 

def getSheet(name):
    sheets = xlrd.open_workbook(name)
    sheet = sheets.sheet_by_index(0)
    return sheet 

def setUp(self):
        self.book = xlrd.open_workbook(from_this_dir('profiles.xls'), formatting_info=True) 

def test_tidy_dimensions(self):
        book = xlrd.open_workbook(from_this_dir('merged_cells.xlsx'))
        for sheet in book.sheets():
            for rowx in xrange(sheet.nrows):
                self.assertEqual(sheet.row_len(rowx), sheet.ncols) 

def test_read_ragged(self):
        book = xlrd.open_workbook(from_this_dir('ragged.xls'), ragged_rows=True)
        sheet = book.sheet_by_index(0)
        self.assertEqual(sheet.row_len(0), 3)
        self.assertEqual(sheet.row_len(1), 2)
        self.assertEqual(sheet.row_len(2), 1)
        self.assertEqual(sheet.row_len(3), 4)
        self.assertEqual(sheet.row_len(4), 4) 

def loadSeries(fileList):
  # Retrieve time series examples
  xData = []
  for fileName in fileList:
    book = xlrd.open_workbook(fileName)
    sheet = book.sheet_by_index(0)
    for rx in range(2,sheet.nrows):
      row = sheet.row(rx)[3:]
      row = [row[x].value for x in range(0,len(row)-4)]
      xData.append(row)
  return xData 

def parse_excel(input_filename):
    with open_workbook(input_filename) as wb:
        service_sheet = wb.sheet_by_name('Service')
        services = list(parse_service_sheet(service_sheet))
    return services 

def process():
    book = xlrd.open_workbook(file_contents=requests.get(URL).content)
    sheet = book.sheet_by_index(0)
    return [
        {
            "country_code": "LV",
            "primary": True,
            "bic": bic.value.upper(),
            "bank_code": bic.value[:4],
            "name": name.value,
            "short_name": name.value,
        }
        for _id, name, _iban_structure, bic in list(sheet.get_rows())[2:]
    ] 

def process():
    book = xlrd.open_workbook(file_contents=requests.get(URL).content)
    sheet = book.sheet_by_index(0)
    return [
        {
            "country_code": "HR",
            "primary": True,
            "bic": bic.value.upper().replace(" ", ""),
            "bank_code": int(bank_code.value),
            "name": name.value,
            "short_name": name.value,
        }
        for _, name, bank_code, bic in list(sheet.get_rows())[4:]
    ] 

def import_xls(request, username):
    if request.user.username != username:
        return HttpResponseForbidden(render_to_string("403.html"))

    handle, filename = mkstemp()
    try:
        os.write(handle, request.FILES['file'].read())
        wb = xlrd.open_workbook(filename)
        for xl_sheet in wb.sheets():
            if xl_sheet.nrows > 0 and xl_sheet.ncols > 0:
                name = '%s - %s' % (
                    splitext(request.FILES['file'].name)[0],
                    xl_sheet.name
                )
                sheet = Sheet(owner=request.user, name=name)
                sheet.jsonify_worksheet(worksheet_from_excel(xl_sheet))
                sheet.save()

                try:
                    calculate(request, sheet.owner.username, sheet.id)
                except:
                    pass

    except Exception:
        return render(
            request,
            'import_xls_error.html',
            {},
            context_instance=RequestContext(request)
        )
    finally:
        os.close(handle)
        os.unlink(filename)
    return HttpResponseRedirect('/') 

def TranslateSpreadsheet(sheet, csvfile):
  print 'Translating spreadsheet %s to CSV %s' % (sheet, csvfile)
  wb = xlrd.open_workbook(sheet)
  sheet = wb.sheet_by_index(0)
  with open(csvfile, 'w') as out:
    writer = csv.writer(out, quotechar='\"')
    date_columns = []
    for rownum in range(0, sheet.nrows):
      row = sheet.row_values(rownum)
      for i in range(0, len(row)):
        if type(row[i]) == unicode:
          row[i] = row[i].encode('ascii', 'ignore')
      # For the first two rows, find any labels containing 'Date' substring...
      if rownum==0 or rownum==1:
        for i in range(0, len(row)):
          if isinstance(row[i], basestring) and 'Date' in row[i]:
            print 'Found date column %s at %d' % (row[i], i)
            date_columns.append(i)
      else:
        for i in date_columns:
          translated_date = xlrd.xldate_as_tuple(row[i], 0)
          row[i] = '%04d-%02d-%02d' % (translated_date[0], translated_date[1], translated_date[2])
      for i in range(0, len(row)):
        if type(row[i]) == float and int(row[i]) == row[i]:
          row[i] = int(row[i])

      # Translate all strings to ascii, throwing away any unknown characters
      writer.writerow(row)


# Find the directory of this script. 

def read_xls(filename):
    data = xlrd.open_workbook(filename)
    table = data.sheet_by_index(0)        #通过索引获取xls文件第0个sheet
    nrows = table.nrows
    d = {}
    for i in range(nrows):
        d[str(i+1)] = table.row_values(i)[1:]  #取编号后的数据，以列表形式存在字典对应的值中
    return d 

def read_xls(filename):
    data = xlrd.open_workbook(filename)
    table = data.sheet_by_index(0)        
    nrows = table.nrows
    d = {}
    for i in range(nrows):
        d[str(i+1)] = table.row_values(i)[1:]  
    return d 

def read_xls(filename):
    data = xlrd.open_workbook(filename)
    table = data.sheet_by_index(0)        #通过索引获取xls文件第0个sheet
    nrows = table.nrows
    l = []
    for i in range(nrows):
        l.append(table.row_values(i))  #list用append方法
    return l 

def read_xls(filename):
    data = xlrd.open_workbook(filename)
    table = data.sheet_by_index(0)        #通过索引获取xls文件第0个sheet
    nrows = table.nrows
    d = {}
    for i in range(nrows):
        d[str(i+1)] = table.row_values(i)[1]   #才发现之前忘记+1了
    return d 

def __init__(self, excel, sheet):
        """
        :param excel: Location of Excel. e.g. "C:/Archive/TestExcel.xlsx"
        :param sheet: Sheet Name. e.g. "Sheet1"
        """
        self.excel = xlrd.open_workbook(excel)
        self.sheet = self.excel.sheet_by_name(sheet)
        
        # something like: [['', '', '', '', ''], ['', '', '', '', ''], ['', '', '', '', ''], ['', '', '', '', '']]
        self.data = [["" for x in range(self.sheet.ncols)] for y in range(self.sheet.nrows)] 
        
        for i in range(self.sheet.nrows):
            for j in range(self.sheet.ncols):
                self.data[i][j] = self.cellxy(i, j)