class Class_1:
    global pd, np, os, xw
    import pandas as pd, numpy as np
    import os
    import xlwings as xw
    
    str_addr_1 = r'd:\test.xlsx'
    str_addr_2 = r'd:\test_2.xlsx'
    os.system("taskkill /f /im excel.exe")
    
    def __init__(self, str_addr_1=str_addr_1, str_addr_2=str_addr_2):
        self.str_addr_1 = str_addr_1
        self.str_addr_2 = str_addr_2

        
class Class_2(Class_1):
    def __init__(self):
        # 调用父类的构造函数
        super().__init__()
        
        self.df_1 = pd.read_excel(self.str_addr_1)  # 默认选择第一个工作表,而不是最新的工作表
        self.df_2 = pd.read_excel(self.str_addr_1, pd.ExcelFile(self.str_addr_1).sheet_names[-1])
        print(self.df_1.iloc[:, -1], self.df_2.iloc[:, -1], sep='\n', end='\n\n')
        print(self.df_1.shape[0], self.df_2.shape[0])  # excel隐藏和筛选对pandas没有影响
        
        self.app = xw.App(visible=False, add_book=False)
        self.wb = self.app.books.open(self.str_addr_1)
        self.sht_1 = self.wb.sheets[0]
        self.sht_2 = self.wb.sheets[-1]
        self.count_1 = self.sht_1.used_range.rows.count - 1  # 含标题行数
        self.count_2 = self.sht_2.used_range.rows.count - 1
        print(self.count_1, self.count_2, end='\n\n')  # excel隐藏和筛选对xlwings.used_range没有影响
        
        self.sht_1.range(2, 3).options(transpose=True).value = ['隐藏不影响赋值']*3
        self.sht_2.range(2, 3).options(transpose=True).value = ['筛选影响赋值']*3
        self.wb.save(self.str_addr_2)
        self.wb.close()
        self.app.kill()
        self.ser_1 = pd.read_excel(self.str_addr_2).iloc[:, -1]
        self.ser_2 = pd.read_excel(self.str_addr_2, pd.ExcelFile(self.str_addr_2).sheet_names[-1]).iloc[:, -1]
        print(self.ser_1, self.ser_2, sep='\n')
        

if __name__ == '__main__':
    Instance = Class_2()