加密Excel文件数据解析
业务需求说明:
- 利用win32com,给目标文档传密码,解析数据
- 主要通过遍历第一列数据,获取其索引号,利用此索引号定位此行后所需要的目标数据
import win32com.client as win32
import os
class Put_password_do_excel(object):
'''
此类主要负责遍历加密文件,传入密码,解析数据
'''
xlApp = win32.gencache.EnsureDispatch('Excel.Application')
xlApp.Visible = True
xlApp.DisplayAlerts = 0
xlApp.Visible = 2
def load_files(self):
'''
加载目标文件夹文件,返回文件全路径
'''
path = 'D:/data'
files = os.listdir(path)
for file in files:
yield path + file
def find_total_data(self, path, password):
'''
加密文件解析,显示所要目标数据
'''
xlopen = self.xlApp.Workbooks.Open(path, False, True, None, Password = password,WriteResPassword = password)
rsheet = xlopen.Worksheets(1)
row_len = rsheet.Range('A65536').End(win32.constants.xlUp).Row
print('一共%s行数据' % row_len)
for rlen in range(1, row_len):
rValue = xlopen.Sheets(1).Cells(rlen,1)
if 'XXX' in str(rValue):
XXX = xlopen.Sheets(1).Cells(rlen, 2)
print('XXX:', XXX)
elif 'YYY' in str(rValue):
YYY = xlopen.Sheets(1).Cells(rlen, 3)
print('YYY:', YYY)
elif 'ZZZ' in str(rValue):
ZZZ = xlopen.Sheets(1).Cells(rlen, 4)
KKK = xlopen.Sheets(1).Cells(rlen, 5)
print('ZZZ:%s; KKK:%s' % (ZZZ, KKK))
else:
pass
xlopen.Close(False)
self.xlApp.Application.Quit()
def main(self, password):
files = self.load_files()
for path in files:
self.find_total_data(path, password)
if __name__ == '__main__':
Put_password_do_excel = Put_password_do_excel()
password = 'XXXXXXXX'
Put_password_do_excel.main(password)