时间戳

首页 > 解决方案 > TypeError: Value must be a sequence - Python/Pandas/OpenPyXL

python - TypeError: Value must be a sequence - Python/Pandas/OpenPyXL

问题描述

我对 Python 和一般编码非常陌生（学习 1.5 周）。

我对这段代码的目标是打开一个包含许多工作表的文件。但我只会使用其中两张。我想将一列数据从 sheet2 复制到 sheet1。然后，我想将包括所有其他工作表在内的整个工作簿保存为具有新名称的新文件。由于 sheet2 中有 7 列数据，并且我一次只将一列迭代到 sheet1，因此我最终应该得到 7 个具有 7 个单独文件名的单独文件。

我一直在使用一个非常简单的模拟文件来练习代码。有效！

但是当我尝试使用包含许多工作表和数据的真实文件时，我收到了这个错误：

TypeError：值必须是一个序列。

Traceback (most recent call last):
  File "C:\Users\PycharmProjects\CopyFile\main.py", line 11, in <module>
    wb_master = xl.load_workbook('Master.xlsx')
  File "C:\Users\PycharmProjects\CopyFile\venv\lib\site-packages\openpyxl\reader\excel.py", line 315, in load_workbook
    reader.read()
  File "C:\Users\PycharmProjects\CopyFile\venv\lib\site-packages\openpyxl\reader\excel.py", line 280, in read
    self.read_worksheets()
  File "C:\Users\PycharmProjects\CopyFile\venv\lib\site-packages\openpyxl\reader\excel.py", line 266, in read_worksheets
    pivot = TableDefinition.from_tree(tree)
  File "C:\Users\PycharmProjects\CopyFile\venv\lib\site-packages\openpyxl\descriptors\serialisable.py", line 83, in from_tree
    obj = desc.from_tree(el)
  File "C:\Users\PycharmProjects\CopyFile\venv\lib\site-packages\openpyxl\descriptors\sequence.py", line 85, in from_tree
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\Users\PycharmProjects\CopyFile\venv\lib\site-packages\openpyxl\desciptors\sequence.py", line 85, in <listcomp>
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\Users\PycharmProjects\CopyFile\venv\lib\site-packages\openpyxl\descriptors\serialisable.py", line 103, in from_tree
    return cls(**attrib)
  File "C:\Users\PycharmProjects\CopyFile\venv\lib\site-packages\openpyxl\pivot\table.py", line 601, in __init__
    self.x = x
  File "C:\Users\Kevin\PycharmProjects\CopyFile\venv\lib\site-packages\openpyxl\descriptors\sequence.py", line 25, in __set__
    raise TypeError("Value must be a sequence")
TypeError: Value must be a sequence

这是我的代码：

import pandas as pd
import openpyxl as xl
from shutil import copyfile

df_aging = pd.read_excel('Master.xlsx', sheet_name='Aging', skiprows=1, usecols='C:I')
df_support = pd.read_excel('Master.xlsx', sheet_name='Supporting Items Import', usecols='A:E')

aging_col = df_aging.columns

master_file = 'Master.xlsx'
wb_master = xl.load_workbook('Master.xlsx')
ws_master = wb_master.worksheets[0]

gl_dict = {11111: 'DESCRIPTION 1',
           22222: 'DESCRIPTION 2',
           33333: 'DESCRIPTION 3',
           44444: 'DESCRIPTION 4',
           55555: 'DESCRIPTION 5',
           66666: 'DESCRIPTION 6',
           77777: 'DESCRIPTION 7'}

file_name_dict = {11111: 'FILENAME 1',
                  22222: 'FILENAME 2',
                  33333: 'FILENAME 3',
                  44444: 'FILENAME 4',
                  55555: 'FILENAME 5',
                  66666: 'FILENAME 6',
                  77777: 'FILENAME 7'}

month = input("Enter month (##): ")
year = input("Enter year (####): ")

for col in aging_col:
    output_file = f'{month}-{year} {file_name_dict[col]}.xlsx'
    copyfile(master_file, output_file)
    for x in range(0, 5):
        wb_output = xl.load_workbook(output_file)
        ws_output = wb_output.worksheets[1]
        df_support['Amount'] = df_aging[col]
        ws_output.cell(row=x+5, column=4).value = df_support['Amount'][x]
        ws_output.cell(row=x+5, column=5).value = gl_dict[col]
        wb_output.save(output_file)

标签： pythonexcelpandasopenpyxl

解决方案

尝试指定文件路径，如下所示：

wb = openpyxl.load_workbook(filename='C:\Users\filepath\Master.xlsx')

推荐阅读