python处理excel（基于openpyxl)

openpyxl spec：教程 — openpyxl 3.0.7 文档

读取excel文件

import openpyxl
import re

#-----------------------------------------------------------
# 打开一个已经存在的Excel表格
# data_only=True：  在单元格中的内容为公式时，得到的是计算结果
# data_only=False： 在单元格中的内容为公式时，得到的是计算公式
#-----------------------------------------------------------
workbook = openpyxl.load_workbook("file_name.xlsx", data_only=True) 

#-----------------------------------------------------------
# 获取所有data_sheet的名字
#-----------------------------------------------------------
ws_list  = workbook.sheetnames 
print (ws_list)

#-----------------------------------------------------------
# 打开data_sheet
#-----------------------------------------------------------
worksheet = workbook[ws_list[0]]  # 1. 使用变量的方式
worksheet = workbook["sheet1"]    # 2. 使用sheet名的方式
for sheet_name in ws_list:        # 3. 遍历所有的sheet，寻找匹配某个关键字的sheet
    match_result = re.match(r'.*sheet1.*', sheet_name)
    if match_result: # 匹配成功
        print (sheet_name)
        worksheet = workbook[sheet_name]

#-----------------------------------------------------------
# 获取data_sheet的基本信息
#-----------------------------------------------------------
print(worksheet.title)      # sheet名
print(worksheet.max_row)    # 最大行号
print(worksheet.max_column) # 最大列号

#-----------------------------------------------------------
# 获取单元格的值
#-----------------------------------------------------------
# 1. 直接通过Excel的索引来使用cell值
print (worksheet['A1'].value) 
# 2. 以对象的方式获取单元格，进而以单元格为单位调用其属性
cell_item = worksheet.cell(1,3) # 第1行，第3列，对应 'C1'
print (cell_item.value)

#-----------------------------------------------------------
# 获取一个范围内单元格的值
#-----------------------------------------------------------
# 3.1 按照行、列的坐标获取一个范围内的单元格
for row_idx in range(1,3):     # "cell"函数识别的idx从1开始； ‘range’函数的范围不包含第2个参数
    for col_idx in range(1,7): # for循环操作范围：A1:F2
        cell_item = worksheet.cell(row_idx, col_idx) #获取单元格的值
        print (cell_item.value)
        #-------------------------------
        # 更改单元格的值
        if cell_item.value == "None": 
            worksheet.cell(row=row_idx, column=col_idx, value=0) # 如果单元格为空，则给单元格的值初始化为0
        else:
            worksheet.cell(row=row_idx, column=col_idx, value=cell_item.value+1) # 更新单元格的值
        #-------------------------------

# 3.2 获取某一整行的单元格，存到列表中
cell_row  = worksheet[1]   # 获取第1行，并将每个单元格的对象存到列表中
print (cell_row[0].value)  # 打印第1行的第A列的单元格的值

# 3.3 获取某一整列的单元格，存到列表中
cell_col  = worksheet['F'] # 获取第F列，并将每个单元格的对象存到列表中
print (cell_col[1].value)  # 获取第F列的第2行的单元格的值

#---- 3.2 和 3.3 得到的返回值是单元格对象组成的元组 ----#
#---- 如果想直接得到一个由单元格的值组成的列表则不行 ----#
#---- 由此我们引入3.4和3.5，可以通过values_only控制 ----#

# 3.4 获取某一范围的单元格（以行为单位），存到生成器中，每个生成器的元素对应一整行
# 3.4.1 -----> 获得单元格的对象 <-----
gen_row = worksheet.iter_rows(min_row=1, max_row=2, min_col=1, max_col=10)
for cell_row in gen_row:       # 每次循环获取一整行
    print (cell_row[5].value)  # 第一次打印F1单元格，第二次打印F2单元格
    for cell in cell_row:      # 遍历一整行
        print (cell.value)           
# 3.4.2 -----> 获得单元格的值 <-----
gen_row = worksheet.iter_rows(min_row=1, max_row=2, min_col=1, max_col=10, values_only=True) # 返回的生成器中的元组由单元格的值组成，而不是对象
cell_list = list(gen_row)[0]   # 得到第1行的单元格的值构成的列表


# 3.5 获取某一范围的单元格（以列为单位），存到生成器中，每个生成器的元素对应一整列
# 3.5.1 -----> 获得单元格的对象 <-----
gen_col = worksheet.iter_cols(min_row=1, max_row=10, min_col=1, max_col=2) # 获取A列和B列的前10行
for cell_col in gen_col:       # 每次循环获取一整列
    print (cell_col[5].value)  # 第一次打印A6单元格，第二次打印B6单元格
    for cell in cell_col:      # 遍历一整列
        print (cell.value)           
# 3.5.2 -----> 获得单元格的值 <-----
gen_col = worksheet.iter_rows(min_row=1, max_row=10, min_col=1, max_col=2, values_only=True) # 返回的生成器中的元组由单元格的值组成，而不是对象
cell_list = list(gen_col)[0]  # 得到由第A列的单元格的值构成的列表

# 3.6 获取某一范围的单元格，存到二维列表中
cell_range = worksheet['A1':'F2']
print (cell_range[1][5].value)    # 打印 'F2' 单元格的值，因为二位列表的索引从0开始

# 3.7 按行循环遍历所有单元格的值
for row in worksheet.values:  # 每次获取worksheet中一整行的单元格
    print (row)               # 直接打印一整行的内容
    for value in row:         # 逐个访问一整行内的每个单元格
        print (value)

#-----------------------------------------------------------
# 单元格的属性
#-----------------------------------------------------------
# 1. 获取单元格的位置
        print (cell_item.row)         # 打印单元格的行号 (从1开始算）
        print (cell_item.row)         # 打印单元格的列号 (从1开始算）
        print (cell_item.coordinate)  # 打印单元格的坐标 (如：A2)

# 2. index 与 坐标 之间相互转化
from openpyxl.utils import get_column_letter, column_index_from_string
        print(get_column_letter(2))            # 返回 B
        print(column_index_from_string("B"))   # 返回 2

创建excel文件

############################## code start #########################################
import openpyxl

workbook  = openpyxl.Workbook()

#=================================================
# 创建worksheet
#=================================================
#-------------------------
# 不推荐的写法：
#-------------------------
# 期望只创建'sheet1', 而实际上会额外创建'Sheet', 且期望创建的'sheet1'会排在Sheet之后
# 当我们重新打开workbook时，如果直接使用workbook.active，拿到的会是Sheet而不是sheet1
worksheet = workbook.create_sheet('sheet1') 
#-------------------------
# 推荐的写法：
#-------------------------
# 方式1：
worksheet = workbook.create_sheet('sheet1', 0)  # worksheet插入到最前面
# 方式2：(更加推荐，因为不会额外创建Sheet)
worksheet = workbook.active # 创建sheet名为：Sheet
worksheet.title = 'sheet1'  # 改名

worksheet['A1'] = "val"

workbook.save('created_file.xlsx')

############################## code end ############################################
#---------------------------------------------------
# 重新打开excel用于验证
#---------------------------------------------------
workbook = openpyxl.load_workbook('created_file.xlsx')
sheetlist = workbook.sheetnames
print(sheetlist)

#worksheet = workbook['sheet1']
worksheet = workbook.active

for row in worksheet.values:
    print(row)

print(worksheet['A1'].value)

合并单元格

读取合并单元格：

合并单元格的有效索引值为：左上角单元格的索引；合并单元格内其他位置的单元格读取到的值会是None。

合并、拆分单元格：

# 方式1
worksheet.merge_cells("A1:F2")
worksheet.unmerge_cells("A1:F2")

#方式2
worksheet.merge_cells(start_row=1, start_column=1, end_row=2, end_column=6)
ws.unmerge_cells(start_row=1, start_column=1, end_row=2, end_column=6)

注意：

1. 对于已经被合并过的单元格，如果想继续合并其他单元格进来，或者直接将其重新以更小范围的单元格进行合并，需要先对已经合并的单元格进行拆分，否则使用office打开excel时会报错。

2. 使用unmerge_cells进行拆分单元格的时候，其范围必须与合并单元格时的范围一致。

识别合并单元格的范围，并进行拆分的方法：

#-------------------------------------------
# 方式 1
#-------------------------------------------
merged_range = list(worksheet.merged_cells.ranges) 
# 这里work_sheet.merged_cells.ranges的返回值的类型是set，
# 如果想要使用下标来引用具体的值，则需要使用list或者tuple进行类型转换
print (merged_range[0])
worksheet.unmerge_cells(str(merged_range[0])) # unmerge_cells需要传递字符串类型参数

#-------------------------------------------
# 方式 2
#-------------------------------------------
for merged_range in worksheet.merged_cells.ranges:
    worksheet.unmerge_cells(str(merged_range))