# coding=utf-8 
import xlwings 
import os 
import sys 
from itertools import product

# 当前路径，当前文件
cur_path, cur_file = os.path.split(os.path.abspath(sys.argv[0])) 
# 上级目录 ..\\WP_V2
# parent_path = os.path.abspath(os.path.join(cur_path, '../'))
filepath = os.path.join(cur_path, 'demo.xlsx')

# xlwings
# Sheet2 样式
# Sheet3 案例
# Sheet4 图表
with xlwings.App(visible=False, add_book=False) as app:
    app.display_alerts=False
    app.screen_updating=False

    if os.path.exists(filepath):
        book = app.books.open(filepath) 
        print('PID:{}, 打开文件:{}'.format(app.pid, book.name))
    else:
        book = app.books.add()  # 或者xlwings.Book()
        print('创建文件:{}'.format(filepath))

    try:
        book.sheets.add('Sheet2', after='Sheet1') 
        sht2 = book.sheets['Sheet2']
        print('创建Sheet2')
    except:
        sht2 = book.sheets['Sheet2']
        print('已存在Sheet2')

    # 删除Sheet1
    try:
        sht1 = book.sheets['Sheet1'] # book.sheets[0] 
        sht1.delete()
    except:
        pass 

#############################  样式 start  #############################
    # 设置值
    sht2.range('A1').value = [1,2,3] #行值
    sht2.range('A2').options(transpose=True).value=[1,2,3] #列值
    sht2.range('D1').options(expand='table').value=[[1,2],[3,4]] #表格

    # 设置公式
    sht2.range('H1').formula = '=SUM(A1:E1)'

    # 设置值2, 读取range范围的值
    for i in range(1, 6):
        for j in range(1, 6):
            sht2.range(10+i, 10+j).value = '({}, {})'.format(10+i, 10+j)
    # print(sht2.range((11,11), (15,15)).expand().value)  # 批量读取
    # print(sht2.range(11,11).expand('right').value)  # 按行读取
    print(sht2.range(11,11).expand('down').value)   # 按列读取
    # options(transpose=True) 按列读取, 否则按行读取
    # print(sht2.range('K11:O15').options(transpose=True).value)   # 按列读取

    # 返回单元格绝对路径
    print(sht2.range('K11').get_address())  # $K$11

    # range对象
    rng1 = sht2.range('A1') 
    # 删除
    # rng1.delete(shift=None) #删除单元格 
    # rng1.clear() # 删除内容和样式
    rng1.clear_contents() #删除内容

    # 合并单元格 
    # sht2.range('F1:H3').merge(across=False)
    # 取消合并单元格 
    # sht2.range('F1:H3').unmerge()
    #设置颜色 
    # (product('ABCDEFGH', '1') 返回值是元组
    for cell in list(map(''.join, product('ABCDE', '1'))):
        sht2.range(cell).color = '#b1c5fd'
    #设置图片
    A10 = sht2.range('A10')
    sht2.pictures.add(os.path.join(cur_path, 'demo.png'), left=A10.left, top=A10.top, width=120, height=100)
    # 设置超链接
    sht2.range('I1').add_hyperlink(address='www.baidu.com', text_to_display='百度', screen_tip='百度一下,你就知道')
    print(sht2.range('I1').hyperlink)
    #设置数字格式 
    sht2.range('A1:A3').number_format = '0.00%' 
    # 行号 1 
    # print(sht2.range('A1:A3').row) 
    # 列号 1 
    # print(sht2.range('A1:A3').column) 
    # 设置行高和列宽 
    sht2.range('A1:A3').row_hight=20 
    sht2.range('A1:A3').column_width=20 
    # 自适应行高和列宽 
    sht2.range('A1:A3').autofit() 
    sht2.range('A1:A3').rows.autofit() 
    sht2.range('A1:A3').columns.autofit() 
    # 字体
    K1 = sht2.range('K1')
    K1.value = '中华儿女'
    K1.font.name='黑体'
    K1.font.color = '#8B0000' # todo
    K1.font.size=28
    K1.font.bold=True
    # 边框的风格和粗细
    K1.api.Borders(9).LineStyle = 5
    K1.api.Borders(9).Weight = 3

    # 数字
    L1 = sht2.range('L1')
    L1.value = '100'
    L1.api.NumberFormat = '0.0'
    # -4108 水平居中, -4131 水平靠左, -4152 水平靠右
    L1.api.HorizontalAlignment = -4152
    # -4130 自动换行对齐, -4108垂直居中, -4160 垂直居上, -4107 垂直居下
    L1.api.VerticalAlignment = -4107
    
#############################  样式 end  #############################


#############################  案例 start  #############################
    try:
        book.sheets.add('Sheet3', after='Sheet2') 
        sht3 = book.sheets['Sheet3']
        print('创建Sheet3')
    except:
        sht3 = book.sheets['Sheet3']
        print('已存在Sheet3')

    # 表头和内容
    titles = [['商品编号', '商品名称', '数量']]
    info_list = [
        [101, '帐篷', 5],
        [104, '行李箱', 12],
        [102, '微波炉', 7],
        [105, '电冰箱', 18]
    ]

    # 写入表头和内容
    sht3.range('A1').value=titles
    sht3.range('A2').value=info_list

    # 读取数据
    goods_list = sht3.range('A2').expand('table').value
    new_info_list = [
        [108, '电视机', 15],
        [104, '行李箱', 12],
        [109, '空调', 8]
    ]
    # 差集
    new_goods_no = list(set([item[0] for item in new_info_list]) - set([item[0] for item in info_list]))
    extra_new_info_list = list(filter(lambda item:item[0] in new_goods_no, new_info_list))
    sht3.range('A2').value = info_list + extra_new_info_list
#############################  案例 end  #############################


#############################  图表 start  #############################
    try:
        book.sheets.add('Sheet4', after='Sheet3') 
        sht4 = book.sheets['Sheet4']
        print('创建Sheet4')
    except:
        sht4 = book.sheets['Sheet4']
        print('已存在Sheet4')

    # https://blog.csdn.net/weixin_46847902/article/details/125695047
#############################  图表 end    #############################

    # 保存关闭
    book.save(filepath) 
    book.close()