时间戳

首页 > 解决方案 > 用于 openpyxl 多个图表创建的 for 循环

python - 用于 openpyxl 多个图表创建的 for 循环

问题描述

我正在尝试创建一个for循环以一次在 openpyxl 中创建多个折线图。数组中的某些索引将是图表从中提取数据的数据的书挡。这在openpyxl中可能吗？

我在 excel 电子表格中的数据如下所示：

1          Time        Battery Voltage
2 2019-06-05 00:00:00      45
3 2019-06-05 00:01:50      49
4 2019-06-05 00:02:30      51
5 2019-06-05 00:04:58      34
...

import os
import openpyxl
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference, Series
from openpyxl.chart.axis import DateAxis
from datetime import date, datetime, timedelta, time

os.chdir('C:\\Users\user\test')
wb = openpyxl.load_workbook('log.xlsx')
sheet = wb['sheet2']
ws2 = wb['sheet2']

graphIntervals = [0,50,51,100,101,150] # filled with tuples of two integers, 
# representing the top-left and bottom right of the rectangular 
# selection of cells containing chart data I'm trying to graph

starts = graphIntervals[::2]
ends = graphIntervals[1::2]

for i in graphIntervals:
    c[i] = LineChart()
    c[i].title = "Chart Title"
    c[i].style = 12
    c[i].y_axis.crossAx = 500
    c[i].x_axis = DateAxis(crossAx=100)
    c[i].x_axis.number_format = 'd-HH-MM-SS'
    c[i].x_axis.majorTimeUnit = "days"

    c[i].y_axis.title = "Battery Voltage"
    c[i].x_axis.title = "Time"

    data = Reference(ws2, min_col=2, min_row=starts, max_col=2, max_row=ends)
    c[i].add_data(data, titles_from_data=True)
    dates = Reference(ws2, min_col=1, min_row=starts, max_row=ends)
    c[i].set_categories(dates)


    s[i] = c[i].series[0]
    s[i].graphicalProperties.line.solidFill = "BE4B48"
    s[i].graphicalProperties.line.width = 25000 # width in EMUs.
    s[i].smooth = True # Make the line smooth
    ws2.add_chart(c[i], "C[i+15]") # +15 for spacing
    wb.save('log.xlsx')

理想情况下，我最终会制作（尽管 graphIntervals/2 中有许多值）图表。

我知道我需要合并zip()到我的data变量中，否则它无法继续使用下一组值来创建电子表格。我认为它会像zip(starts, ends)但我不确定。

通过openpyxl可以实现这些吗？虽然我还没有找到，但有人有我可以参考的例子吗？

标签： pythonexcelopenpyxl

解决方案

遵循评论中的建议。这是在 for 循环中调用的函数：

for i in range(0, len(graphIntervals), 2):
    min_row = graphIntervals[i] + 1
    max_row = graphIntervals[i+1] + 1

    # skip headers on first row
    if min_row == 1:
        min_row = 2

    dates = chart.Reference(ws2, min_col=1, min_row=min_row, max_row=max_row)
    vBat = chart.Reference(ws2, min_col=2, min_row=min_row, max_col=2, max_row=max_row)
    qBat = chart.Reference(ws2, min_col=3, min_row=min_row, max_col=3, max_row=max_row)

推荐阅读