加密Excel文件数据解析

加密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)
        # 操作Sheet1
        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)