116 lines
4.6 KiB
Python
116 lines
4.6 KiB
Python
import openpyxl
|
|
import os
|
|
|
|
class AnyType(str):
|
|
def __ne__(self, __value: object) -> bool:
|
|
return False
|
|
|
|
ANY = AnyType("*")
|
|
|
|
def get_excel_full_path_or_raise(base_folder, file_path):
|
|
"""
|
|
Securely resolve Excel file paths within a designated directory.
|
|
|
|
Args:
|
|
base_folder: The base folder name (use "." for current directory)
|
|
file_path: The requested file path
|
|
|
|
Returns:
|
|
str: The absolute path if valid
|
|
|
|
Raises:
|
|
ValueError: If the path is invalid or outside the allowed directory
|
|
"""
|
|
# Get the directory where the script is located
|
|
current_dir = os.path.dirname(os.path.abspath(__file__))
|
|
|
|
# If base_folder is ".", use the current directory, otherwise create subdirectory path
|
|
if base_folder == ".":
|
|
base_dir = current_dir
|
|
else:
|
|
base_dir = os.path.join(current_dir, base_folder)
|
|
|
|
# Normalize the file path to prevent directory traversal
|
|
normalized_file_path = os.path.normpath(file_path)
|
|
|
|
# Check for directory traversal attempts
|
|
if os.path.isabs(normalized_file_path) or normalized_file_path.startswith('..'):
|
|
raise ValueError("Invalid file path. Absolute paths and parent directory references are not allowed.")
|
|
|
|
# Construct the full path
|
|
full_path = os.path.join(base_dir, normalized_file_path)
|
|
|
|
# Resolve any remaining relative components
|
|
resolved_path = os.path.abspath(full_path)
|
|
|
|
# Ensure the resolved path is still within the base directory
|
|
if not resolved_path.startswith(os.path.abspath(base_dir)):
|
|
raise ValueError("Invalid file path. Path must be within the designated directory.")
|
|
|
|
return resolved_path
|
|
|
|
class exLoadoutReadColumn:
|
|
@classmethod
|
|
def INPUT_TYPES(cls):
|
|
return {
|
|
"required": {
|
|
"excel_path": ("STRING", {"default": "exLoadoutList.xlsx"}), # Default Excel filename
|
|
"sheet_name": ("STRING", {"default": "MODELS"}), # Default sheet name
|
|
"column_letter": ("STRING", {"default": "A"}), # Column selection by letter
|
|
},
|
|
}
|
|
|
|
RETURN_TYPES = (ANY,)
|
|
RETURN_NAMES = ("output_list",)
|
|
OUTPUT_IS_LIST = (True,)
|
|
FUNCTION = "read_excel_column"
|
|
CATEGORY = "exLoadout"
|
|
DESCRIPTION = "Reads all values from a specified column in an Excel spreadsheet and returns them as a comma-separated string inside a list."
|
|
|
|
def read_excel_column(self, excel_path, sheet_name, column_letter):
|
|
# ✅ Secure path resolution for Excel file - look in current directory
|
|
full_excel_path = get_excel_full_path_or_raise(".", excel_path)
|
|
|
|
# Validate file extension
|
|
if not full_excel_path.lower().endswith(".xlsx"):
|
|
raise ValueError("Invalid file type. Only .xlsx files are supported.")
|
|
|
|
# Check if file exists
|
|
if not os.path.exists(full_excel_path):
|
|
base_dir = os.path.dirname(os.path.abspath(__file__))
|
|
raise FileNotFoundError(f"Excel file not found: {os.path.basename(full_excel_path)}\n"
|
|
f"Expected location: {full_excel_path}\n"
|
|
f"Make sure the file exists in: {base_dir}")
|
|
|
|
# Load the Excel workbook
|
|
workbook = openpyxl.load_workbook(full_excel_path, data_only=True)
|
|
|
|
if sheet_name not in workbook.sheetnames:
|
|
workbook.close()
|
|
raise ValueError(f"Sheet '{sheet_name}' not found in the Excel file")
|
|
|
|
sheet = workbook[sheet_name]
|
|
|
|
# Convert column letter to index (A = 1, B = 2, etc.)
|
|
try:
|
|
column_index = openpyxl.utils.column_index_from_string(column_letter)
|
|
except ValueError:
|
|
workbook.close()
|
|
raise ValueError(f"Invalid column letter: {column_letter}")
|
|
|
|
# Read all non-empty values in the column, excluding the header
|
|
values = [
|
|
str(sheet.cell(row=row_idx, column=column_index).value)
|
|
for row_idx in range(2, sheet.max_row + 1) # Start from row 2 to skip header
|
|
if sheet.cell(row=row_idx, column=column_index).value is not None
|
|
]
|
|
|
|
workbook.close()
|
|
|
|
# Join values into a single comma-separated string
|
|
output_string = ", ".join(values)
|
|
return ([output_string],) # Output as a list
|
|
|
|
NODE_CLASS_MAPPINGS = {"exLoadoutReadColumn": exLoadoutReadColumn}
|
|
NODE_DISPLAY_NAME_MAPPINGS = {"exLoadoutReadColumn": "exLoadout Read Column"}
|