在Python中使用Openpyxl修改现有的Excel文件 [英] Modify an existing Excel file using Openpyxl in Python
问题描述
我基本上试图从CSV文件中复制一些特定的列,并使用python粘贴这些
在现有的excel文件[*。xlsx]。例如,您有一个CSV文件,如下所示:
I am basically trying to copy some specific columns from a CSV file and paste those in an existing excel file[*.xlsx] using python. Say for example, you have a CSV file like this :
col_1 col_2 col_3 col_4
1 2 3 4
5 6 7 8
9 10 11 12
col_3和col_4,并将这些在col_8和col_9在现有的excel文件[这是一个.XLSX格式]。
我已经尝试过各种方式来解决,但是找不到确切的方法。
i尝试这样的操作:
So, i wanted to copy the both col_3 and col_4 and paste those in col_8 and col_9 in an existing excel file [which is a .XLSX format]. I have tried this in various way to solve, but could not find out the exact way. i tried something like this :
with open( read_x_csv, 'rb') as f:
reader = csv.reader(f)
for row in reader:
list1 = row[13]
queue1.append(list1)
list2 = row[14]
queue2.append(list2)
list3 = row[15]
queue3.append(list3)
list4 = row[16]
queue4.append(list4)
,然后
rb = open_workbook("Exact file path.....")
wb = copy(rb)
ws = wb.get_sheet(0)
row_no = 0
for item in queue1:
if(item != ""):
ii = int(item)
ws.write(row_no,12,ii)
row_no = row_no + 1
#ws.write(item)
print item
else:
ws.write(row_no,12,item)
row_no = row_no + 1
wb.save("Output.xls")
但是这个解决方案的问题是它不允许我保存为* .XLSX格式,这是
严格要求我。
but problem with this solution is it does not allow me to save as *.XLSX format which is strictly required for me.
我试图使用Openpyxl,因为它可以处理* .XLSX格式,但无法找到一种方法来修改现有的Excel文件。任何人都可以请帮助这个?
I have tried to use Openpyxl as it can handle *.XLSX format, but could not find out a way to modify the existing excel file. can anyone please help on this?
怀疑:
1)我们真的可以从CSV文件读取整列并存储到数组/列表
using python?
2)我们可以使用
openpyxl或任何其他软件包修改.XLSX格式的现有excel文件吗?
Doubt : 1) Can we really read a whole column from a CSV file and store into an array/list using python? 2) Can we modify the existing excel file which is in .XLSX format using openpyxl or any other package?
推荐答案
您可以尝试以下实施
from openpyxl import load_workbook
import csv
def update_xlsx(src, dest):
#Open an xlsx for reading
wb = load_workbook(filename = dest)
#Get the current Active Sheet
ws = wb.get_active_sheet()
#You can also select a particular sheet
#based on sheet name
#ws = wb.get_sheet_by_name("Sheet1")
#Open the csv file
with open(src) as fin:
#read the csv
reader = csv.reader(fin)
#enumerate the rows, so that you can
#get the row index for the xlsx
for index,row in enumerate(reader):
#Assuming space separated,
#Split the row to cells (column)
row = row[0].split()
#Access the particular cell and assign
#the value from the csv row
ws.cell(row=index,column=7).value = row[2]
ws.cell(row=index,column=8).value = row[3]
#save the csb file
wb.save(dest)
-
真正从CSV文件中读取整列,并使用python存储到数组/列表中? 不,因为文件是顺序读取的,csv reader不能读取一列数据到一行。相反,您可以读取整个内容,并使用izip和islice获取特定的列。您还可以使用numpy.array
我们可以使用openpyxl或任何其他软件包修改.XLSX格式的现有Excel文件吗? 是,请参阅上面的示例
Can we modify the existing excel file which is in .XLSX format using openpyxl or any other package? Yes, see the example above
这篇关于在Python中使用Openpyxl修改现有的Excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!