python 实现excel数据处理，文件遍历，并作图

通过python对excel的数据处理以及作图，完美的替代了前面VBA代码的功能，并能一键处理任意多个文件，自动化程度高了很多：VBA代码详解

本例程用到的数据以及代码下载：源数据以及代码

注意：代码后期进行了稍许更新，代码以这篇博文为准。

from openpyxl.drawing.image import Image
from matplotlib.ticker import FuncFormatter
import matplotlib.pyplot as plt
import pandas as pd
import openpyxl
import os

font1 = { 'size':8 }    # 用于设置图例的字体大小
# mycolors用于指定折线图每组数据的颜色
mycolors = ['black','dimgray','rosybrown','lightcoral','brown','red','sienna','chocolate','darkgray','palevioletred',\
          'darkorange','tan','gold','darkkhaki','olive','yellow','olivedrab','chartreuse','darkseagreen','navajowhite',\
          'green','lime','mediumaquamarine','lightseagreen','teal','cyan','cadetblue','deepskyblue','steelblue','dodgerblue',\
          'mediumblue','slateblue','darkslateblue','blueviolet','indigo','violet','purple','fuchsia','mediumvioletred','deeppink']

def dataProcess(fName):        # 对单独一个excel表格的数据处理，通过遍历文件变化fName实现一次运行处理多个文件
    fileDir = fName    # 文件位置
    wb=openpyxl.load_workbook(fileDir)          # 打开工作簿
    wsRawData = wb["Sheet1"]                   # 打开存放原始数据的工作表，这个工作表名称需要根据具体情况更换

    if "Process" in wb.sheetnames:               # 判断Process工作表是否存在
        wsProcess = wb["Process"]
        print("Process is exists")
    else:
        wsProcess = wb.create_sheet("Process", 1)     # 若不存在则新建序号为1的工作表,并命名为Process
        print("Sheet1 is NOT exists")

    if "Summary" in wb.sheetnames:
        wsSummary = wb["Summary"]
        print("Summary is exists")
        return                      # 如果表格存在，说明数据已经被处理过了，直接返回
    else:    
        wsSummary = wb.create_sheet("Summary", 0)   # 否则新建序号为0的工作表,并命名为Summary
        print("Summary is NOT exists")

    for i in range(1,41):       # 因为一共有40列数据，需要遍历40次（前闭后开区间）
        wsProcess.cell(row = 1, column = i).value = wsRawData.cell(row = 1, column = i).value        # 表头拷贝
        wsProcess.cell(row = 4, column = i).value = wsRawData.cell(row = 1, column = i).value

        sum = 0
        for j in range(2,51):       # 计算前50个数据的平均值
            sum = sum + wsRawData.cell(row = j, column = i).value
        wsProcess.cell(row = 2, column = i).value = sum / 49

        rowCnt = 2
        while wsRawData.cell(row = rowCnt,column = i).value != None:    # 计算所有原始数据的百分比
            wsProcess.cell(row = rowCnt+3, column = i).value = (wsRawData.cell(row = rowCnt, column = i).value - wsProcess.cell(2, i).value) /\
                                                                wsProcess.cell(row = 2,column = i).value
            wsProcess.cell(row = rowCnt+3, column = 41).value = (rowCnt-2)*2.5/60    # 每个采样点用时2.5s,这里是将采样点序号转换成时间
            rowCnt = rowCnt + 1
        
        if wsProcess.cell(row=rowCnt+2,column=i).value < 0 :     # 自动去掉一些不太好的数据
            continue
        xline = getData(wsProcess,5,rowCnt+3,41,41)    # 获取表格指定区域的数据
        rangeD = getData(wsProcess,5,rowCnt+3,i,i)
        plt.plot(xline,rangeD,color=mycolors[i-1],label=wsProcess.cell(row = 1,column = i).value)        # 添加一组数据到图中

    filePath,fileFullName = os.path.split(fName)    # '/home/shadow/test.xlsx' 返回 '/home/shadow/' 和 'test.xlsx'
    fName1,ext = os.path.splitext(fileFullName)    # '/home/shadow/test.xlsx' 返回 '/home/shadow/test' 和 '.xlsx'。 这两行是为了获取不带后缀的文件名
    plt.xlabel('Time(min)')       
    plt.ylabel('ChangeRate(%)')
    plt.gca().yaxis.set_major_formatter(FuncFormatter(to_percent))    # 将纵坐标以百分数显示
    plt.title(fName)         # 图的文件名
    plt.legend(loc = 'upper left',ncol=3,prop = font1)        # 画图(图例以3列的形式显示在左上角，字体为font1)
    plt.savefig("./pic/" + fName1 + ".png")        # 保存图（必须手动创建pic文件夹）
    #plt.show()            # 显示折线图，若不需要可以注释掉
    plt.close()             # 把plt对象的数据清空，不然之前循环的添加的数据也会保留
    img = Image("./pic/" + fName1 + ".png")    # 读取图片
    img.width = 900
    img.height = 500
    wsSummary.add_image(img, "A1")        # 将图片保存到excel中

    wb.save(fileDir)    # 保存编辑过后的excel文件（可以用不同的文件名实现另存为）

def to_percent(temp, position):        # 功能：将坐标轴的数据转换成百分数
    return '%1.1f'%(100*temp)+'%'          # 这里的数据可能需要根据情况修改

def get_files():        # 返回当前文件夹的下所有指定类型的文件全路径
    result = []
    filter = [".xlsx"]          # 文件类型过滤器
    for filepath,dirnames,filenames in os.walk(r'.'):       # 指定要访问的文件夹('.'表示当前文件夹)
        for filename in filenames:
            apath = os.path.join(filepath,filename)
            postfix = os.path.splitext(apath)[1]
            if postfix in filter:
                result.append(apath)
    return result

def getData(ws,rMin,rMax,cMin,cMax):        # 返回ws表格指定范围的数据
    array = []
    for DataRange in ws.iter_rows(min_row=rMin,max_row=rMax,min_col=cMin,max_col=cMax):
        for cel in DataRange:
            array.append(cel.value)
    return array

if not os.path.exists("./pic"):     # 若当前路径下不存在pic文件夹，则创建
    os.makedirs("./pic")
    
result = get_files()
i = 0
while i < len(result):
    # print(result[i])
    dataProcess(result[i])
    i = i + 1

os.system('pause')