如何使用 python 在现有的 excel 文件中添加一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44811523/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I add a column to an existing excel file using python?
提问by Sarah Porgess
here is my code:
这是我的代码：
import openpyxl, pprint
wb = openpyxl.load_workbook('/Users/sarahporgess/Desktop/SSA1.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
data = {}
for row in range(1,sheet.max_row+1):
date = sheet['A' +str(row)].value
gamma = sheet['B' +str(row)].value
theta = sheet['C' +str(row)].value
ratio = float(gamma)/float(theta)
resultFile = open('SSA2.csv' , 'w')
resultFile.write( pprint.pformat(date))
resultFile.write( pprint.pformat(gamma))
resultFile.write( pprint.pformat(theta))
resultFile.write( pprint.pformat(ratio))
print(ratio)
sheet['D1']=ratio
resultFile.close()
print('Done.')
my existing excel file currently has three columns: "date, gamma, theta". I want to add a fourth column called "ratio" that is the ratio of gamma/theta. How do I add another column to an existing excel document using python? This code creates an excel document with the 4 elements printed into one cell
我现有的 excel 文件目前有三列：“日期、伽马、θ”。我想添加名为“ratio”的第四列，它是 gamma/theta 的比率。如何使用 python 向现有的 excel 文档添加另一列？此代码创建一个 Excel 文档，其中 4 个元素打印到一个单元格中
采纳答案by Rakesh Adhikesavan
It is easier to use the Pandas package
使用 Pandas 包更容易
import pandas as pd
file_name = #Path to your file
df = pd.read_excel(file_name) #Read Excel file as a DataFrame
df['Ratio'] = df['Gamma']/df['Theta']
#Display top 5 rows to check if everything looks good
df.head(5)
#To save it back as Excel
df.to_excel("path to save") #Write DateFrame back as Excel file
回答by Mike - SMT
From your updated question I have rewrote my answer.
从您更新的问题中，我重写了我的答案。
You don't need to use another library to accomplish what you are trying to do. Here is another option for accomplishing what you want.
你不需要使用另一个库来完成你想要做的事情。这是实现您想要的功能的另一种选择。
import openpyxl
import pprint
wb = openpyxl.load_workbook('/Users/sarahporgess/Desktop/SSA1.xlsx')
sheet = wb.active
# you may also use the wb.get_sheet_by_name('Sheet1') method here.
data = {}
for row in range(1,sheet.max_row+1):
date = sheet.cell(row = row, column = 1) # use .cell() to get values of cells
gamma = sheet.cell(row = row, column = 2)
theta = sheet.cell(row = row, column = 3)
print(date, gamma, theta)
ratio = float(gamma)/float(theta)
new_wb = openpyxl.Workbook() # creates new workbook to be saved as results
# you can also open a wookbook here instead but I wrote it to create a results workbook as I didnt already have one.
new_sheet = new_wb.active
new_sheet['A1'] = pprint.pformat(date)
new_sheet['B1'] = pprint.pformat(gamma)
new_sheet['C1'] = pprint.pformat(theta)
new_sheet['D1'] = pprint.pformat(ratio)
print(ratio)
# save new workbook as SSA2
new_wb.save('/Users/sarahporgess/Desktop/SSA2.xlsx')
print('Done.')
回答by Charlie Clark
It's not clear from your code whether you want to print the result or edit the existing file. If you're editing an Excel file then you might want to create a formula and let Excel do the calculation for you.
从您的代码中不清楚您是要打印结果还是编辑现有文件。如果您正在编辑 Excel 文件，那么您可能需要创建一个公式并让 Excel 为您进行计算。
import openpyxl
wb = openpyxl.load_workbook('/Users/sarahporgess/Desktop/SSA1.xlsx')
sheet = wb['Sheet1']
for row in sheet:
date, gamma, theta = row
ratio = theta.offset(column=1)
ratio.value = "=B{0}/C{0}".format(theta.row) # if you want the formula
# ratio.value = gamma/theta # if you just want the calculation
wb.save(…)
