python操作excel

示例：

#!/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')