# # 引入库
import xlwings as xw
import time
#新建工作簿 (如果不接下一条代码的话,Excel只会一闪而过,卖个萌就走了)
练习的时候建议直接用下面这条,这样的话就不会频繁打开新的Excel
wb = xw.Book('example.xlsx')
print('-----新建sheet-----')
wb.sheets.add('jenny')
print('-----引用-----')
sht = wb.sheets[0]
#sht = wb.sheets[第一个sheet名]
rng = sht.range('a1')
print ("引用单元格: "+str(rng.value))
rng = sht.range('a1:a5')
#rng = sht['a1:a5']
#rng = sht[:5,0]
print ("引用区域: "+str(rng.value))
print('-----写入-----')
print('-----写入单元格-----')
sht.range('a1').value = 'Hello'
time.sleep(3)
print('-----按行写入-----')
sht.range('a1').value = [1,2,3,4]
time.sleep(3)
sht.range('a2:a5').value = [5,6,7,8]
print('-----按列写入-----')
sht.range('a2').options(transpose=True).value = [5,6,7,8]
sht.range('a6').expand('table').value = [['a','b','c'],['d','e','f'],['g','h','i']]
#B2到E5单元格全部赋同样的值
sht.range("B2","E5").value="bb"
print('-----读取单元格内容-----')
print("读取A1:D4: "+str(sht.range('a1:d4').value))
a = sht.range('a1:d1').value
print("读取A1:D1: "+str(a))
for i in a:
print("读取单元格: "+str(i))
print("单元格数据类型: "+str(type(i)))
a = sht.range('a:a').value
print (a)
print(len(a))
rng = sht.range('a1').expand('table')
nrows = rng.rows.count
a = sht.range(f'a1:a{nrows}').value
print("读取excel的第一列: "+str(a))
ncols = rng.columns.count
#用切片
fst_col = sht[0,:ncols].value
print("读取excel的第一行: "+str(fst_col))
print('-----读取格式-----')
font_name = sht.range('A1').api.Font.Name # 获取字体名称
font_size = sht.range('A1').api.Font.Size # 获取字体大小
bold = sht.range('A1').api.Font.Bold # 获取是否加粗,True--加粗,False--未加粗
color = sht.range('A1').api.Font.Color # 获取字体颜色
print("A1单元格字体:"+font_name)
print("A1单元格大小:"+str(font_size))
print("A1单元格是否加粗:"+str(bold))
print("A1单元格颜色:"+str(color))
print('-----设置格式-----')
sht.range('A1').api.Font.Name = 'Times New Roman' # 设置字体为Times New Roman
sht.range('A1').api.Font.Size = 15 # 设置字号为15
sht.range('A1').api.Font.Bold = True # 加粗
sht.range('A1').api.Font.Color = 0x0000ff # 设置为红色RGB(255,0,0)
font_name = sht.range('A1').api.Font.Name # 获取字体名称
font_size = sht.range('A1').api.Font.Size # 获取字体大小
bold = sht.range('A1').api.Font.Bold # 获取是否加粗,True--加粗,False--未加粗
color = sht.range('A1').api.Font.Color # 获取字体颜色
print("A1单元格字体:"+font_name)
print("A1单元格大小:"+str(font_size))
print("A1单元格是否加粗:"+str(bold))
print("A1单元格颜色:"+str(color))
print('-----设置背景色-----')
sht.range('A6:V10').color = (255,0,255)
time.sleep(3)
sht["A1048576"].end('up').row #最大行,根据单元格位置
print(sht["XFD1"].end('left').column) #最大列,根据单元格位置
print('-----清理内容,清理数据及格式-----')
sht.range('A6:V10').clear()
print('-----按行写入-----')
sht.range('b2').value = ["papi","lucy","kunal","snoopy"]
print('-----按列写入-----')
sht.range('b3').options(transpose=True).value = ["year","month"]
print('-----删除行和列,插入行和列-----')
sht.api.rows(1).delete #删除首行
time.sleep(3)
sht.api.columns(1).delete# 删除首列
sht.api.columns(2).insert #插入列
sht.api.rows(2).insert #插入行
time.sleep(3)
print('-----合并单元格-----')
sht.range('A1:A5').api.merge()
wb.save('example.xlsx')
wb.close()
app.quit()
#
手机扫一扫
移动阅读更方便
你可能感兴趣的文章