|
[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, 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}") (提示：岳阳市麦克技术服务有限公司为您提供岳阳市专业的劳务派遣公司、岳阳市劳务外包公司、岳阳市最好的劳务派遣公司、岳阳市劳务服务外包公司、岳阳市物业管理服务、岳阳市保安服务、岳阳市人事代理、岳阳市人力资源外包服务公司、岳阳市区域人力资源服务商) |