示例：
#!/usr/bin/python
# -*- coding:utf-8 -*-
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter, column_index_from_string
# ? 参考链接： https://zhuanlan.zhihu.com/p/344123753
# ? 新建
wb = Workbook() # 实例化
ws = wb.active # 激活 worksheet
# 方式一：数据可以直接分配到单元格中(可以输入公式)
ws['A1'] = 42
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
# 方式二：可以附加行，从第一列开始附加(从最下方空白处，最左开始)(可以输入多行)
ws.append(['n1', 11, 12])
ws.append(['n2', 21, 22])
wb.save('demo.xlsx')
# ? 打开已有 xsl
wb = load_workbook('demo.xlsx')
ws = wb.active # 激活 worksheet
# ? 创建表
ws_n3 = wb.create_sheet("x-sheet3") # 方式一：插入到最后(default)
ws_n2 = wb.create_sheet("x-sheet2", 1) # 方式二：插入到指定位置
# ? 选择表
print(wb.sheetnames)
ws0 = wb[wb.sheetnames[0]]
ws3 = wb["x-sheet2"]
ws4 = wb.get_sheet_by_name("x-sheet3")
# ? 单元格访问
ws = ws_n2
ws['A1'] = 'A1' # 设置值
cell = ws['A1']
print(cell.value)
d22 = ws.cell(row=2, column=2)
d33 = ws.cell(row=3, column=3, value=10) # 设置空值时使用默认值
for i in range(4):
ws['A{0}'.format(i + 1)] = i + 1
ws['B{0}'.format(i + 1)] = i + 1
ws['C{0}'.format(i + 1)] = i + 1
ws['D{0}'.format(i + 1)] = i + 1
# ? 取多个单元格
# %% 取出单个列
print('取出单个列')
cell_range = ws['A'] # 取出来 A 列，用 ws[1] 也可以
for cell in cell_range:
print('{0}{1}'.format(cell.column_letter, cell.row), cell.value) # cell.column, cell.col_idx
# %% 取出多列
print('取出多列')
cell_range = ws['A:B'] # 取出来两列
for cells in cell_range:
for cell in cells:
print('{0}{1}'.format(cell.column_letter, cell.row), cell.value) # cell.column, cell.col_idx
# %% 取出多行多列-方法2
print('取出多行多列')
cell_range = ws['A1':'C2'] # 取出来 [A1, B1, C1], [A2, B2, C2]
for cells in cell_range:
for cell in cells:
print('{0}{1}'.format(cell.column_letter, cell.row), cell.value) # cell.column, cell.col_idx
# %% 取出多行多列-方法1
print('取出多行多列')
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print('{0}{1}'.format(cell.column_letter, cell.row), cell.value) # cell.column, cell.col_idx
# ? 取行，列的统计数据，前者为生成器
print(len(list(ws.rows)), len(list(ws.columns)), ws.max_row, ws.max_column)
# ! 删除工作表
ws_n4 = wb.create_sheet("x-sheet4")
ws.remove(ws_n4)
# ! 部分列名称删除了，有空缺
print(get_column_letter(2), column_index_from_string('B'))
# * 其他补充：字体，对齐方式，行高，列宽，合并单元格，拆分单元格
# ! 扩展补充：生成 2D 图表， 3D图表
# 保存 xsl
wb.save('demo.xlsx')
