linux,  python

Python操作Excel (xlwings模块)

xlwings 需要安装有 Excel 软件,允许读取和写入 .xls和 .xlsx 两种格式文件并进行增删改查,可以调用 Excel 文件中 VBA 写好的程序,与 matplotlib 、 pandas 的兼容性强。

安装

xlwings 是一个非标准库,需要在命令行中安装,在终端(Mac)/命令行(Win)使用pip安装即可,一般不会出现什么问题。

pip install xlwings
pip install xlwings

前置知识

可以看到,和 xlwings 直接对接的是 apps,也就是 Excel 应用程序,然后才是 工作簿 books 和工作表 sheets,这点和 openpyxl 有较大区别,也正是因为此,xlwings 需要依然安装有 Excel 应用程序的环境。

打开Excel程序

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
#新建工作簿 (如果不接下一条代码的话,Excel只会一闪而过,卖个萌就走了)
import xlwings as xw app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 #新建工作簿 (如果不接下一条代码的话,Excel只会一闪而过,卖个萌就走了)
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
#新建工作簿 (如果不接下一条代码的话,Excel只会一闪而过,卖个萌就走了)

打开、保存、关闭工作薄、关闭程序

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
#打开工作簿
app.books.open(r'test.xlsx')#频繁打开表格
#wb = xw.Book('example.xlsx') #固定打开表格,练习的时候建议直接用这条,这样的话就不会频繁打开新的Excel
# 保存工作簿
wb.save()
# 关闭工作簿
wb.close()
# 关闭程序
app.quit()
import xlwings as xw app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 #打开工作簿 app.books.open(r'test.xlsx')#频繁打开表格 #wb = xw.Book('example.xlsx') #固定打开表格,练习的时候建议直接用这条,这样的话就不会频繁打开新的Excel # 保存工作簿 wb.save() # 关闭工作簿 wb.close() # 关闭程序 app.quit()
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭

#打开工作簿
app.books.open(r'test.xlsx')#频繁打开表格
#wb = xw.Book('example.xlsx') #固定打开表格,练习的时候建议直接用这条,这样的话就不会频繁打开新的Excel

# 保存工作簿
wb.save() 

# 关闭工作簿
wb.close()
 
# 关闭程序
app.quit() 

新建 Excel 文件

无论是新建还是打开都记得保存工作簿、关闭工作簿、关闭程序

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
wb = app.books.add()
wb.save(r'rew_test.xlsx')
wb.close()
app.quit()
import xlwings as xw app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 wb = app.books.add() wb.save(r'rew_test.xlsx') wb.close() app.quit()
import xlwings as xw

app = xw.App(visible=True, add_book=False)  # 程序可见,只打开不新建工作薄
app.display_alerts = False  # 警告关闭
app.screen_updating = False  # 屏幕更新关闭

wb = app.books.add()
wb.save(r'rew_test.xlsx')
wb.close()
app.quit()

读取内容

示例文件 test.xlsx 如下:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active
# 获取单个单元格的值
A1 = sheet.range('A1').value
print(A1)
# 获取横向或纵向多个单元格的值,返回列表
A1_A3 = sheet.range('A1:A3').value
print(A1_A3)
# 获取给定范围内多个单元格的值,返回嵌套列表,按行为列表
A1_C4 = sheet.range('A1:C4').value
print(A1_C4)
wb.save()
wb.close()
app.quit()
import xlwings as xw app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 wb = app.books.open(r'test.xlsx') sheet = wb.sheets.active # 获取单个单元格的值 A1 = sheet.range('A1').value print(A1) # 获取横向或纵向多个单元格的值,返回列表 A1_A3 = sheet.range('A1:A3').value print(A1_A3) # 获取给定范围内多个单元格的值,返回嵌套列表,按行为列表 A1_C4 = sheet.range('A1:C4').value print(A1_C4) wb.save() wb.close() app.quit()
import xlwings as xw

app = xw.App(visible=True, add_book=False)  # 程序可见,只打开不新建工作薄
app.display_alerts = False  # 警告关闭
app.screen_updating = False  # 屏幕更新关闭

wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active

# 获取单个单元格的值
A1 = sheet.range('A1').value
print(A1)

# 获取横向或纵向多个单元格的值,返回列表
A1_A3 = sheet.range('A1:A3').value
print(A1_A3)

# 获取给定范围内多个单元格的值,返回嵌套列表,按行为列表
A1_C4 = sheet.range('A1:C4').value
print(A1_C4)

wb.save()
wb.close()
app.quit()

返回值

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
姓名
['姓名', '大强', '二强']
[['姓名', '年龄', '城市'], ['大强', 34.0, '广州'], ['二强', 33.0, '上海'], ['三强', 32.0, '深圳']]
姓名 ['姓名', '大强', '二强'] [['姓名', '年龄', '城市'], ['大强', 34.0, '广州'], ['二强', 33.0, '上海'], ['三强', 32.0, '深圳']]
姓名
['姓名', '大强', '二强']
[['姓名', '年龄', '城市'], ['大强', 34.0, '广州'], ['二强', 33.0, '上海'], ['三强', 32.0, '深圳']]

写入数据

初始表格 test.xlsx 的内容:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active
# 写入 1 个单元格
sheet.range('A6').value = '小明'
# 写入 1 行记录
sheet.range('A7').value = ['小兰', 23, '长沙']
# 横向写入A8:C8
sheet.range('A8').value = ['小白',25,'杭州']
# 纵向写入D1:D8
sheet.range('D1').options(transpose=True).value = ['身高',1.7,1.75,1.8,1.83,1.65,1.7,1.88]
# 写入范围内多个单元格
sheet.range('A9').options(expand='table').value = [['小蓝',22,'合肥',1.66], ['小紫',32,'南昌',1.67]]
wb.save()
wb.close()
app.quit()
import xlwings as xw app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 wb = app.books.open(r'test.xlsx') sheet = wb.sheets.active # 写入 1 个单元格 sheet.range('A6').value = '小明' # 写入 1 行记录 sheet.range('A7').value = ['小兰', 23, '长沙'] # 横向写入A8:C8 sheet.range('A8').value = ['小白',25,'杭州'] # 纵向写入D1:D8 sheet.range('D1').options(transpose=True).value = ['身高',1.7,1.75,1.8,1.83,1.65,1.7,1.88] # 写入范围内多个单元格 sheet.range('A9').options(expand='table').value = [['小蓝',22,'合肥',1.66], ['小紫',32,'南昌',1.67]] wb.save() wb.close() app.quit()
import xlwings as xw

app = xw.App(visible=True, add_book=False)  # 程序可见,只打开不新建工作薄
app.display_alerts = False  # 警告关闭
app.screen_updating = False  # 屏幕更新关闭

wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active

# 写入 1 个单元格
sheet.range('A6').value = '小明'

# 写入 1 行记录
sheet.range('A7').value = ['小兰', 23, '长沙']

# 横向写入A8:C8
sheet.range('A8').value = ['小白',25,'杭州']

# 纵向写入D1:D8
sheet.range('D1').options(transpose=True).value = ['身高',1.7,1.75,1.8,1.83,1.65,1.7,1.88]

# 写入范围内多个单元格
sheet.range('A9').options(expand='table').value = [['小蓝',22,'合肥',1.66], ['小紫',32,'南昌',1.67]]


wb.save()
wb.close()
app.quit()

修改后test.xlsx表格内容:

获取数据范围(几行几列)

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active
shape = sheet.used_range.shape
print(shape)
wb.save()
wb.close()
app.quit()
import xlwings as xw app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 wb = app.books.open(r'test.xlsx') sheet = wb.sheets.active shape = sheet.used_range.shape print(shape) wb.save() wb.close() app.quit()
import xlwings as xw

app = xw.App(visible=True, add_book=False)  # 程序可见,只打开不新建工作薄
app.display_alerts = False  # 警告关闭
app.screen_updating = False  # 屏幕更新关闭

wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active


shape = sheet.used_range.shape
print(shape)


wb.save()
wb.close()
app.quit()

修改行高列宽、输出行高列宽

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active
# 修改
sheet.range('A1:A2').row_height = 30
sheet.range('A1:A2').column_width = 20
# 输出
print(sheet.range('A1:A2').row_height)
print(sheet.range('A1:A2').column_width)
wb.save()
wb.close()
app.quit()
import xlwings as xw app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 wb = app.books.open(r'test.xlsx') sheet = wb.sheets.active # 修改 sheet.range('A1:A2').row_height = 30 sheet.range('A1:A2').column_width = 20 # 输出 print(sheet.range('A1:A2').row_height) print(sheet.range('A1:A2').column_width) wb.save() wb.close() app.quit()
import xlwings as xw

app = xw.App(visible=True, add_book=False)  # 程序可见,只打开不新建工作薄
app.display_alerts = False  # 警告关闭
app.screen_updating = False  # 屏幕更新关闭

wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active

# 修改
sheet.range('A1:A2').row_height = 30
sheet.range('A1:A2').column_width = 20

# 输出
print(sheet.range('A1:A2').row_height)
print(sheet.range('A1:A2').column_width)



wb.save()
wb.close()
app.quit()

获取、设置公式

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active
# 获取公式
print(sheet.range('B11').formula_array)
# 写入公式
sheet.range('B11').formula='=SUM(B2,B3)'
wb.save()
wb.close()
app.quit()
import xlwings as xw app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 wb = app.books.open(r'test.xlsx') sheet = wb.sheets.active # 获取公式 print(sheet.range('B11').formula_array) # 写入公式 sheet.range('B11').formula='=SUM(B2,B3)' wb.save() wb.close() app.quit()
import xlwings as xw

app = xw.App(visible=True, add_book=False)  # 程序可见,只打开不新建工作薄
app.display_alerts = False  # 警告关闭
app.screen_updating = False  # 屏幕更新关闭

wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active

# 获取公式
print(sheet.range('B11').formula_array)
# 写入公式
sheet.range('B11').formula='=SUM(B2,B3)'



wb.save()
wb.close()
app.quit()

获取、设置及清除颜色格式

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄
app.display_alerts = False # 警告关闭
app.screen_updating = False # 屏幕更新关闭
wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active
# 获取颜色
print(sheet.range('C1').color)
# 设置颜色
sheet.range('C1').color = (255, 0, 120)
# 清除颜色
sheet.range('C1').color = None
wb.save()
wb.close()
app.quit()
import xlwings as xw app = xw.App(visible=True, add_book=False) # 程序可见,只打开不新建工作薄 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 wb = app.books.open(r'test.xlsx') sheet = wb.sheets.active # 获取颜色 print(sheet.range('C1').color) # 设置颜色 sheet.range('C1').color = (255, 0, 120) # 清除颜色 sheet.range('C1').color = None wb.save() wb.close() app.quit()
import xlwings as xw

app = xw.App(visible=True, add_book=False)  # 程序可见,只打开不新建工作薄
app.display_alerts = False  # 警告关闭
app.screen_updating = False  # 屏幕更新关闭

wb = app.books.open(r'test.xlsx')
sheet = wb.sheets.active



# 获取颜色
print(sheet.range('C1').color)
# 设置颜色
sheet.range('C1').color = (255, 0, 120)
# 清除颜色
sheet.range('C1').color = None



wb.save()
wb.close()
app.quit()

以上仅是针对一些常用操作给出代码示例与讲解,更多的操作可以阅读官方文档。

留言

您的邮箱地址不会被公开。 必填项已用 * 标注