From f7f5069a2209cfa39b65f492f32270a5f554bed0 Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Thu, 23 Oct 2025 10:10:21 +0000
Subject: (대표님) EDP 해양 관련 개발 사항들
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 components/form-data-plant/export-excel-form.tsx | 674 +++++++++++++++++++++++
 1 file changed, 674 insertions(+)
 create mode 100644 components/form-data-plant/export-excel-form.tsx

(limited to 'components/form-data-plant/export-excel-form.tsx')

diff --git a/components/form-data-plant/export-excel-form.tsx b/components/form-data-plant/export-excel-form.tsx
new file mode 100644
index 00000000..1efa5819
--- /dev/null
+++ b/components/form-data-plant/export-excel-form.tsx
@@ -0,0 +1,674 @@
+import ExcelJS from "exceljs";
+import { saveAs } from "file-saver";
+import { toast } from "sonner";
+
+// Define the column type enum
+export type ColumnType = "STRING" | "NUMBER" | "LIST" | string;
+
+// Define the column structure
+export interface DataTableColumnJSON {
+  key: string;
+  label: string;
+  type: ColumnType;
+  options?: string[];
+  shi?: string | null; // Updated to support both string and boolean for backward compatibility
+  required?: boolean; // Required field indicator
+  // Add any other properties that might be in columnsJSON
+}
+
+// Define a generic data interface
+export interface GenericData {
+  [key: string]: any;
+  TAG_NO?: string; // Since TAG_NO seems important in the code
+}
+
+// Define error structure
+export interface DataError {
+  tagNo: string;
+  rowIndex: number;
+  columnKey: string;
+  columnLabel: string;
+  errorType: string;
+  errorMessage: string;
+  currentValue?: any;
+  expectedFormat?: string;
+}
+
+// Define the options interface for the export function
+export interface ExportExcelOptions {
+  tableData: GenericData[];
+  columnsJSON: DataTableColumnJSON[];
+  formCode: string;
+  editableFieldsMap?: Map<string, string[]>; // 새로 추가
+  onPendingChange?: (isPending: boolean) => void;
+  validateData?: boolean; // Option to enable/disable data validation
+}
+
+// Define the return type
+export interface ExportExcelResult {
+  success: boolean;
+  error?: any;
+  errorCount?: number;
+  hasErrors?: boolean;
+}
+
+/**
+ * Check if a field is editable for a specific TAG_NO
+ */
+function isFieldEditable(
+  column: DataTableColumnJSON, 
+  tagNo: string, 
+  editableFieldsMap: Map<string, string[]>
+): boolean {
+  // SHI-only fields (shi === "OUT" or shi === null) are never editable
+  if (column.shi === "OUT" || column.shi === null) return false;
+  
+  // System fields are never editable
+  if (column.key === "TAG_NO" || column.key === "TAG_DESC" || column.key === "status") return false;
+  
+  // If no editableFieldsMap provided, assume all non-SHI fields are editable
+  if (!editableFieldsMap || editableFieldsMap.size === 0) return true;
+  
+  // If TAG_NO not in map, no fields are editable
+  if (!editableFieldsMap.has(tagNo)) return false;
+  
+  // Check if this field is in the editable fields list for this TAG_NO
+  const editableFields = editableFieldsMap.get(tagNo) || [];
+  return editableFields.includes(column.key);
+}
+
+/**
+ * Get the read-only reason for a field
+ */
+function getReadOnlyReason(
+  column: DataTableColumnJSON, 
+  tagNo: string, 
+  editableFieldsMap: Map<string, string[]>
+): string {
+  if (column.shi === "OUT" || column.shi === null) {
+    return "SHI-only field";
+  }
+  
+  if (column.key === "TAG_NO" || column.key === "TAG_DESC" || column.key === "status") {
+    return "System field";
+  }
+  
+  if (!editableFieldsMap || editableFieldsMap.size === 0) {
+    return "No restrictions";
+  }
+  
+  if (!editableFieldsMap.has(tagNo)) {
+    return "No editable fields for this TAG";
+  }
+  
+  const editableFields = editableFieldsMap.get(tagNo) || [];
+  if (!editableFields.includes(column.key)) {
+    return "Not editable for this TAG";
+  }
+  
+  return "Editable";
+}
+
+/**
+ * Validate data and collect errors
+ */
+function validateTableData(
+  tableData: GenericData[],
+  columnsJSON: DataTableColumnJSON[]
+): DataError[] {
+  const errors: DataError[] = [];
+  const tagNoSet = new Set<string>();
+
+  tableData.forEach((rowData, index) => {
+    const rowIndex = index + 2; // Excel row number (header is row 1)
+    const tagNo = rowData.TAG_NO || `Row-${rowIndex}`;
+
+    // Check for duplicate TAG_NO
+    if (rowData.TAG_NO) {
+      if (tagNoSet.has(rowData.TAG_NO)) {
+        errors.push({
+          tagNo,
+          rowIndex,
+          columnKey: "TAG_NO",
+          columnLabel: "TAG NO",
+          errorType: "DUPLICATE",
+          errorMessage: "Duplicate TAG_NO found",
+          currentValue: rowData.TAG_NO,
+        });
+      } else {
+        tagNoSet.add(rowData.TAG_NO);
+      }
+    }
+
+    // Validate each column
+    columnsJSON.forEach((column) => {
+      const value = rowData[column.key];
+      const isEmpty = value === undefined || value === null || value === "";
+
+      // Required field validation
+      if (column.required && isEmpty) {
+        errors.push({
+          tagNo,
+          rowIndex,
+          columnKey: column.key,
+          columnLabel: column.label,
+          errorType: "REQUIRED",
+          errorMessage: "Required field is empty",
+          currentValue: value,
+        });
+      }
+
+      if (!isEmpty) {
+        // Type validation
+        switch (column.type) {
+          case "NUMBER":
+            if (isNaN(Number(value))) {
+              errors.push({
+                tagNo,
+                rowIndex,
+                columnKey: column.key,
+                columnLabel: column.label,
+                errorType: "TYPE_MISMATCH",
+                errorMessage: "Value is not a valid number",
+                currentValue: value,
+                expectedFormat: "Number",
+              });
+            }
+            break;
+
+          case "LIST":
+            if (column.options && !column.options.includes(String(value))) {
+              errors.push({
+                tagNo,
+                rowIndex,
+                columnKey: column.key,
+                columnLabel: column.label,
+                errorType: "INVALID_OPTION",
+                errorMessage: "Value is not in the allowed options list",
+                currentValue: value,
+                expectedFormat: column.options.join(", "),
+              });
+            }
+            break;
+
+          case "STRING":
+            // Additional string validations can be added here
+            if (typeof value !== "string" && typeof value !== "number") {
+              errors.push({
+                tagNo,
+                rowIndex,
+                columnKey: column.key,
+                columnLabel: column.label,
+                errorType: "TYPE_MISMATCH",
+                errorMessage: "Value is not a valid string",
+                currentValue: value,
+                expectedFormat: "String",
+              });
+            }
+            break;
+        }
+      }
+    });
+  });
+
+  return errors;
+}
+
+/**
+ * Create error sheet with validation results
+ */
+function createErrorSheet(workbook: ExcelJS.Workbook, errors: DataError[]) {
+  const errorSheet = workbook.addWorksheet("Errors");
+
+  // Error sheet headers
+  const errorHeaders = [
+    "TAG NO",
+    "Row Number",
+    "Column",
+    "Error Type",
+    "Error Message",
+    "Current Value",
+    "Expected Format",
+  ];
+
+  errorSheet.addRow(errorHeaders);
+
+  // Style error sheet header
+  const errorHeaderRow = errorSheet.getRow(1);
+  errorHeaderRow.font = { bold: true, color: { argb: "FFFFFFFF" } };
+  errorHeaderRow.alignment = { horizontal: "center" };
+  
+  errorHeaderRow.eachCell((cell) => {
+    cell.fill = {
+      type: "pattern",
+      pattern: "solid",
+      fgColor: { argb: "FFDC143C" }, // Crimson background
+    };
+  });
+
+  // Add error data
+  errors.forEach((error) => {
+    const errorRow = errorSheet.addRow([
+      error.tagNo,
+      error.rowIndex,
+      error.columnLabel,
+      error.errorType,
+      error.errorMessage,
+      error.currentValue || "",
+      error.expectedFormat || "",
+    ]);
+
+    // Color code by error type
+    errorRow.eachCell((cell, colNumber) => {
+      let bgColor = "FFFFFFFF"; // Default white
+      
+      switch (error.errorType) {
+        case "REQUIRED":
+          bgColor = "FFFFCCCC"; // Light red
+          break;
+        case "TYPE_MISMATCH":
+          bgColor = "FFFFEECC"; // Light orange
+          break;
+        case "INVALID_OPTION":
+          bgColor = "FFFFFFE0"; // Light yellow
+          break;
+        case "DUPLICATE":
+          bgColor = "FFFFE0E0"; // Very light red
+          break;
+      }
+
+      cell.fill = {
+        type: "pattern",
+        pattern: "solid",
+        fgColor: { argb: bgColor },
+      };
+    });
+  });
+
+  // Auto-fit columns
+  errorSheet.columns.forEach((column) => {
+    let maxLength = 0;
+    column.eachCell({ includeEmpty: false }, (cell) => {
+      const columnLength = String(cell.value).length;
+      if (columnLength > maxLength) {
+        maxLength = columnLength;
+      }
+    });
+    column.width = Math.min(Math.max(maxLength + 2, 10), 50);
+  });
+
+  // Add summary at the top
+  errorSheet.insertRow(1, [`Total Errors Found: ${errors.length}`]);
+  const summaryRow = errorSheet.getRow(1);
+  summaryRow.font = { bold: true, size: 14 };
+  if (errors.length > 0) {
+    summaryRow.getCell(1).fill = {
+      type: "pattern",
+      pattern: "solid",
+      fgColor: { argb: "FFFFC0C0" }, // Light red background
+    };
+  }
+
+  // Adjust header row number
+  const newHeaderRow = errorSheet.getRow(2);
+  newHeaderRow.font = { bold: true, color: { argb: "FFFFFFFF" } };
+  newHeaderRow.alignment = { horizontal: "center" };
+  
+  newHeaderRow.eachCell((cell) => {
+    cell.fill = {
+      type: "pattern",
+      pattern: "solid",
+      fgColor: { argb: "FFDC143C" },
+    };
+  });
+
+  return errorSheet;
+}
+
+/**
+ * Export table data to Excel with data validation for select columns
+ * @param options Configuration options for Excel export
+ * @returns Promise with success/error information
+ */
+export async function exportExcelData({
+  tableData,
+  columnsJSON,
+  formCode,
+  editableFieldsMap = new Map(), // 새로 추가
+  onPendingChange,
+  validateData = true
+}: ExportExcelOptions): Promise<ExportExcelResult> {
+  try {
+    if (onPendingChange) onPendingChange(true);
+
+    // Validate data first if validation is enabled
+    const errors = validateData ? validateTableData(tableData, columnsJSON) : [];
+
+    // Create a new workbook
+    const workbook = new ExcelJS.Workbook();
+
+    // 데이터 시트 생성
+    const worksheet = workbook.addWorksheet("Data");
+
+    // 유효성 검사용 숨김 시트 생성
+    const validationSheet = workbook.addWorksheet("ValidationData");
+    validationSheet.state = "hidden"; // 시트 숨김 처리
+
+    // 1. 유효성 검사 시트에 select 옵션 추가
+    const selectColumns = columnsJSON.filter(
+      (col) => col.type === "LIST" && col.options && col.options.length > 0
+    );
+
+    // 유효성 검사 범위 저장 맵 (컬럼 키 -> 유효성 검사 범위)
+    const validationRanges = new Map<string, string>();
+
+    selectColumns.forEach((col, idx) => {
+      const colIndex = idx + 1;
+      const colLetter = validationSheet.getColumn(colIndex).letter;
+
+      // 헤더 추가 (컬럼 레이블)
+      validationSheet.getCell(`${colLetter}1`).value = col.label;
+
+      // 옵션 추가
+      if (col.options) {
+        col.options.forEach((option, optIdx) => {
+          validationSheet.getCell(`${colLetter}${optIdx + 2}`).value = option;
+        });
+
+        // 유효성 검사 범위 저장 (ValidationData!$A$2:$A$4 형식)
+        validationRanges.set(
+          col.key,
+          `ValidationData!${colLetter}$2:${colLetter}${
+            col.options.length + 1
+          }`
+        );
+      }
+    });
+
+    // 2. 데이터 시트에 헤더 추가
+    const headers = columnsJSON.map((col) => {
+      let headerLabel = col.label;
+      if (col.required) {
+        headerLabel += " *"; // Required fields marked with asterisk
+      }
+      return headerLabel;
+    });
+    worksheet.addRow(headers);
+
+    // 헤더 스타일 적용
+    const headerRow = worksheet.getRow(1);
+    headerRow.font = { bold: true };
+    headerRow.alignment = { horizontal: "center" };
+    
+    // 각 헤더 셀에 스타일 적용
+    headerRow.eachCell((cell, colNumber) => {
+      const columnIndex = colNumber - 1;
+      const column = columnsJSON[columnIndex];
+      
+      if (column?.shi === "OUT" || column?.shi === null ) {
+        // SHI-only 필드는 더 진한 음영으로 표시
+        cell.fill = {
+          type: "pattern",
+          pattern: "solid",
+          fgColor: { argb: "FFFF9999" }, // 연한 빨간색 배경
+        };
+        cell.font = { bold: true, color: { argb: "FF800000" } }; // 진한 빨간색 글자
+      } else if (column?.required) {
+        // Required 필드는 파란색 배경
+        cell.fill = {
+          type: "pattern",
+          pattern: "solid",
+          fgColor: { argb: "FFCCE5FF" }, // 연한 파란색 배경
+        };
+        cell.font = { bold: true, color: { argb: "FF000080" } }; // 진한 파란색 글자
+      } else {
+        // 일반 필드는 기존 스타일
+        cell.fill = {
+          type: "pattern",
+          pattern: "solid",
+          fgColor: { argb: "FFCCCCCC" }, // 연한 회색 배경
+        };
+      }
+    });
+
+    // 3. 데이터 행 추가
+    tableData.forEach((rowData, rowIndex) => {
+      const rowValues = columnsJSON.map((col) => {
+        const value = rowData[col.key];
+        return value !== undefined && value !== null ? value : "";
+      });
+      const dataRow = worksheet.addRow(rowValues);
+      
+      // Get errors for this row
+      const rowErrors = errors.filter(err => err.rowIndex === rowIndex + 2);
+      const hasErrors = rowErrors.length > 0;
+      
+      // 각 데이터 셀에 적절한 스타일 적용
+      dataRow.eachCell((cell, colNumber) => {
+        const columnIndex = colNumber - 1;
+        const column = columnsJSON[columnIndex];
+        const tagNo = rowData.TAG_NO || "";
+        
+        // Check if this cell has errors
+        const cellHasError = rowErrors.some(err => err.columnKey === column.key);
+        
+        // Check if this field is editable for this specific TAG_NO
+        const fieldEditable = isFieldEditable(column, tagNo, editableFieldsMap);
+        const readOnlyReason = getReadOnlyReason(column, tagNo, editableFieldsMap);
+        
+        if (!fieldEditable) {
+          // Read-only field styling
+          let bgColor = "FFFFCCCC"; // Default light red for read-only
+          let fontColor = "FF666666"; // Gray text
+          
+          if (column?.shi === "OUT" || column?.shi === null ) {
+            // SHI-only fields get a more distinct styling
+            bgColor = cellHasError ? "FFFF6666" : "FFFFCCCC"; // Darker red if error
+            fontColor = "FF800000"; // Dark red text
+          } else {
+            // Other read-only fields (editableFieldsMap restrictions)
+            bgColor = cellHasError ? "FFFFAA99" : "FFFFDDCC"; // Orange-ish tint
+            fontColor = "FF996633"; // Brown text
+          }
+          
+          cell.fill = {
+            type: "pattern",
+            pattern: "solid",
+            fgColor: { argb: bgColor },
+          };
+          cell.font = { italic: true, color: { argb: fontColor } };
+          
+          // Add comment to explain why it's read-only
+          if (readOnlyReason !== "Editable") {
+            cell.note = {
+              texts: [{ text: `Read-only: ${readOnlyReason}` }],
+              margins: {
+                insetmode: "custom",
+                inset: [0.13, 0.13, 0.25, 0.25]
+              }
+            };
+          }
+        } else if (cellHasError) {
+          // Editable field with validation error
+          cell.fill = {
+            type: "pattern",
+            pattern: "solid",
+            fgColor: { argb: "FFFFDDDD" },
+          };
+          cell.font = { color: { argb: "FFCC0000" } };
+        }
+        // If field is editable and has no errors, no special styling needed
+      });
+    });
+
+    // 4. 데이터 유효성 검사 적용
+    const maxRows = 5000; // 데이터 유효성 검사를 적용할 최대 행 수
+
+    columnsJSON.forEach((col, idx) => {
+      const colLetter = worksheet.getColumn(idx + 1).letter;
+      
+      // LIST 타입이고 유효성 검사 범위가 있는 경우에만 적용
+      if (col.type === "LIST" && validationRanges.has(col.key)) {
+        const validationRange = validationRanges.get(col.key)!;
+
+        // 유효성 검사 정의
+        const validation = {
+          type: "list" as const,
+          allowBlank: !col.required,
+          formulae: [validationRange],
+          showErrorMessage: true,
+          errorStyle: "warning" as const,
+          errorTitle: "유효하지 않은 값",
+          error: "목록에서 값을 선택해주세요.",
+        };
+
+        // 모든 데이터 행에 유효성 검사 적용 (최대 maxRows까지)
+        for (
+          let rowIdx = 2;
+          rowIdx <= Math.min(tableData.length + 1, maxRows);
+          rowIdx++
+        ) {
+          const cell = worksheet.getCell(`${colLetter}${rowIdx}`);
+          
+          // Only apply validation to editable cells
+          const rowData = tableData[rowIdx - 2]; // rowIdx is 1-based, data array is 0-based
+          if (rowData) {
+            const tagNo = rowData.TAG_NO || "";
+            const fieldEditable = isFieldEditable(col, tagNo, editableFieldsMap);
+            
+            if (fieldEditable) {
+              cell.dataValidation = validation;
+            }
+          }
+        }
+
+        // 빈 행에도 적용 (최대 maxRows까지) - 기본적으로 편집 가능하다고 가정
+        if (tableData.length + 1 < maxRows) {
+          for (
+            let rowIdx = tableData.length + 2;
+            rowIdx <= maxRows;
+            rowIdx++
+          ) {
+            worksheet.getCell(`${colLetter}${rowIdx}`).dataValidation = validation;
+          }
+        }
+      }
+      
+      // Read-only 필드의 빈 행들에도 음영 처리 적용 (기본적으로 SHI-only 필드에만)
+      if (col.shi === "OUT" || col.shi === null ) {
+        for (let rowIdx = tableData.length + 2; rowIdx <= maxRows; rowIdx++) {
+          const cell = worksheet.getCell(`${colLetter}${rowIdx}`);
+          cell.fill = {
+            type: "pattern",
+            pattern: "solid",
+            fgColor: { argb: "FFFFCCCC" },
+          };
+          cell.font = { italic: true, color: { argb: "FF666666" } };
+        }
+      }
+    });
+
+    // 5. 컬럼 너비 자동 조정
+    columnsJSON.forEach((col, idx) => {
+      const column = worksheet.getColumn(idx + 1);
+
+      // 최적 너비 계산
+      let maxLength = col.label.length;
+      tableData.forEach((row) => {
+        const value = row[col.key];
+        if (value !== undefined && value !== null) {
+          const valueLength = String(value).length;
+          if (valueLength > maxLength) {
+            maxLength = valueLength;
+          }
+        }
+      });
+
+      // 너비 설정 (최소 10, 최대 50)
+      column.width = Math.min(Math.max(maxLength + 2, 10), 50);
+    });
+
+    // 6. 에러 시트 생성 (에러가 있을 경우에만)
+    if (errors.length > 0) {
+      createErrorSheet(workbook, errors);
+    }
+
+    // 7. 범례 추가 (별도 시트)
+    const legendSheet = workbook.addWorksheet("Legend");
+    legendSheet.addRow(["Excel Template Legend"]);
+    legendSheet.addRow([]);
+    legendSheet.addRow(["Symbol", "Description"]);
+    legendSheet.addRow(["Red background header", "SHI-only fields that cannot be edited"]);
+    legendSheet.addRow(["Blue background header", "Required fields (marked with *)"]);
+    legendSheet.addRow(["Gray background header", "Regular optional fields"]);
+    legendSheet.addRow(["Light red background cells", "Cells with validation errors OR SHI-only fields"]);
+    legendSheet.addRow(["Light orange background cells", "Fields not editable for specific TAG (based on editableFieldsMap)"]);
+    legendSheet.addRow(["Cell comments", "Hover over read-only cells to see the reason why they cannot be edited"]);
+    
+    if (errors.length > 0) {
+      legendSheet.addRow([]);
+      legendSheet.addRow([`Note: ${errors.length} validation errors found in the 'Errors' sheet`]);
+      const errorNoteRow = legendSheet.getRow(legendSheet.rowCount);
+      errorNoteRow.font = { bold: true, color: { argb: "FFCC0000" } };
+    }
+
+    // Add editableFieldsMap summary if available
+    if (editableFieldsMap.size > 0) {
+      legendSheet.addRow([]);
+      legendSheet.addRow([`Editable Fields Map Summary (${editableFieldsMap.size} TAGs):`]);
+      const summaryHeaderRow = legendSheet.getRow(legendSheet.rowCount);
+      summaryHeaderRow.font = { bold: true, color: { argb: "FF000080" } };
+      
+      // Show first few examples
+      let count = 0;
+      for (const [tagNo, editableFields] of editableFieldsMap) {
+        if (count >= 5) { // Show only first 5 examples
+          legendSheet.addRow([`... and ${editableFieldsMap.size - 5} more TAGs`]);
+          break;
+        }
+        legendSheet.addRow([`${tagNo}:`, editableFields.join(", ")]);
+        count++;
+      }
+    }
+    
+    // 범례 스타일 적용
+    const legendHeaderRow = legendSheet.getRow(1);
+    legendHeaderRow.font = { bold: true, size: 14 };
+    
+    const legendTableHeader = legendSheet.getRow(3);
+    legendTableHeader.font = { bold: true };
+    legendTableHeader.eachCell((cell) => {
+      cell.fill = {
+        type: "pattern",
+        pattern: "solid",
+        fgColor: { argb: "FFCCCCCC" },
+      };
+    });
+
+    // 8. 파일 다운로드
+    const buffer = await workbook.xlsx.writeBuffer();
+    const fileName = errors.length > 0 
+      ? `${formCode}_data_with_errors_${new Date().toISOString().slice(0, 10)}.xlsx`
+      : `${formCode}_data_${new Date().toISOString().slice(0, 10)}.xlsx`;
+    
+    saveAs(new Blob([buffer]), fileName);
+
+    const message = errors.length > 0 
+      ? `Excel 내보내기 완료! (${errors.length}개의 검증 오류 발견)`
+      : "Excel 내보내기 완료!";
+    
+    toast.success(message);
+    
+    return { 
+      success: true, 
+      errorCount: errors.length, 
+      hasErrors: errors.length > 0 
+    };
+  } catch (err) {
+    console.error("Excel export error:", err);
+    toast.error("Excel 내보내기 실패.");
+    return { success: false, error: err };
+  } finally {
+    if (onPendingChange) onPendingChange(false);
+  }
+}
\ No newline at end of file
-- 
cgit v1.2.3

