# -*- coding: utf-8 -*-
# @Time    : 2020/12/10 9:29
# @File    : xml_first.py
# @Author  : hakim
#实现接口生成xml入参和参数化.xlsx
import openpyxl
import os

####------excel转换成dict
def excelReadData(filename):
    wb = openpyxl.load_workbook( filename )
    sheet = wb.active  # 获取当前被激活的工作薄
    # print(sheet.max_row) # 最大行数
    # print(sheet.max_column) # 最大列数
    datas = {}  # 这步也要转字典类型
    # 进行逐行遍历
    for row in sheet.rows:
        K, V = row
        xx = '{\'' + K.value + '\'' + ":'" + V.value + '\'}'
        # print(type(json.loads(xx)))
        datas.update( eval( xx ) )  # eval实现str转成dict 并进行update字典添加
    # print( datas )
    return datas


sequence = list( map( lambda x: chr( x ), range( ord( 'A' ), ord( 'Z' ) + 1 ) ) )


####------实现26进制（A-Z）与10进制互相转换
def ten2TwentySix(num):
    L = []
    num = num - 1;
    if num > 25:
        while True:
            d = int( num / 26 )
            remainder = num % 26
            if d <= 25:
                L.insert( 0, sequence[remainder] )
                L.insert( 0, sequence[d - 1] )
                break
            else:
                L.insert( 0, sequence[remainder] )
                num = d - 1
    else:
        L.append( sequence[num] )
    return "".join( L )

####---dict转成excel参数化
def excelWrite(sd):
    if os.path.exists( "xml_参数化.xlsx" ):
        os.remove( "xml_参数化.xlsx" )
    f = openpyxl.Workbook()  # 创建工作簿
    sheet1 = f.active  # 当前工作表的名称
    # 指定的Excel的表单名（就是我们在Excel常见的左下角表单名sheet）
    sheet1.title = 'sheet1'
    print( "sheet names:", f.sheetnames )
    sheet1.cell( row=1, column=1, value="数据类型" )
    sheet1.cell( row=1, column=2, value="数据描述" )
    i = 2;  # 从第三列开始写入
    for key, value in sd.items():
        # print( key )
        # 写入 row：行 column：列
        i += 1;
        sheet1.cell( row=1, column=i, value=key )
        sheet1.column_dimensions[ten2TwentySix( i)].width = 15.0
    # print( i )
    sheet1.cell( row=1, column=i + 1, value="SYS_RESP_CODE" )
    sheet1.cell( row=1, column=i + 2, value="SYS_RESP_DESC" )
    sheet1.cell( row=1, column=i + 3, value="执行" )
    # ---------设置excel字段的列宽
    sheet1.column_dimensions['A'].width = 15.0  # 第一行
    sheet1.column_dimensions[ten2TwentySix( 2 )].width = 30.0  # 第二行
    sheet1.column_dimensions[ten2TwentySix( i + 1 )].width = 20.0
    sheet1.column_dimensions[ten2TwentySix( i + 2 )].width = 20.0
    # ---------设置excel字段的默认值
    sheet1.cell( row=2, column=1, value="正常" )
    sheet1.cell( row=2, column=i + 1, value="000000000000" )
    sheet1.cell( row=2, column=i + 2, value="success" )
    sheet1.cell( row=2, column=i + 3, value="TRUE" )
    # print( i )

    f.save( "xml_参数化.xlsx" )  # 保存文件
    print( 'xml_参数化.xlsx 写入成功' )


####---dict遍历导出xml的报文入参txt文件
def xmlToTxt(dict):
    # 写之前，检查文件是否存在，存在则删除
    if os.path.exists( "xml_入参.txt" ):
        os.remove( "xml_入参.txt" )
    # 以写的方式打开文件，如果文件不存在，就会自动创建
    file_write_obj = open( "xml_入参.txt", "w" )
    for key, value in dict.items():
        # 添加xml注释
        file_write_obj.writelines( "        <!--" + value + "-->" )
        file_write_obj.write( '\n' )
        # 添加入参
        file_write_obj.writelines( "        <" + str( key ) + ">" + "${" + str( key ) + "}" + "</" + str( key ) + ">" )
        file_write_obj.write( '\n' )
    print( "xml_入参.txt写入成功" )


if __name__ == '__main__':
    datas = excelReadData( "接口字段.xlsx" )
    xmlToTxt( datas )
    excelWrite( datas )