Python模块05/pandas操作excel、合并单元格及读取、添加下拉列表

1. 合并单元格

• 示例

def merge_cell(file_path):
    """合并单元格"""
    wb = load_workbook(file_path)
    sheets = wb.sheetnames
    for single_sheet in sheets:
        ws = wb.get_sheet_by_name(single_sheet)
        # 获取第一列数据
        type_list = []
        i = 2
        while True:
            r = ws.cell(i, 1).value
            if r:
                type_list.append(r)
            else:
                break
            i += 1

        # 判断合并单元格的始末位置
        s = 0
        e = 0
        flag = type_list[0]
        for i in range(len(type_list)):
            if type_list[i] != flag:
                flag = type_list[i]
                e = i - 1
                if e >= s:
                    ws.merge_cells("A" + str(s + 2) + ":A" + str(e + 2))
                    s = e + 1
            if i == len(type_list) - 1:
                e = i
                ws.merge_cells("A" + str(s + 2) + ":A" + str(e + 2))
    wb.save(file_path)

2. 读取合并单元格

• 示例

import openpyxl
import pandas as pd

# 拆分所有的合并单元格，并赋予合并之前的值。
def unmerge_and_fill_cells(worksheet):
    all_merged_cell_ranges = list(
        worksheet.merged_cells.ranges
    )

    for merged_cell_range in all_merged_cell_ranges:
        merged_cell = merged_cell_range.start_cell
        worksheet.unmerge_cells(range_string=merged_cell_range.coord)

        for row_index, col_index in merged_cell_range.cells:
            cell = worksheet.cell(row=row_index, column=col_index)
            cell.value = merged_cell.value

# 读取原始xlsx文件，拆分并填充单元格，然后生成中间临时文件。
def unmerge_cell(filename):
    wb = openpyxl.load_workbook(filename)
    for sheet_name in wb.sheetnames:
        sheet = wb[sheet_name]
        unmerge_and_fill_cells(sheet)
    filename = filename.replace(".xls", "_temp.xls")
    wb.save(filename)
    wb.close()

if __name__ == '__main__':
    unmerge_cell(r"test.xlsx")

3. 生成excel添加下拉列表

• 示例

from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation


def add_drop_down_list(in_path, out_path):
    """ excel添加下拉列表 """
    wb = load_workbook(in_path)

    # 获取当前使用sheet页内容
    ws = wb.active

    # 设置下拉框列表
    dv = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)

    # 填写错误提示
    dv.error = '请填写正确的值！'
    dv.errorTitle = 'Invalid Entry'

    # 默认提示
    # Optionally set a custom prompt message
    # dv.prompt = 'Please select from the list'
    # dv.promptTitle = 'List Selection'

    # 设置验证的单元格范围
    max_row = ws.max_row
    for i in range(2, max_row + 1):
        r = ws.cell(row=i, column=1)
        dv.add(r)

    ws.add_data_validation(dv)
    wb.save(out_path)


if __name__ == '__main__':
    in_path = 'test.xlsx'
    out_path = 'xxx.xlsx'
    add_drop_down_list(in_path, out_path)