Modify an existing Excel file using Openpyxl in Python

Solution 1:

You can try the following implementation

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)

• Can we really read a whole column from a CSV file and store into an array/list using python? No, because files are read sequentially, csv reader cannot read a column of data to a row. Instead you may read the whole content and use izip and islice to get a particular column. You can also use numpy.array

• Can we modify the existing excel file which is in .XLSX format using openpyxl or any other package? Yes, see the example above

Solution 2:

As it is 2021, get_sheet_by_name is deprecated and raises an DeprecationWarning with the following message: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).

The following snippet can be used in order to not raise the warning.

from openpyxl import load_workbook

file_path = 'test.xlsx'

wb = load_workbook(file_path)

ws = wb['SHEET_NAME']  # or wb.active

ws['G6'] = 123

wb.save(file_path)

Solution 3:

from openpyxl import load_workbook
# Class to manage excel data with openpyxl.

class Copy_excel:
    def __init__(self,src):
        self.wb = load_workbook(src)
        #self.ws = self.wb.get_sheet_by_name("Sheet1") # Deprecated
        self.ws = self.wb["Sheet1"]
        self.dest="destination.xlsx"

    # Write the value in the cell defined by row_dest+column_dest         
    def write_workbook(self,row_dest,column_dest,value):
        c = self.ws.cell(row = row_dest, column = column_dest)
        c.value = value
    
    # Save excel file
    def save_excel(self) :  
        self.wb.save(self.dest)