詳解Python如何實現Excel資料讀取和寫入

2022-04-20 19:01:26

1. 功能分析

1.載入資料夾內所有的Excel資料；

2.生產貢獻度分析圖表(以柱狀圖顯示錶格資料)；

3.提起Excel表格中指定列資料；

4.定向篩選所需資料；

5.多表資料統計排行；

6.多表資料合併新excel檔案。

2.系統開發環境

Anaconda3,在conda 中，window和ubuntu中的python功能一樣。

pycharm。

3.安裝依賴庫

這些依賴包都要裝好

import os
import xlrd2 #xlrd: 對Excel進行讀相關操作
import xlwt #xlwt: 對Excel進行寫相關操作，且只能建立一個全新的Excel然後進行寫入和儲存。
import numpy
import matplotlib
from prettytable import PrettyTable  #PrettyTable 是python中的一個第三方庫，可用來生成美觀的ASCII格式的表格
from matplotlib import pyplot as plt

4. 主函數設計

Excel資料分析師的主函數main(),主要用於實現系統的主介面。在主函數main()中，首先呼叫get_files_name()函數獲取檔名。

get_files_name()函數程式碼如下：

#匯入檔案
def get_files_name():
    """
    用於獲取檔名
    :return: 返回值為檔名組成的列表
    """
    file_list = os.listdir('./data')
    return file_list

然後呼叫load_data()函數來讀取excel檔案並字典方式儲存。

#儲存生產excel表
def load_data(file_list):
    """
    用於讀取指定的檔案並儲存至字典資料結構中
    :param file_list: 需要載入的檔案列表
    :return: 儲存了檔案內容的字典
    """
    dictory = {}
    for file in file_list:
        # 獲取表格檔案
        book = xlrd2.open_workbook('./data/'+file)
        # 獲取表格中的所有sheet
        names = book.sheet_names()
        # 獲取第一個sheet
        sheet = book.sheet_by_index(0)
        # 獲取當前表格的行數
        rows = sheet.nrows
        # 獲取當前表格的列數
        cols = sheet.ncols
        # 獲取表標頭檔案，即表格第一行
        head = sheet.row_values(0)
        for row in range(rows-1):
            # 如果當前字典中沒有該城市則建立一個
            if not sheet.cell_value(row+1, 0) in dictory.keys():
                dictory[sheet.cell_value(row+1, 0)] = {}
            for col in range(cols-1):
                dictory[sheet.cell_value(row+1, 0)][head[col+1]] = float(sheet.cell_value(row+1, col+1))
    return dictory

接著呼叫menu()函數生成功能選擇選單。

menu()函數程式碼如下：

# 列印選單
def menu():
    print("  ----------Excel 資料分析師----------")
    print("{:<30}".format("  ==============功能選單============== "))
    print("{:<30}".format("   1. 顯示當前資料                     "))
    print("{:<30}".format("   2. 以柱狀圖展示當前資料              "))
    print("{:<30}".format("   3. 提起指定列                       "))
    print("{:<30}".format("   4. 定向篩選指定元素                       "))
    print("{:<30}".format("   5. 資料排行                         "))
    print("{:<30}".format("   6. 重新載入資料                      "))
    print("{:<30}".format("   7. 儲存當前資料                      "))
    print("{:<30}".format("   0. 退出程式                          "))
    print("{:<30}".format(" ==================================== "))
    print("{:<30}".format(" 說明：輸入相應數位後按下回車選擇指定功能 "))
    print('n')

並且應用if語句控制各個子函數的呼叫，從而實現對Excel檔案的選擇，Excel資料的載入，選擇、篩選、合併、排序和統計等功能。

主函數完整程式碼如下：

if __name__ == "__main__":
    # 匯入檔案
    files = get_files_name()
    data = {}
    print("當前data資料夾下的檔案如下：")
    num = 1
    for file in files:
        print(num, file)
        num += 1
    while(1):
        index_str = input("請選擇需要匯入的檔案序號（多個檔案匯入時用空格分開， 輸入0則匯入所有檔案，輸入多檔案則自動合併）：")
        index_list = index_str.split(' ')
        try:
            index_list.remove('')
        except:
            pass
        choice_file_list = []
        if index_list[0] == '0':
            choice_file_list = files
            break
        else:
            try:
                for item in index_list:
                    choice_file_list.append(files[int(item)-1])
            except:
                print("輸入序號有誤")
                continue
        if choice_file_list:
            break
        else:
            print("輸入序號有誤")
    data = load_data(choice_file_list)
    print("匯入資料成功n")
    # 呼叫函數，列印選單
    menu()
    while 1:
        choice = input("請選擇指定功能：")
        if choice == '0':
            print("n退出程式n")
            exit()
        elif choice == '1':
            print("當前功能：顯示當前資料")
            show_data(data)
            input('n按下回車返回選單')
            menu()
        elif choice == '2':
            print("當前功能：以柱狀圖顯示資料")
            draw_plot(data)
            input('n按下回車返回選單')
            menu()
        elif choice == '3':
            print("當前功能：篩選指定列")
            keys = list(data[list(data.keys())[0]].keys())
            print("當前表格中的列如下：")
            num = 1
            for key in keys:
                print(num, key)
                num += 1
            choice_col_list = []
            while (1):
                index_str = input("請選擇需要篩選出的列序號（多列之間用空格分開，0代表所有列）：")
                index_list = index_str.split(' ')
                try:
                    index_list.remove('')
                except:
                    pass
                choice_file_list = []
                if index_list[0] == '0':
                    choice_col_list = keys
                    break
                else:
                    try:
                        for item in index_list:
                            choice_col_list.append(keys[int(item) - 1])
                    except:
                        print("輸入序號有誤")
                        continue
                if choice_col_list:
                    break
                else:
                    print("輸入序號有誤")
            data = get_specified_cols(data, choice_col_list)
            print("篩選成功")
            input('n按下回車返回選單')
            menu()
        elif choice == '4':
            print("當前功能：篩選指定行")
            keys = list(data[list(data.keys())[0]].keys())
            print("當前表格中的列如下：")
            num = 1
            print(num, "城市")
            num += 1
            for key in keys:
                print(num, key)
                num += 1
            col = int(input("請輸入需要進行篩選的資料所在的列："))-2
            if col == -1:
                col = '城市'
            else:
                col = keys[col]
            op_list = ['<', '<=', '=', '>=', '>']
            print("比較操作符如下：")
            num = 1
            for op in op_list:
                print(num, op)
                num += 1
            operation = int(input("請輸入比較操作符前的序號："))-1
            operation = op_list[operation]
            value = input("請輸入需要篩選的值：")
            data = get_specified_data(data, operation, col, value)
            print("篩選成功")
            input('n按下回車返回選單')
            menu()
        elif choice == '5':
            print("當前功能：資料排序")
            keys = list(data[list(data.keys())[0]].keys())
            print("當前表格中的列如下：")
            num = 1
            for key in keys:
                print(num, key) #顯示當前表格中的所有的列
                num += 1
            col = int(input("請輸入需要進行排序的資料所在的列：")) - 1
            col = keys[col]
            reverse = input("排序方式：n1 從大到小排序n2 從小到大排序n")
            if reverse == '1':
                data = sort_data(data, col, True)
            elif reverse == '2':
                data = sort_data(data, col, False)
            else:
                print("輸入有誤")
            input('n按下回車返回選單')
            menu()
        elif choice == '6':
            # 匯入檔案
            files = get_files_name()
            data = {}
            print("當前資料夾下的檔案如下：")
            num = 1
            for file in files:
                print(num, file)
                num += 1
            while (1):
                index_str = input("請選擇需要匯入的檔案序號（多個檔案匯入時用空格分開， 輸入0則匯入所有檔案，輸入多檔案則自動合併）：")
                index_list = index_str.split(' ')
                try:
                    index_list.remove('')
                except:
                    pass
                choice_file_list = []
                if index_list[0] == '0':
                    choice_file_list = files
                    break
                else:
                    try:
                        for item in index_list:
                            choice_file_list.append(files[int(item) - 1])
                    except:
                        print("輸入序號有誤")
                        continue
                if choice_file_list:
                    break
                else:
                    print("輸入序號有誤")
            data = load_data(choice_file_list)
            print("匯入資料成功n")
            # 列印選單
            menu()
        elif choice == '7':
            print("當前功能：儲存資料")
            save(data)
            input('n按下回車返回選單')
            menu()
        else:
            print("請輸入正確的數位")
            input('n按下回車返回選單')
            menu()

5.模組設計

載入資料夾內所有的Excel資料

show_data()函數通過PrettyTable 庫(PrettyTable 庫是python中的一個第三方庫，可用來生成美觀的ASCII格式的表格)將之前儲存的字典資料生成表格。

#載入顯示資料
def show_data(dictory):
    try:
        keys = list(dictory[list(dictory.keys())[0]].keys())
    except:
        print("當前資料為空")
        return
    head = ['城市']
    head.extend(keys)
    table = PrettyTable(head)
    for key in dictory.keys():
        line = [key]
        for key_2 in keys:
            line.append(dictory[key][key_2])
        table.add_row(line)
    print(table)

效果圖如下：

生產貢獻度分析圖表(以柱狀圖顯示錶格資料)

draw_plot( )函數使用了matplotlib庫。通過atplotlib.rc( )來設定字型，通過plt.bar( )函數來繪製柱狀圖，通過plt.legend( )函數來給圖新增圖例。

#製作圖表
def draw_plot(dictory):
    font = {'family': 'MicroSoft Yahei', 'weight': 'bold', 'size': 7}
    matplotlib.rc('font', **font) #設定中文字型
    # 定義三個顏色
    index = numpy.arange(len(dictory.keys()))
    color = [(256 / 256, 0 / 256, 0 / 256, 1),
            (0 / 256, 0 / 256, 256 / 256, 1),
            (0 / 256, 256 / 256, 0 / 256, 1),
            (0 / 256, 0 / 256, 0 / 256, 1)]
    first_key = list(dictory.keys())
    first_key = first_key[0]
    cols = list(dictory[first_key].keys())
    data = []
    for i in range(len(cols)):
        data.append([])
    for key in dictory.keys():
        for col in range(len(cols)):
            data[col].append(dictory[key][cols[col]])
    offset = -1/4
    for i in range(len(cols)):
        plt.bar(index+offset, data[i], color=color[i], width=1 / 5) #通過bar函數可以用柱狀圖來表達一些變數的統計分佈
        offset += 1/4
    plt.xticks(index, dictory.keys())#表示刻度
    plt.legend(cols)#給影象加上圖例
    plt.show()

效果圖

提起Excel表格中指定列資料

get_specified_cols()函數根據使用者在選單輸入的列名，通過字典的索引篩選出列名，載入指定列的所有資料。

#提起指定列
def get_specified_cols(dictory, col_name_list):
    """
    篩選出指定的列
    :param dictory:原始字典
    :param col_name_list: 需要篩選出的列名，城市名預設出現
    :return: 篩選之後的字典
    """
    new_dict = {}
    for key in dictory.keys():
        new_dict[key] = {}
        for col_name in col_name_list:
            new_dict[key][col_name] = dictory[key][col_name]
    return new_dict