python绘制excel表格曲线

最新推荐文章于 2025-05-31 06:00:00 发布

FantasticStone

最新推荐文章于 2025-05-31 06:00:00 发布

阅读量3.4k

点赞数

CC 4.0 BY-SA版权

分类专栏：语言类文章标签： python

本文链接：https://blog.youkuaiyun.com/kernelke/article/details/12516135

语言类专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一个使用Python库openpyx1实现Excel文件自动化创建及图表绘制的脚本。该脚本能够解析指定的文本文件，从中抽取关键词列并将其导入Excel工作表中。此外，它还能根据指定的数据列自动生成散点图，并调整图表样式与数据范围。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import os,sys,math
from openpyx1.workbook import Workbook
from openpyx1.writer.excel import save_workbook
from openpyx1.chart import ScatterChart, Serie, Reference, LineChart, BarChart

global g_LineNum
g_LineNum = 0
global g_colNum
g_colNum = 0

#print Release Version
print "==========Version: Release V1.0.0============="

#Parase Keywords Lis
keyWordsList = ["TxSINR", "BLER"]

#Draw chart depend column list[X_axis, Y_axis]
chartList = ["TxSINR", "BLER"]

#Save keywords column number in *.txt file
colNumList = []

#describe X_axis, Y_axis column in excel [emluX, emluY]
XYColList = []

#emluatorString
emluatorString = "test"

#get current system
curPath = os.getcwd()
parPath = os.path.baspath(os.path.join(os.path.dirname(''), os.path.pardir))

#ReadResultFileName
Resultfilename = "test.txt"

#new excel work book
workBook = Workbook()

def listFiles():
    SaveData(Resultfilename)
    try:
        workBook.save(r'test.xlsx')
    except IOError:
        print "ERROR:Please Close Excel File<Exit>"
        exit(1)

def SaveData(filename):
workSheet = workBook.creat_sheet()
titleName = fileName[0:-4] #drop ".txt"

    if (len(titleName) > 30):
        workSheet.title = titleName[0:30]
    else:
        workSheet.title = titleName

#===========Save data to .xlsx==============
workSheet.cell(row=0, column=0).value = emluatorString

    #============clean list======================
    del colNumlist[0:len(colNumlist)]
    del XYColList[0:len(XYColList)]

    #=============Parase Result File==============
    try:
        fileOpen = open("c:\\" + Resultfilename)
        g_LineNum = 1
        for line in fileOpen:
            chLine = line.split()
            g_colNum = len(chLine)
            #get the column number config in keyWordsList
            if (g_LineNum == 1):
                for i in range(0, len(chLine)):
                    for keyword in keyWordsList:
                        if (keyword == chLine[i]):
                            colNumList.append(i)
                            workSheet.cell(row=g_LineNum, column=(len(colNumList)-1)).value = chLine[i]
                            for chartName in chartList:
                                if (chartName == keyword):
                                    XYColList.append(len(colNumList)-1)
            elif (g_LineNum%2 == 0):
            #else:
                for i in range(0, len(chLine)):
                    for index in colNumList:
                        if (i == index):
                            workSheet.cell(row=((g_lineNum/2)+1), column=colNumList.index(index)).value = chLine[i]
            g_LineNum = g_LineNum + 1
          fileOpen.close()
        except IOError:
            print "Not Found Debug File:" + "c:\\" + Resultfilename
            exit(1)

        #================draw graph========================
        workSheet.merge_cells(range_string="A1:B1")
        workSheet.merge_cells(range_string="C1:D1")

xyserie1 = Serie(Reference(workSheet, (2,XYColList[1]), ((g_LineNum/2)+1,XYColList[1])), \
xvalues=Reference(workSheet,(2,XYColList[0]), ((g_LineNum/2)+1, XYColList[0])), legend=Reference(workSheet, (0,0)))

        debugXmax = 0
        debugYmax = 0
        debugXmin = 0
        debugYmin = 0

        Xmin = 0
        Ymin = 0
        Xmax = 0
        Ymax = 0

Xunit = 0
Yunit = 0

debugXmin = debugXmax = workSheet.cell(row=2, column=XYColList[0]).value
debugYmin = debugYmax = workSheet.cell(row=2, column=XYColList[1]).value

        for i range(3, (g_LineNum/2)+2):
            if debugXmin > workSheet.cell(row=i, column=XYColList[0]).value:
                debugXmin = workSheet.cell(row=i, column=XYColList[0]).value
            if debugXmax < workSheet.cell(row=i, column=XYColList[0]).value:
                debugXmax = workSheet.cell(row=i, column=XYColList[0]).value
            if debugYmin > workSheet.cell(row=i, column=XYColList[1]).value:
                debugYmin = workSheet.cell(row=i, column=XYColList[1]).value
            if debugYMax < workSheet.cell(row=i, column=XYColList[1]).value:
                debugYMax = workSheet.cell(row=i, column=XYColList[1]).value

        Xmin = debugXmin
        Ymin = debugYmin
        Xmax = debugXmax
        Ymax = debugYmax

        if abs(Xmax) < 1:
            Xuint = 0.2
        else:
            Xuint = math.ceil((Xmax - Xmin) / 10)

        if abs(Ymax) < 1:
            Yuint = 0.2
        else:
            Yuint = math.ceil((Ymax - Ymin) / 10)

#scatterchart = ScatterChart()
xyserie1.marker = "auto"

scatterchart = ScatterChart()
scatterchart.add_serie(xyserie1)

        scatterchart.x_axis.min = Xmin
        scatterchart.x_axis.max = Xmax
        scatterchart.x_axis.unit = Xunit
        scatterchart.y_axis.min = 0
        scatterchart.y_axis.max = Ymax + 0.2
        scatterchart.y_axis.uint = Yunit

        #graph area
        scatterchart.drawing.left = 200
        scatterchart.drawint.top = 50
        scatterchart.drawing.height = 600
        scatterchart.drawing.width = 1000

        #present
        scatterchart.width = .9
        scatterchart.height = .9
        scatterchart.margin_top = .1
        workSheet.add_chart(scatterchart)

        #=============clean list==================
        del colNumList[0:len(colNumList)]
        del XYColList[0:len(XYColList)]

if __name__ == '__main__':
ListFiles()