我有一段代码,可以将下载的csv数据复制/粘贴到预定义的excel模板中。有些csv文件在粘贴之前必须被转置。这在我的电脑上没有问题,但在mac电脑上它给出了一个无法转换{0!r} “到Excel”。格式(值)错误。在
以下是我使用的代码:
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))
这个问题似乎只发生在粘贴之前需要转置的文件,因为其他文件实际上是在没有问题的情况下粘贴的。在
我在这里读了其他的答案,但是没有什么能帮我解决这个问题。任何帮助都将不胜感激。在
以下是完整的回溯错误:
^{pr2}$
结果是转置时创建的头,openpyxl似乎不喜欢其中的0,将dataframe_to_行(df,index=False,header=True)中的r改为header=False解决了这个问题。在
相关问题 更多 >
编程相关推荐