在 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 格式].我已经尝试过以各种方式解决这个问题，但找不到确切的方法.我试过这样的事情:

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 文件中读取一整列并存储到数组/列表中吗?使用蟒蛇?2)我们可以修改现有的.XLSX格式的excel文件吗?openpyxl 或任何其他包?

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阅读器不能把一列数据读到一行.相反，您可以阅读整个内容并使用 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屋！