import xlwings as xw

# 创建app
app = xw.App(visible= True, add_book= False)
app.display_alerts = True
app.screen_updating = True

# 创建工作簿
wb = app.books.add()
sht = wb.sheets[0]
sht.name = 'sht'

# 读取工作簿
# 复制工作表
wb_open = app.books.open('open.xlsx')
sht_open = wb_open.sheets[0] # 等同于 wb_open.sheets(1)
sht_open.copy(after= sht)

# 数据第1行 == excel.row(2)==excel第2行 == xlwings.sht[1]==xlwings第2行 == pandas.DataFrame.iloc[0]==pandas第1行
# excel.row(1)==标题行 , xlwings.sht[1]==数据第1行 , pandas.DataFrame.iloc[1]==数据第2行
# excel.column(1)==第1列 , xlwings.sht[,1]==第2列 , pandas.DataFrame.iloc[,1]==第2列
# 即python使用0-based索引方式, excel使用1-based索引方式, 且pandas将列标题从数据中剥离
n = 10
df = pd.DataFrame(np.zeros(n), columns= ['df'])
for i in range(n):
    df.iloc[i,0] = sht_open[i+1,0].value

# 选择单元格
sht[0,0] # sht[行,列] 0-based
sht.range('A1') # sht.range("列行") 1-based
sht.range(1,1) # sht.range(行,列) 1-based
sht.cells(1,1) # sht.cells(行,列) 1-based
sht.cells(1,'A') # sht.cells(行,列) 1-based

# 写入数据
sht.range('A1').value = 'A1'
sht.range('A2').expand('table').value = ['A2','B2']
sht.range('A3').options(transpose= True).value = ['A3','A4']

# 选择删除
sht.api.Range("C:D, F:G").Delete()

# 调整格式
sht.autofit()
sht.range('A1:B4').row_height = 40
sht.range('A1:B4').column_width = 17
sht.range('A1:B4').api.HorizontalAlignment = -4108
sht.range('A1').api.Font.Size = 20
sht.range('A1').api.Font.Bold = True
sht.range('A1:B1').api.Merge()
sht.range('A1').color = 255,200,255

# 保存关闭
wb.save('save.xlsx')
wb.close()
wb_open.close()
app.kill()

'''其他笔记'''
'''使用exec'''
i = 1
j = "A"
# sht.cells(i, eval（"'{}'".format(j))) # 调用值为字符的变量 必须带''
# eval（"{},'{}'".format(i,j)) # 输出元组 不能用于cells
eval（"sht.cells({},'{}')".format(i,j))