Python实现CSV数据整理到Excel中并生成图表1

#encoding=utf-8
import openpyxl
from  openpyxl import load_workbook
import os, sys
import csv
import matplotlib
from datetime import datetime
from openpyxl import load_workbook
import os,sys,time
from openpyxl.chart import LineChart, Reference
from copy import deepcopy
from openpyxl import Workbook, load_workbook
from openpyxl.chart import ScatterChart, Series, Reference
from openpyxl.chart.layout import Layout, ManualLayout
from openpyxl.chart.axis import DateAxis
import matplotlib.pyplot as plt
import traceback
import win32api,win32con
exepath=os.path.dirname(sys.executable)

def createSheetname(testitem):
    #20path是资源档的路径
    os.chdir(exepath)
    for i in os.listdir(exepath):
        if testitem in i and 'xxx' not in i and '.' not in i:
            20path=os.path.join(exepath,i)
            break
    os.chdir(20path)
    sheetname0=[]
    for i in os.listdir(20path):
        sheetname0.append(i.split(' ')[0]+'_SKU'+list(i.split(' ')[1])[0]+'#'+list(i.split(' ')[1])[1]+list(i.split(' ')[1])[2])+'TestReport'
        # print('创建的Sheet的名字是：%s'%sheetname)

    print(sheetname0)
    sheetname = sorted(set(sheetname0), key=sheetname0.index)
    print(sheetname)
    return sheetname
# createSheetname('R20')
def samplesheetname():
    os.chdir(exepath)
    workbook=load_workbook(r'xxxx.xlsx')
    sheet_names=workbook.sheetnames
    print(sheet_names)
    return sheet_names
#复制sheet,sourcename
def copysheet(sourcename,copyname):
    wb=openpyxl.load_workbook(r'xxxxx.xlsx')
    sheet=wb.get_sheet_by_name(sourcename)
    new_sheet=wb.copy_worksheet(sheet)
    new_sheet.title=copyname
    wb.save(r'xxxx.xlsx')
def renamesheet(sourcename,copyname):
    wb = openpyxl.load_workbook(r'xxxx.xlsx')
    sheet = wb.get_sheet_by_name(sourcename)
    sheet.title= copyname
    # new_sheet.title = copyname
    wb.save(r'xxxxxx.xlsx')

# copysheet()

#分析CSV数据表把数据表中的数据整理到report中
def cinbenchtochart_B():
    # exepath=r'C:\Users\613\Desktop\logfiles'
    # exepath=os.path.dirname(sys.executable)
    R20path=exepath+'\\R20\\'
    os.chdir(R20path)
    dircontent=os.listdir(R20path)
    for i in dircontent:
        if 'R20B' in i:
            #ToSheetname是保存的sheet的名字
            Tosheetname=(i.split(' ')[0]+'_SKU'+list(i.split(' ')[1])[0]+'#'+list(i.split(' ')[1])[1]+list(i.split(' ')[1])[2])
            print(Tosheetname)
            #CSVpath是CSV的路径
            csvpath=R20path+'\%s\\'%i
            print(csvpath)
            os.chdir(csvpath)
            for i in os.listdir(csvpath):
                if 'xxxx' in i and'.csv' in i:
                    filename=i
                    print(filename)
            # filename=open(r'xxxx.csv'%username)
            # print(filename)
            STDscore = []
            with open(filename, 'r', encoding='utf-8-sig') as file:
                rows = csv.reader(file)
            #     for row in rows:
            # rows=csv.reader(filename)

            #----------------------------
                for row in rows:
                    print(row)
                    for j in row:
                        if "CB" in j:
                            print(j)
                            print(row.index(j))
                            Scorecol=row.index(j)
                            print(Scorecol)
                    STDscore.append(row[Scorecol])

            print("STDscore")
            print(STDscore)
            #STDscore将CSV中数据放到STD数组中STDscore[1:]
            os.chdir(exepath)
            workbook=openpyxl.load_workbook(r'xxx.xlsx')
            print(workbook.sheetnames)
            cinebench20sheet=workbook.get_sheet_by_name(Tosheetname)
            for i in range(1,len(STDscore)):
                sheet.cell(i+2,3).value=float(STDscore[i])

            workbook.save(r'%s\xxxxx.xlsx'%exepath)
            workbook.close()

def cinbenchtochart_P():
   
    R20path=exepath+'\\R20\\'
    os.chdir(20path)
    csvpath=os.listdir(R20path)
    for i in csvpath:
        if 'R20P' in i:
            #ToSheetname是保存的sheet的名字
            Tosheetname=(i.split(' ')[0]+'_SKU'+list(i.split(' ')[1])[0]+'#'+list(i.split(' ')[1])[1]+list(i.split(' ')[1])[2])
            print(Tosheetname)
            #CSVpath是CSV的路径
            csvpath=20path+'\%s\\'%i
            print(csvpath)
            os.chdir(csvpath)
            for i in os.listdir(csvpath):
                if 'xxxx' in i and'.csv' in i:
                    filename=i
                    print(filename)
            # filename=open(r'xxxxx.csv'%username)
            # print(filename)
            STDscore = []
            with open(filename, 'r', encoding='utf-8-sig') as file:
                rows = csv.reader(file)
            #     for row in rows:
            # rows=csv.reader(filename)

            #----------------------------
                for row in rows:
                    print(row)
                    for j in row:
                        if "CB" in j:
                            print(j)
                            print(row.index(j))
                            Scorecol=row.index(j)
                            print(Scorecol)
                    STDscore.append(row[Scorecol])

            print("STDscore")
            print(STDscore)
            #STDscore将CSV中数据放到STD数组中STDscore[1:]
            os.chdir(exepath)
            workbook=openpyxl.load_workbook(r'xxxx.xlsx')
            print(workbook.sheetnames)
            sheet=workbook.get_sheet_by_name(Tosheetname)
            for i in range(1,len(STDscore)):
                cinebench20sheet.cell(i+2,2).value=float(STDscore[i])

            workbook.save(r'%s\xxxx.xlsx'%exepath)
            workbook.close()

def createCinbenchChart(sheetname):
    # 打开Excel文件
    # exepath=os.path.dirname(sys.executable)
    os.chdir(exepath)
    wb = load_workbook('xxxx.xlsx', data_only=False)
    # 获取Sheet对象
    ws = wb.get_sheet_by_name(sheetname)

    chart = LineChart()  # 折线图
    chart.title = 'xxxx'  # 图表的标题
    # 设置图表数据范围，最大行为31
    # min_col分类起始列，min_row:分类的行，max_cal：取到第几列
    # 第三步：选择X、Y轴数据，并创建成一个数据系列
    values = Reference(ws, min_col=2, min_row=2, max_col=3, max_row=32)
    labels = Reference(ws, min_col=1, min_row=3, max_col=1, max_row=32)
    chart.add_data(values, titles_from_data=True)
     #legend和majorGridlines属性，可以关闭图例和主要网格线
    chart.legend.position = "b"  # 将图例设置在底部(r，l，t，b，和tr分别右，右，左，顶部，底部和顶部。openpyxl默认值为r。)
    chart.set_categories(labels)
    # 设置折线的样式
    s1 = chart.series[0]
    s1.marker.symbol = 'circle'
    s1 = chart.series[1]
    s1.marker.symbol = 'circle'
    # 插入位置
    ws.add_chart(chart, "E3")
    # wb.save("chart_layout.xlsx")
    # 保存工作簿
    # wb.save("chart.xlsx")
    wb.save('xxxx.xlsx')
    wb.close()