当前位置：首页 > news >正文

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

news 2025/9/9 13:07:21

代码

import osimport numpy
import pandas as pd
import openpyxl
from openpyxl.styles import Font
from openpyxl.styles import Border, Sidedef read(file):# 读取表格A和表格Bdf_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_stringdf_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# 编写一个函数来尝试将值转换为floatdef 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 = 2while True:r = ws.cell(i, 1).valueif r:type_list.append(r)else:breaki += 1# 判断合并单元格的始末位置s = 0e = 0flag = type_list[0]for i in range(len(type_list)):if type_list[i] != flag:flag = type_list[i]e = i - 1if e >= s:ws.merge_cells("A" + str(s + 2) + ":A" + str(e + 2))s = e + 1if i == len(type_list) - 1:e = iws.merge_cells("A" + str(s + 2) + ":A" + str(e + 2))### 合并列num_rows = ws.max_rowcombine_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 = columnsws.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_stylewb.save('output_excel_file.xlsx')try:os.remove('temp.xlsx')except FileNotFoundError:passexcept Exception as e:passreturn 'output_excel_file.xlsx'