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'
输出效果
参考文章
您阅读本篇文章共花了：
发表评论