绘制使用openpyxl的折线图-轴/绘图问题

Question

我在获取Openpyxl的LineChart（）功能时遇到问题，无法以我喜欢的方式绘制图表。在

我一直在使用the official page的文档，但是我得到了。在

这是所需的结果（忽略颜色/格式，只需获得正确的数据点，然后我就可以设置样式）：

我尝试过按照文档中演示的方式将数据重新排列到主列表中的垂直切片中，但是我不明白图形实际上是如何使用

for i in masterList:
    #print ("appending ", i, "to the sheet")
    sheet.append(i)

节，以及以下行：

^{pr2}$

整个功能如下。version=“v1.9”，currentCell=我们有数据的日期数，sheet是工作簿中当前活动的工作表。在

def drawChart(self, sheet, currentCell, version):
    print ("CurrentCell = ", currentCell)
    ### Get the chart data
    dateData, versionData, versionXABData = ([] for i in range(3)) #Make 3 lists 
    for i in range(currentCell):
        temp = sheet.cell(row = 7, column = 4+i).value
        if not temp: 
            temp = 0
            dateData.append(temp)
        else: dateData.append(temp) #Put the dates in a list

    for i in range(currentCell):
        temp = sheet.cell(row = 28, column = 4+i).value
        if not temp: 
            temp = 0
            versionData.append(temp)
        else: versionData.append(temp) #Put the version Totals in another

    for i in range(currentCell):
        temp = sheet.cell(row = 27, column = 4+i).value
        if not temp: 
            temp = 0
            versionXABData.append(temp)
        else: versionXABData.append(temp) #Put the version XAB bugs in another

    print ("Dates are: ", dateData, '\n', "VersionData is: ",versionData, '\n', "Version XAB is: ", versionXABData, '\n')

    masterList = [list() for i in range(currentCell)] #Make a list containing the total number of empty lists for each day we have data for
    masterList[0].append("Date")
    masterList[0].append("Total "+ version +" Bugs")
    masterList[0].append("Total "+ version +" XAB Bugs")
    print (masterList[0])


    for i in range(1, currentCell):
        #print (" Length of dataData = ", len(dateData), '\n', "Length of versionData = ", len(versionData), '\n', "Length of versionXABData = ", len(versionXABData), '\n',"i = ", i)
        masterList[i].append(dateData[i])
        masterList[i].append(versionData[i])
        masterList[i].append(versionXABData[i])

    for i in masterList:
        #print ("Appending ", i, "to the sheet")
        sheet.append(i)

    chart1 = LineChart()
    chart1.title = "DoT Bug Burndown"
    chart1.style = 13
    chart1.y_axis.title = "No of Bugs"
    chart1.x_axis.title = "Date"
    chart1.width = 30
    chart1.height = 20

    data = Reference(sheet, min_col = 4, min_row = 7, max_col = currentCell, max_row = 28)
    chart1.add_data(data, titles_from_data=True)
    sheet.add_chart(chart1, "K31")

Answer 1

通过在读取新数据后将新数据写入新的工作表来解决这个问题。在

它最初的方式意味着它没有使用正确的轴方向，因此下面的修订代码将主列表的内容附加到工作簿的新工作表中，以数据列（而不是行）的形式出现，如支持文档的折线图示例所示。在

def drawChart(self, sheet, sheet2, currentCell, version):
    print ("CurrentCell = ", currentCell)
    ### Get the chart data
    dateData, versionData, versionXABData = ([] for i in range(3)) #Make 3 lists 
    for i in range(currentCell):
        temp1 = sheet.cell(row = 7, column = 4+i).value
        temp2 = str(temp1)
        temp3 = temp2[:10]
        if not temp1: 
            temp2 = 0
            dateData.append(temp3)
        else: dateData.append(temp3) #Put the dates in a list

    for i in range(currentCell):
        temp = sheet.cell(row = 28, column = 4+i).value
        if not temp: 
            temp = 0
            versionData.append(temp)
        else: versionData.append(temp) #Put the version Totals in another

    for i in range(currentCell):
        temp = sheet.cell(row = 27, column = 4+i).value
        if not temp: 
            temp = 0
            versionXABData.append(temp)
        else: versionXABData.append(temp) #Put the version XAB bugs in another

    print ("Dates are: ", dateData, '\n', "VersionData is: ",versionData, '\n', "Version XAB is: ", versionXABData, '\n')

    masterList = [list() for i in range(currentCell)] #Make a list containing the total number of empty lists for each day we have data for
    masterList[0].append("Date")
    masterList[0].append("Total "+ version +" Bugs")
    masterList[0].append("Total "+ version +" XAB Bugs")

    print ("MasterList = ", masterList[0])

    for i in range(1, currentCell):
        #print (" Length of dataData = ", len(dateData), '\n', "Length of versionData = ", len(versionData), '\n', "Length of versionXABData = ", len(versionXABData), '\n',"i = ", i)
        masterList[i].append(dateData[i])
        masterList[i].append(versionData[i])
        masterList[i].append(versionXABData[i])




    for i in masterList:
        print ("Appending ", i, "to the sheet")
        sheet2.append(i)

    chart1 = LineChart()
    chart1.title = version + " Bug Burndown"
    chart1.style = 2

    chart1.y_axis.title = "No of Bugs"
    chart1.width = 30
    chart1.height = 20




    data = Reference(sheet2, min_col=2, min_row=1, max_col=3, max_row=36)
    chart1.add_data(data, titles_from_data=True)

    #s = Series(y, xvalues = x)

    dates = Reference(sheet2, min_col=1, min_row=2, max_row=36)
    chart1.set_categories(dates)

    chart1.legend.position = 'b'

    s1 = chart1.series[0]
    s2 = chart1.series[1]
    s1.graphicalProperties.line.width = 50000
    s2.graphicalProperties.line.width = 50000