#!/usr/bin/env python
# _*_ coding:utf-8 _*_
 
"""
 
@Time    : 2019-12-27 17:16
@Author  : Peanut_C
@FileName: excel_converter.py
 
"""
 
 
import os
import shutil
import xlwings as xw
 
current_dir = os.getcwd()
src_dir = os.path.join(current_dir, 'src_dir')
dst_dir = os.path.join(current_dir, 'dst_dir')
exist_list = ['YYYY', 'XXXX']  # 要保留行的A列關(guān)鍵字
 
 
def file_copy(source_dir, destination_dir):
    os.chdir(source_dir)
    for file in os.listdir(source_dir):
        shutil.copy(file, destination_dir)
    print('INFO ===>>> 文件拷貝完成！')
 
 
def excel_modifier(wk_dir):
    os.chdir(wk_dir)
    for file in os.listdir(wk_dir):
        # 檢查文件格式是否為xls
        # print(type(os.path.splitext(file)[1]))
        if os.path.splitext(file)[1] != '.xls':
            print(file, '===>>>文件格式不正確，請(qǐng)檢查！')
        else:
            print('開(kāi)始處理===>>>', file)
            # 創(chuàng)建app，打開(kāi)工作表
            app = xw.App(visible=False, add_book=False)
            app.screen_updating = False
            app.display_alerts = False
            load_wb = app.books.open(file)
            load_ws = load_wb.sheets.active
            print('\t已打開(kāi)工作表……')
 
            # 獲取總行數(shù)（列數(shù)固定不需要獲?。?
            rows = load_ws.api.UsedRange.Rows.count
            # cols = load_ws.api.UsedRange.Columns.count
 
            # 獲取需要處理的A列范圍
            a_range = load_ws.range('A1:A'+str(rows-4))  # 得到range對(duì)象
 
            # 將range中每行對(duì)象存放到列表中并倒序
            print('\t開(kāi)始獲取標(biāo)志列……')
            cell_list = []
            for cell in a_range:
                cell_list.append(cell)
            cell_list.reverse()
            # print(cell_list)
 
            # 將表頭拆分、重新合并，為插入的值騰地方
            print('\t開(kāi)始調(diào)整合并單元格……')
            load_ws.range('H3:J3').api.unmerge()  # 拆分單元格
            load_ws.range('H3:I3').api.merge()  # 合并單元格
            load_ws.range('J3').value = 'xxx'  # 插入值
 
            # 設(shè)定將A列每個(gè)值與要保留列表比對(duì)，比對(duì)不上則刪除整行
            print('\t開(kāi)始調(diào)整行和列……')
            for cell in cell_list:
                if cell.value is not None:  # 單元格不為空則開(kāi)始比對(duì)
                    find_flag = 0  # 匹配標(biāo)志
                    for exist_value in exist_list:
                        if cell.value.find(exist_value) != -1:
                            find_flag = 1  # 匹配則將標(biāo)志置為1
                            break  # 一個(gè)單元格只要匹配就不再比對(duì)保留列表剩下的值
                        else:
                            continue  # 匹配不上則繼續(xù)
                    if find_flag == 0:  # 沒(méi)匹配上的刪除整行
                        cell_to_del = cell.address
                        # print(cell_to_del)
                        load_ws.range(cell_to_del).api.EntireRow.Delete()
                else:  # 單元格為空直接刪除
                    cell_to_del = cell.address
                    # print(cell_to_del)
                    load_ws.range(cell_to_del).api.EntireRow.Delete()
 
            # 處理列，將指定列從大到小刪除（避免先刪除小列導(dǎo)致后續(xù)列號(hào)變動(dòng)）
            load_ws.api.columns('K').delete
            load_ws.api.columns('G').delete
            load_ws.api.columns('B').delete
            # 美化處理后的Excel
            print('\t開(kāi)始美化表格……')
            load_ws.range('A1:H24').columns.autofit()
            # 處理完畢，保存、關(guān)閉、退出Excel
            load_wb.save()
            load_wb.close()
            app.quit()
            print('處理完畢===>>>', file, '\n\n')
 
 
if __name__ == '__main__':
    file_copy(src_dir, dst_dir)
    excel_modifier(dst_dir)
    print('任務(wù)結(jié)束，請(qǐng)至dst_dir目錄查看文件！\n\n')
    os.system('pause')