2020-10-23

import pandas as pd
import numpy as np
import os
import re
import xlwt
import xlrd


def Get_Filenames(path):
    filenames = []
    for file in os.listdir(path):
        if '.xlsx' in file and '透视分析' in file:
            filenames.append(file)
    return filenames

def Get_List_Name(filenames):
    listing = []
    for filename in filenames:
        if '.xlsx' in filename and '透视分析' in filename:
            list_name = ''.join(re.findall('[\u4e00-\u9fa5]', filename))
            listing.append(list_name)
    return listing

def Vlookup(f1, f2):
    df = pd.merge(f1, f2, on=['标签名', '枚举值'], how='outer')
    return df

def Save_Merge_Files(filenames, final_file):
    df1 = pd.read_excel(filenames[0], header=1)
    for filename in filenames[1:]:
        df2 = pd.read_excel(filename, header=1)
        df1 = Vlookup(df1, df2) 
    df1.to_excel(final_file) 

def Format_File(path, final_file):
    workbook = xlrd.open_workbook(path + '//' + final_file)
    sheet1 = workbook.sheet_by_index(0)
    first_col = sheet1.col_values(0)
    first_row = sheet1.row_values(0)
    col_num = len(first_col)
    row_num = len(first_row)
    alignment = xlwt.Alignment()
    alignment.horz = 0x02 # 设置水平居中
    alignment.vert = 0x01 # 设置垂直居中
    style = xlwt.easyxf('font: bold on') # 加粗
    style.alignment = alignment

    workbook1 = xlwt.Workbook()
    worksheet = workbook1.add_sheet('My Sheet')
    n = int(row_num/2 - 1)
    for i in range(n):
        a = (i+1)*2
        b = (i+1)*2 + 1
        worksheet.col(a).width = 3800
        worksheet.col(b).width = 3800
        worksheet.write_merge(0, 0, a, b, first_row[a], style)
    n = 0    
    for i in range(2,col_num-1):
        if first_col[i] == first_col[i+1]:
            n += 1
        else:
            worksheet.write_merge(i-n,i, 0, 0, first_col[i], style)
            n=0
    for i in range(1,col_num):
        for j in range(1,row_num):
            worksheet.write(i,j, label = sheet1.cell_value(i, j))
    worksheet.col(1).width = 5200
    worksheet.col(0).width = 4500

    workbook1.save('合并后数据.xls')

def Def_Font():
    # 设置字体
    font = xlwt.Font()
    font.name = '微软雅黑'
    font.colour_index = 1
    font.height = 20*10
    font.bold = False

def Def_Alignment():
    # 设置单元格对齐方式
    alignment = xlwt.Alignment()
    alignment.horz = 0x02 # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    alignment.vert = 0x01 # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
    alignment.wrap = 0 # 自动换行

def Def_Frame():
    # 设置边框
    borders = xlwt.Borders()
    # 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
    # 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    # borders.left_colour = 0x00
    # borders.right_colour = 0x00
    # borders.top_colour = 0x00
    # borders.bottom_colour = 0x00

def Set_sheet():

    # workbook1 = xlwt.Workbook()
    # worksheet = workbook1.add_sheet('My Sheet')
    # n = int(row_num/2 - 1)
    # for i in range(n):
    #     a = (i+1)*2
    #     b = (i+1)*2 + 1
    #     worksheet.col(a).width = 3800
    #     worksheet.col(b).width = 3800
    #     worksheet.write_merge(0, 0, a, b, first_row[a], style)
    # n = 0    
    # for i in range(2,col_num-1):
    #     if first_col[i] == first_col[i+1]:
    #         n += 1
    #     else:
    #         worksheet.write_merge(i-n,i, 0, 0, first_col[i], style)
    #         n=0
    # for i in range(1,col_num):
    #     for j in range(1,row_num):
    #         worksheet.write(i,j, label = sheet1.cell_value(i, j))
    # worksheet.col(1).width = 5200
    # worksheet.col(0).width = 4500

    # workbook1.save('合并后数据.xls')
    pass


def main():

    path = os.getcwd()
    final_file = '合并后的文件.xlsx'

    files = Get_Filenames(path)
    list_name = Get_List_Name(files)
    Save_Merge_Files(files, final_file)
    Format_File(path, final_file)

if __name__ == "__main__":
    main()