资料

xlwings 官网：https://docs.xlwings.org/en/stable/index.html

运营学 Python ｜ Python&Excel 联动(上)

注意：使用这个模型需要安装 Excel，相较于 openpyxl 可能效率不足，但是可实时看到结果

操作流程

  1. 打开 Jupyter Notebook—新建 Python 文件
  2. 安装模块 !pip3 install xlwings --user
  3. xlwings 的操作逻辑依次为 App—Book—Sheet—Range

创建场景

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import xlwings as xw

# app应用 Excel程序
app = xw.App(visible=True, add_book=False)

# 工作簿 book
wb = app.books.add()

# 表 sheet
# sht = wb.sheets.add('first_sht')  # 增
sht = wb.sheets('Sheet1')         # 选

# 单元格
sht.range('A1').value = '简说 Python'

# 保存与退出
wb.save('xlwings_wb.xlsx')
wb.close()
app.quit()

修改场景

1
2
3
4
5
6
7
8
9
10
import xlwings as xw

app = xw.App(visible=True, add_book=False)
book = app.books.open('xlwings_wb.xlsx')  # 打开excel文件
sheet = book.sheets('Sheet1')             # 选择sheet
sheet.range('B2').value = '简说Python'

book.save(book.fullname)
book.close()
app.quit()

xlwings 语法

https://docs.xlwings.org/en/stable/api/index.html

Books/Book

Book: https://docs.xlwings.org/en/stable/api/book.html

1
2
3
Books.active
Books.add()
Books.open()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Book.activate()
Book.api
Book.app
Book.caller()
Book.close()
Book.fullname
Book.json()
Book.macro()
Book.name
Book.names
Book.render_template()
Book.save()
Book.selection
Book.set_mock_caller()
Book.sheet_names
Book.sheets
Book.to_pdf()

Sheets/Sheet

Sheet: https://docs.xlwings.org/en/stable/api/sheet.html

1
2
Sheets.active
Sheets.add()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sheet.activate()
Sheet.api
Sheet.autofit()
Sheet.book
Sheet.cells
Sheet.charts
Sheet.clear()
Sheet.clear_contents()
Sheet.clear_formats()
Sheet.copy()
Sheet.delete()
Sheet.freeze_panes
Sheet.index
Sheet.name
Sheet.names
Sheet.page_setup
Sheet.pictures
Sheet.range()
Sheet.render_template()
Sheet.select()
Sheet.shapes
Sheet.tables
Sheet.to_html()
Sheet.to_pdf()
Sheet.used_range
Sheet.visible

Range

range:https://docs.xlwings.org/en/stable/api/range.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Range.add_hyperlink()         Range.address
Range.adjust_indent()         Range.api
Range.autofill()              Range.autofit()
Range.clear()                 Range.clear_contents()
Range.clear_formats()         Range.color
Range.column                  Range.column_width
Range.columns                 Range.copy()
Range.copy_picture()          Range.count
Range.current_region          Range.delete()
Range.end()                   Range.expand()
Range.formula                 Range.formula2
Range.formula_array           Range.get_address()
Range.group()                 Range.has_array
Range.height                  Range.hyperlink
Range.insert()                Range.last_cell
Range.left                    Range.merge()
Range.merge_area              Range.merge_cells
Range.name                    Range.note
Range.number_format           Range.offset()
Range.options()               Range.paste()
Range.raw_value               Range.resize()
Range.row                     Range.row_height
Range.rows                    Range.select()
Range.shape                   Range.sheet
Range.size                    Range.table
Range.to_pdf()                Range.to_png()
Range.top                     Range.ungroup()
Range.unmerge()               Range.value
Range.width                   Range.wrap_text

一些功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
sheet1.range("A1")
sheet1.range("A1:C3")
sheet1.range((1,1))
sheet1.range((1,1), (3,3))
sheet1.range("NamedRange")

# Or using index/slice notation
sheet1["A1"]
sheet1["A1:C3"]
sheet1[0, 0]
sheet1[0:4, 0:4]
sheet1["NamedRange"]

sheet1.range('A1').color = (255, 255, 255)  # or '#ffffff
sheet1.range('A2').color = None

sheet['A1'].font.name = 'Calibri'
sheet['A1'].font.bold = True
sheet['A1'].font.color = (255, 0, 0)  # or '#ff0000'
sheet['A1'].font.italic = True


案例