如何解决Openpyxl 3.1.1读取透视表时的TypeError: expected <class 'float'>错误

问题描述 #

我使用openpyxl 3.1.1读取源工作簿，仅需提取单元格显示值而非底层公式，却遭遇TypeError: expected <class 'float'>错误。我尝试用以下代码将所有值为"-"的单元格转换为0，但问题仍未解决：

source_row_data = [float(col_value) if col_value != "-" else 0 for col_value in source_row_data]

想请教：这个方案是否正确？数据存储在透视表中是不是导致问题的原因？

完整报错信息 #

File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\descriptors\base.py", line 59, in _convert  
    value = expected_type(value)
            ^^^^^^^^^^^^^^^^^^^^
ValueError: could not convert string to float: '-'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\Kyle\DEV\Satori\Attribution Analysis\UpdatePortfolio.py", line 37, in <module>
    wb = openpyxl.load_workbook(wb_name, data_only=True)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\reader\excel.py", line 346, in load_workbook
    reader.read()
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\reader\excel.py", line 301, in read
    self.read_worksheets()
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\reader\excel.py", line 278, in read_worksheets
    pivot.cache = self.parser.pivot_caches[pivot.cacheId]
                  ^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\reader\workbook.py", line 131, in pivot_caches
    records = get_rel(self.archive, cache.deps, cache.id, RecordList)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\packaging\relationship.py", line 163, in get_rel
    obj = cls.from_tree(tree)
          ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\descriptors\serialisable.py", line 87, in from_tree
    obj = desc.expected_type.from_tree(el)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\descriptors\serialisable.py", line 87, in from_tree
    obj = desc.expected_type.from_tree(el)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\descriptors\serialisable.py", line 103, in from_tree
    return cls(**attrib)
           ^^^^^^^^^^^^^
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\pivot\fields.py", line 147, in __init__
    self.v = v
    ^^^^^^
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\descriptors\base.py", line 71, in __set__
    value = _convert(self.expected_type, value)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Kyle\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\descriptors\base.py", line 61, in _convert
    raise TypeError('expected ' + str(expected_type))
TypeError: expected <class 'float'>

完整代码 #

import openpyxl
import calendar


# Open the destination workbook
try:
    dest_wb = openpyxl.load_workbook('Liquid Portfolio Attribution Analysis - Blank.xlsx')
except ValueError as e:
    print(e)
    print("Error occurred in cell:", e.cell.coordinate)
    print("Cell value:", e.cell.value)    

# Define the destination sheet and the starting row
dest_sheet = dest_wb['Data']

def get_maximum_rows(*, sheet_object):
    rows = 0
    for max_row, row in enumerate(sheet_object, 1):
        if not all(col.value is None for col in row):
            rows += 1
    return rows

# Get the maximum row with data in the destination sheet
dest_last_row = get_maximum_rows(sheet_object=dest_sheet)

# Set the starting row for copying data in the destination sheet
if dest_last_row == 4:
    dest_row = 5
else:
    dest_row = dest_last_row + 1

# Loop through each set of workbooks
for month in ['04']:
    for alpha in ['I', 'II']:
        # Open the source workbook
        wb_name = f'Copy of 2022-{month} Alpha {alpha} - Workbook.xlsx'
        wb = openpyxl.load_workbook(wb_name, data_only=True)

        # Get the PCAP sheet
        sheet_name = f'2022-{month} PCAP (R)'
        sheet = wb[sheet_name]

        # Copy the data and paste it into the destination sheet

        # Add the date to column B
        last_day = calendar.monthrange(2022, int(month))[1]
        date_str = f'{int(month)}/{last_day}/2022'
        dest_sheet[f'B{dest_row+1}'].value = date_str

        #Create dictionary with key value pairs to map source columns to destination columns
        data_map = {'A': 'C', 'B': 'E', 'D': 'F', 'F': 'G', 'H': 'H', 'U': 'J', 'AI': 'I', 'AO': 'K'}

        #Determines the last row to copy based on the max number of rows between the source and dest sheets
        last_row = sheet.max_row - 1
        print(f"Source sheet last row: {sheet.max_row}")
        print(f"Destination row: {dest_row}")

        
        for row in range(6, last_row + 1):
            dest_row +=1

            source_row_data = [col.value for col in sheet[f'A{row}:AO{row}'][0]] #Creates a list of values for each column in the current row of source sheet
            source_row_data = [float(col_value) if col_value != "-" else 0 for col_value in source_row_data] #Check if there are formulas returning "-" i.e. 0
            print(source_row_data)
            
            if any(col_value is not None for col_value in source_row_data):
                for source_col, dest_col in data_map.items(): #Iterates through items in data_map dictionary
                    if len(source_col) == 1:
                        source_col_val = source_row_data[ord(source_col) - 65]  
                    else:
                        # Treat the column as a special case and manually convert it to the corresponding column number
                        col_num = (ord(source_col[0]) - 64) * 26 + (ord(source_col[1]) - 65)
                        source_col_val = source_row_data[col_num - 1]

                    #Sets dest_col_val to the cell in the destination sheet corr. to current dest column being iterated over and current row
                    dest_col_val = dest_sheet[f'{dest_col}{dest_row}'] 
                    if source_col in ['H', 'U', 'AI'] and row == last_row:
                        dest_col_val.value = None
                    else:
                        dest_col_val.value = source_col_val
                #print(f"Source column length: {len(source_col)}")
            else:
                print(f'Row {row} has blank data')

        wb.close()

dest_wb.save('Liquid Portfolio Attribution Analysis - Copy 2.xlsx')

源工作簿截图 #

问题分析与解决 #

1. 你的转换方案为什么没用？
   从报错栈能明确看到，错误出在openpyxl.load_workbook(wb_name, data_only=True)这一步——也就是加载工作簿时就报错了，根本没走到你处理source_row_data的代码环节。所以你写的转换逻辑根本没机会执行，自然解决不了问题。

2. 透视表确实是问题根源
   没错，问题就是透视表导致的。openpyxl在解析透视表缓存时，遇到了格式不兼容的情况：透视表缓存里的某个字段被标记为float类型，但实际存储的值是字符串"-"，解析时无法转成float，直接抛出错误。

3. 可行的解决办法

• 临时方案：跳过透视表解析
  加载工作簿时加上read_only=True参数，这种模式下openpyxl不会解析透视表缓存，只会读取单元格的显示值，能绕过这个错误。注意这种模式下工作簿是只读的，但你只是读取源数据，刚好适用：

  wb = openpyxl.load_workbook(wb_name, data_only=True, read_only=True)
  

• 彻底方案：修复源文件的透视表
  打开源Excel文件，刷新透视表，确保所有数值型字段的计算结果都是合法数字，把显示"-"的地方改成空值或者0，然后重新保存。这样openpyxl就能正常解析了。

• 备选方案：改用pandas读取
  pandas的read_excel函数对这种格式问题兼容性更好，能自动处理很多类型转换问题：

  import pandas as pd
  df = pd.read_excel(wb_name, sheet_name=sheet_name, skiprows=5) # 根据你的数据起始行调整skiprows
  

内容的提问来源于stack exchange，提问作者Kyle Massimilian