python，pandas ，openpyxl提取excel特定数据，合并单元格合并列，设置表格格式，设置字体颜色，

python，pandas ，openpyxl提取excel特定数据，合并单元格合并列，设置表格格式，设置字体颜色，

代码

import os

import numpy
import pandas as pd
import openpyxl
from openpyxl.styles import Font
from openpyxl.styles import Border, Side

def read(file):
    # 读取表格A和表格B
    df_a = pd.read_excel(file, skiprows=9)  # 用实际的文件路径替换 '表格A.xlsx'

    df_b = pd.DataFrame()
    columns_to_copy = ['Case NO', 'Serial NO', 'Net Weight', 'Length', 'Width', 'Thickness', 'ThicknessRange',
                       'ArealWeight', 'ArealWeightRange', 'TensileStrength', 'Elongation', 'SurfaceRoughnessSSide',
                       'SurfaceRoughnessMSide', 'SurfaceGlossSSide', 'SurfaceGlossMSide', 'Wettability', 'WrapHeight',
                       'HTAntiOxidization', 'Cr'
                       ]
    df_subset = df_a[columns_to_copy]

    # 将所选列复制到表格B的相应位置,空列用于合并使用
    df_b['Case NO'] = df_subset['Case NO']
    df_b['Serial NO'] = df_subset['Serial NO']
    df_b['Net Weight'] = df_subset['Net Weight']
    df_b['Length'] = df_subset['Length']
    df_b['Areal Weight'] = df_subset['ArealWeight']
    df_b['Tensile Strength'] = df_subset['TensileStrength']
    df_b['Column1'] = [None] * len(df_b)
    

    # 定义每列的区间要求，使用 numpy.inf 表示正无穷大，-numpy.inf 表示负无穷大
    column_ranges = {
        'Areal Weight': (54 - 2.5, 54 + 2.5),  # 0 到正无穷大
        'Tensile Strength': (300, numpy.inf),  # 负无穷大到 200
        'Elongation': (5, numpy.inf),
        'Wettability': '合格',
        'Warp Height': (-numpy.inf, 10),
        'Surface Roughness M': (-numpy.inf, 3.0),
        'Surface Roughness S': (-numpy.inf, 0.4),
    }

    df_b = df_b.iloc[1:-1]
    # 遍历每列并根据不同的条件进行处理
    for column, range_or_string in column_ranges.items():
        if isinstance(range_or_string, tuple):  # 区间检查
            min_value, max_value = range_or_string
            df_b[column] = df_b[column].apply(
                lambda x: x if (x == 0 or (min_value <= float(x) <= max_value)) else str(x) + 'XX')
        elif isinstance(range_or_string, str):  # 字符串设置
            df_b[column] = range_or_string

    # 编写一个函数来尝试将值转换为float
    def try_convert_to_float(value):
        try:
            return float(value)
        except (ValueError, TypeError):
            return value

    # 使用applymap将DataFrame中的值尝试转换为float，保留无法转换的原始值
    df_b = df_b.applymap(try_convert_to_float)

    # 定义一个函数来设置样式，将文本居中对齐和上下居中对齐
    def set_cell_style(value):
        style = 'text-align: center; vertical-align: middle;'
        return style

    # 使用Styler对象来应用样式，同时设置文本的居中对齐和上下居中对齐
    df_b = df_b.style.applymap(lambda x: set_cell_style(x))

    # 保存到新文件
    df_b.to_excel('temp.xlsx', index=False, engine='openpyxl')

    # 合并单元格
    wb = openpyxl.load_workbook('temp.xlsx')
    ws = wb.active
    #第一列连续相同值的合并单元格
    # 获取第一列数据
    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))

    ### 合并列
    num_rows = ws.max_row

    combine_columns = {
        ('F', 'G'),
        ('H', 'I'),
        ('J', 'K'),
        ('L', 'M'),
        ('N', 'P'),
        ('Q', 'R'),
        ('S', 'T'),
    }

    for i in range(num_rows):
        for columns in combine_columns:
            start, end = columns
            ws.merge_cells(start + str(i + 1) + ":" + end + str(i + 1))

    # 定义不同列的字体配置
    font_columns = [
        (['A', 'B', 'C', 'D'], Font(name='Times New Roman', size=9, bold=True)),
        (['E', 'F', 'H', 'L', 'Q', 'S'], Font(name='Times New Roman', size=12)),
        (['J', 'N'], Font(name='宋体', size=12)),
    ]

    # 设置列的字体样式
    for labels, font in font_columns:
        for label in labels:
            for cell in ws[label]:
                cell.font = font
                # XX结尾的数据改成红色
                if cell.value and str(cell.value).endswith("XX"):
                    cell.value = cell.value[:-2]
                    cell.font = Font(name='Times New Roman', size=12, bold=True, color="FF0000", )
    # 创建一个边框样式
    border_style = Border(
        left=Side(border_style='thin', color='000000'),
        right=Side(border_style='thin', color='000000'),
        top=Side(border_style='thin', color='000000'),
        bottom=Side(border_style='thin', color='000000')
    )

    # 遍历工作表中的所有单元格并应用边框样式
    for row in ws.iter_rows():
        for cell in row:
            cell.border = border_style
    wb.save('output_excel_file.xlsx')

    try:
        os.remove('temp.xlsx')
    except FileNotFoundError:
        pass
    except Exception as e:
        pass
    return 'output_excel_file.xlsx'

输出效果