Python生成Excel工作表时重复插入首轮图表问题求助

我编写了一段Python代码，用于获取用户输入数据并生成Excel表格，每组数据对应独立Worksheet。文本数据可正常生成，但图表存在异常：所有工作表插入的都是第一轮循环生成的图表。循环结束后图表文件会被删除，但我认为这并非问题根源。

代码如下：

import xlsxwriter
import matplotlib.pyplot as plt
import numpy as np
import os

num = int(input('Input amount of registers: '))
x = 1
x_ind = np.arange(x)

workbook = xlsxwriter.Workbook('Registers.xlsx')

for i in range(num):
    worksheet = workbook.add_worksheet()
    a1 = int(input('Input Power Level 1: '))
    a2 = int(input('Input Min: '))
    a3 = int(input('Input Max: '))
    b1 = int(input('Input Power Level 2: '))
    b2 = int(input('Input Min: '))
    b3 = int(input('Input Max: '))
    c1 = int(input('Input Power Level 3: '))
    c2 = int(input('Input Min: '))
    c3 = int(input('Input Max: '))
    d1 = int(input('Input Power Level 4: ' ))
    d2 = int(input('Input Min: '))
    d3 = int(input('Input Max: '))
    
    row = 0
    
    col1 = workbook.add_format({'bg_color':'red'})
    col2 = workbook.add_format({'bg_color':'green'})
    
    #PW1
    worksheet.write(row, 0, "Input Power Level 1")
    worksheet.write(row, 1, a1)
    worksheet.write(row, 2, "ALARM 1")
    worksheet.write(row, 3, '', col1)
    worksheet.write(row, 4, 'Min Limit')
    worksheet.write(row, 5, a2)
    worksheet.write(row, 6, 'Max Limit')
    worksheet.write(row, 7, a3)
    #Bar1
    pr = 0
    if a2 < 0 and a3 < 0:
        a21 = a2 + (a2*(-1))
        a31 = a3 + (a2*(-1))
        a11 = a1 + (a2*(-1))
        pr = a11/a31 * 100
    elif a2 < 0 and a3 >= 0 and a1 >= 0:
        a21 = a2 + (a2*(-1))
        a31 = a3 + (a2*(-1))
        a11 = a1 + (a2*(-1))
        pr = a11/a31 * 100
    elif a2 < 0 and a3 >= 0 and a1 < 0:
        a21 = a2 + (a2*(-1))
        a31 = a3 + (a2*(-1))
        a11 = a1 + (a2*(-1))
        pr = a11/a31 * 100
    elif a2 >= 0 and a3 >= 0 and a1 >= 0:
        pr = a1/a3 * 100       
    pr = round(pr, 1)
    plt.subplot(1,4,1)
    if a2 < 0 and a3 < 0:
        a22 = a2 + (a2*(-1))
        a32 = a3 + (a2*(-1))
        a12 = a1 + (a2*(-1))
        plt.bar(x_ind, a32, color='white')
        plt.bar(x_ind, a12 , color='purple')
    elif a2 < 0 and a3 >= 0 and a1 >= 0:
        a22 = a2 + (a2*(-1))
        a32 = a3 + (a2*(-1))
        a12 = a1 + (a2*(-1))
        plt.bar(x_ind, a32, color='white')
        plt.bar(x_ind, a12 , color='purple')
    elif a2 < 0 and a3 >= 0 and a1 < 0:
        a22 = a2 + (a2*(-1))
        a32 = a3 + (a2*(-1))
        a12 = a1 + (a2*(-1))
        plt.bar(x_ind, a32, color='white')
        plt.bar(x_ind, a12 , color='purple')
    elif a2 >= 0 and a3 >= 0 and a1 >= 0:
        plt.bar(x_ind, a3, color='white')
        plt.bar(x_ind, a1 , color='purple')

    plt.xticks(x_ind, [a1])
    plt.yticks(x_ind, [pr])
    plt.xlabel('Power Level 1, dBm')
    plt.ylabel('Percent of completion, %')
    row += 1
    
    #PW2
    worksheet.write(row, 0, "Input Power Level 2")
    worksheet.write(row, 1, b1)
    worksheet.write(row, 2, "ALARM 2")
    worksheet.write(row, 3, '', col2)
    worksheet.write(row, 4, 'Min Limit')
    worksheet.write(row, 5, b2)
    worksheet.write(row, 6, 'Max Limit')
    worksheet.write(row, 7, b3)
    #Bar2
    pr = 0
    if b2 < 0 and b3 < 0:
        b21 = b2 + (b2*(-1))
        b31 = b3 + (b2*(-1))
        b11 = b1 + (b2*(-1))
        pr = b11/b31 * 100
    elif b2 < 0 and b3 >= 0 and b1 >= 0:
        b21 = b2 + (b2*(-1))
        b31 = b3 + (b2*(-1))
        b11 = b1 + (b2*(-1))
        pr = b11/b31 * 100
    elif b2 < 0 and b3 >= 0 and b1 < 0:
        b21 = b2 + (b2*(-1))
        b31 = b3 + (b2*(-1))
        b11 = b1 + (b2*(-1))
        pr = b11/b31 * 100
    elif b2 >= 0 and b3 >= 0 and b1 >= 0:
        pr = b1/b3 * 100
    pr = round(pr, 1)
    plt.subplot(1,4,2)
    if b2 < 0 and b3 < 0:
        b22 = b2 + (b2*(-1))
        b32 = b3 + (b2*(-1))
        b12 = b1 + (b2*(-1))
        plt.bar(x_ind, b32, color='white')
        plt.bar(x_ind, b12 , color='purple')
    elif b2 < 0 and b3 >= 0 and b1 >= 0:
        b22 = b2 + (b2*(-1))
        b32 = b3 + (b2*(-1))
        b12 = b1 + (b2*(-1))
        plt.bar(x_ind, b32, color='white')
        plt.bar(x_ind, b12 , color='purple')
    elif b2 < 0 and b3 >= 0 and b1 < 0:
        b22 = b2 + (b2*(-1))
        b32 = b3 + (b2*(-1))
        b12 = b1 + (b2*(-1))
        plt.bar(x_ind, b32, color='white')
        plt.bar(x_ind, b12 , color='purple')
    elif b2 >= 0 and b3 >= 0 and b1 >= 0:
        plt.bar(x_ind, b3, color='white')
        plt.bar(x_ind, b1 , color='purple')

    plt.xticks(x_ind, [b1])
    plt.yticks(x_ind, [pr])
    plt.xlabel('Power Level 2, dBm')
    plt.ylabel('')
    row += 1
    
    #PW3
    worksheet.write(row, 0, "Input Power Level 3")
    worksheet.write(row, 1, c1)
    worksheet.write(row, 2, "ALARM 3")
    worksheet.write(row, 3, '', col1)
    worksheet.write(row, 4, 'Min Limit')
    worksheet.write(row, 5, c2)
    worksheet.write(row, 6, 'Max Limit')
    worksheet.write(row, 7, c3)
    #Bar3 
    pr = 0
    if c2 < 0 and c3 < 0:
        c21 = c2 + (c2*(-1))
        c31 = c3 + (c2*(-1))
        c11 = c1 + (c2*(-1))
        pr = c11/c31 * 100
    elif c2 < 0 and c3 >= 0 and c1 >= 0:
        c21 = c2 + (c2*(-1))
        c31 = c3 + (c2*(-1))
        c11 = c1 + (c2*(-1))
        pr = c11/c31 * 100
    elif c2 < 0 and c3 >= 0 and c1 < 0:
        c21 = c2 + (c2*(-1))
        c31 = c3 + (c2*(-1))
        c11 = c1 + (c2*(-1))
        pr = c11/c31 * 100
    elif c2 >= 0 and c3 >= 0 and c1 >= 0:
        pr = c1/c3 * 100    
    pr = round(pr, 1)
    plt.subplot(1,4,3)
    if c2 < 0 and c3 < 0:
        c22 = c2 + (c2*(-1))
        c32 = c3 + (c2*(-1))
        c12 = c1 + (c2*(-1))
        plt.bar(x_ind, c32, color='white')
        plt.bar(x_ind, c12 , color='purple')
    elif c2 < 0 and c3 >= 0 and c1 >= 0:
        c22 = c2 + (c2*(-1))
        c32 = c3 + (c2*(-1))
        c12 = c1 + (c2*(-1))
        plt.bar(x_ind, c32, color='white')
        plt.bar(x_ind, c12 , color='purple')
    elif c2 < 0 and c3 >= 0 and c1 < 0:
        c22 = c2 + (c2*(-1))
        c32 = c3 + (c2*(-1))
        c12 = c1 + (c2*(-1))
        plt.bar(x_ind, c32, color='white')
        plt.bar(x_ind, c12 , color='purple')
    elif c2 >= 0 and c3 >= 0 and c1 >= 0:
        plt.bar(x_ind, c3, color='white')
        plt.bar(x_ind, c1 , color='purple')

    plt.xticks(x_ind, [c1])
    plt.yticks(x_ind, [pr])
    plt.xlabel('Power Level 3, dBm')
    plt.ylabel('')
    row += 1
    
    #PW4
    worksheet.write(row, 0, "Input Power Level 4")
    worksheet.write(row, 1, d1)
    worksheet.write(row, 2, "ALARM 4")
    worksheet.write(row, 3, '', col2)
    worksheet.write(row, 4, 'Min Limit')
    worksheet.write(row, 5, d2)
    worksheet.write(row, 6, 'Max Limit')
    worksheet.write(row, 7, d3)
    #Bar4
    pr = 0
    if d2 < 0 and d3 < 0:
        d21 = d2 + (d2*(-1))
        d31 = d3 + (d2*(-1))
        d11 = d1 + (d2*(-1))
        pr = d11/d31 * 100
    elif d2 < 0 and d3 >= 0 and d1 >= 0:
        d21 = d2 + (d2*(-1))
        d31 = d3 + (d2*(-1))
        d11 = d1 + (d2*(-1))
        pr = d11/d31 * 100
    elif d2 < 0 and d3 >= 0 and d1 < 0:
        d21 = d2 + (d2*(-1))
        d31 = d3 + (d2*(-1))
        d11 = d1 + (d2*(-1))
        pr = d11/d31 * 100
    elif d2 >= 0 and d3 >= 0 and d1 >= 0:
        pr = d1/d3 * 100    
    pr = round(pr, 1)
    plt.subplot(1,4,4)
    if d2 < 0 and d3 < 0:
        d22 = d2 + (d2*(-1))
        d32 = d3 + (d2*(-1))
        d12 = d1 + (d2*(-1))
        plt.bar(x_ind, d32, color='white')
        plt.bar(x_ind, d12 , color='purple')
    elif d2 < 0 and d3 >= 0 and d1 >= 0:
        d22 = d2 + (d2*(-1))
        d32 = d3 + (d2*(-1))
        d12 = d1 + (d2*(-1))
        plt.bar(x_ind, d32, color='white')
        plt.bar(x_ind, d12 , color='purple')
    elif d2 < 0 and d3 >= 0 and d1 < 0:
        d22 = d2 + (d2*(-1))
        d32 = d3 + (d2*(-1))
        d12 = d1 + (d2*(-1))
        plt.bar(x_ind, d32, color='white')
        plt.bar(x_ind, d12 , color='purple')
    elif d2 >= 0 and d3 >= 0 and d1 >= 0:
        plt.bar(x_ind, d3, color='white')
        plt.bar(x_ind, d1 , color='purple')

    plt.xticks(x_ind, [d1])
    plt.yticks(x_ind, [pr])
    plt.xlabel('Power Level 4, dBm')
    plt.ylabel('')
    
    plt.tight_layout(pad=3.0)
    plt.savefig('Registers.png')
    
    row += 2
    worksheet.insert_image(row, 0, 'Registers.png')
            
workbook.close()  

问题分析 #

1. Matplotlib绘图状态未重置：循环中持续使用同一个全局figure对象，新的绘图会叠加在旧内容上，或残留之前的绘图参数，导致保存的图片不符合当前循环的输入数据。
2. Xlsxwriter图片缓存机制：当重复使用同一个图片文件名时，xlsxwriter会缓存第一次读取的图片二进制数据，后续插入操作直接复用缓存，而非读取更新后的文件内容。

修复方案 #

关键修改点 #

1. 每次循环创建新的Matplotlib Figure：在循环开始时调用plt.figure()，确保每次绘图都使用全新的上下文，避免状态残留。
2. 使用唯一的图片文件名：通过循环索引i生成唯一文件名（如f'Registers_{i}.png'），避免文件覆盖引发的缓存问题。
3. 及时关闭Figure并清理临时文件：插入图片后关闭当前Figure释放资源，同时删除临时图片文件，避免磁盘残留。

修复后的代码 #

import xlsxwriter
import matplotlib.pyplot as plt
import numpy as np
import os

num = int(input('输入寄存器组数: '))
x = 1
x_ind = np.arange(x)

workbook = xlsxwriter.Workbook('Registers.xlsx')

for i in range(num):
    # 创建新的绘图上下文
    plt.figure()
    worksheet = workbook.add_worksheet()
    
    # 读取用户输入（保持原有逻辑）
    a1 = int(input('输入功率等级1: '))
    a2 = int(input('输入最小值: '))
    a3 = int(input('输入最大值: '))
    b1 = int(input('输入功率等级2: '))
    b2 =