I had a similar question regarding the interaction between excel and python (in particular, pandas), and I was referred to this question.
Thanks to some pointers by stackoverflow community, I found a package called xlwings that seems to cover a lot of the functionalities HaPsantran required.
To use the OP's example:
Working with an existing excel file, you can drop an anchor in the data block (Sheet3) you want to import to pandas by naming it in excel and do:
# opened an existing excel file
wb = Workbook(Existing_file)
# Find in the excel file a named cell and reach the boundary of the cell block (boundary defined by empty column / row) and read the cell
df = Range(Anchor).table.value
# import pandas and manipulate the data block
df = pd.DataFrame(df) # into Pandas DataFrame
df['sum'] = df.sum(axis= 1)
# write back to Sheet3
Range(Anchor).value = df.values
tested that this implementation didn't temper existing formula in the excel file
Let me know if this solves your problem and if there's anything I can help.
Big kudos to the developer of xlwings, they made this possible.
Below is an update to my earlier answer after further question from @jamzsabb, and to reflect a changed API after xlwings updated to >= 0.9.0.
import xlwings as xw
import pandas as pd
target_df = xw.Range('A7').options(pd.DataFrame, expand='table').value # only do this if the 'A7' cell (the cell within area of interest) is in active worksheet
#otherwise do:
#sht = xw.Book(r'path to your xlxs file
ame_of_file.xlsx`).sheets['name of sheet']
#target_df = sht.Range('A7').options(pd.DataFrame, expand='table').value # you can also change 'A7' to any name that you've given to a cell like 'interest_table`
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…