如何使用python将列添加到现有excel文件？

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文件目前有三列：“date，gamma，theta”。我想添加一个名为“ratio”的第四列，即gamma / theta的比率。如何使用python将另一列添加到现有的Excel文档？此代码创建一个excel文档，其中4个元素打印在一个单元格中

3 个解决方案

#1

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

#2

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.')

#3

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(…)

#1