From 7d39ab4b1e9f92d14d640506d9639a4b054154a9 Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Mon, 11 Aug 2025 00:17:59 +0000
Subject: (대표님) spreadJS 로직 수정
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 components/form-data/spreadJS-dialog.tsx | 760 ++++++++++++++++++++-----------
 1 file changed, 496 insertions(+), 264 deletions(-)

(limited to 'components/form-data/spreadJS-dialog.tsx')

diff --git a/components/form-data/spreadJS-dialog.tsx b/components/form-data/spreadJS-dialog.tsx
index 7ed861c2..1d0796fe 100644
--- a/components/form-data/spreadJS-dialog.tsx
+++ b/components/form-data/spreadJS-dialog.tsx
@@ -16,7 +16,7 @@ import { DataTableColumnJSON, ColumnType } from "./form-data-table-columns";
 // SpreadSheets를 동적으로 import (SSR 비활성화)
 const SpreadSheets = dynamic(
   () => import("@mescius/spread-sheets-react").then(mod => mod.SpreadSheets),
-  { 
+  {
     ssr: false,
     loading: () => (
       <div className="flex items-center justify-center h-full">
@@ -77,6 +77,13 @@ interface ValidationError {
   message: string;
 }
 
+interface CellMapping {
+  attId: string;
+  cellAddress: string;
+  isEditable: boolean;
+  dataRowIndex?: number;
+}
+
 interface TemplateViewDialogProps {
   isOpen: boolean;
   onClose: () => void;
@@ -110,7 +117,7 @@ export function TemplateViewDialog({
   const [isPending, setIsPending] = React.useState(false);
   const [hasChanges, setHasChanges] = React.useState(false);
   const [currentSpread, setCurrentSpread] = React.useState<any>(null);
-  const [cellMappings, setCellMappings] = React.useState<Array<{attId: string, cellAddress: string, isEditable: boolean}>>([]);
+  const [cellMappings, setCellMappings] = React.useState<CellMapping[]>([]);
   const [isClient, setIsClient] = React.useState(false);
   const [templateType, setTemplateType] = React.useState<'SPREAD_LIST' | 'SPREAD_ITEM' | null>(null);
   const [validationErrors, setValidationErrors] = React.useState<ValidationError[]>([]);
@@ -125,25 +132,25 @@ export function TemplateViewDialog({
   // 사용 가능한 템플릿들을 필터링하고 설정
   React.useEffect(() => {
     if (!templateData) return;
-    
+
     let templates: TemplateItem[];
     if (Array.isArray(templateData)) {
       templates = templateData as TemplateItem[];
     } else {
       templates = [templateData as TemplateItem];
     }
-    
+
     // CONTENT가 있는 템플릿들 필터링
     const validTemplates = templates.filter(template => {
       const hasSpreadListContent = template.SPR_LST_SETUP?.CONTENT;
       const hasSpreadItemContent = template.SPR_ITM_LST_SETUP?.CONTENT;
       const isValidType = template.TMPL_TYPE === "SPREAD_LIST" || template.TMPL_TYPE === "SPREAD_ITEM";
-      
+
       return isValidType && (hasSpreadListContent || hasSpreadItemContent);
     });
-    
+
     setAvailableTemplates(validTemplates);
-    
+
     // 첫 번째 유효한 템플릿을 기본으로 선택
     if (validTemplates.length > 0 && !selectedTemplateId) {
       setSelectedTemplateId(validTemplates[0].TMPL_ID);
@@ -159,7 +166,7 @@ export function TemplateViewDialog({
       setTemplateType(template.TMPL_TYPE as 'SPREAD_LIST' | 'SPREAD_ITEM');
       setHasChanges(false);
       setValidationErrors([]);
-      
+
       // SpreadSheets 재초기화
       if (currentSpread) {
         const template = availableTemplates.find(t => t.TMPL_ID === templateId);
@@ -175,12 +182,26 @@ export function TemplateViewDialog({
     return availableTemplates.find(t => t.TMPL_ID === selectedTemplateId);
   }, [availableTemplates, selectedTemplateId]);
 
+  // 편집 가능한 필드 목록 계산
   const editableFields = React.useMemo(() => {
-    if (!selectedRow?.TAG_NO || !editableFieldsMap.has(selectedRow.TAG_NO)) {
-      return [];
+    // SPREAD_ITEM인 경우: selectedRow의 TAG_NO로 확인
+    if (templateType === 'SPREAD_ITEM' && selectedRow?.TAG_NO) {
+      if (!editableFieldsMap.has(selectedRow.TAG_NO)) {
+        return [];
+      }
+      return editableFieldsMap.get(selectedRow.TAG_NO) || [];
+    }
+    
+    // SPREAD_LIST인 경우: 첫 번째 행의 TAG_NO를 기준으로 처리
+    if (templateType === 'SPREAD_LIST' && tableData.length > 0) {
+      const firstRowTagNo = tableData[0]?.TAG_NO;
+      if (firstRowTagNo && editableFieldsMap.has(firstRowTagNo)) {
+        return editableFieldsMap.get(firstRowTagNo) || [];
+      }
     }
-    return editableFieldsMap.get(selectedRow.TAG_NO) || [];
-  }, [selectedRow?.TAG_NO, editableFieldsMap]);
+    
+    return [];
+  }, [templateType, selectedRow?.TAG_NO, tableData, editableFieldsMap]);
 
   // 필드가 편집 가능한지 판별하는 함수
   const isFieldEditable = React.useCallback((attId: string, rowData?: GenericData) => {
@@ -189,15 +210,36 @@ export function TemplateViewDialog({
     if (columnConfig?.shi === true) {
       return false; // columnsJSON에서 shi가 true이면 편집 불가
     }
-    
+
     // TAG_NO와 TAG_DESC는 기본적으로 편집 가능 (columnsJSON의 shi가 false인 경우)
     if (attId === "TAG_NO" || attId === "TAG_DESC") {
       return true;
     }
+
+    // SPREAD_ITEM인 경우: editableFields 체크
+    if (templateType === 'SPREAD_ITEM') {
+      return editableFields.includes(attId);
+    }
     
-    // SPREAD_LIST인 경우는 기본적으로 편집 가능 (개별 행의 shi 상태는 저장시 확인)
+    // SPREAD_LIST인 경우: 개별 행의 편집 가능성도 고려
+    if (templateType === 'SPREAD_LIST') {
+      // 기본적으로 editableFields에 포함되어야 함
+      if (!editableFields.includes(attId)) {
+        return false;
+      }
+      
+      // rowData가 제공된 경우 해당 행의 shi 상태도 확인
+      if (rowData && rowData.shi === true) {
+        return false;
+      }
+      
+      return true;
+    }
+    
+    // 기본적으로는 editableFields 체크
+    // return editableFields.includes(attId);
     return true;
-  }, [templateType, selectedRow, columnsJSON, editableFieldsMap]);
+  }, [templateType, columnsJSON, editableFields]);
 
   // 편집 가능한 필드 개수 계산
   const editableFieldsCount = React.useMemo(() => {
@@ -205,22 +247,22 @@ export function TemplateViewDialog({
   }, [cellMappings]);
 
   // 셀 주소를 행과 열로 변환하는 함수
-  const parseCellAddress = (address: string): {row: number, col: number} | null => {
+  const parseCellAddress = (address: string): { row: number, col: number } | null => {
     if (!address || address.trim() === "") return null;
-    
+
     const match = address.match(/^([A-Z]+)(\d+)$/);
     if (!match) return null;
-    
+
     const [, colStr, rowStr] = match;
-    
+
     let col = 0;
     for (let i = 0; i < colStr.length; i++) {
       col = col * 26 + (colStr.charCodeAt(i) - 65 + 1);
     }
     col -= 1;
-    
+
     const row = parseInt(rowStr) - 1;
-    
+
     return { row, col };
   };
 
@@ -248,7 +290,7 @@ export function TemplateViewDialog({
         // 커스텀 타입의 경우 추가 검증 로직이 필요할 수 있음
         break;
     }
-    
+
     return null;
   };
 
@@ -270,7 +312,7 @@ export function TemplateViewDialog({
         // 단일 행 검증
         const cellValue = activeSheet.getValue(cellPos.row, cellPos.col);
         const errorMessage = validateCellValue(cellValue, columnConfig.type, columnConfig.options);
-        
+
         if (errorMessage) {
           errors.push({
             cellAddress: mapping.cellAddress,
@@ -281,312 +323,502 @@ export function TemplateViewDialog({
           });
         }
       } else if (templateType === 'SPREAD_LIST') {
-        // 복수 행 검증
-        for (let i = 0; i < tableData.length; i++) {
-          const targetRow = cellPos.row + i;
-          const cellValue = activeSheet.getValue(targetRow, cellPos.col);
-          const errorMessage = validateCellValue(cellValue, columnConfig.type, columnConfig.options);
-          
-          if (errorMessage) {
-            errors.push({
-              cellAddress: `${String.fromCharCode(65 + cellPos.col)}${targetRow + 1}`,
-              attId: mapping.attId,
-              value: cellValue,
-              expectedType: columnConfig.type,
-              message: errorMessage
-            });
-          }
+        // 복수 행 검증 - 각 매핑은 이미 개별 행을 가리킴
+        const cellValue = activeSheet.getValue(cellPos.row, cellPos.col);
+        const errorMessage = validateCellValue(cellValue, columnConfig.type, columnConfig.options);
+
+        if (errorMessage) {
+          errors.push({
+            cellAddress: mapping.cellAddress,
+            attId: mapping.attId,
+            value: cellValue,
+            expectedType: columnConfig.type,
+            message: errorMessage
+          });
         }
       }
     });
 
     setValidationErrors(errors);
     return errors;
-  }, [currentSpread, selectedTemplate, cellMappings, columnsJSON, templateType, tableData]);
-
-  // LIST 타입 컬럼에 드롭다운 설정
-  const setupListValidation = React.useCallback((activeSheet: any, cellPos: {row: number, col: number}, options: string[], rowCount: number = 1) => {
-    // ComboBox 셀 타입 생성
-    const comboBoxCellType = new GC.Spread.Sheets.CellTypes.ComboBox();
-    comboBoxCellType.items(options);
-    comboBoxCellType.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text);
-
-    // 단일 셀 또는 범위에 적용
-    for (let i = 0; i < rowCount; i++) {
-      const targetRow = cellPos.row + i;
-      activeSheet.setCellType(targetRow, cellPos.col, comboBoxCellType);
+  }, [currentSpread, selectedTemplate, cellMappings, columnsJSON, templateType]);
+
+  // ═══════════════════════════════════════════════════════════════════════════════
+  // 🛠️ 헬퍼 함수들
+  // ═══════════════════════════════════════════════════════════════════════════════
+
+  // 🎨 셀 스타일 생성
+  const createCellStyle = React.useCallback((isEditable: boolean) => {
+    const style = new GC.Spread.Sheets.Style();
+    if (isEditable) {
+      style.backColor = "#f0fdf4"; // 연한 초록 (편집 가능)
+    } else {
+      style.backColor = "#f9fafb"; // 연한 회색 (읽기 전용)
+      style.foreColor = "#6b7280";
+    }
+    return style;
+  }, []);
+
+  // 📋 최적화된 LIST 드롭다운 설정
+const setupOptimizedListValidation = React.useCallback((activeSheet: any, cellPos: { row: number, col: number }, options: string[], rowCount: number) => {
+  try {
+    console.log(`🎯 Setting up DataValidation dropdown for ${rowCount} rows with options:`, options);
+
+    // 🚨 성능 임계점 확인
+    if (rowCount > 100) {
+      console.warn(`⚡ Large dataset (${rowCount} rows): Using simple validation only`);
+      setupSimpleValidation(activeSheet, cellPos, options, rowCount);
+      return;
+    }
+
+    // ✅ 1단계: options 철저하게 정규화 (이것이 에러 방지의 핵심!)
+    let safeOptions;
+    try {
+      safeOptions = options
+        .filter(opt => opt !== null && opt !== undefined && opt !== '') // null, undefined, 빈값 제거
+        .map(opt => {
+          // 모든 값을 안전한 문자열로 변환
+          let str = String(opt);
+          // 특수 문자나 문제 있는 문자 처리
+          str = str.replace(/[\r\n\t]/g, ' '); // 줄바꿈, 탭을 공백으로
+          str = str.replace(/[^\x20-\x7E\u00A1-\uFFFF]/g, ''); // 제어 문자 제거
+          return str.trim();
+        })
+        .filter(opt => opt.length > 0 && opt.length < 255) // 빈값과 너무 긴 값 제거
+        .filter((opt, index, arr) => arr.indexOf(opt) === index) // 중복 제거
+        .slice(0, 100); // 최대 100개로 제한
+
+      console.log(`📋 Original options:`, options);
+      console.log(`📋 Safe options:`, safeOptions);
+    } catch (filterError) {
+      console.error('❌ Options filtering failed:', filterError);
+      safeOptions = ['Option1', 'Option2']; // 안전한 폴백 옵션
+    }
+
+    if (safeOptions.length === 0) {
+      console.warn(`⚠️ No valid options found, using fallback`);
+      safeOptions = ['Please Select'];
+    }
+
+    // ✅ 2단계: DataValidation 생성 (엑셀 스타일 드롭다운)
+    let validator;
+    try {
+      validator = GC.Spread.Sheets.DataValidation.createListValidator(safeOptions);
+      console.log(`✅ DataValidation validator created successfully`);
+    } catch (validatorError) {
+      console.error('❌ Failed to create validator:', validatorError);
+      return;
+    }
+
+    // ✅ 3단계: 셀/범위에 적용
+    try {
+      if (rowCount > 1) {
+        // 범위에 적용
+        const range = activeSheet.getRange(cellPos.row, cellPos.col, rowCount, 1);
+        range.dataValidator(validator);
+        console.log(`✅ DataValidation applied to range [${cellPos.row}, ${cellPos.col}, ${rowCount}, 1]`);
+      } else {
+        // 단일 셀에 적용
+        activeSheet.setDataValidator(cellPos.row, cellPos.col, validator);
+        console.log(`✅ DataValidation applied to single cell [${cellPos.row}, ${cellPos.col}]`);
+      }
+    } catch (applicationError) {
+      console.error('❌ Failed to apply DataValidation:', applicationError);
       
-      // 추가로 데이터 검증도 설정
-      const validator = GC.Spread.Sheets.DataValidation.createListValidator(options);
-      activeSheet.setDataValidator(targetRow, cellPos.col, validator);
+      // 폴백: 개별 셀에 하나씩 적용
+      console.log('🔄 Trying individual cell application...');
+      for (let i = 0; i < Math.min(rowCount, 50); i++) {
+        try {
+          const individualValidator = GC.Spread.Sheets.DataValidation.createListValidator([...safeOptions]);
+          activeSheet.setDataValidator(cellPos.row + i, cellPos.col, individualValidator);
+          console.log(`✅ Individual DataValidation set for row ${cellPos.row + i}`);
+        } catch (individualError) {
+          console.warn(`⚠️ Failed individual cell ${cellPos.row + i}:`, individualError);
+        }
+      }
+    }
+
+    console.log(`✅ DataValidation dropdown setup completed`);
+
+  } catch (error) {
+    console.error('❌ DataValidation setup failed completely:', error);
+    console.error('Error stack:', error.stack);
+    console.log('🔄 Falling back to no validation');
+  }
+}, []);
+
+// ⚡ 단순 검증 설정 (드롭다운 없이 검증만)
+const setupSimpleValidation = React.useCallback((activeSheet: any, cellPos: { row: number, col: number }, options: string[], rowCount: number) => {
+  try {
+    console.log(`⚡ Setting up simple validation (no dropdown UI) for ${rowCount} rows`);
+    
+    const safeOptions = options
+      .filter(opt => opt != null && opt !== '')
+      .map(opt => String(opt).trim())
+      .filter(opt => opt.length > 0);
+
+    if (safeOptions.length === 0) return;
+
+    const validator = GC.Spread.Sheets.DataValidation.createListValidator(safeOptions);
+    
+    // 범위로 적용 시도
+    try {
+      activeSheet.getRange(cellPos.row, cellPos.col, rowCount, 1).dataValidator(validator);
+      console.log(`✅ Simple validation applied to range`);
+    } catch (rangeError) {
+      console.warn('Range validation failed, trying individual cells');
+      // 폴백: 개별 적용
+      for (let i = 0; i < Math.min(rowCount, 100); i++) {
+        try {
+          activeSheet.setDataValidator(cellPos.row + i, cellPos.col, validator);
+        } catch (individualError) {
+          // 개별 실패해도 계속
+        }
+      }
+    }
+    
+  } catch (error) {
+    console.error('❌ Simple validation failed:', error);
+  }
+}, []);
+
+// ═══════════════════════════════════════════════════════════════════════════════
+// 🔍 디버깅용: 에러 발생 원인 추적
+// ═══════════════════════════════════════════════════════════════════════════════
+
+const debugDropdownError = (options: any[], attId: string) => {
+  console.group(`🔍 Debugging dropdown for ${attId}`);
+  
+  console.log('Original options type:', typeof options);
+  console.log('Is array:', Array.isArray(options));
+  console.log('Length:', options?.length);
+  console.log('Raw options:', options);
+  
+  if (Array.isArray(options)) {
+    options.forEach((opt, index) => {
+      console.log(`[${index}] Type: ${typeof opt}, Value: "${opt}", String: "${String(opt)}"`);
+      
+      // 문제 있는 값 체크
+      if (opt === null) console.warn(`  ⚠️ NULL value at index ${index}`);
+      if (opt === undefined) console.warn(`  ⚠️ UNDEFINED value at index ${index}`);
+      if (typeof opt === 'object') console.warn(`  ⚠️ OBJECT value at index ${index}:`, opt);
+      if (typeof opt === 'string' && opt.includes('\n')) console.warn(`  ⚠️ NEWLINE in string at index ${index}`);
+      if (typeof opt === 'string' && opt.length === 0) console.warn(`  ⚠️ EMPTY STRING at index ${index}`);
+    });
+  }
+  
+  console.groupEnd();
+};
+
+  // 🚀 행 용량 확보
+  const ensureRowCapacity = React.useCallback((activeSheet: any, requiredRowCount: number) => {
+    const currentRowCount = activeSheet.getRowCount();
+    if (requiredRowCount > currentRowCount) {
+      activeSheet.setRowCount(requiredRowCount + 10); // 여유분 추가
+      console.log(`📈 Expanded sheet to ${requiredRowCount + 10} rows`);
     }
   }, []);
 
+  // 🛡️ 시트 보호 및 이벤트 설정
+  const setupSheetProtectionAndEvents = React.useCallback((activeSheet: any, mappings: CellMapping[]) => {
+    console.log(`🛡️ Setting up protection and events for ${mappings.length} mappings`);
+    
+    // 시트 보호 설정
+    activeSheet.options.isProtected = true;
+    activeSheet.options.protectionOptions = {
+      allowSelectLockedCells: true,
+      allowSelectUnlockedCells: true,
+      allowSort: false,
+      allowFilter: false,
+      allowEditObjects: false,
+      allowResizeRows: false,
+      allowResizeColumns: false
+    };
+
+    // 🎯 변경 감지 이벤트
+    const changeEvents = [
+      GC.Spread.Sheets.Events.CellChanged,
+      GC.Spread.Sheets.Events.ValueChanged,
+      GC.Spread.Sheets.Events.ClipboardPasted
+    ];
+
+    changeEvents.forEach(eventType => {
+      activeSheet.bind(eventType, () => {
+        console.log(`📝 ${eventType} detected`);
+        setHasChanges(true);
+      });
+    });
+
+    // 🚫 편집 시작 권한 확인 (수정됨)
+    activeSheet.bind(GC.Spread.Sheets.Events.EditStarting, (event: any, info: any) => {
+      console.log(`🎯 EditStarting: Row ${info.row}, Col ${info.col}`);
+      
+      // ✅ 정확한 매핑 찾기 (행/열 정확히 일치)
+      const exactMapping = mappings.find(m => {
+        const cellPos = parseCellAddress(m.cellAddress);
+        return cellPos && cellPos.row === info.row && cellPos.col === info.col;
+      });
+
+      if (!exactMapping) {
+        console.log(`ℹ️ No mapping found for [${info.row}, ${info.col}] - allowing edit`);
+        return; // 매핑이 없으면 허용 (템플릿 영역 밖)
+      }
+
+      console.log(`📋 Found mapping: ${exactMapping.attId} at ${exactMapping.cellAddress}`);
+
+      // 기본 편집 권한 확인
+      if (!exactMapping.isEditable) {
+        console.log(`🚫 Field ${exactMapping.attId} is not editable`);
+        toast.warning(`${exactMapping.attId} field is read-only`);
+        info.cancel = true;
+        return;
+      }
+
+      // SPREAD_LIST 개별 행 SHI 확인
+      if (templateType === 'SPREAD_LIST' && exactMapping.dataRowIndex !== undefined) {
+        const dataRowIndex = exactMapping.dataRowIndex;
+        
+        console.log(`🔍 Checking SHI for data row ${dataRowIndex}`);
+        
+        if (dataRowIndex >= 0 && dataRowIndex < tableData.length) {
+          const rowData = tableData[dataRowIndex];
+          if (rowData?.shi === true) {
+            console.log(`🚫 Row ${dataRowIndex} is in SHI mode`);
+            toast.warning(`Row ${dataRowIndex + 1}: ${exactMapping.attId} field is read-only (SHI mode)`);
+            info.cancel = true;
+            return;
+          }
+        } else {
+          console.warn(`⚠️ Invalid dataRowIndex: ${dataRowIndex} (tableData.length: ${tableData.length})`);
+        }
+      }
+
+      console.log(`✅ Edit allowed for ${exactMapping.attId}`);
+    });
+
+    // ✅ 편집 완료 검증 (수정됨)
+    activeSheet.bind(GC.Spread.Sheets.Events.EditEnded, (event: any, info: any) => {
+      console.log(`🏁 EditEnded: Row ${info.row}, Col ${info.col}`);
+      
+      // ✅ 정확한 매핑 찾기
+      const exactMapping = mappings.find(m => {
+        const cellPos = parseCellAddress(m.cellAddress);
+        return cellPos && cellPos.row === info.row && cellPos.col === info.col;
+      });
+
+      if (!exactMapping) {
+        console.log(`ℹ️ No mapping found for [${info.row}, ${info.col}] - skipping validation`);
+        return;
+      }
+
+      const columnConfig = columnsJSON.find(col => col.key === exactMapping.attId);
+      if (columnConfig) {
+        const cellValue = activeSheet.getValue(info.row, info.col);
+        console.log(`🔍 Validating ${exactMapping.attId}: "${cellValue}"`);
+        
+        const errorMessage = validateCellValue(cellValue, columnConfig.type, columnConfig.options);
+        const cell = activeSheet.getCell(info.row, info.col);
+
+        if (errorMessage) {
+          console.log(`❌ Validation failed: ${errorMessage}`);
+          
+          // 🚨 에러 스타일 적용 (편집 가능 상태 유지)
+          const errorStyle = new GC.Spread.Sheets.Style();
+          errorStyle.backColor = "#fef2f2";
+          errorStyle.foreColor = "#dc2626";
+          errorStyle.borderLeft = new GC.Spread.Sheets.LineBorder("#dc2626", GC.Spread.Sheets.LineStyle.thick);
+          errorStyle.borderRight = new GC.Spread.Sheets.LineBorder("#dc2626", GC.Spread.Sheets.LineStyle.thick);
+          errorStyle.borderTop = new GC.Spread.Sheets.LineBorder("#dc2626", GC.Spread.Sheets.LineStyle.thick);
+          errorStyle.borderBottom = new GC.Spread.Sheets.LineBorder("#dc2626", GC.Spread.Sheets.LineStyle.thick);
+          
+          activeSheet.setStyle(info.row, info.col, errorStyle);
+          cell.locked(!exactMapping.isEditable);
+          
+          toast.warning(`Invalid value in ${exactMapping.attId}: ${errorMessage}. Please correct the value.`, { duration: 5000 });
+        } else {
+          console.log(`✅ Validation passed`);
+          
+          // ✅ 정상 스타일 복원
+          const normalStyle = createCellStyle(exactMapping.isEditable);
+          activeSheet.setStyle(info.row, info.col, normalStyle);
+          cell.locked(!exactMapping.isEditable);
+        }
+      }
+    });
+
+    console.log(`🛡️ Protection and events configured for ${mappings.length} mappings`);
+  }, [templateType, tableData, createCellStyle, validateCellValue, columnsJSON]);
+
+  // ═══════════════════════════════════════════════════════════════════════════════
+  // 🏗️ 메인 SpreadSheets 초기화 함수
+  // ═══════════════════════════════════════════════════════════════════════════════
+
   const initSpread = React.useCallback((spread: any, template?: TemplateItem) => {
     const workingTemplate = template || selectedTemplate;
     if (!spread || !workingTemplate) return;
 
     try {
+      // 🔄 초기 설정
       setCurrentSpread(spread);
       setHasChanges(false);
       setValidationErrors([]);
 
-      // SPR_LST_SETUP.CONTENT와 SPR_ITM_LST_SETUP.CONTENT 중에서 값이 있는 것을 찾아서 사용
+      // 📋 템플릿 콘텐츠 및 데이터 시트 추출
       let contentJson = null;
       let dataSheets = null;
-      
-      // SPR_LST_SETUP.CONTENT가 있으면 우선 사용
+
+      // SPR_LST_SETUP.CONTENT 우선 사용
       if (workingTemplate.SPR_LST_SETUP?.CONTENT) {
         contentJson = workingTemplate.SPR_LST_SETUP.CONTENT;
         dataSheets = workingTemplate.SPR_LST_SETUP.DATA_SHEETS;
-        console.log('Using SPR_LST_SETUP.CONTENT for template:', workingTemplate.NAME, '(TMPL_TYPE:', workingTemplate.TMPL_TYPE, ')');
-      } 
-      // SPR_ITM_LST_SETUP.CONTENT가 있으면 사용
+        console.log('✅ Using SPR_LST_SETUP.CONTENT for template:', workingTemplate.NAME);
+      }
+      // SPR_ITM_LST_SETUP.CONTENT 대안 사용
       else if (workingTemplate.SPR_ITM_LST_SETUP?.CONTENT) {
         contentJson = workingTemplate.SPR_ITM_LST_SETUP.CONTENT;
         dataSheets = workingTemplate.SPR_ITM_LST_SETUP.DATA_SHEETS;
-        console.log('Using SPR_ITM_LST_SETUP.CONTENT for template:', workingTemplate.NAME, '(TMPL_TYPE:', workingTemplate.TMPL_TYPE, ')');
+        console.log('✅ Using SPR_ITM_LST_SETUP.CONTENT for template:', workingTemplate.NAME);
       }
 
       if (!contentJson) {
-        console.warn('No CONTENT found in template:', workingTemplate.NAME);
+        console.warn('❌ No CONTENT found in template:', workingTemplate.NAME);
         return;
       }
 
-      console.log(`Loading template content for: ${workingTemplate.NAME} (Type: ${workingTemplate.TMPL_TYPE})`);
+      // 🏗️ SpreadSheets 초기화
+      const jsonData = typeof contentJson === 'string' ? JSON.parse(contentJson) : contentJson;
       
-      const jsonData = typeof contentJson === 'string' 
-        ? JSON.parse(contentJson) 
-        : contentJson;
-
-      // 렌더링 일시 중단
+      // 성능을 위한 렌더링 일시 중단
       spread.suspendPaint();
 
       try {
-        // fromJSON으로 템플릿 구조 로드
+        // 템플릿 구조 로드
         spread.fromJSON(jsonData);
-
-        // 활성 시트 가져오기
         const activeSheet = spread.getActiveSheet();
         
-        // 시트 보호 먼저 해제
+        // 시트 보호 해제 (편집을 위해)
         activeSheet.options.isProtected = false;
 
-        // MAP_CELL_ATT 정보를 사용해서 데이터 매핑
+        // 📊 셀 매핑 및 데이터 처리
         if (dataSheets && dataSheets.length > 0) {
-          const mappings: Array<{attId: string, cellAddress: string, isEditable: boolean}> = [];
+          const mappings: CellMapping[] = [];
           
+          // 🔄 각 데이터 시트의 매핑 정보 처리
           dataSheets.forEach(dataSheet => {
             if (dataSheet.MAP_CELL_ATT) {
               dataSheet.MAP_CELL_ATT.forEach(mapping => {
                 const { ATT_ID, IN } = mapping;
-                
+
                 if (IN && IN.trim() !== "") {
                   const cellPos = parseCellAddress(IN);
                   if (cellPos) {
-                    const isEditable = isFieldEditable(ATT_ID);
                     const columnConfig = columnsJSON.find(col => col.key === ATT_ID);
-                    
-                    mappings.push({
-                      attId: ATT_ID,
-                      cellAddress: IN,
-                      isEditable: isEditable
-                    });
-                    
-                    // 템플릿 타입에 따라 다른 데이터 처리
+
+                    // 🎯 템플릿 타입별 데이터 처리
                     if (templateType === 'SPREAD_ITEM' && selectedRow) {
-                      // 단일 행 처리 (기존 로직)
+                      // 📝 단일 행 처리 (SPREAD_ITEM)
+                      const isEditable = isFieldEditable(ATT_ID);
+                      
+                      // 매핑 정보 저장
+                      mappings.push({
+                        attId: ATT_ID,
+                        cellAddress: IN,
+                        isEditable: isEditable,
+                        dataRowIndex: 0
+                      });
+
                       const cell = activeSheet.getCell(cellPos.row, cellPos.col);
                       const value = selectedRow[ATT_ID];
-                      if (value !== undefined && value !== null) {
-                        cell.value(value);
-                      }
                       
-                      if (value === undefined || value === null) {
-                        cell.value(null);
-                      }
+                      // 값 설정
+                      cell.value(value ?? null);
+
+                      // 🎨 스타일 및 편집 권한 설정
+                      cell.locked(!isEditable);
+                      const style = createCellStyle(isEditable);
+                      activeSheet.setStyle(cellPos.row, cellPos.col, style);
 
-                      // LIST 타입 컬럼에 드롭다운 설정
+                      // 📋 LIST 타입 드롭다운 설정
                       if (columnConfig?.type === "LIST" && columnConfig.options && isEditable) {
-                        setupListValidation(activeSheet, cellPos, columnConfig.options, 1);
+                        setupOptimizedListValidation(activeSheet, cellPos, columnConfig.options, 1);
                       }
 
-                      // 스타일 적용
-                      cell.locked(!isEditable);
-                      const existingStyle = activeSheet.getStyle(cellPos.row, cellPos.col);
-                      const newStyle = existingStyle ? Object.assign(new GC.Spread.Sheets.Style(), existingStyle) : new GC.Spread.Sheets.Style();
-                      
-                      if (isEditable) {
-                        newStyle.backColor = "#f0fdf4";
-                      } else {
-                        newStyle.backColor = "#f9fafb";
-                        newStyle.foreColor = "#6b7280";
-                      }
-                      
-                      activeSheet.setStyle(cellPos.row, cellPos.col, newStyle);
-                      
                     } else if (templateType === 'SPREAD_LIST' && tableData.length > 0) {
-                      // 복수 행 처리 - 첫 번째 행부터 시작해서 아래로 채움
+                      // 📊 복수 행 처리 (SPREAD_LIST) - 성능 최적화됨
+                      console.log(`🔄 Processing SPREAD_LIST for ${ATT_ID} with ${tableData.length} rows`);
                       
-                      // LIST 타입 컬럼에 드롭다운 설정 (모든 행에 대해)
-                      if (columnConfig?.type === "LIST" && columnConfig.options && isEditable) {
-                        setupListValidation(activeSheet, cellPos, columnConfig.options, tableData.length);
-                      }
+                      // 🚀 행 확장 (필요시)
+                      ensureRowCapacity(activeSheet, cellPos.row + tableData.length);
+
+                      // 📋 각 행마다 개별 매핑 생성
+                      tableData.forEach((rowData, index) => {
+                        const targetRow = cellPos.row + index;
+                        const targetCellAddress = `${String.fromCharCode(65 + cellPos.col)}${targetRow + 1}`;
+                        const cellEditable = isFieldEditable(ATT_ID, rowData);
+                        
+                        // 개별 매핑 추가
+                        mappings.push({
+                          attId: ATT_ID,
+                          cellAddress: targetCellAddress, // 각 행마다 다른 주소
+                          isEditable: cellEditable,
+                          dataRowIndex: index // 원본 데이터 인덱스
+                        });
+                        
+                        console.log(`📝 Mapping ${ATT_ID} Row ${index}: ${targetCellAddress} (${cellEditable ? 'Editable' : 'ReadOnly'})`);
+                      });
 
-                      // 필요한 경우 행 추가 (tableData 길이만큼 충분히 확보)
-                      const currentRowCount = activeSheet.getRowCount();
-                      const requiredRowCount = cellPos.row + tableData.length;
-                      if (requiredRowCount > currentRowCount) {
-                        activeSheet.setRowCount(requiredRowCount + 10); // 여유분 추가
+                      // 📋 LIST 타입 드롭다운 설정 (조건부)
+                      if (columnConfig?.type === "LIST" && columnConfig.options) {
+                        // 편집 가능한 행이 하나라도 있으면 드롭다운 설정
+                        const hasEditableRows = tableData.some((rowData) => isFieldEditable(ATT_ID, rowData));
+                        if (hasEditableRows) {
+                          setupOptimizedListValidation(activeSheet, cellPos, columnConfig.options, tableData.length);
+                        }
                       }
 
+                      // 🎨 개별 셀 데이터 및 스타일 설정
                       tableData.forEach((rowData, index) => {
                         const targetRow = cellPos.row + index;
                         const cell = activeSheet.getCell(targetRow, cellPos.col);
                         const value = rowData[ATT_ID];
                         
-                        if (value !== undefined && value !== null) {
-                          cell.value(value);
-                        }
-
-                        if (value === undefined || value === null) {
-                          cell.value(null);
-                        }
+                        // 값 설정
+                        cell.value(value ?? null);
+                        console.log(`📝 Row ${targetRow}: ${ATT_ID} = "${value}"`);
                         
+                        // 편집 권한 및 스타일 설정
                         const cellEditable = isFieldEditable(ATT_ID, rowData);
                         cell.locked(!cellEditable);
-                        
-                        // 스타일 적용
-                        const existingStyle = activeSheet.getStyle(targetRow, cellPos.col);
-                        const newStyle = existingStyle ? Object.assign(new GC.Spread.Sheets.Style(), existingStyle) : new GC.Spread.Sheets.Style();
-                        
-                        if (cellEditable) {
-                          newStyle.backColor = "#f0fdf4";
-                        } else {
-                          newStyle.backColor = "#f9fafb";
-                          newStyle.foreColor = "#6b7280";
-                        }
-                        
-                        activeSheet.setStyle(targetRow, cellPos.col, newStyle);
+                        const style = createCellStyle(cellEditable);
+                        activeSheet.setStyle(targetRow, cellPos.col, style);
                       });
                     }
-                    
-                    console.log(`Mapped ${ATT_ID} to cell ${IN} - ${isEditable ? 'Editable' : 'Read-only'}`);
+
+                    console.log(`📌 Mapped ${ATT_ID} → ${IN} (${templateType})`);
                   }
                 }
               });
             }
           });
-          
-          setCellMappings(mappings);
-          
-          // 시트 보호 설정
-          activeSheet.options.isProtected = true;
-          activeSheet.options.protectionOptions = {
-            allowSelectLockedCells: true,
-            allowSelectUnlockedCells: true,
-            allowSort: false,
-            allowFilter: false,
-            allowEditObjects: false,
-            allowResizeRows: false,
-            allowResizeColumns: false
-          };
-
-          // 이벤트 리스너 추가
-          activeSheet.bind(GC.Spread.Sheets.Events.CellChanged, (event: any, info: any) => {
-            console.log('Cell changed:', info);
-            setHasChanges(true);
-          });
-
-          activeSheet.bind(GC.Spread.Sheets.Events.ValueChanged, (event: any, info: any) => {
-            console.log('Value changed:', info);
-            setHasChanges(true);
-          });
-
-          // 복사 붙여넣기 이벤트 추가
-          activeSheet.bind(GC.Spread.Sheets.Events.ClipboardPasted, (event: any, info: any) => {
-            console.log('Clipboard pasted:', info);
-            setHasChanges(true);
-          });
-
-          // 편집 시작 시 읽기 전용 셀 확인
-          activeSheet.bind(GC.Spread.Sheets.Events.EditStarting, (event: any, info: any) => {
-            const mapping = mappings.find(m => {
-              const cellPos = parseCellAddress(m.cellAddress);
-              return cellPos && cellPos.row <= info.row && cellPos.col === info.col;
-            });
-            
-            if (mapping) {
-              // columnsJSON에서 해당 필드의 shi 확인
-              const columnConfig = columnsJSON.find(col => col.key === mapping.attId);
-              if (columnConfig?.shi === true) {
-                toast.warning(`${mapping.attId} field is read-only (Column configuration)`);
-                info.cancel = true;
-                return;
-              }
-              
-              // SPREAD_LIST인 경우 해당 행의 데이터에서 shi 확인
-              if (templateType === 'SPREAD_LIST') {
-                const dataRowIndex = info.row - parseCellAddress(mapping.cellAddress)!.row;
-                const rowData = tableData[dataRowIndex];
-                if (rowData && rowData.shi === true) {
-                  toast.warning(`Row ${dataRowIndex + 1}: ${mapping.attId} field is read-only (SHI mode)`);
-                  info.cancel = true;
-                  return;
-                }
-              }
-              
-              if (!mapping.isEditable) {
-                toast.warning(`${mapping.attId} field is read-only`);
-                info.cancel = true;
-              }
-            }
-          });
 
-          // 편집 종료 시 데이터 검증
-          activeSheet.bind(GC.Spread.Sheets.Events.EditEnded, (event: any, info: any) => {
-            const mapping = mappings.find(m => {
-              const cellPos = parseCellAddress(m.cellAddress);
-              return cellPos && cellPos.row <= info.row && cellPos.col === info.col;
-            });
-            
-            if (mapping) {
-              const columnConfig = columnsJSON.find(col => col.key === mapping.attId);
-              if (columnConfig) {
-                const cellValue = activeSheet.getValue(info.row, info.col);
-                const errorMessage = validateCellValue(cellValue, columnConfig.type, columnConfig.options);
-                
-                if (errorMessage) {
-                  toast.warning(`Invalid value in ${mapping.attId}: ${errorMessage}`);
-                  // 스타일을 오류 상태로 변경
-                  const errorStyle = new GC.Spread.Sheets.Style();
-                  errorStyle.backColor = "#fef2f2";
-                  errorStyle.foreColor = "#dc2626";
-                  activeSheet.setStyle(info.row, info.col, errorStyle);
-                } else {
-                  // 정상 스타일로 복원
-                  const cellEditable = isFieldEditable(mapping.attId);
-                  const normalStyle = new GC.Spread.Sheets.Style();
-                  normalStyle.backColor = cellEditable ? "#f0fdf4" : "#f9fafb";
-                  normalStyle.foreColor = cellEditable ? "#000000" : "#6b7280";
-                  activeSheet.setStyle(info.row, info.col, normalStyle);
-                }
-              }
-            }
-          });
+          // 💾 매핑 정보 저장 및 이벤트 설정
+          setCellMappings(mappings);
+          setupSheetProtectionAndEvents(activeSheet, mappings);
         }
+
       } finally {
+        // 렌더링 재개
         spread.resumePaint();
       }
 
     } catch (error) {
-      console.error('Error initializing spread:', error);
+      console.error('❌ Error initializing spread:', error);
       toast.error('Failed to load template');
-      if (spread && spread.resumePaint) {
+      if (spread?.resumePaint) {
         spread.resumePaint();
       }
     }
-  }, [selectedTemplate, templateType, selectedRow, tableData, isFieldEditable, columnsJSON, setupListValidation, validateCellValue]);
+  }, [selectedTemplate, templateType, selectedRow, tableData, isFieldEditable, columnsJSON, createCellStyle, setupOptimizedListValidation, ensureRowCapacity, setupSheetProtectionAndEvents]);
 
   // 변경사항 저장 함수
   const handleSaveChanges = React.useCallback(async () => {
@@ -608,7 +840,7 @@ export function TemplateViewDialog({
       const activeSheet = currentSpread.getActiveSheet();
 
       if (templateType === 'SPREAD_ITEM' && selectedRow) {
-        // 단일 행 저장 (기존 로직)
+        // 단일 행 저장
         const dataToSave = { ...selectedRow };
 
         cellMappings.forEach(mapping => {
@@ -649,21 +881,21 @@ export function TemplateViewDialog({
 
           // 각 매핑에 대해 해당 행의 값 확인
           cellMappings.forEach(mapping => {
-            // columnsJSON에서 해당 필드의 shi 확인
-            const columnConfig = columnsJSON.find(col => col.key === mapping.attId);
-            const isColumnEditable = columnConfig?.shi !== true;
-            const isRowEditable = originalRow.shi !== true;
-            
-            if (mapping.isEditable && isColumnEditable && isRowEditable) {
-              const cellPos = parseCellAddress(mapping.cellAddress);
-              if (cellPos) {
-                const targetRow = cellPos.row + i;
-                const cellValue = activeSheet.getValue(targetRow, cellPos.col);
-                
-                // 값이 변경되었는지 확인
-                if (cellValue !== originalRow[mapping.attId]) {
-                  dataToSave[mapping.attId] = cellValue;
-                  hasRowChanges = true;
+            if (mapping.dataRowIndex === i && mapping.isEditable) {
+              const columnConfig = columnsJSON.find(col => col.key === mapping.attId);
+              const isColumnEditable = columnConfig?.shi !== true;
+              const isRowEditable = originalRow.shi !== true;
+
+              if (isColumnEditable && isRowEditable) {
+                const cellPos = parseCellAddress(mapping.cellAddress);
+                if (cellPos) {
+                  const cellValue = activeSheet.getValue(cellPos.row, cellPos.col);
+
+                  // 값이 변경되었는지 확인
+                  if (cellValue !== originalRow[mapping.attId]) {
+                    dataToSave[mapping.attId] = cellValue;
+                    hasRowChanges = true;
+                  }
                 }
               }
             }
@@ -715,9 +947,9 @@ export function TemplateViewDialog({
 
   return (
     <Dialog open={isOpen} onOpenChange={onClose}>
-      <DialogContent 
-        className="w-[80%] max-w-none h-[80vh] flex flex-col" 
-        style={{maxWidth:"80vw"}}
+      <DialogContent
+        className="w-[80%] max-w-none h-[80vh] flex flex-col"
+        style={{ maxWidth: "80vw" }}
       >
         <DialogHeader className="flex-shrink-0">
           <DialogTitle>SEDP Template - {formCode}</DialogTitle>
@@ -791,11 +1023,11 @@ export function TemplateViewDialog({
             </div>
           </DialogDescription>
         </DialogHeader>
-        
+
         {/* SpreadSheets 컴포넌트 영역 */}
         <div className="flex-1 overflow-hidden">
           {selectedTemplate && isClient && isDataValid ? (
-            <SpreadSheets 
+            <SpreadSheets
               key={`${selectedTemplate.TMPL_TYPE}-${selectedTemplate.TMPL_ID}-${selectedTemplateId}`}
               workbookInitialized={initSpread}
               hostStyle={hostStyle}
@@ -823,10 +1055,10 @@ export function TemplateViewDialog({
             <Button variant="outline" onClick={onClose}>
               Close
             </Button>
-            
+
             {hasChanges && (
-              <Button 
-                variant="default" 
+              <Button
+                variant="default"
                 onClick={handleSaveChanges}
                 disabled={isPending || validationErrors.length > 0}
               >
@@ -845,8 +1077,8 @@ export function TemplateViewDialog({
             )}
 
             {validationErrors.length > 0 && (
-              <Button 
-                variant="outline" 
+              <Button
+                variant="outline"
                 onClick={validateAllData}
                 className="text-red-600 border-red-300 hover:bg-red-50"
               >
-- 
cgit v1.2.3

