1.读取关联表格代码(再import openpyxl库的前提下)
#######################################################
#coding=utf-8 import os import random import string import openpyxl from openpyxl import * from openpyxl.reader.excel import load_workbook from openpyxl.compat import range from openpyxl.cell.read_only import EMPTY_CELL import win32com.client from openpyxl import Workbook from openpyxl.utils import get_column_letter from openpyxl.utils import column_index_from_string import sys reload(sys) sys.setdefaultencoding( "GBK" ) def Rir_sheet(wb,sheet_name,rc): sheet = wb[sheet_name] sheetdata = wb[sheet_name] url = str(rc) chrvalue = "" for i in url: if "a" <= i and i <= "z" or i >= 'A' and i <= 'Z': chrvalue = chrvalue + i cellvalue = sheetdata.cell(row=int(url.strip(chrvalue)), column=column_index_from_string(chrvalue)).value.decode( 'GBK') if "!" in cellvalue and cellvalue[0] == "=": sheetvalue = cellvalue.split("!")[0].strip("=") return Rir_sheet(wb, sheetvalue, cellvalue.split("!")[1]) elif "!" not in cellvalue and cellvalue[0] == "=": return Rir_cell(wb, sheet_name, cellvalue.strip('=')) else: return cellvalue def Rir_cell(wb,sheet_name,rc): ws=wb[sheet_name] url = str(rc) chrvalue = "" for i in url: if "a" <= i and i <= "z" or i >= 'A' and i <= 'Z': chrvalue = chrvalue + i cellvalue = ws.cell(row=int(url.strip(chrvalue)), column=column_index_from_string(chrvalue)).value.decode('GBK') if "!" in cellvalue and cellvalue[0] == "=": sheetvalue = cellvalue.split("!")[0].strip("=") return Rir_sheet(wb, sheetvalue, cellvalue.split("!")[1]) elif "!" not in cellvalue and cellvalue[0] == "=": return Rir_cell(wb,sheet_name,cellvalue.strip('=')) else: return cellvalue if __name__ == '__main__': wb = openpyxl.load_workbook(u'C://Users/Administrator//Desktop//测试文档.xlsx') print Rir_cell(wb,'Sheet2',"D2")#######################################################
2.实际项目中的代码如下
#######################################################
#coding=utf-8 import os import random import string import openpyxl from openpyxl.reader.excel import load_workbook from openpyxl.compat import range from openpyxl.cell.read_only import EMPTY_CELL from openpyxl.utils import column_index_from_string import win32com.client import sys reload(sys) sys.setdefaultencoding( "UTF-8" ) #获取Excel中数据 # 返回值形如: # =[{A1:A2,B1:B2,C3:C2,...},{A1:A3,B1:B3,C3:C3,...},{A1:A4,B1:B4,C3:C4,...},....] def GetSheetData( file_path, sheet_name): wb = openpyxl.load_workbook(file_path) # 获取workbook中所有的表格 sheets = wb.sheetnames # 按照指定sheet_name去查询 sheet = wb[sheet_name] # 统计第一行中字段多少,一遍后面使用 column_names = [] for r in range(1, sheet.max_column + 1): column_names.append(str(sheet.cell(row=1, column=r).value)) column_num = len(column_names) list = [] if sheet_name in sheets: sheet = wb[sheet_name] for r in range(2, sheet.max_row + 1): dict = {} for c in range(1, sheet.max_column + 1): if str(sheet.cell(row=r, column=c).value) == "None": dict[column_names[c - 1].decode('UTF-8')] = "" else: cellvalue = str(sheet.cell(row=r, column=c).value).decode('UTF-8') if "!" in cellvalue and cellvalue[0] == "=": # 判断数据是否是关联不同sheet页单元格数据,是就是下面方式处理 sheetvalue = cellvalue.split("!")[0].strip("=") if sheetvalue in sheets: # 判断获取的sheet页是否在本Excel中是否存字,存在就调用Rir_sheet(wb,sheet_name,rc)方法 dict[column_names[c - 1].decode('UTF-8')] = Rir_sheet(wb, sheetvalue, cellvalue.split("!")[1]) else: dict[column_names[c - 1].decode('UTF-8')] = cellvalue elif "!" not in cellvalue and cellvalue[0] == "=": # 判断数据是否是关联不同sheet页单元格数据,是就是下面方式处理 dict[column_names[c - 1].decode('UTF-8')] = Rir_cell(wb, sheet_name, cellvalue.strip('=')) else: # 如果无关联就直接存字 dict[column_names[c - 1].decode('UTF-8')] = cellvalue num = 0 for i in dict.values(): if len(i.replace(" ", '')) == 0: num = num + 1 if num != column_num: list.append(dict) return list else: print 'your input not in sheets' #######同一个Excel中,不同sheet页的单元格之间的关联值获取######## def Rir_sheet( wb, sheet_name, rc): sheet = wb[sheet_name] sheetdata = wb[sheet_name] url = str(rc) chrvalue = "" for i in url: if "a" <= i and i <= "z" or i >= 'A' and i <= 'Z': chrvalue = chrvalue + i cellvalue = sheetdata.cell(row=int(url.strip(chrvalue)), column=column_index_from_string(chrvalue)).value.decode( 'UTF-8') if "!" in cellvalue and cellvalue[0] == "=": sheetvalue = cellvalue.split("!")[0].strip("=") return Rir_sheet(wb, sheetvalue, cellvalue.split("!")[1]) elif "!" not in cellvalue and cellvalue[0] == "=": return Rir_cell(wb, sheet_name, cellvalue.strip('=')) else: return cellvalue #######同一个Excel中,相同sheet页的单元格之间的关联值获取######## def Rir_cell( wb, sheet_name, rc): ws = wb[sheet_name] url = str(rc) chrvalue = "" for i in url: if "a" <= i and i <= "z" or i >= 'A' and i <= 'Z': chrvalue = chrvalue + i cellvalue = ws.cell(row=int(url.strip(chrvalue)), column=column_index_from_string(chrvalue)).value.decode('UTF-8') if "!" in cellvalue and cellvalue[0] == "=": sheetvalue = cellvalue.split("!")[0].strip("=") return Rir_sheet(wb, sheetvalue, cellvalue.split("!")[1]) elif "!" not in cellvalue and cellvalue[0] == "=": return Rir_cell(wb, sheet_name, cellvalue.strip('=')) else: return cellvalue if __name__ == '__main__': a=GetSheetData(u'C://Users/Administrator//Desktop//测试文档.xlsx','Sheet2') print a[0]['colum6'] print a[0]['colum2'] print a[0]['colum3'] print a[0]['colum4']
############################################
转载请注明出处
###########################################
跨Excel的关联值获取,后续空了补上
###########################################