python读取Excel中关联表格的数据(只要是同Excel中

来自优快云-Mr熊

https://blog.youkuaiyun.com/qq_41030861/article/details/80515984

谁能告诉我前面这一堆都是什么啊。。。

 

python读取Excel中关联表格的数据(只要是同Excel中)---可以解决无限次同一个Excel中跨sheet或同sheet中表格关联--

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']
--------------------- 
作者：Mr熊 
来源：优快云 
原文：https://blog.youkuaiyun.com/qq_41030861/article/details/80515984 
版权声明：本文为博主原创文章，转载请附上博文链接！