目的:
实验excel隐藏和筛选对python和xlwings的影响(完整版)
特别是如何对未筛选行赋值,需逐行赋值,range(行,列).value
环境:
Jupyter Notebook
其他环境需删除display函数
代码:
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('\texcel隐藏和筛选对pandas没有影响')
print(self.df_1.iloc[:, -1], self.df_2.iloc[:, -1], sep='\n')
print('不含标题的行数:', self.df_1.shape[0], self.df_2.shape[0], end='\n\n')
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 # 含标题的行数
self.count_2 = self.sht_2.used_range.rows.count
print('\texcel隐藏和筛选对xlwings.used_range没有影响')
print(self.sht_1.used_range, self.sht_2.used_range, sep='\n')
print('不含标题的行数:', self.count_1-1, self.count_2-1, end='\n\n')
self.sht_1.range(2, 2).options(transpose=True).value = ['隐藏不影响赋值']*3
self.sht_2.range(2, 2).options(transpose=True).value = ['筛选影响赋值']*3
print('\texcel筛选对xlwings.range有影响')
print('隐藏不会中断:', self.sht_1.range(2, 2).expand('down'))
print('筛选会中断:', self.sht_2.range(2, 2).expand('down'), self.sht_2.range(2, 2).options(transpose=True))
print('虽可选取,但不能对未筛选行正确赋值:', self.sht_2.range((2, 2), (self.sht_2.used_range.rows.count, 2)), end='\n\n')
print('\t需逐行赋值')
# 不含标题的行数n=pandas.shape[0],含标题的行数n+1=xlwings.used_range.rows.count,遍历从不含标题的第1行到第n行,即[0,n)
for i in range(0, self.sht_2.used_range.rows.count-1): # [不含标题的第1行, 第n行]=[0,n-1]=[0,n)=[0,xlwings.used_range.rows.count-1)
print(f"不含标题的0_based_indexing[{i}], 不含标题的第{i+1}行, 含标题的第{i+2}行, excel的1_based_indexing[{i+2}]")
self.sht_2.range(i+2, 3).value = '逐行赋值'
print(self.sht_2.range(i+2, 3), self.sht_2.range(i+2, 3).value)
self.wb.save(self.str_addr_2) # 覆盖工作簿
self.wb.close()
self.app.kill()
self.df_1 = pd.read_excel(self.str_addr_2) # (默认第一个工作表)
self.df_2 = pd.read_excel(self.str_addr_2, pd.ExcelFile(self.str_addr_2).sheet_names[-1])
print('\n\texcel筛选对xlwings.range的影响展示')
display(self.df_1, self.df_2)
if __name__ == '__main__':
Instance = Class_2()截图:
