import pandas as pd from openpyxl import load_workbook from datetime import datetime, timedelta import os # Added for path manipulation import tempfile # Import tempfile import logging # Import logging import sys # Import sys to target stderr # Configure logging logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - [%(filename)s:%(lineno)d] - %(message)s', stream=sys.stderr) # Log to stderr, usually captured by platforms def process_files(file1_path, file2_path, output_filename="generated_report.xlsx"): """ Processes two input Excel files and generates a combined report. Saves the output to a temporary directory to avoid potential permission issues. Args: file1_path (str): Path to the first input Excel file (data source). file2_path (str): Path to the second input Excel file (template). output_filename (str): Desired *base* name for the output report file (e.g., 'report.xlsx'). The actual path will be in a temp directory. Returns: str: The full path to the generated output Excel file in a temporary directory. Returns None if an error occurs during processing. """ logging.info(f"Starting report generation. Input 1: {file1_path}, Input 2: {file2_path}") try: # 读取第一个文件 logging.info(f"Reading header from: {file1_path}") header_df = pd.read_excel(file1_path, sheet_name='HEADER') logging.info(f"Reading dimension data from: {file1_path}") dimension_df = pd.read_excel(file1_path, sheet_name='Dimension', skiprows=12) dimension_df.columns = dimension_df.iloc[0] dimension_df = dimension_df.iloc[1:].reset_index(drop=True) logging.info(f"Reading sand data from: {file1_path}") sand_df = pd.read_excel(file1_path, sheet_name='Sand', header=None) # 读取第二个文件 logging.info(f"Loading template workbook: {file2_path}") wb = load_workbook(file2_path) # Check if 'WACKER' sheet exists if 'WACKER' not in wb.sheetnames: logging.error("Error: Template file must contain a sheet named 'WACKER'.") return None # Indicate error wacker_sheet = wb['WACKER'] logging.info("Template sheet 'WACKER' found.") # 获取Sales Order Quantity和Quality Assured By sales_order_quantity = header_df.iloc[5, 2] quality_assured_by = header_df.iloc[3, 7] logging.info(f"Extracted Sales Order Qty: {sales_order_quantity}, Assured By: {quality_assured_by}") # 定义元素和行号的对应关系 (Copied from original script) element_row_mapping = { 'Al': 9, 'Ca': 10, 'Cu': 11, 'Fe': 12, 'K': 13, 'Li': 14, 'Mg': 15, 'Mn': 16, 'Na': 17, 'Ti': 18, 'Zr': 19 } element_col_mapping = { 'Al': 4, 'Ca': 5, 'Cu': 6, 'Fe': 7, 'K': 8, 'Li': 9, 'Mg': 10, 'Mn': 11, 'Na': 12, 'Ti': 13, 'Zr': 14 } logging.info("Starting iteration through dimension data.") # 遍历Dimension表格中的每个Customer ID for index, row in dimension_df.iterrows(): customer_id = row['Customer ID'] # Ensure customer_id is a valid sheet name (Excel has limitations) safe_customer_id = str(customer_id).replace('/', '-').replace('\\', '-').replace('?', '').replace('*', '').replace('[', '').replace(']', '') safe_customer_id = safe_customer_id[:31] # Max sheet name length # Handle potential NaN or empty Customer ID if pd.isna(customer_id) or not str(customer_id).strip(): logging.warning(f"Skipping row {index+14} due to missing or invalid Customer ID.") continue logging.debug(f"Processing Customer ID: {customer_id} (Sheet Name: {safe_customer_id})") # Debug level for per-row info inspection_date_str = "" inspection_date = None # Initialize inspection_date try: # Check if inspection_date is already datetime or needs conversion if isinstance(row['Inspection Date'], datetime): inspection_date = row['Inspection Date'] else: inspection_date = pd.to_datetime(row['Inspection Date']) inspection_date_str = inspection_date.strftime('%Y-%m-%d') logging.debug(f"Parsed Inspection Date for {customer_id}: {inspection_date_str}") except Exception as date_parse_e: logging.warning(f"Could not parse Inspection Date for Customer ID {customer_id}: {date_parse_e}. Skipping date fields.") # inspection_date remains None new_sheet_title = safe_customer_id # Avoid duplicate sheet names if safe_customer_id becomes the same for different original IDs sheet_count = 1 while new_sheet_title in wb.sheetnames: suffix = f"_{sheet_count}" max_len = 31 - len(suffix) new_sheet_title = safe_customer_id[:max_len] + suffix sheet_count += 1 logging.debug(f"Creating new sheet with title: {new_sheet_title}") new_sheet = wb.create_sheet(title=new_sheet_title) # 复制WACKER表格的内容到新工作表 for row_wacker in wacker_sheet.iter_rows(values_only=True): new_sheet.append(row_wacker) # 填充数据 logging.debug(f"Populating sheet {new_sheet_title} with data for {customer_id}") new_sheet['B3'] = str(sales_order_quantity) + ' PCS' new_sheet['B4'] = customer_id # Use original ID here if inspection_date: # Only fill dates if parsing was successful new_sheet['D4'] = inspection_date_str new_sheet['B5'] = inspection_date_str new_sheet['D5'] = (inspection_date + timedelta(days=730)).strftime('%Y-%m-%d') # 从sand表中获取当前customer_id的数据 sand_rows = sand_df[sand_df[2] == customer_id] # 使用第3列（索引2）作为Crucible ID if not sand_rows.empty: sand_row = sand_rows.iloc[0] # 填充元素数据 (with added error handling) for element, target_row in element_row_mapping.items(): try: source_col = element_col_mapping[element] # Check if value exists and handle potential errors value = sand_row.get(source_col) # Use .get for safety if value is not None and not pd.isna(value): new_sheet[f'D{target_row}'] = value else: logging.warning(f"Missing or invalid sand data for {element}, Customer ID {customer_id}, Col Index {source_col}") # Optionally fill with a default value or leave blank # new_sheet[f'D{target_row}'] = "N/A" except KeyError: logging.warning(f"Column index {source_col} not found in sand_row for {element}, Customer ID {customer_id}") except Exception as elem_fill_e: # Log error but continue processing other elements/rows logging.error(f"Error filling element {element} for Customer ID {customer_id}: {elem_fill_e}") # 填充Analysis result/分析结果 (with added error handling) dim_mapping = { 20: 'OD1', 21: 'OD2', 22: 'OD3', 23: 'Height', 24: 'Wall11', 25: 'Wall12', 26: 'Wall13', 27: 'Wall2', 28: 'Wall3' } for target_row, source_col_name in dim_mapping.items(): try: # Check if value exists and handle potential errors value = row.get(source_col_name) # Use .get for safety if value is not None and not pd.isna(value): new_sheet[f'D{target_row}'] = value else: logging.warning(f"Missing or invalid dimension data for {source_col_name}, Customer ID {customer_id}") # Optionally fill with a default value or leave blank # new_sheet[f'D{target_row}'] = "N/A" except KeyError: logging.warning(f"Column '{source_col_name}' not found in dimension_df for Customer ID {customer_id}") except Exception as dim_fill_e: # Log error but continue processing other dimensions/rows logging.error(f"Error filling dimension {source_col_name} for Customer ID {customer_id}: {dim_fill_e}") # 保持"批准人："文本，并在其后添加名字 new_sheet['D29'] = f"批准人：{quality_assured_by}" logging.debug(f"Finished populating sheet for Customer ID: {customer_id}") logging.info("Finished iterating through dimension data.") # Remove the original template sheet if it exists and wasn't intended to be kept if 'WACKER' in wb.sheetnames: logging.info("Removing original 'WACKER' template sheet.") del wb['WACKER'] # Remove template if no longer needed # Create a temporary file path for the output try: temp_dir = tempfile.gettempdir() # Ensure the base output filename is used, not a potentially problematic one from input args safe_output_filename = os.path.basename(output_filename if output_filename else "generated_report.xlsx") # Create a unique temporary file path temp_output_path = os.path.join(temp_dir, safe_output_filename) logging.info(f"Attempting to save report to temporary path: {temp_output_path}") wb.save(temp_output_path) logging.info(f"Successfully saved report to: {temp_output_path}") return temp_output_path # Return the full path to the temporary file except Exception as save_error: # Log the exception with traceback logging.exception(f"Error saving workbook to temporary path {temp_output_path}: {save_error}") return None except FileNotFoundError as fnf_error: logging.exception(f"Error: Input file not found. Check paths: {file1_path}, {file2_path}. Error: {fnf_error}") return None except KeyError as key_error: logging.exception(f"Error: Missing expected column or sheet name: {key_error}. Check input file formats.") return None except Exception as general_error: # Log other unexpected errors with traceback logging.exception(f"An unexpected error occurred in process_files: {general_error}") return None # Keep the original script behavior if run directly (optional) if __name__ == "__main__": # Setup logging for direct script execution as well logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - [%(filename)s:%(lineno)d] - %(message)s', stream=sys.stderr) # Define default input/output files for direct execution default_file1 = '1.xls' default_file2 = '2.xlsx' default_output = '2_updated.xlsx' # For direct run, save locally logging.info(f"Running script directly. Processing {default_file1} and {default_file2}...") # For direct run, let's keep saving locally for simplicity, unless specified otherwise output_path = process_files(default_file1, default_file2, default_output) # Use local path for direct run if output_path: logging.info(f"Report generated successfully: {output_path}") else: logging.error("Report generation failed.")