xlwings详细使用
在我们操作之前可以先了解下,如下内容:
- 新建:创建一个不存在的工作薄或者工作表
- 打开:打开一个已经存在的工作薄
- 引用:就是告诉程序,你要操作哪个对象。比如你打开了A、B、C三个工作薄,现在你想操作A工作薄,就要先引用A
- 激活:我们可以同时打开多个工作薄,但是一次只能操作一个工作簿,我们正在操作的这个工作薄称为**当前活动工作薄。
在xlwings中
- Excel程序用App来表示,多个Excel程序集合用Apps表示;
- 单个工作簿用Book表示,工作簿集合用Books表示;
- 单个工作表用Sheet表示,工作表集合用Sheets表示;
- 区域用Range表示,既可以是一个单元格,也可以是一片单元格区域。
对Excel进行操作主要使用如下三个类:
1 import xlwings as xw 2 xw.App # 打开一个excel应用 3 xw.Book # 创建一个工作薄 4 xw.Sheet # 创建一个工作表
初试:创建一个excel表格并保存
1 import xlwings as xw 2 3 # 打开excel,参数visible表示处理过程是否可视,add_book表示是否打开新的Excel程序 4 with xw.App(visible=True,add_book=False) as app: 5 # 创建一个工作薄 6 book = app.books.add() 7 # 工作薄中创建一个sheet表 8 sht = book.sheets.add() 9 # 向表格的A1单元格写入“Hello Python” 10 sht.range('A1').value = 'Hello Python' 11 # 保存 12 book.save('d:\\test.xlsx')
理解App
App就是我们打开的一个Excel应用,在我们程序员看来一个App对象就是一个Excel的实例,在此实例下创建工作薄。因此我们要创建工作簿,就必须先创建App实例。一个App实例可以创建多个工作簿Book。使用xlwings可以创建一个或者多个App,而每个App中又可以创建多个工作薄Book,并且多个App之间是相互独立的。
要使用xlwings就需要先引用该库
import xlwings as xw
引入之后,我们可以查看xw下所有的app
1 Apps = xw.apps 2 count = Apps.count 3 print(count) # 打印个数是1,是指当前打开的这个app
创建App
我们可以通过xw.app()创建一个新的app实例
app=xw.App(visible=True,add_book=False) # 当然也可以通过app.visible = True设置可见性,visible设置是否展示工作簿,True展示,False不展示;add_book 是否添加工作簿,True添加,False不添加
在操作一个app对象的时候要先引用工作薄,但是引用并不代表激活,激活就是当前操作的工作薄。
常用的属性有:
app.screen_updating:打开屏幕更新,我们可以看到xlwings对Excel进行操作的过程,关闭更新可以加速脚本运行。默认是打开的。 app.display_alerts:在使用Excel的过程中,经常会遇到一些提醒信息,比如关闭前的保存提示、数据有效性的警告窗口,若想隐藏这些窗口可以设置成False。如果提醒信息是需要反馈的,Excel会选择默认的方式True |
1 import xlwings as xw 2 3 app = xw.App(visible=False, add_book=False) # 界面设置 4 app.display_alerts = False # 关闭提示信息 5 app.screen_updating = False # 关闭显示更新 6 7 wb = app.books.add() # 创建新的工作簿 8 sht = wb.sheets['Sheet1'] # 实例化工作表 9 sht.range('A1').value = 'Hello World!' 10 print(sht.range('A1').value) # 读取 11 wb.close() 12 app.kill()
其中关闭app有两种方式,通过测试使用kill()函数更快些。
app.kill():通过杀掉进程,强制Excel app退出 或 app.quit():退出excel程序,不保存任何工作簿
工作簿Book与Books
前面介绍了app,并且一个app可以包含多个工作薄,如何在app中创建工作薄呢?
创建Book对象
官方给出的创建工作薄的方式如下:
xw.Book |
xw.books | |
---|---|---|
新建工作簿 |
|
|
未保存的工作簿 |
|
|
有全路径的工作簿 |
|
|
两种方式的区别:方式1是创建一个新的App,并在新App中新建一个Book,方式2是在当前App下新建一个Book。
如果是打开一个已经存在的则使用:
wb = app.books.open('绝对或者相对路径的excel文件') 或 wb = xw.Book('绝对或者相对路径的excel文件')
xw.Book('绝对或者相对路径的excel文件')
既可以打开工作薄也可以引用工作簿
激活与保存
1 wb.activate() 2 # 如果steal_focus=True, 则把窗口显示到最上层,并且把焦点从Python切换到Excel 3 wb.activate(steal_focus=True)
保存工作薄
wb.save() # 或者使用指定路径保存 wb.save('存储路径')
关闭
1 import xlwings as xw 2 3 app=xw.App(visible=True,add_book=False) 4 app.display_alerts=False 5 app.screen_updating=False 6 # 文件位置:filepath,打开test文档,然后保存,关闭,结束程序 7 filepath=r'test.xlsx' 8 wb=app.books.open(filepath) 9 10 wb.save() 11 wb.close() 12 app.quit()
若想获取当前活动App中的所有books,可以直接通过下列方式
1 import xlwings as xw 2 3 books = xw.books # 当前活动App的工作簿集合 4 5 # books = app.books # 或者使用app.books获取
工作表Sheet与查看所有Sheets
新建Sheet
sht = wb.sheets.add() # 或者 sht = wb.sheets.add('test',after='sheet2')
参数1为工作表名称,省略的话为Excel默认名称,参数2为插入位置,可选before
或者after,
若想引用某一个Sheet,可以通过下面方式
sht = wb.sheets('sheet1') # 指定名称获取sheet工作表 sht = wb.sheets(1) # 根据序号获取 sht = xw.sheets.active #获取当前活动的工作表
1 import xlwings as xw 2 3 app=xw.App(visible=True,add_book=False) 4 app.display_alerts=False 5 app.screen_updating=False 6 # 文件位置:filepath,打开test文档,然后保存,关闭,结束程序 7 filepath=r'test.xlsx' 8 wb=app.books.open(filepath) 9 10 # add()是在现有的sheets集合列表中追加新的Sheet 11 sht1 = wb.sheets.add() 12 sht2 = wb.sheets.add() 13 print(wb.sheets.count) 14 15 sht3 = wb.sheets(1) 16 # sht1.activate() 17 sht3.range('A1').value = 'Hello Running' 18 19 wb.save('test1.xlsx') 20 wb.close() 21 app.quit()
sheet对象可以调用的方法有:
sheet.activate sheet.charts sheet.index sheet.api sheet.clear sheet.name sheet.autofit sheet.clear_contents sheet.names sheet.book sheet.delete sheet.pictures sheet.cells sheet.impl sheet.range ......
常用的有:
1 sht.clear() # 清除工作表所有内容和格式 2 sht.clear_contents() # 清除工作表的所有内容但是保留原有格式 3 sht.delete() # 删除工作表 4 sht.autofit('c') # 自动调整列宽 5 sht.autofit('r') # 自动调整行高 6 sht.autofit() # 自动调整行高列宽 7 sht.select() # 在活动工作簿中选择
可以通过属性获取获取工作表的名称、所有单元格的区域对象、当前工作表的索引值
sht.name sht.cells sht.index sht.names
引用区域与单元格操作
在操作区域或者单元格之前,首先就要引用他们,其实就是表明你要操作的区域或者单元格是哪些。可以认为区域是多个单元格。引用区域的方式有很多种,下面列举一下常见的引用方式:
1 xw.Range('A1:D4') 2 xw.Range((1,1), (4,4)) 3 xw.Range(xw.Range('A1'),xw.Range('D4')) 4 xw.Range(xw.Range('A1:E6'),xw.Range('C3:D7')) 5 xw.Range('NamedRange') 6 app.range("A1") # 注意是小写的range 7 sht.range('A1') 8 xw.books['MyBook.xlsx'].sheets[0].range('A1') 9 sht['A1'] 10 sht['A1:D4'] 11 sht[0,5] 12 sht[:5,:5]
区域管理可以通过如下方式:
1 range.offset(row_offset=5,column_offset=2) #表示偏移,row_offset行偏移量(正数表示向下偏移,负数相反),column_offset列偏移量(正数表示向右偏移,负数相反) 注意:是将选区范围进行偏移,内容不进行偏移 2 range.expand(mode='down') # 扩展区域,参数可选取 'down' , 'right' ,'table' ,类似我们使用向下、向右或者下右方的区域扩展操作。 3 range.resize(row_size=4, column_size=2) #表示调整选中区域的大小,参数表示调整后区域的行、列的数量。4 range.current_region #表示全选 类似Ctrl + A
对区域或单元格进行操作:
1)存储数据
1 #储存单个值 ".value“属性 2 sht.range('A1').value=1 3 # 储存列表 4 sht.range('A1').value=[1,2,3] # 将列表[1,2,3]储存在A1:C1中 5 sht.range('A1').options(transpose=True).value=[1,2,3] # 将列表[1,2,3]储存在A1:A3中 6 sht.range('A1').options(expand='table').value=[[1,2],[3,4]] # 将2x2表格,即二维数组,储存在A1:B2中,如第一行1,2,第二行3,4
2)读取数据
1 #读取单个值 2 a=sht.range('A1').value # 将A1的值,读取到a变量中 3 print(a) 4 #将值读取到列表中 5 a=sht.range('A1:A2').value #将A1到A2的值,读取到a列表中 6 print(a) 7 a=sht.range('A1:B2').value # 将第一行和第二行的数据按二维数组的方式读取 8 print(a)
3)清除与删除
1 rng.clear_contents() # 清除range的内容 2 rng.clear() # 清除格式和内容 3 rng.delete(shift=None) # 删除
4)其他设置
1 rng.number_format # 获取数字格式 2 rng.number_format = '0.00%' # 设置数字格式 3 rng.insert(shift=None, copy_origin='format_from_left_or_above') 4 rng.row# 返回区域第一行的行号 5 rng.column # 返回区域的第一列的号,注意返回的列号不是ABCD,而是1234 6 7 # 获取行高 或者设置行高 8 rng.row_height 9 rng.row_height = 20 10 11 # 获取列宽或设置列宽 12 rng.column_width 13 rng.column_width = 20 14 15 # 自适应行高列宽 16 rng.autofit() 17 rng.columns.autofit() 18 rng.rows.autofit() 19 20 # 合并单元格 21 rng.merge(across=False) 22 rng.merge_area # 返回合并单元格区域 23 rng.merge_cells # 返回True或者False,测试是否在合并单元格区域 24 rng.unmerge() # 取消单元格合并 25 26 # 背景色 27 rng.color # 获取指定区域的背景色 28 xw.Range('A1').color = (255,255,255) # 设置背景色 29 xw.Range('A2').color = None # 去除背景色
其他参考
1 range.add_hyperlink range.clear_contents range.count 2 range.address range.color range.current_region 3 range.api range.column range.end 4 range.autofit range.column_width range.expand 5 range.clear range.columns range.formula 6 ...等等
range.add_hyperlink('https://www.baidu.com','百度') # 单元格他砍价超链接,参数1是url,参数2是单元格显示文字,参数3可省略,默认显示点击提示信息 range.color = (128,128,128) # RGB通道颜色,可获取or设置 range.row/column # 获取第几行/列,注意是第几而不是下标 range.formula # 可以设置计算表达式,用来进行表内计算 range.current_region # 返回当前range所在区域的区域表达,这个比较难描述,好比一个Excel中互相连接的单元格都是连城一片,两个片之间没有任何相邻就是互相独立的。 range.count # 返回这个range中共有多少单元格,合并单元格仍然按未合并的算 range.offset(a,b) # 获取到当前range向右a格,向下移动b格同样大小的那片区域,ab可以为负值 range.rows/columns # 返回行/列的各个range对象 range.expand
参考案例代码:
1)批量写入并读取数据
1 import xlwings as xw 2 3 wb = xw.Book() 4 sht = wb.sheets.active 5 # 向工作表中写入行列值 6 for i in range(1, 6): 7 for j in range(1, 6): 8 sht.range(i, j).value = '({}, {})'.format(i, j) 9 print(sht.range((1, 1), (5, 5)).expand().value) # 批量读取 10 print(sht.range(1, 1).expand('right').value) # 按行读 11 print(sht.range(1, 1).expand('down').value) # 按列读 12 wb.close() 13
2)提前设置好表格的颜色
1 import xlwings as xw 2 from itertools import product 3 4 app = xw.App(visible=False) # 隐藏Excel 5 wb = app.books.open('test.xlsx') # 打开工作簿 6 sht = wb.sheets['Sheet1'] # 实例化工作表 7 for cell in list(map(''.join, product('ABCDEFGH', '1'))): # A1 B1 C1 D1 E1 F1 G1 H1 8 print(cell, sht.range(cell).color) # 填充颜色 9 wb.close()
3)局中插入图片
1 import os 2 import xlwings as xw 3 4 wb = xw.Book() 5 sht = wb.sheets['Sheet1'] 6 rng = sht.range('A1') 7 fileName = os.path.join(os.getcwd(), 'aa.png') 8 width, height = 120, 100 # 指定图片大小 9 left = rng.left + (rng.width - width) / 2 # 居中 10 top = rng.top + (rng.height - height) / 2 11 sht.pictures.add(fileName, left=left, top=top, width=width, height=height) 12 wb.save('test2.xlsx') 13 wb.close()
综合案例:
1 import xlwings as xw 2 3 wb = xw.Book() 4 sht = wb.sheets[0] 5 6 info_list = [['110202111111234','帐篷',5],['110202111118891','行李箱','16'],['110202111111004','微波炉','20'],['110202111132741','电冰箱','13'],['110202111109852','乐事薯片','30'],['110202111112030','鲁花花生油','12'],['110202111190391','羽绒服','9'],['110202111122319','防晒霜','18'],] 7 # 写入表头 8 titles = [['商品编号','商品名称','数量']] 9 sht.range('a1').value = titles 10 sht.range('a2').value = info_list # 写入数据 11 wb.save('goods.xlsx') # 保存数据
若想更新里面的数据,由于有些商品被卖出,商品数量就会发生变化。另外还有一批货是新引入的。参考代码如下:
1 import xlwings as xw 2 3 wb = xw.Book() 4 sht = wb.sheets[0] 5 6 7 info_list = [['110202111111234','帐篷','5'], 8 ['110202111118891','行李箱','16'], 9 ['110202111111004','微波炉','20'], 10 ['110202111132741','电冰箱','13'], 11 ['110202111109852','乐事薯片','30'], 12 ['110202111112030','鲁花花生油','12'], 13 ['110202111190391','羽绒服','9'], 14 ['110202111122319','防晒霜','18'], 15 ] 16 # 写入表头 17 titles = [['商品编号','商品名称','数量']] 18 sht.range('a1').value = titles 19 20 # 写入数据 21 sht.range('a2').value = info_list 22 23 # 保存数据 24 wb.save('goods.xlsx') 25 26 # 读取数据 27 goods_list = sht.range('a2').expand('table').value 28 29 for goods in goods_list: 30 goods[0] = str(int(goods[0])) 31 goods[2] = int(goods[2]) 32 print(goods_list) 33 new_info = [['110202111111234','帐篷',5], 34 ['110202111118891','行李箱',16], 35 ['110202111111004','微波炉',20], 36 ['110202111132741','电冰箱',10], 37 ['110202111124660','羊毛衫',8], 38 ['110202111109852','乐事薯片',10], 39 ['110202111112030','鲁花花生油',12], 40 ['110202111190391','羽绒服',0], 41 ['110202111122319','防晒霜',9], 42 ['110202111124560','牛仔裤',18], 43 ['110202111134798','老爹鞋',11]] 44 # 去重 45 extra = [i for i in new_info if i not in goods_list] 46 # print(extra) 47 48 # 读取extra每个商品的包裹号,判断是否存在并更新,然后添加 49 ids = sht.range(2, 1).expand('down').value 50 51 ids = [str(int(id)) for id in ids] 52 rows = len(sht.range('a2').expand('table').value) 53 # 更新已有数据的库存 54 for goods in extra: 55 if goods[0] in ids: 56 row_number = ids.index(goods[0]) 57 print(row_number,goods[1]) 58 sht[row_number+1,2].value = goods[2] 59 else: 60 for i in range(3): 61 sht[rows+1,i].value =goods[i] 62 rows+=1 63 64 wb.save('goods.xlsx')
设置字体样式和字体颜色
1 # coding: utf-8 2 3 import xlwings as xw 4 5 app=xw.App(visible=False,add_book=False) 6 filepath = '../data/test.xlsx' 7 wb=app.books.open(filepath) 8 sht = wb.sheets('Sheet1') 9 font_name = sht.range('A1').api.Font.Name # 获取字体名称 10 font_size = sht.range('A1').api.Font.Size # 获取字号 11 bold = sht.range('A1').api.Font.Bold # 获取是否加粗,True--加粗,False--未加粗 12 color = sht.range('A1').api.Font.Color # 获取字体颜色 13 print(font_name) 14 print(font_size) 15 print(bold) 16 print(color) 17 print('-----设置-----') 18 sht.range('A1').api.Font.Name = 'Times New Roman' # 设置字体为Times New Roman 19 sht.range('A1').api.Font.Size = 15 # 设置字号为15 20 sht.range('A1').api.Font.Bold = True # 加粗 21 sht.range('A1').api.Font.Color = (255,0,0) # 设置为红色RGB(255,0,0) 22 font_name = sht.range('A1').api.Font.Name # 获取字体名称 23 font_size = sht.range('A1').api.Font.Size # 获取字体大小 24 bold = sht.range('A1').api.Font.Bold # 获取是否加粗,True--加粗,False--未加粗 25 color = sht.range('A1').api.Font.Color # 获取字体颜色 26 print(font_name) 27 print(font_size) 28 print(bold) 29 print(color) 30 wb.save() 31 wb.close() 32 app.quit()
补充资料
获取工作表的总行数和总列数
1 workbook=xw.Book(r'path) 2 workbook.sheets[1].range(1, 1).expand().shape
返回:(25087, 3)
标签:sht,wb,app,xlwings,Excel,介绍,A1,range,xw From: https://www.cnblogs.com/ppguo/p/16767580.html