本帖最后由 greatpeng 于 2024-10-31 17:34 编辑
python写的小工具
功能：根据工资表生成工资条，如果该员工没有该项数据，则生成的工资条隐藏该列。
工资表示例：
生成的工资条效果：
新手，编译完后可执行文件太大，不会处理。
有需要的自行编译吧。源码如下：
[Python] 纯文本查看 复制代码 import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Alignment
from tkinter import filedialog, Tk, simpledialog
def select_file():
root = Tk()
root.withdraw() # Hide the main window.
file_path = filedialog.askopenfilename(title="请选择工资表", filetypes=[("Excel files", "*.xlsx")])
return file_path if file_path else None
def save_file(book, default_name="工资条"):
root = Tk()
root.withdraw() # Hide the main window.
save_path = filedialog.asksaveasfilename(initialfile=default_name,
defaultextension=".xlsx",
filetypes=[("Excel files", "*.xlsx")],
title="数据已经生成，请选择保存位置")
book.save(save_path)
print(f"File saved as {save_path}")
def find_header_row_and_total_row(df):
# Find the row indices of '姓名' and '合计'
header_row = None
total_row = None
for i, row in df.iterrows():
if '姓名' in row.values:
header_row = i
if '合计' in row.values:
total_row = i
break # Stop searching once we find '合计'
if header_row is None or total_row is None:
raise ValueError("Header row containing '姓名' or '合计' not found.")
return header_row, total_row
def extract_data(input_path):
# Load the first sheet of the Excel file into a DataFrame
df = pd.read_excel(input_path, header=None)
# Find the header row and total row
header_row, total_row = find_header_row_and_total_row(df)
# Find the end column index ('备注')
end_col_index = df.iloc[header_row].tolist().index('备注') + 1
# Select the relevant columns and rows
relevant_data = df.iloc[header_row + 1:total_row, :end_col_index]
# Convert to dictionary
data_dict = {}
for index, row in relevant_data.iterrows():
name = row[0]
if pd.isna(name): # Skip if the name cell is NaN
continue
if name not in data_dict:
data_dict[name] = {}
for col_name, value in zip(df.iloc[header_row], row):
if col_name != '签字' and pd.notna(value):
data_dict[name][col_name] = value
return data_dict, df.iloc[header_row, :end_col_index].tolist(), input_path
def add_custom_row(ws, custom_name, num_cols):
# Add a merged and centered row with the custom name
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=num_cols)
ws.cell(row=1, column=1, value=custom_name).alignment = Alignment(horizontal='center', vertical='center')
def generate_pay_slips(data_dict, original_header, file_path):
# Get the filename without extension
file_name = file_path.split('/')[-1].split('.')[0]
# Create a new workbook for the output
wb = Workbook()
# Remove the default sheet created by openpyxl
default_sheet = wb.active
wb.remove(default_sheet)
# Generate pay slips for each employee
for name, details in data_dict.items():
# Create a new sheet for this employee
ws = wb.create_sheet(name)
# Filter out empty columns
filtered_header = [h for h in original_header if details.get(h)]
filtered_data = [details.get(h, '') for h in filtered_header]
# Add a merged and centered row with the custom name
num_cols = len(filtered_header)
add_custom_row(ws, file_name, num_cols)
# Write the filtered header row
ws.append(filtered_header)
# Write the filtered data row
ws.append(filtered_data)
# Save the workbook
save_file(wb, "工资条")
if __name__ == "__main__":
input_path = select_file()
if input_path:
try:
data_dict, original_header, _ = extract_data(input_path)
generate_pay_slips(data_dict, original_header, input_path)
except Exception as e:
print(f"An error occurred: {e}") | 
发表于 2024-10-31 17:25
|
发表于 2024-11-5 10:19
