[python 学习笔记] openpyxl 的使用 - excel中数据转成列表/ 字典

openpyxl 支持的格式： .xlsx , .xlsm, .xltx, .xltm

1. 打开文件  --  打开一个新文件  /  打开已有文件

from openpyxl import Workbook, load_workbook
# 打开一个新文件
wb = Workbook()
# 打开sheet，（当打开新文件时，会同时创建一个sheet，此时只需要激活就好）
ws = wb.active


#########################################################
# 打开一个已有文件
wb = load_workbook(file_path)
# 打开sheet, (一般打开一个已经存在的文件后，我们希望直接操作其中的某个表格)
# 我们可以get所有表格的名字，再从中选择某个表格
sheet_list = wb.sheetnames
ws = wb[sheet_list[0]]
# 或者直接用表格的名字
ws = wb['某个表格的名字']

 2.  新建sheet / 删除sheet / 修改  sheet name

# 新建表格
ws1 = wb.create_sheet('表格名字')    # 在最后插入表格
ws2 = wb.create_sheet('表格名字', 0)   # 在开头插入表格
ws3 = wb.create_sheet('表格名字', -1)   # 在倒数第二的位置插入表格

# 删除sheet
wb.remove(ws)

# 修改sheet 的name
ws1.title = 'new_name'

 3. 读数据

 行的最大值： ws.max_row

 列的最大值： ws.max_column

# 读取数据，比如把excel中的一个table按行读取出来，存入一个二维的list

total_list = []
for r in range(1, ws.max_row+1):
    row_list = []  # 每一行建立一个list
    for c in range(1, ws.max_column+1):
        v = ws.cell(r, c).value
        row_list.append(v)
    total_list.append(row_list)

print(total_list)


返回值：
[['A', 'B', 'C', 'D', 'E'], [1, 2, 4, 6, 8], [4, 6, 7, 9, 0], [2, 6, 4, 5, 8]]

 也可以这样：

# 读取数据，比如把excel中的一个table按行读取出来，存入一个二维的list
total_list = []
for row in ws.rows:   # ws.rows是一个生成器
    row_list = []
    for cell in row:    # 直接从行中取每个cell
        row_list.append(cell.value)
    total_list.append(row_list)

print(total_list)

返回值：
[['A', 'B', 'C', 'D', 'E'], [1, 2, 4, 6, 8], [4, 6, 7, 9, 0], [2, 6, 4, 5, 8]]

把读取出的列表，转成字典， 比如有这样的一个excel表格：

首先将其中所有值读取到total_list中，形成一个二维列表，可以直接使用上面的代码：

# 读取数据，比如把excel中的一个table按行读取出来，存入一个二维的list 
total_list = []
for row in ws.rows:
    row_list = []
    for cell in row:
        row_list.append(cell.value)
    total_list.append(row_list)

再将这个二维列表转成字典，字典的样式为：{'A'：{'python':1, 'java':4, 'php':2},  'B':...}

按照这种样式来说，当选择了第一行中的某个值，需要继续遍历下面几行，同一个index对应的值

所以，外层循环为第一行中的每个cell， 内层循环为除第一行外的所有行， 也可以是谁作为key，就让谁作外层循环

total_dic = {}
name_list = total_list[0][1:]
for name in name_list:
    total_dic.setdefault(name, {})
    index = total_list[0].index(name)
    for line in total_list[1:]:
        total_dic[name].setdefault(line[0], 0)   # 设置默认值是0
        total_dic[name][line[0]] = line[index]

print(total_dic)

返回值：

 4.  写数据

 1. 把二维列表写入文件   --  直接ws.append()

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = 'sheet_name'

# 比如想把这个二维列表存入excel中
total_list = [['A', 'B', 'C', 'D', 'E'], [1, 2, 4, 6, 8], [4, 6, 7, 9, 0], [2, 6, 4, 5, 8]]

for line in total_list:
    ws.append(line)      # 可以直接用append的方式写入

wb.save(file_path)   # 注意，写入后一定要保存！！！！！

2. 把二维列表写入excel  -- 通过循环单元格写入

from openpyxl import Workbook


wb = Workbook()
ws = wb.active
ws.title = 'sheet_name'

# 比如想把这个二维列表存入excel中
total_list = [['A', 'B', 'C', 'D', 'E'], [1, 2, 4, 6, 8], [4, 6, 7, 9, 0], [2, 6, 4, 5, 8]]

for r in range(len(total_list)):
    for c in range(len(total_list[0])):
        ws.cell(r + 1, c + 1).value = total_list[r][c]   # excel中的行和列是从1开始计数的，所以需要+1

 3. 写入 / 修改某个单元格的值  --  4种方法

ws['A1'] = 3

ws.cell(1,1).value = 3

ws.cell(row = 1, column = 1).value = 3

ws.cell(row =1, column=1, value = 3)

5. 合并单元格 & 插入行/ 列  & 移动单元格

合并单元格： 

ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')

# 等同于
ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=2, end_column=4)

插入行/列：

ws.insert_rows(2)     # 在原有的第2行前插入一行
ws.inser_cols(2)   # 在原有的第2列前插入一列

删除行/ 列：

ws.delete_cols(2,3)   # 表示删除的起始列是2，删除列的个数是3列

原表格：

删除列后：

3. 移动单元格：  

下面的代码执行的是： 将D4:F10的单元格，向上移动1行，向右移动2列，移动之后，涉及的单元格将被重写

ws.move_range("D4:F10", rows=-1, cols=2)

参考官方文档：https://openpyxl.readthedocs.io/en/stable/validation.html