基于需求的Excel特殊读取和text动态显示实战

需求：

读取Excel数据并进行处理使之组合显示
基于上一篇截图实战的结果的动态显示

GUI界面显示，格式类似于Excel表格

使用python开发，需要text的动态显示

实现:

介绍对 Excel 操作：

1. 普通表格读取；
2. 多列表格合并（一对一）
3. 有颜色表格按条件对表头读取；

列出需要导入的包：

from tkinter import Tk,Label,Entry,Button,Listbox,mainloop,END
import tkinter.font as tkFont
import tkinter.ttk as ttk
import pandas as pd
# import prettytable as pt
import openpyxl
import xlrd
import time

1. 普通表格读取：

'''
read the url from excel
'''
# the file storage path
def read_url(path):
    if path != None:
        df5 = pd.read_excel(path, usecols=[5],names=None,skiprows=3)
        df5.fillna("",inplace=True) # value read that is null replaced by ""
        df_li5 = df5.values.tolist() # transform the result read to list
        return df_li5

2. 多列表格合并（一对一）：

'''
read the name from excel
'''
def read_name(path):
    if path != None:
    	# Read the first 5 columns of data
        df = pd.read_excel(path, usecols=[0],names=None,skiprows=3)
        df1 = pd.read_excel(path, usecols=[1],names=None,skiprows=3)
        df2 = pd.read_excel(path, usecols=[2],names=None,skiprows=3)
        df3 = pd.read_excel(path, usecols=[3],names=None,skiprows=3)
        df4 = pd.read_excel(path, usecols=[4],names=None,skiprows=3)
        df.fillna("",inplace=True)
        df1.fillna("",inplace=True)
        df2.fillna("",inplace=True)
        df3.fillna("",inplace=True)
        df4.fillna("",inplace=True)
        # take data
        df_li = df.values
        df_li1 = df1.values
        df_li2 = df2.values
        df_li3 = df3.values
        df_li4 = df4.values
        name = []
        # merge multiple columns
        for i, j, m, n, p in zip(df_li,df_li1,df_li2,df_li3,df_li4):
            sum = i + j + m + n + p
            name.append(sum)
        return name

ZIP 函数详解：

def zip(*iterables):
    # zip('ABCD', 'xy') --> Ax By
    sentinel = object()
    iterators = [iter(it) for it in iterables]
    while iterators:
        result = []
        for it in iterators:
            elem = next(it, sentinel)
            if elem is sentinel:
                return
            result.append(elem)
        yield tuple(result)

利用zip函数可以对数据按顺序进行组合，而不是直接插入到某个位置或者简单接在前一个列表的结尾

3. 有颜色表格按条件对表头读取：

类似于这种情况的Excel表格的读取，我们需要对表格填充颜色进行判断，最红按条件读取特定的元素

'''
read the key from excel
'''
def read_key(path):
    if path != None:
        workbook=openpyxl.load_workbook(path)
        data = xlrd.open_workbook(path)
        table = data.sheets()[0] # select sheet that contain data
        # test
        '''
        data = xlrd.open_workbook(path)
        table = data.sheets()[0]
        for i in range(10):
            cell = table.cell(2,i)
            print(cell)
        # output special element
        cell = table.cell(2,6)
        print(cell)
        '''
        # read data by distinguish color
        sheet = workbook.worksheets[0]
        rows = sheet.max_row
        yellow1 = []
        yellow2 = []
        yellow3 = []
        for i in range(5,rows):
            ce = sheet.cell(row=i,column=7)
            fill = ce.fill
            if fill.start_color.rgb=="FFFFFF00": # yellow
                yellow1.append(table.cell(2,6)) # read header
            else:
                yellow1.append("")
        for i in range(5,rows):
            ce = sheet.cell(row=i,column=9)
            fill = ce.fill
            if fill.start_color.rgb=="FFFFFF00":
                yellow2.append(table.cell(2,8))
            else:
                yellow2.append("")
        for i in range(5,rows):
            ce = sheet.cell(row=i,column=11)
            fill = ce.fill
            if fill.start_color.rgb=="FFFFFF00":
                yellow3.append(table.cell(2,10))
            else:
                yellow3.append("")
        zipped = zip(yellow1,yellow2,yellow3)
        return list(zipped) # transform from array to list

提出 Python GUI 图形化界面编程，我这里使用的是 tkinter ，当我对Excel表格数据读取完毕之后需要在 tkinter 上以Excel表格的形式显示出来，首先想到的便是 table

数据显示操作

class PFrame():
    """use a ttk.TreeView as a multicolumn ListBox"""
    def __init__(self,table_header = None,table_list = None):
        self.table_header = table_header
        self.table_list = table_list
        self.tree = None
        self._setup_widgets()
        self._build_tree()

    def _setup_widgets(self):
        self.container = ttk.Frame()
        # self.container.pack(fill='both', expand=False)
        self.container.place(x=10,y=50,width=780,height=180)
        # create a treeview with dual scrollbars
        self.tree = ttk.Treeview(columns=self.table_header, show="headings")
        self.vsb = ttk.Scrollbar(orient="vertical",
            command=self.tree.yview)
        self.hsb = ttk.Scrollbar(orient="horizontal",
            command=self.tree.xview)
        self.tree.configure(yscrollcommand=self.vsb.set,
            xscrollcommand=self.hsb.set)
        self.tree.grid(column=0, row=0, sticky='nsew', in_=self.container)
        self.vsb.grid(column=1, row=0, sticky='ns', in_=self.container)
        self.hsb.grid(column=0, row=1, sticky='ew', in_=self.container)

        self.container.grid_columnconfigure(0, weight=1)
        self.container.grid_rowconfigure(0, weight=1)

    def _build_tree(self):
        for col in self.table_header:
            self.tree.heading(col, text=col.title(),
                command=lambda c=col: sortby(self.tree, c, 0))
            # adjust the column's width to the header string
            self.tree.column(col,
                width=tkFont.Font().measure(col.title()))

        for item in self.table_list:
            self.tree.insert('', 'end', values=item)
            # adjust column's width if necessary to fit each value
            for ix, val in enumerate(item):
                col_w = tkFont.Font().measure(val)
                if self.tree.column(self.table_header[ix],width=None)<col_w:
                    self.tree.column(self.table_header[ix], width=col_w)


    def _rebuild_tree(self):
        self.tree.configure(columns=self.table_header)
        self._build_tree()

    def _set_header(self,header = None):
        self.table_header = header

    def _set_list(self,list = None):
        self.table_list = list

def sortby(tree, col, descending):
    """sort tree contents when a column header is clicked on"""
    # grab values to sort
    data = [(tree.set(child, col), child) \
        for child in tree.get_children('')]
    # if the data to be sorted is numeric change to float
    #data =  change_numeric(data)
    # now sort the data in place
    data.sort(reverse=descending)
    for ix, item in enumerate(data):
        tree.move(item[1], '', ix)
    # switch the heading so it will sort in the opposite direction
    tree.heading(col, command=lambda col=col: sortby(tree, col, \
        int(not descending)))

代码解释
	自定义了一个类实现数据特定格式显示，类中使用到的是tree和table
	定义了一个sortby函数，实现tree中数据的按顺序读取和排列
	PFrame类和sortby函数可以直接使用

'''
header 代表数据显示框的表头 ;  zipped  代表数据内容
两者都必须是list类型，可以进行强制转型list()
'''
mc_listbox = PFrame(table_header=header,table_list=zipped)

text 数据动态显示

text 组件是基于tkinter 的，架设于图形化界面之上，一般用来动态显示操作过程

textvar = "%s,%s"%(getlocal_time(),read_url(path)[i]) # Define variables and pass parameters
text_box.insert(END,textvar+'\n') # Insert a new line to the end
text_box.update() # update line

至此，关键部分已经解决！