openpyxl合并单元格

原创已于 2025-05-27 10:11:31 修改 · 1w 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#python #开发语言 #Excel读写操作

于 2020-11-26 22:27:11 首次发布

python 专栏收录该内容

32 篇文章

订阅专栏

本文介绍了如何使用Python库openpyxl进行Excel单元格的合并操作，并提供了具体的代码示例，展示了不同方式下的合并效果及注意事项。

一、生成新的Excel

#合并单元格：
from openpyxl import Workbook
from openpyxl.styles import Alignment

book = Workbook()
sheet = book.active
fanwei = "'A{}:B{}'".format('1','2')
sheet.merge_cells('A1:B2') #这样可以
sheet.merge_cells(fanwei) #这样不行
sheet.merge_cells('A{}:B{}'.format(1,2)) #这样可以？神奇
sheet.merge_cells('A{}:B{}'.format('1','2')) #这样也行

cell = sheet.cell(row=1, column=3) #注意，在openpyxl的逻辑里面，编号是从1开始的，而非0；
cell.value = 'Sunny day'           # 对于合并的单元格而言，能被赋值等操作的，只有合并前最左上角的单元格
cell.alignment = Alignment(horizontal='center', vertical='center')

book.save('merging.xlsx')

比较一下这个区别

fanwei = "'A{}:B{}'".format('1','2')
sheet.merge_cells('A1:B2') #这样可以
sheet.merge_cells(fanwei) #这样不行
sheet.merge_cells('A{}:B{}'.format(1,2)) #这样可以？神奇
sheet.merge_cells('A{}:B{}'.format('1','2')) #这样也行

二、读取现有的Excel

import pandas as pd
from openpyxl import workbook  #这个用于生成新的Excel
from openpyxl import load_workbook #这个用于读取已有的Excel

test_file2 = Common.convert_to_linux(r'C:\Users\Administrator\Desktop\XXXXXXX-20240801-2000.xlsx')


wb = load_workbook(test_file2)
ws = wb.active #获取一个活动的工作表

# 看看 ws中有多少关于合并单元格的属性
[i  for i in dir(ws) if re.search(r'merge', str(i), re.I)]

wb中与worksheet有关的属性

[i for i in dir(wb) if re.search('sheet',i,re.I)]

['_active_sheet_index',
 '_add_sheet',
 '_sheets',
 'chartsheets',
 'copy_worksheet',
 'create_chartsheet',
 'create_sheet',
 'get_sheet_by_name',
 'get_sheet_names',
 'move_sheet',
 'remove_sheet',
 'sheetnames',
 'worksheets']

从wb对象获取ws

wb.active可以获取一个活动的工作表

但这样更直接：

ws3 = wb['工作表1']
for i in ws3.merged_cells:
    print(i)
    
# 输出结果：B2:C2
    
# A1:A17 之所以不是合并单元格，是因为之前我在ws里面已经做了unmerge_cells操作
# ws.unmerge_cells(str(element))

ws中关于单元格合并的“属性”

['_clean_merge_range',
 'merge_cells',
 'merged_cell_ranges',
 'merged_cells',
 'unmerge_cells']

挨个试一遍

ranges就似乎有点多余

读取后的合并单元格的内容

for element in ws.merged_cells:
    print(element)
    for cell in element.cells:
        print(ws.cell(cell[0], cell[1]).value)
    break #只看第一个合并单元格

A2:A17
BY240801
None
None
None
None
None
None
None
None
None
None
None
None
None
None
None

加上ranges其实也一样

for element in ws.merged_cells.ranges:
    print(element)
    for cell in element.cells:
        print(ws.cell(cell[0], cell[1]).value)
    break #只看第一个合并单元格

A2:A17
BY240801
None
None
None
None
None
None
None
None
None
None
None
None
None
None
None

ws.merged_cell_ranges在后续版本中会被deprecated

ws.merge_cells 不是attributes，而是method。

是在写入的时候，用于合并单元格的。

合并单元格的填充

将合并单元格中所有单元格用各个合并range的start_cell进行填充。

既然如此，就直接用ws.merged_cells即可

这样居然行不通

for element in ws.merged_cells:
    print(element)
    for cell in element.cells: #这里cell已经只是一个tuple，跟Excel或者ws其实没有关系
        ws.cell(cell[0], cell[1]).value = element.start_cell.value #这个行不通

A2:A17

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-116-dfa1c13730c6> in <module>
      2     print(element)
      3     for cell in element.cells: #这里cell已经只是一个tuple，跟Excel或者ws其实没有关系
----> 4         ws.cell(cell[0], cell[1]).value = element.start_cell.value

AttributeError: 'MergedCell' object attribute 'value' is read-only

改进一下

for merged_cell  in ws.merged_cells:
    #print(merged_cell)
    value_start_cell = merged_cell.start_cell.value
    #print(value_start_cell)
    
    #取消合并单元格
    #合并单元格中，只有start_cell的值才被允许修改
    #但经过unmerge_cells处理之后，其他单元格的值也可以修改了
    ws.unmerge_cells(str(merged_cell)) 
    
    #修改被合并的单元格的值
    for cell in merged_cell.cells: #cell 是一个（1,2）之类的tuple，跟Excel或者ws其实没有关系
        row,col = cell
        ws.cell(row,col).value = value_start_cell

修改的效果如下：

两个注意点：关于ws.cell()函数

1. 两个参数分别是（行数，列数）

但诸如A2， B10之类的，是列在前，行在后。

2. 这俩参数就是存粹的数字，不能有字母

A20之类的，必须转换成(20, 1)

列字母和列数之间的转换

from openpyxl.utils import get_column_letter, column_index_from_string

输出为pandas dataframe形式

在输出之前，来看看ws有哪些跟row，col有关的attributes

[i for i in dir(ws) if re.search(r'row|col', i, re.I)]

['BREAK_COLUMN',
 'BREAK_ROW',
 '_add_column',
 '_add_row',
 '_cells_by_col',
 '_cells_by_row',
 '_current_row',
 '_invalid_row',
 '_print_cols',
 '_print_rows',
 'col_breaks',
 'column_dimensions',
 'columns',
 'delete_cols',
 'delete_rows',
 'insert_cols',
 'insert_rows',
 'iter_cols',
 'iter_rows',
 'max_column',
 'max_row',
 'min_column',
 'min_row',
 'print_title_cols',
 'print_title_rows',
 'row_breaks',
 'row_dimensions',
 'rows']

可以看出， ws的起始的row和column都是1

具体整理成dataframe的代码，参考这个

# 使用openpyxl加载工作簿
wb = load_workbook('path_to_your_excel_file.xlsx')
# 选择活动的工作表或者特定的一个工作表
ws = wb.active  # 或者 wb['Sheet1'] 来选择特定的工作表名
 
# 将工作表数据转换为列表形式，然后传递给pandas的DataFrame构造函数
data = []
for row in ws.iter_rows(values_only=True):
    data.append(row)
 
# 创建DataFrame，可能需要指定列名，尤其是当第一行是列名时
df = pd.DataFrame(data[1:], columns=data[0])  # 假设第一行是列名

也可以参考这个函数

def excel_to_df(excel_file):
    wb = load_workbook(excel_file)
    # ws = wb.active #这个会获取第一个sheet
    ws = wb['工作表1']

    #填充合并单元格的内容
    for merged_cell  in ws.merged_cells:
        #print(merged_cell)
        value_start_cell = merged_cell.start_cell.value
        #print(value_start_cell)

        #取消合并单元格
        #合并单元格中，只有start_cell的值才被允许修改
        #但经过unmerge_cells处理之后，其他单元格的值也可以修改了
        ws.unmerge_cells(str(merged_cell)) 

        #修改被合并的单元格的值
        for cell in merged_cell.cells: #cell 是一个（1,2）之类的元组
            row,col = cell
            ws.cell(row,col).value = value_start_cell

    #遍历生成
    lst = []
    for row in ws.iter_rows(values_only=True):
        lst.append(row)
    df_tmp = pd.DataFrame(lst[1:], columns=lst[0])

    # 去除空白行和列
    df_tmp = df_tmp.dropna(axis='index',subset=['样本编号'])
    df_tmp = df_tmp.dropna(axis=1,how='all')
    df_tmp.Barcode号 = df_tmp.Barcode号.map(int)
    df_tmp.日期 = df_tmp.日期.map(int)
    
    return df_tmp

后来，我发现openpyxl只能操作xlsx文件，不能操作xls文件。

原以为可以用xlrd实现同样的需求，但发现xlrd居然不支持处理合并单元格。

那么xlrd其实已经没有使用价值，可以用pd.read_excel完全替代：

def excel_to_df2(excel_file):
    dtype = {'Barcode号':str,'日期':str}
    df_tmp = pd.read_excel(excel_file,dtype=dtype)
    df_tmp = df_tmp.dropna(axis='index', subset=['样本编号'])
    df_tmp = df_tmp.dropna(axis='columns', how='all')
    df_tmp.pooling = df_tmp.pooling.fillna(method='ffill')#对指定列操作,填充合并的单元格
    return(df_tmp)