工资表生成工资条，并隐藏没有数据的列

greatpeng

python写的小工具
功能：根据工资表生成工资条，如果该员工没有该项数据，则生成的工资条隐藏该列。

工资表示例：


生成的工资条效果：



新手，编译完后可执行文件太大，不会处理。
有需要的自行编译吧。源码如下：

[Python] 纯文本查看 复制代码

?

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040 041 042 043 044 045 046 047 048 049 050 051 052 053 054 055 056 057 058 059 060 061 062 063 064 065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 085 086 087 088 089 090 091 092 093 094 095 096 097 098 099 100 101 102 103 104 105 106 107 108 109 110

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}")

nbmissliu

你打包的时候使用虚拟环境打包就好了， 为什么打包很大， 是因为你把你本机所有下载的库全部打包进去了， 所以特别大。

caochanyue

很实用的功能，学习了

hellopojie520

很实用的功能，学习了

seing520

很实用的功能，学习了

greatpeng

nbmissliu 发表于 2024-11-3 09:35
你打包的时候使用虚拟环境打包就好了， 为什么打包很大， 是因为你把你本机所有下载的库全部打包进去了，  ...

好的，我找找教程，学一下。

kenxy

每个人的工资条可以选择单独生成一个文件吗？

greatpeng

kenxy 发表于 2024-11-7 11:26
每个人的工资条可以选择单独生成一个文件吗？

可以，你需要吗？

greatpeng

kenxy 发表于 2024-11-7 11:26
每个人的工资条可以选择单独生成一个文件吗？

[Python] 纯文本查看 复制代码

?

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040 041 042 043 044 045 046 047 048 049 050 051 052 053 054 055 056 057 058 059 060 061 062 063 064 065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 085 086 087 088 089 090 091 092 093 094 095 096 097 098 099 100 101 102 103 104 105 106 107 108 109 110 111 112

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, save_path):     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 directory path for saving files     dir_path = filedialog.askdirectory(title="请选择保存目录")       if not dir_path:         print("No directory selected. Exiting...")         return       # Get the filename without extension     file_name = file_path.split('/')[-1].split('.')[0]       # Generate pay slips for each employee     for name, details in data_dict.items():         # Create a new workbook for this employee         wb = Workbook()           # Remove the default sheet created by openpyxl         default_sheet = wb.active         wb.remove(default_sheet)           # 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 for this employee         save_path = f"{dir_path}/{name}.xlsx"         save_file(wb, save_path)   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}")

kkisme

感觉好厉害！收藏了，没准有一天能用到