excel报表处理
根据业务需求，每月都要对报表处理一次，手动太麻烦，还容易出错，用py脚本，实现对应处理，能更快；个人需求可能并不符合所有要求
- 涉及python模块
pandas、numpy、re - 代码:
import pandas as pd
import numpy as np
import re
import os
def list_columns(df):
print("列名标号:")
for i, col in enumerate(df.columns):
print(f"{i}: {col}")
def remove_columns_from_excel(df):#1列表删除
list_columns(df)
selected_columns = input("请输入要保留的列的标号（以空格分隔）: ").split()
selected_columns = [int(col) for col in selected_columns]
return df.iloc[:, selected_columns]
def clean_urls_in_column(df):#2url匹配
list_columns(df)
column_index = int(input("请输入URL所在列的标号: "))
# 正则表达式匹配基本的URL结构
url_pattern = re.compile(r'^(https?://)?([\w\-\.]+)(\.\w+)(/[\w\-\.]*)*')
def clean_url(cell):
if pd.isna(cell) or cell in ['undefined', 'NaN', 'NaN//undefined', '待确认']:
return ""
# 确保cell是字符串
cell = str(cell).strip()
# 去除两侧的引号
if cell.startswith('"') and cell.endswith('"'):
cell = cell[1:-1]
# 修正分隔符（将逗号、中文逗号等替换为点）
cell = re.sub(r'[，,]', '.', cell)
# 使用正则表达式匹配并修复URL
match = url_pattern.match(cell)
if match:
protocol = match.group(1) or "http://"
domain = match.group(2) + match.group(3)
path = match.group(4) or ""
return f"{protocol}{domain}{path}"
else:
return ""
df.iloc[:, column_index] = df.iloc[:, column_index].apply(clean_url)
return df
def filter_and_delete_rows(df):#3
list_columns(df)
column_index = int(input("请输入要操作的列的标号: "))
# 获取唯一值列表，包括空值
unique_values = df.iloc[:, column_index].unique()
value_to_index = {str(i): v for i, v in enumerate(unique_values) if pd.notna(v)}
# 添加空值选项
if df.iloc[:, column_index].isnull().any():
value_to_index[str(len(value_to_index))] = None
# 列出所有唯一值并标号
print("可用的筛选条件:")
for i, value in value_to_index.items():
display_value = value if value is not None else '空值'
print(f"{i}: {display_value}")
# 接收用户输入的标号
condition_indexes = input("请输入要删除的条件标号(可以输入多个标号，用空格分隔): ").split()
# 将输入的标号转换为对应的值
conditions_to_delete = [value_to_index.get(idx) for idx in condition_indexes if idx in value_to_index]
# 筛选并删除符合条件的行，包括空值
df = df[~df.iloc[:, column_index].isin(conditions_to_delete) & ~df.iloc[:, column_index].isnull()]
return df
def search_and_delete(df):#4
list_columns(df)
print("选择删除方式:")
print("1: 删除整行")
print("2: 仅删除单元格内容")
delete_option = int(input("请输入选项: "))
search_terms = input("请输入要检索的内容(多个内容请用空格分隔): ").split()
if delete_option == 1:
df = df[~df.apply(lambda row: any(term in str(cell) for term in search_terms for cell in row), axis=1)]
elif delete_option == 2:
def clean_cell(cell):
cell_str = str(cell)
for term in search_terms:
if term in cell_str:
cell_str = cell_str.replace(term, "")
return cell_str.strip()
df = df.applymap(clean_cell)
return df
def replace_chinese_symbols(df):#5
# 定义中文符号和对应的英文符号
replacements = {
'，': ',',
'。': '.',
'！': '!',
'？': '?',
'：': ':',
'；': ';',
'（': '(',
'）': ')',
'【': '[',
'】': ']',
'《': '<',
'》': '>',
'、': ',',
'——': '-'
}
def replace_symbols(cell):
if pd.isna(cell) or cell == "":
return cell # 保持空白或 NaN
if isinstance(cell, str):
for ch, repl in replacements.items():
cell = cell.replace(ch, repl)
return cell
df = df.applymap(replace_symbols)
return df
def remove_duplicate_urls(df):
list_columns(df)
column_index = int(input("请输入要去重的列的标号: "))
# 获取指定列的数据
column_data = df.iloc[:, column_index]
# 创建一个新的列表来存储处理后的数据
cleaned_column = []
seen = set()
for cell in column_data:
if pd.notna(cell):
# 拆分内容为列表并去重
urls = cell.split('; ')
unique_urls = []
for url in urls:
if url not in seen:
seen.add(url)
unique_urls.append(url)
# 如果该单元格中有唯一的URL，保留它们，否则置空
if unique_urls:
cleaned_column.append('; '.join(unique_urls))
else:
cleaned_column.append("")
else:
cleaned_column.append(cell)
# 更新DataFrame的指定列
df.iloc[:, column_index] = cleaned_column
return df
def generate_sub_table_in_same_file(df, writer):
list_columns(df)
selected_columns = input("请输入要保留的列的标号（以空格分隔）: ").split()
selected_columns = [int(col) for col in selected_columns]
# 添加是否进行筛选的选项
apply_filter = input("是否要进行筛选? (y/n): ").strip().lower()
if apply_filter == 'y':
filter_column_index = int(input("请输入要根据哪列进行筛选的列标号: "))
# 使用 fillna 将 NaN 替换为字符串标识符，以便用户选择
unique_values = df.iloc[:, filter_column_index].fillna("空值").unique()
print("筛选条件标号:")
for i, value in enumerate(unique_values):
print(f"{i}: {value}")
condition_index = int(input("请输入筛选的条件标号: "))
condition_value = unique_values[condition_index]
# 选择筛选并删除还是筛选并保留
filter_option = input("请选择操作: 1 - 筛选并删除, 2 - 筛选并保留: ").strip()
# 处理筛选条件，包括空值
if condition_value == "空值":
mask = df.iloc[:, filter_column_index].isna()
else:
mask = df.iloc[:, filter_column_index] == condition_value
if filter_option == '1':
df_sub = df[~mask]
elif filter_option == '2':
df_sub = df[mask]
else:
print("无效选项，默认筛选并删除")
df_sub = df[~mask]
else:
# 不进行筛选，直接保留所有行
df_sub = df.copy()
# 无论是否筛选，保留用户选择的列
df_sub = df_sub.iloc[:, selected_columns]
new_sheet_name = input("请输入新表的名称: ")
df_sub.to_excel(writer, sheet_name=new_sheet_name, index=False)
def split_table_in_half(df, writer):
# 计算总行数并计算出中点
total_rows = len(df)
midpoint = total_rows // 2
# 确保第一部分和第二部分数据的数量
first_half = df.iloc[:midpoint, :]
second_half = df.iloc[midpoint:, :]
# 获取用户输入的新表名称
first_sheet_name = input("请输入第一部分表的名称: ")
second_sheet_name = input("请输入第二部分表的名称: ")
# 保存两部分数据到新的表中
first_half.to_excel(writer, sheet_name=first_sheet_name, index=False)
second_half.to_excel(writer, sheet_name=second_sheet_name, index=False)
print(f"表已分割，并保存为 '{first_sheet_name}' 和 '{second_sheet_name}' 两个新表。")
def append_data_from_excel_b_to_a(df_a, df_b):
# 获取A和B表的列名
columns_a = df_a.columns
columns_b = df_b.columns
# 创建一个包含所有A表列的空值DataFrame
empty_df = pd.DataFrame(columns=columns_a)
# 遍历B表的列，如果列在A表中存在，则将数据添加到A表对应的列，否则添加空值列
for col in columns_a:
if col in columns_b:
empty_df[col] = df_b[col]
else:
empty_df[col] = pd.NA # 添加空值列
# 将处理后的B表数据追加到A表
df_a = pd.concat([df_a, empty_df], ignore_index=True)
return df_a
def main():
while True:
file_path = input("请输入Excel文件路径: ")
xls = pd.ExcelFile(file_path)
# 如果文件中有多个表，让用户选择要使用的表
if len(xls.sheet_names) > 1:
print("请选择要使用的表:")
for i, sheet_name in enumerate(xls.sheet_names):
print(f"{i}: {sheet_name}")
sheet_index = int(input("请输入表的标号: "))
df = pd.read_excel(xls, sheet_name=xls.sheet_names[sheet_index])
else:
df = pd.read_excel(xls)
with pd.ExcelWriter(file_path, mode='a', engine='openpyxl') as writer:
print("\n请选择要执行的操作:")
print("0: 用户添加")
print("1: 列表删除")
print("2: URL格式识别与删除")
print("3: 对用户指定列筛选并删除筛出的行")
print("4: 指定内容检索并删除")
print("5: 替换所有中文符号为英文")
print("6: 域名去重")
print("7: 附表生成")
print("8: 表格对半分割")
choice = int(input("请输入选项: "))
if choice == 0:
file_b_path = input("请输入Excel B文件路径: ")
df_b = pd.read_excel(file_b_path)
df = append_data_from_excel_b_to_a(df, df_b)
elif choice == 1:
df = remove_columns_from_excel(df)
elif choice == 2:
df = clean_urls_in_column(df)
elif choice == 3:
df = filter_and_delete_rows(df)
elif choice == 4:
df = search_and_delete(df)
elif choice == 5:
df = replace_chinese_symbols(df)
elif choice == 6:
df = remove_duplicate_urls(df)
elif choice == 7:
generate_sub_table_in_same_file(df, writer)
elif choice == 8:
split_table_in_half(df, writer)
else:
print("无效选项")
return
if choice not in [7, 8]:
# 生成新的文件名，如果存在同名文件则追加数字后缀
base_name, ext = os.path.splitext(file_path)
output_path = base_name + f'_output.xlsx'
counter = 1
while os.path.exists(output_path):
output_path = base_name + f'_output_{counter}.xlsx'
counter += 1
df.to_excel(output_path, index=False)
print(f"操作后的Excel文件已保存为: {output_path}")
continue_choice = input("是否继续执行其他操作？(y/n): ").strip().lower()
if continue_choice != 'y':
print("程序结束。")
break
if __name__ == "__main__":
main()
- 功能0:用户添加
使用场景，A表多个表头与参数，需向A表补充B表中记录的信息，且B表信息不一定全，B表中内容补充到A表末尾
- 功能1:列表删除
使用场景:列出所有表头，按需求输入要保留的列名，其他的删除，且按照输入的列名顺序对剩下列排序
- 功能2:URL格式识别与删除
适用场景:对指定列，进行url正则匹配，删除不是url格式的内容，并对只有域名的进行补充完整协议http://
- 功能3:对用户指定列筛选并删除筛出的行
适用场景:列出所有列名，选择要筛的列，分类并列出对应列的内容，对指定内容进行删除(会删除整行数据)
- 功能4: 指定内容检索并删除
使用场景:在表格中指定内容进行删选删除，一种为仅删除该内容，另一种为删除整行内容
- 功能5: 替换所有中文符号为英文
使用场景:替换符号
- 功能6: 域名去重
使用场景:去除某列中重复的域名
- 功能7: 附表生成
使用场景:选择要保留的列，选择是要根据哪列进行筛选，是筛选并删除还是筛选并保留，之后生成一张新表。
- 功能8: 表格对半分割
标签：index,报表,df,py,excel,cell,column,print,columns From： https://www.cnblogs.com/daydaydream/p/18455995使用场景:对指定表格中的内容进行分割，方便后续处理