How to use python to merge multiple sheets from an excel file and values from particular cells

Question

I have an excel file with multiple sheets, the actual data I need from each sheet is from cell B7 to F38, how can I merge all the sheets' data into one by using Python?

Answer 1

Collect the data with pandas, then concatenate the contents of the sheets (if they have the same column names) and insert the resulting dataframe somewhere (e.g. on the first sheet), see:

import xlwings as xw
import pandas as pd

path = r"test.xlsx"

df_dict = pd.read_excel(path, sheet_name=None)
df_result = pd.concat(df_dict.values(), axis=0)

wb = xw.Book(path)
ws = wb.sheets[0]
ws["A1"].options(pd.DataFrame, index=False).value = df_result