值错误:无法将 0 转换为 Excel

本文介绍了值错误:无法将 0 转换为 Excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一段代码可以将下载的 csv 中的数据复制/粘贴到预定义的 excel 模板中.某些 csv 文件在粘贴之前必须转置.这在我的 PC 上没有问题，但在 Mac 上它给出了无法将 {0!r} 转换为 Excel".format(value) 错误.

I have a piece of code that copy/pastes data from Downloaded csv's into a predefined excel template. Some of the csv files have to be transposed before being pasted. This works with no issues on my PC, but on a mac it gives a Cannot convert {0!r} to Excel".format(value) error.

这是我使用的代码:

def read_transpose(account_id): excel_template = load_workbook('Cost_Optimization_Template.xlsx') ec2_utilization = excel_template['EC2 RI Utilization '] rds_utilization = excel_template['RDS RI Utilization '] elasticity = excel_template['Elasticty'] ec2_newri = excel_template['EC2 New RI'] coverage = excel_template['RI Coverage'] accounts = excel_template['Accounts'] services = excel_template['Services'] spot_by_account = excel_template['Spot Usage by Account'] spot = excel_template['Spot Usage'] storage = excel_template['Storage'] storage_by_account = excel_template['Storage by Account'] cloudwatch_accounts = excel_template['CloudWatch Accounts'] instance_types = excel_template['Instance Types'] ri_graph = excel_template['RI Graph'] instructions = excel_template['Instructions'] instructions['C5'] = account_id tab_list = [ec2_utilization, rds_utilization, elasticity, ec2_newri, coverage, accounts, services, spot_by_account, spot, storage, storage_by_account, cloudwatch_accounts, instance_types, ri_graph] #print excel_template.sheetnames file_list = ['ri-subscriptions.csv', 'ri-subscriptions (1).csv', 'costs.csv', 'ec2-recommendations.csv', 'ri-instanceTypes.csv', 'costs_(1).csv', 'costs_(2).csv', 'costs_(3).csv', 'costs_(4).csv', 'costs (5).csv', 'costs (6).csv', 'costs (7).csv', 'costs (8).csv', 'ri-utilization.csv'] i = 0 for file in file_list: # catching empty file excpetions in case there is no data to read and/or transpose so this tab needs to be skipped print (file) wb = openpyxl.Workbook() ws = wb.active # for transposed files if file == 'costs_(1).csv' or file == 'costs_(2).csv' or file == 'costs_(3).csv' or file == 'costs_(4).csv' or file == 'costs_(5).csv' or file == 'costs (6).csv' or file == 'costs (7).csv' or file == 'costs (8).csv': df = pd.read_csv(file) df = df.transpose() print ('hit transpose') for r in dataframe_to_rows(df, index=False, header=True): ws.append(r) ws.delete_rows(1) # necessary to delete the index row that gets created when transposing else: df = pd.read_csv(file) print ('hit non transpose') for r in dataframe_to_rows(df, index=False, header=True): ws.append(r) excel_template.save('cost_optimization_template_{0}.xlsx'.format(account_id))

这个问题似乎只发生在需要在粘贴之前转置的文件，因为其他文件实际上是在粘贴时没有问题.

The issue seems to occur only with files that need to be transposed before being pasted as the other ones are actually getting pasted with no issues.

我在这里通读了其他答案，但没有任何帮助为我解决这个问题.任何帮助将不胜感激.

I read through other answers out here but nothing helped solved this issue for me. Any help would be much appreciated.

以下是完整的回溯错误:

Below is the full traceback error:

Traceback (most recent call last): File "cost_optimization.py", line 701, in <module> read_transpose('111') File "cost_optimization.py", line 612, in read_transpose ws.append(r) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/openpyxl/worksheet/worksheet.py", line 654, in append cell = Cell(self, row=row_idx, column=col_idx, value=content) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/openpyxl/cell/cell.py", line 120, in __init__ self.value = value File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/openpyxl/cell/cell.py", line 252, in value self._bind_value(value) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/openpyxl/cell/cell.py", line 218, in _bind_value raise ValueError("Cannot convert {0!r} to Excel".format(value)) ValueError: Cannot convert 0 to Excel

推荐答案

原来是转置时创建的标头，openpyxl 似乎不喜欢那里的 0，在 dataframe_to_rows(df,index=False, header=True) 到 header=False 解决了它.

Turned out to be the header that got created when transposing, and openpyxl didn't seem to like the 0 in there, changing for r in dataframe_to_rows(df, index=False, header=True) to header=False solved it.