How to update an existing row or table of an existing xlsx file using pyexcel for python

Question

I am trying to use python to update an xlsx spread sheet used for keeping track of weekly trends/metrics. This particular xlsx file has 4 sheets on “Sheet one” I have 52 tables, one for each week of the year. Each table is named “table1- table52” I would like to search the sheet for the appropriate table of the current week and only update that table. I have used the below code to write to a file but it strips out all of the existing format and formulas I have set up to auto populate other sheets. Also it does not write to the existing file its output is a brand new file.

So in a nut shell I would like to open test1.xlsx read sheet one find table3 and update columns (RR, OVP, CVP, TVC, and PP) or rows whichever one is easier. I’m sure there is probably something simple I am missing.

I have attached a picture of the xlsx file and the python cod e I have so far. I have looked at numpy, openpyxl, pandas, and xlsxwriter. I am open to using any one of these modules

#How to update an existing row or table of an existing file using pyexcel for python
import pyexcel as pe
import pyexcel.ext.xls

RR1 ="Null"
OVC1 =1
CVC1 =1
TVC1 =1
PP1 =1

sheet = pe.get_sheet(file_name="test1.xlsx") # Original file.
sheet.row[3] = [RR1 , OVC1 , CVC1 , TVC1 , PP1] # Parameters I wish to use to update original file.
sheet.save_as("test.xlsx") # This is a different file
print pe.get_sheet(file_name="test.xlsx")

Answer 1

openpyxl does a great job opening an existing Excel (xls/xlsx) and updating a specific row, col.

import openpyxl
xfile = openpyxl.load_workbook('test.xlsx')

sheet = xfile.get_sheet_by_name('Sheet1')
sheet['A1'] = 'hello world'
xfile.save('text2.xlsx')

We can also use row, column notation like

sheet.cell(row=1, column=1).value = 'hello world'