import os
import pandas as pd
from docx import Document
from [Link] import Pt # Import Pt for font size
import sys # Import sys for better error handling exit
# --- Configuration ---
excel_path = "excel_format.xlsx"
template_path = "doc_format.docx"
# output_path = "[Link]" # Changed output name to avoid overwriting original test
# --- Desired Font and Size ---
desired_font_name = "Aptos Display"
desired_font_size_pt = 10 # Size in points
# --- Load Data from Excel ---
print(f"Loading data from {excel_path}...")
try:
# Load Excel with openpyxl engine
# Using header=None assumes your data starts from row 0.
# Adjust skiprows if your actual data starts later.
df = pd.read_excel(excel_path, header=None, engine="openpyxl")
print("Excel data loaded successfully.")
# print("First few rows of DataFrame:")
# print([Link]()) # Optional: uncomment to see the loaded data structure
except FileNotFoundError:
print(f"Error: Excel file not found at {excel_path}")
[Link](1) # Use [Link] for a cleaner exit on critical errors
except Exception as e:
print(f"Error loading Excel file: {e}")
[Link](1)
# --- Load the Word template ---
print(f"Loading Word template from {template_path}...")
try:
doc = Document(template_path)
print("Word template loaded successfully.")
except FileNotFoundError:
print(f"Error: Word template not found at {template_path}")
[Link](1)
except Exception as e:
print(f"Error loading Word template: {e}")
[Link](1)
# --- Function to find string in DataFrame ---
def find_string_in_df(df, search_string):
"""
Find the row and column index of a cell containing a specific string in a
DataFrame.
"""
try:
# Find the row index of the cell containing the search string
row_index = df[[Link](lambda x: [Link](str).[Link](search_string,
case=False).any(), axis=1)].index[0]
# Get the column index of the cell containing the search string
col_index =
[Link][[Link][row_index].astype(str).[Link](search_string,
case=False)].tolist()[0]
return int(row_index), int(col_index)
except IndexError:
return None, None
# Search for all the columns that contain only "NaN" values
empty_columns = [Link][[Link]().all()].tolist()
print(f"Empty columns: {empty_columns}") # Debugging line to check empty columns
# Drop empty columns and colums which are not between them
# ie. columns that are not between the first and last empty column
# eg. Empty columns: [0, 5] and columns are [0, 1, 2, 3, 4, 5, 6, 7, 8]
# will drop columns [0] from the left and [5, 6, 7, 8] from the right
# Get the first and last empty column indices
if len(empty_columns) > 1:
first_empty_col = empty_columns[0]+1 # +1 to start from the next column after
the first empty column
last_empty_col = empty_columns[-1]-1 # -1 to end at the last empty column
before the last empty column
# Drop columns outside the range of the first and last empty column
df = [Link][:, first_empty_col:last_empty_col]
print(f"Dropping columns outside the range of empty columns: {first_empty_col}
to {last_empty_col}")
elif len(empty_columns) == 1:
last_empty_col = empty_columns[0]-1
# Drop columns outside the range of the first and last empty column
df = [Link][:last_empty_col]
else:
print("No empty columns found. No columns will be dropped.")
# If no empty columns, keep the DataFrame as is
# --- Mapping of placeholders to cell values ---
# Ensure the row and column indices (0-indexed) match your Excel file exactly.
# Ensure placeholder strings match the ones in your DOCX template exactly.
print("Defining replacements...")
try:
# Find the coordinates of the required strings in the DataFrame
# Adjust these strings based on your Excel structure
# Example: "Basic" and "HRA" are assumed to be headers or unique identifiers in
your Excel file
Basic_coords = find_string_in_df(df, "Basic")
HRA_coords = find_string_in_df(df, "HRA")
Transport_coords = find_string_in_df(df, "Transport Allowance")
Special_coords = find_string_in_df(df, "Special Allowance")
Gross_coords = find_string_in_df(df, "Total Gross Salary")
Deduction_coords = find_string_in_df(df, "Deduction Head")
EPF_coords = find_string_in_df(df, "EPF Employee Contribution")
ESI_coords = find_string_in_df(df, "ESI Employee Contribution")
Nett_coords = find_string_in_df(df, "Net Salary")
EPF_Employer_coords = find_string_in_df(df, "EPF Employer Contribution")
Medical_coords = find_string_in_df(df, "Medical Allowance")
Insurance_coords = find_string_in_df(df, "Term Insurance")
Gratuity_coords = find_string_in_df(df, "Gratuity")
Fixed_CTC_coords = find_string_in_df(df, "Fixed CTC")
KPI1_coords = find_string_in_df(df, "On achieving a revenue of INR 15 Cr")
KPI2_coords = find_string_in_df(df, "KPI Based Performance Incentive: On
achieving a revenue")
Annual_Variable_coords = find_string_in_df(df, "Annual Variable Pay")
Total_CTC_coords = find_string_in_df(df, "Total Annual CTC")
Employee_name_coords = find_string_in_df(df, "Employee Name")
Employee_id_coords = find_string_in_df(df, "Employee ID")
Employee_designation_coords = find_string_in_df(df, "Employee Designation")
print("Coordinates found successfully.")
except Exception as e:
print(f"Error finding coordinates in DataFrame: {e}")
# For Monthly and Yearly, we assume they are in the same row but different columns
# Adjust the indices based on your Excel structure
# Example: Assuming Monthly is +1 Column and Yearly is +2 Column from Basic
try:
replacements = {
# Calculate current date
"[Insert Date]": [Link]().strftime("%d-%m-%Y"),
# Employee details - Example using [Link][row, col]
"[Employee Name]": [Link][Employee_name_coords[0],
Employee_name_coords[1]],
"[Employee ID]": [Link][Employee_id_coords[0], Employee_id_coords[1]],
"[Employee Designation]": [Link][Employee_designation_coords[0],
Employee_designation_coords[1]],
# Salary details
"[Insert Monthly Basic]": [Link][Basic_coords[0], Basic_coords[1]+1],
"[Insert Yearly Basic]": [Link][Basic_coords[0], Basic_coords[1]+2],
"[Insert Monthly HRA]": [Link][HRA_coords[0], HRA_coords[1]+1],
"[Insert Yearly HRA]": [Link][HRA_coords[0], HRA_coords[1]+2],
"[Insert Monthly Transport Allowance]": [Link][Transport_coords[0],
Transport_coords[1]+1],
"[Insert Yearly Transport Allowance]": [Link][Transport_coords[0],
Transport_coords[1]+2],
"[Insert Monthly Special Allowance]": [Link][Special_coords[0],
Special_coords[1]+1],
"[Insert Yearly Special Allowance]": [Link][Special_coords[0],
Special_coords[1]+2],
"[Insert Monthly Gross Salary]": [Link][Gross_coords[0],
Gross_coords[1]+1],
"[Insert Yearly Gross Salary]": [Link][Gross_coords[0],
Gross_coords[1]+2],
# Deductions Head
"[Insert Monthly (INR)]": [Link][Deduction_coords[0],
Deduction_coords[1]+1], # Assuming this is a value, not just a header
"[Insert Yearly (INR)]": [Link][Deduction_coords[0],
Deduction_coords[1]+2], # Assuming this is a value, not just a header
# Deductions
"[Insert Monthly EPF]": [Link][EPF_coords[0], EPF_coords[1]+1],
"[Insert Yearly EPF]": [Link][EPF_coords[0], EPF_coords[1]+2],
"[Insert Monthly ESI]": [Link][ESI_coords[0], ESI_coords[1]+1],
"[Insert Yearly ESI]": [Link][ESI_coords[0], ESI_coords[1]+2],
"[Insert Monthly Nett Salary]": [Link][Nett_coords[0], Nett_coords[1]+1],
"[Insert Yearly Nett Salary]": [Link][Nett_coords[0], Nett_coords[1]+2],
"[Insert Monthly EPF Employer]": [Link][EPF_Employer_coords[0],
EPF_Employer_coords[1]+1],
"[Insert Yearly EPF Employer]": [Link][EPF_Employer_coords[0],
EPF_Employer_coords[1]+2],
"[Insert Monthly Medical Allowance]": [Link][Medical_coords[0],
Medical_coords[1]+1],
"[Insert Yearly Medical Allowance]": [Link][Medical_coords[0],
Medical_coords[1]+2],
"[Insert Monthly Term Insurance]": [Link][Insurance_coords[0],
Insurance_coords[1]+1],
"[Insert Yearly Term Insurance]": [Link][Insurance_coords[0],
Insurance_coords[1]+2],
"[Insert Monthly Gratuity]": [Link][Gratuity_coords[0],
Gratuity_coords[1]+1],
"[Insert Yearly Gratuity]": [Link][Gratuity_coords[0],
Gratuity_coords[1]+2],
"[Insert Monthly Fixed CTC]": [Link][Fixed_CTC_coords[0],
Fixed_CTC_coords[1]+1],
"[Insert Yearly Fixed CTC]": [Link][Fixed_CTC_coords[0],
Fixed_CTC_coords[1]+2],
# Annual Variable Pay
"[Insert KPI 1]": [Link][KPI1_coords[0], KPI1_coords[1]+1],
"[Insert KPI 2]": [Link][KPI2_coords[0], KPI2_coords[1]+1],
"[Insert Annual Variable]": [Link][Annual_Variable_coords[0],
Annual_Variable_coords[1]+1],
"[Insert Total Annual CTC]": [Link][Total_CTC_coords[0],
Total_CTC_coords[1]+1],
}
print("Replacements dictionary created.")
# Convert all replacement values to strings to avoid TypeErrors during
replacement
for key, value in [Link]():
# Handle potential NaN values from Excel gracefully
if [Link](value):
replacements[key] = "" # Replace NaN with an empty string or other
default
# If Value is numeric, add comma formatting eg. value = 1200 => "1,200"
elif isinstance(value, (int, float)):
replacements[key] = f"{value:,.0f}"
else:
# Format numeric values nicely if needed, e.g., two decimal places
# if isinstance(value, (int, float)):
# replacements[key] = f"{value:,.2f}" # Example formatting for
numbers
# else:
# replacements[key] = str(value)
replacements[key] = str(value) # Simple string conversion
print("Replacement values converted to strings.")
except IndexError as e:
print(f"Error accessing data in DataFrame. Check your [Link] indices: {e}")
print(f"DataFrame shape: {[Link]}")
[Link](1)
except Exception as e:
print(f"An error occurred while preparing replacements: {e}")
[Link](1)
# --- Function to replace text and apply style in a paragraph ---
# This function replaces all occurrences of all placeholders in a single paragraph.
# It clears the original content and adds the new text as a single run,
# then applies the desired font and size to that new run.
# NOTE: This approach WILL remove any formatting that existed *within* the
# original paragraph (e.g., bold words, italics, different fonts/sizes)
# if a replacement occurs in that paragraph.
def replace_text_and_style_in_paragraph(paragraph, replacements_dict, font_name,
font_size_pt):
"""
Replaces placeholders in a paragraph and applies specific styling to the new
text.
Args:
paragraph: The docx paragraph object.
replacements_dict: Dictionary of {placeholder: value}.
font_name: The name of the desired font (e.g., "Aptos Display").
font_size_pt: The desired font size in points (e.g., 10).
"""
original_text = [Link]
new_text = original_text
# Apply all replacements to the text
for placeholder, value in replacements_dict.items():
new_text = new_text.replace(placeholder, value)
# If any replacement was made, update the paragraph and style the new run
if new_text != original_text:
# Clear existing content and its runs/formatting
[Link]()
# Add the new text as a single run
new_run = paragraph.add_run(new_text)
# Apply the desired font and size to the new run
font = new_run.font
[Link] = font_name
[Link] = Pt(font_size_pt)
# --- Perform Replacements ---
print("Performing replacements in the document...")
# 1. Replace in regular paragraphs (outside tables)
print("Replacing and styling in document paragraphs...")
for para in [Link]:
replace_text_and_style_in_paragraph(para, replacements, desired_font_name,
desired_font_size_pt)
# 2. Replace in table cells
print("Replacing and styling in document tables...")
for table in [Link]:
for row in [Link]:
for cell in [Link]:
# Cells can contain multiple paragraphs
for para in [Link]:
replace_text_and_style_in_paragraph(para, replacements,
desired_font_name, desired_font_size_pt)
print("Replacement complete.")
# --- Save the modified document ---
# Generate file name
# Make a folder if it doesn't exist
[Link]("output", exist_ok=True)
emp_name = str(replacements["[Employee Name]"]).replace(" ", "_")
output_path = f"output/Salary_Revision_{emp_name}.docx"
print(f"Saving the modified document to {output_path}...")
try:
[Link](output_path)
print(f"Document successfully saved to {output_path}")
except Exception as e:
print(f"Error saving the document: {e}")
finally:
print("Process completed.")
# Optionally, you can close the document if needed
# [Link]() # Not necessary for python-docx, but good practice in some
contexts