(고건) 협력업체 평가 대상 관리 페이지 내 엑셀 템플릿 다운로드 및 엑셀 데이터 업로드 기능 추가

6 files changed, 709 insertions, 133 deletions

diff --git a/lib/evaluation-target-list/service.ts b/lib/evaluation-target-list/service.ts
index 6e2dbfe6..572a9006 100644
--- a/lib/evaluation-target-list/service.ts
+++ b/lib/evaluation-target-list/service.ts
@@ -1,41 +1,53 @@
-'use server'
+'use server';
 
-import { and, or, desc, asc, ilike, eq, isNull, sql, count, inArray, gte, lte, ne } from "drizzle-orm";
-
-import { getFiltersStateParser, getSortingStateParser } from "@/lib/parsers";
-import { filterColumns } from "@/lib/filter-columns";
-import db from "@/db/db";
+/* IMPORT */
+import {
+  and,
+  asc,
+  count,
+  desc,
+  eq,
+  gte,
+  ilike,
+  inArray,
+  isNotNull,
+  lte,
+  or,
+  sql,
+  type SQL,
+} from 'drizzle-orm';
+import { authOptions } from '@/app/api/auth/[...nextauth]/route';
 import {
+  contracts,
+  esgEvaluationItems,
+  EVALUATION_DEPARTMENT_CODES,
   evaluationTargets,
   evaluationTargetReviewers,
   evaluationTargetReviews,
-  users,
-  vendors,
-  type EvaluationTargetStatus,
-  type Division,
-  type MaterialType,
-  type DomesticForeign,
-  EVALUATION_DEPARTMENT_CODES,
-  EvaluationTargetWithDepartments,
   evaluationTargetsWithDepartments,
-  periodicEvaluations,
-  reviewerEvaluations,
-  evaluationSubmissions,
   generalEvaluations,
-  esgEvaluationItems,
-  contracts,
-  projects
-} from "@/db/schema";
-
-
-import { GetEvaluationTargetsSchema } from "./validation";
-import { PgTransaction } from "drizzle-orm/pg-core";
-import { getServerSession } from "next-auth/next"
-import { authOptions } from "@/app/api/auth/[...nextauth]/route"
-import { sendEmail } from "../mail/sendEmail";
-import type { SQL } from "drizzle-orm"
-import { DEPARTMENT_CODE_LABELS } from "@/types/evaluation";
-import { revalidatePath,unstable_noStore } from "next/cache";
+  periodicEvaluations,
+  projects,
+  type Division,
+  type EvaluationTargetWithDepartments,
+  type MaterialType,
+  users,
+  vendors,
+} from '@/db/schema';
+import db from '@/db/db';
+import { decryptWithServerAction } from '@/components/drm/drmUtils';
+import { DEPARTMENT_CODE_LABELS } from '@/types/evaluation';
+import ExcelJS from 'exceljs';
+import { filterColumns } from '@/lib/filter-columns';
+import { getServerSession } from 'next-auth/next';
+import { PgTransaction } from 'drizzle-orm/pg-core';
+import { revalidatePath, unstable_noStore } from 'next/cache';
+import { selectUsers } from '../admin-users/repository';
+import { selectVendors } from '../vendors/repository';
+import { sendEmail } from '../mail/sendEmail';
+import { type GetEvaluationTargetsSchema } from './validation';
+
+// ----------------------------------------------------------------------------------------------------
 
 export async function selectEvaluationTargetsFromView(
   tx: PgTransaction<any, any, any>,
@@ -674,10 +686,8 @@ export async function getAvailableReviewers(departmentCode?: string) {
         // departmentName: "API로 추후", // ✅ 부서명도 반환
       })
       .from(users)
-      .where(ne(users.domain, "partners"))
       .orderBy(users.name)
       // .limit(100);
-      //partners가 아닌 domain에 따라서 필터링
 
     return reviewers;
   } catch (error) {
@@ -1487,3 +1497,466 @@ export async function deleteEvaluationTargets(targetIds: number[]) {
   }
 }
 
+/* HELPER FUNCTION FOR GETTING CURRENT USER ID */
+export async function getCurrentUserId(): Promise<number> {
+  try {
+    const session = await getServerSession(authOptions);
+    return session?.user?.id ? Number(session.user.id) : 3; // 기본값 3, 실제 환경에서는 적절한 기본값 설정
+  } catch (error) {
+    console.error('Error in Getting Current Session User ID:', error);
+    return 3; // 기본값 3
+  }
+}
+
+/* FUNCTION FOR GENERATING EXCEL TEMPLATE */
+export async function generateEvalTargetTemplate(): Promise<ArrayBuffer> {
+  try {
+    // [01] Create a new workbook and worksheet
+    const workbook = new ExcelJS.Workbook();
+    const worksheet = workbook.addWorksheet('협력업체 평가 대상 관리 템플릿');
+    workbook.creator = 'eVCP System';
+    workbook.created = new Date();
+
+    // [02] Generate Header Rows
+    worksheet.mergeCells(1, 1, 2, 6);
+    worksheet.getCell(1, 1).value = '기본 정보';
+    worksheet.mergeCells(1, 7, 2, 9);
+    worksheet.getCell(1, 7).value = 'L/D 클레임 정보';
+    worksheet.mergeCells(1, 10, 1, 19);
+    worksheet.getCell(1, 10).value = '담당자 지정';
+    worksheet.mergeCells(2, 10, 2, 11);
+    worksheet.getCell(2, 10).value = '발주 평가 담당';
+    worksheet.mergeCells(2, 12, 2, 13);
+    worksheet.getCell(2, 12).value = '조달 평가 담당';
+    worksheet.mergeCells(2, 14, 2, 15);
+    worksheet.getCell(2, 14).value = '품질 평가 담당';
+    worksheet.mergeCells(2, 16, 2, 17);
+    worksheet.getCell(2, 16).value = '설계 평가 담당';
+    worksheet.mergeCells(2, 18, 2, 19);
+    worksheet.getCell(2, 18).value = 'CS 평가 담당';
+    const templateHeaders = [
+      { key: 'evaluationYear', width: 15 },
+      { key: 'division', width: 15 },
+      { key: 'vendorCode', width: 20 },
+      { key: 'vendorName', width: 20 },
+      { key: 'materialType', width: 20 },
+      { key: 'adminComment', width: 30 },
+      { key: 'ldClaimCount', width: 15 },
+      { key: 'ldClaimAmount', width: 20 },
+      { key: 'ldClaimCurrency', width: 15 },
+      { key: 'orderEvalName', width: 20 },
+      { key: 'orderEvalEmail', width: 30 },
+      { key: 'procurementEvalName', width: 20 },
+      { key: 'procurementEvalEmail', width: 30 },
+      { key: 'qualityEvalName', width: 20 },
+      { key: 'qualityEvalEmail', width: 30 },
+      { key: 'designEvalName', width: 20 },
+      { key: 'designEvalEmail', width: 30 },
+      { key: 'csEvalName', width: 20 },
+      { key: 'csEvalEmail', width: 30 },
+    ];
+    worksheet.columns = templateHeaders;
+    const headers = [
+      '평가년도',
+      '구분',
+      '협력업체 코드',
+      '협력업체명',
+      '자재구분',
+      '관리자 의견',
+      '클레임 건수',
+      '클레임 금액',
+      '통화단위',
+      '이름',
+      '이메일',
+      '이름',
+      '이메일',
+      '이름',
+      '이메일',
+      '이름',
+      '이메일',
+      '이름',
+      '이메일',
+    ];
+    headers.forEach((header, idx) => {
+      worksheet.getRow(3).getCell(idx + 1).value = header;
+    });
+
+    // [03] Apply Header Styles
+    function applyHeaderStyle(row: ExcelJS.Row) {
+      row.eachCell(cell => {
+        cell.fill = {
+          type: 'pattern',
+          pattern: 'solid',
+          fgColor: { argb: 'FFD9D9D9' },
+        };
+        cell.font = { bold: true };
+        cell.alignment = { horizontal: 'center', vertical: 'middle' };
+        cell.border = {
+          top: { style: 'thin' },
+          left: { style: 'thin' },
+          bottom: { style: 'thin' },
+          right: { style: 'thin' },
+        };
+      });
+    }
+    applyHeaderStyle(worksheet.getRow(1));
+    applyHeaderStyle(worksheet.getRow(2));
+    applyHeaderStyle(worksheet.getRow(3));
+
+    // [04] Generate Data Validation Sheet
+    const validationSheet = workbook.addWorksheet('ValidationData');
+    validationSheet.state = 'hidden';
+
+    const DIVISION_LIST = ['해양', '조선'];
+    const MATERIAL_TYPE_LIST = ['장비재', '벌크'];
+    const CURRENCY_LIST = ['KRW (원)', 'USD (달러)', 'EUR (유로)', 'JPY (엔)'];
+
+    validationSheet.getColumn(1).values = ['구분', ...DIVISION_LIST];
+    validationSheet.getColumn(2).values = ['자재구분', ...MATERIAL_TYPE_LIST];
+    validationSheet.getColumn(3).values = ['통화단위', ...CURRENCY_LIST];
+
+    const divisionCol = templateHeaders.findIndex(h => h.key === 'division') + 1;
+    const materialCol = templateHeaders.findIndex(h => h.key === 'materialType') + 1;
+    const currencyCol = templateHeaders.findIndex(h => h.key === 'ldClaimCurrency') + 1;
+
+    if (divisionCol > 0) {
+      (worksheet as any).dataValidations.add(`${worksheet.getColumn(divisionCol).letter}4:${worksheet.getColumn(divisionCol).letter}1000`, {
+        type: 'list',
+        allowBlank: false,
+        formulae: [`ValidationData!$A$2:$A$${DIVISION_LIST.length + 1}`],
+      });
+    }
+
+    if (materialCol > 0) {
+      (worksheet as any).dataValidations.add(`${worksheet.getColumn(materialCol).letter}4:${worksheet.getColumn(materialCol).letter}1000`, {
+        type: 'list',
+        allowBlank: false,
+        formulae: [`ValidationData!$B$2:$B$${MATERIAL_TYPE_LIST.length + 1}`],
+      });
+    }
+
+    if (currencyCol > 0) {
+      (worksheet as any).dataValidations.add(`${worksheet.getColumn(currencyCol).letter}4:${worksheet.getColumn(currencyCol).letter}1000`, {
+        type: 'list',
+        allowBlank: false,
+        formulae: [`ValidationData!$C$2:$C$${CURRENCY_LIST.length + 1}`],
+      });
+    }
+
+    // [05] Add Sample Data Row
+    worksheet.addRow({
+      evaluationYear: '2025',
+      division: '해양',
+      vendorCode: 'A00000000',
+      vendorName: '(주)협력업체',
+      materialType: '장비재',
+      ldClaimCount: '0',
+      ldClaimAmount: '0',
+      ldClaimCurrency: 'KRW (원)',
+      orderEvalName: '홍길동',
+      orderEvalEmail: 'hong@example.com',
+    });
+
+    // [06] Apply Border to Data Cells
+    for (let rowIdx = 4; rowIdx <= 10; rowIdx++) {
+      const row = worksheet.getRow(rowIdx);
+      for (let colIdx = 1; colIdx <= templateHeaders.length; colIdx++) {
+        const cell = row.getCell(colIdx);
+        if (!cell.value) {
+          cell.value = '';
+        }
+        cell.border = {
+          top: { style: 'thin' },
+          left: { style: 'thin' },
+          bottom: { style: 'thin' },
+          right: { style: 'thin' },
+        };
+      }
+    }
+
+    return await workbook.xlsx.writeBuffer() as ArrayBuffer;
+  } catch (error) {
+    console.error('Error in generating Excel template:', error);
+    throw new Error('Failed to generate Excel template');
+  }
+}
+
+/* FUNCTION FOR IMPORTING EXCEL FILE */
+export async function importEvalTargetExcel(file: File): Promise<{
+  errorFile?: Blob;
+  errorMessage?: string;
+  successMessage?: string;
+}> {
+  const DIVISION_MAP: Record<string, string> = {
+    '해양': 'PLANT',
+    '조선': 'SHIP',
+  };
+  const MATERIAL_TYPE_MAP: Record<string, string> = {
+    '장비재': 'EQUIPMENT',
+    '벌크': 'BULK',
+  };
+  const CURRENCY_MAP: Record<string, string> = {
+    'KRW (원)': 'KRW',
+    'USD (달러)': 'USD',
+    'EUR (유로)': 'EUR',
+    'JPY (엔)': 'JPY',
+  };
+
+  function getCellText(cell: ExcelJS.Cell): string {
+    const value = cell.value;
+    if (!value) {
+      return '';
+    }
+
+    if (typeof value === 'string' || typeof value === 'number') {
+      return value.toString().trim();
+    }
+
+    if (typeof value === 'object' && 'text' in value) {
+      return value.text?.toString().trim() || '';
+    }
+
+    return '';
+  }
+
+  try {
+    const arrayBuffer = await decryptWithServerAction(file);
+    const workbook = new ExcelJS.Workbook();
+    await workbook.xlsx.load(arrayBuffer);
+    
+    const worksheet = workbook.getWorksheet(1);
+    if (!worksheet) {
+      return { errorMessage: '워크시트를 찾을 수 없습니다.' };
+    }
+    const errors: string[] = [];
+    const importDataList: {
+      evaluationYear: number;
+      division: string;
+      vendorId: number;
+      vendorCode: string;
+      vendorName: string;
+      materialType: string;
+      adminComment: string;
+      ldClaimCount: number;
+      ldClaimAmount: number;
+      ldClaimCurrency: string;
+      reviewers: { departmentCode: string; reviewerUserId: number; }[];
+    }[] = [];
+
+    const rows = worksheet.getRows(4, worksheet.rowCount - 3);
+
+    if (!rows) {
+      return { errorMessage: '새로 추가할 평가 대상 데이터가 존재하지 않습니다.' };
+    }
+
+    const duplicateCheckSet = new Set<string>();
+
+    for (const [index, row] of rows.entries()) {
+      const rowIndex = index + 4;
+      try {
+        const rawDivision = row.getCell(2).value?.toString().trim() || '';
+        const rawMaterialType = row.getCell(5).value?.toString().trim() || '';
+        const rawCurrency = row.getCell(9).value?.toString().trim() || 'KRW (원)';
+
+        const rowData = {
+          evaluationYear: Number(row.getCell(1).value) || 0,
+          division: DIVISION_MAP[rawDivision] || '',
+          vendorId: 0,
+          vendorCode: row.getCell(3).value?.toString().trim() || '',
+          vendorName: row.getCell(4).value?.toString().trim() || '',
+          materialType: MATERIAL_TYPE_MAP[rawMaterialType] || '',
+          adminComment: row.getCell(6).value?.toString().trim() || '',
+          ldClaimCount: Number(row.getCell(7).value) || 0,
+          ldClaimAmount: Number(row.getCell(8).value) || 0,
+          ldClaimCurrency: CURRENCY_MAP[rawCurrency] || 'KRW',
+          reviewers: [] as { departmentCode: string; reviewerUserId: number; }[],
+        };
+
+        if (!rowData.evaluationYear && !rowData.division && !rowData.vendorCode && !rowData.materialType) {
+          continue;
+        }
+
+        if (!rowData.evaluationYear || !rowData.division || !rowData.vendorCode || !rowData.materialType) {
+          errors.push(`행 ${rowIndex}: 필수 필드(평가년도, 구분, 협력업체 코드, 자재구분)가 누락되었습니다.`);
+          continue;
+        }
+
+        if (!Object.values(DIVISION_MAP).includes(rowData.division)) {
+          errors.push(`행 ${rowIndex}: 구분 값(${rawDivision})이 잘못되었습니다.`);
+          continue;
+        }
+
+        if (!Object.values(MATERIAL_TYPE_MAP).includes(rowData.materialType)) {
+          errors.push(`행 ${rowIndex}: 자재구분 값(${rawMaterialType})이 잘못되었습니다.`);
+          continue;
+        }
+
+        if (!Object.values(CURRENCY_MAP).includes(rowData.ldClaimCurrency)) {
+          errors.push(`행 ${rowIndex}: 통화 값(${rawCurrency})이 잘못되었습니다.`);
+          continue;
+        }
+
+        const duplicateKey = `${rowData.evaluationYear}_${rowData.division}_${rowData.vendorCode}`;
+        if (duplicateCheckSet.has(duplicateKey)) {
+          errors.push(`행 ${rowIndex}: 시트 내에 중복된 평가 대상 항목이 있습니다. 평가 대상은 평가년도, 구분, 협력업체가 모두 같을 수 없습니다.`);
+          continue;
+        }
+        duplicateCheckSet.add(duplicateKey);
+
+        const existingTargetList = await db.transaction(async (tx) => {
+          const selectRes = await tx
+            .select({ id: evaluationTargets.id })
+            .from(evaluationTargets)
+            .where(
+              and(
+                eq(evaluationTargets.evaluationYear, rowData.evaluationYear),
+                eq(evaluationTargets.vendorCode, rowData.vendorCode),
+                eq(evaluationTargets.division, rowData.division as 'PLANT' | 'SHIP'),
+              )
+            )
+            .limit(1);
+          return selectRes;
+        });
+
+        if (existingTargetList.length > 0) {
+          errors.push(`행 ${rowIndex}: 등록된 평가 대상 항목이 이미 존재합니다. 평가 대상은 평가년도, 구분, 협력업체가 모두 같을 수 없습니다.`);
+          continue;
+        }
+
+        const { vendorList } = await db.transaction(async (tx) => {
+          const selectRes = await selectVendors(tx, {
+            where: eq(vendors.vendorCode, rowData.vendorCode),
+          });
+
+          return { vendorList: selectRes };
+        });
+
+        if (vendorList.length === 0) {
+          errors.push(`행 ${rowIndex}: 협력업체 코드(${rowData.vendorCode})가 존재하지 않습니다. 다시 한 번 확인해주십시오.`);
+          continue;
+        }
+        rowData.vendorId = vendorList[0].id;
+
+        const DEPARTMENT_MAP = [
+          { code: 'ORDER_EVAL', label: '발주 평가 담당', nameCol: 10, emailCol: 11 },
+          { code: 'PROCUREMENT_EVAL', label: '조달 평가 담당', nameCol: 12, emailCol: 13 },
+          { code: 'QUALITY_EVAL', label: '품질 평가 담당', nameCol: 14, emailCol: 15 },
+          { code: 'DESIGN_EVAL', label: '설계 평가 담당', nameCol: 16, emailCol: 17 },
+          { code: 'CS_EVAL', label: 'CS 평가 담당', nameCol: 18, emailCol: 19 },
+        ];
+
+        for (const department of DEPARTMENT_MAP) {
+          const managerName = getCellText(row.getCell(department.nameCol));
+          const managerEmail = getCellText(row.getCell(department.emailCol));
+          if (managerEmail) {
+
+            if (rowData.materialType === 'BULK' && ['DESIGN_EVAL', 'CS_EVAL'].includes(department.code)) {
+              errors.push(`행 ${rowIndex}: 자재구분이 '벌크'인 경우, ${department.label}자를 지정할 수 없습니다.`);
+              continue;
+            }
+            
+            const { userList } = await db.transaction(async (tx) => {
+              const selectRes = await selectUsers(tx, {
+                where: and(eq(users.email, managerEmail), eq(users.isActive, true), isNotNull(users.userCode)),
+                orderBy: [asc(users.name)],
+              });
+
+              return { userList: selectRes };
+            });
+
+            if (userList.length === 0) {
+              errors.push(`행 ${rowIndex}: 입력한 ${department.label}(${managerName}/${managerEmail})이 존재하지 않습니다. 다시 한 번 확인해주십시오.`);
+              continue;
+            }
+            rowData.reviewers.push({
+              departmentCode: department.code,
+              reviewerUserId: userList[0].id,
+            });
+          }
+        }
+
+        if (rowData.reviewers.length === 0) {
+          errors.push(`행 ${rowIndex}: 최소 한 명 이상의 담당자를 지정해주십시오.`);
+          continue;
+        }
+
+        importDataList.push(rowData);
+
+      } catch (error) {
+        errors.push(`행 ${rowIndex}: 데이터 처리 중 오류가 발생했습니다 - ${error}`);
+      }
+    }
+
+    if (errors.length > 0) {
+      const errorWorkbook = new ExcelJS.Workbook();
+      const errorWorksheet = errorWorkbook.addWorksheet('Import Errors');
+      
+      errorWorksheet.columns = [
+        { header: '오류 내용', key: 'error', width: 80 },
+      ];
+
+      errorWorksheet.getRow(1).font = { bold: true };
+      errorWorksheet.getRow(1).fill = {
+        type: 'pattern',
+        pattern: 'solid',
+        fgColor: { argb: 'FFFF0000' }
+      };
+
+      errors.forEach(error => {
+        errorWorksheet.addRow({ error });
+      });
+
+      const buffer = await errorWorkbook.xlsx.writeBuffer();
+      const errorFile = new Blob([buffer], { 
+        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 
+      });
+      
+      return { 
+        errorFile,
+        errorMessage: `${errors.length}개의 오류가 발견되었습니다. 오류 파일을 확인하십시오.` 
+      };
+    }
+
+    if (importDataList.length === 0) {
+      return { errorMessage: '새로 추가할 평가 대상 데이터가 존재하지 않습니다. 파일을 다시 한 번 확인해주십시오.' };
+    }
+
+    const currentUserId = await getCurrentUserId();
+    
+    for (const importData of importDataList) {  
+      const {
+        evaluationYear,
+        division,
+        vendorId,
+        materialType,
+        adminComment,
+        ldClaimCount,
+        ldClaimAmount,
+        ldClaimCurrency,
+        reviewers,
+      } = importData;
+      await createEvaluationTarget(
+        {
+          evaluationYear,
+          division: division as 'PLANT' | 'SHIP',
+          vendorId,
+          materialType: materialType as 'EQUIPMENT' | 'BULK',
+          adminComment,
+          ldClaimCount,
+          ldClaimAmount,
+          ldClaimCurrency: ldClaimCurrency as 'KRW' | 'USD' | 'EUR' | 'JPY',
+          reviewers: reviewers as {
+            departmentCode: 'ORDER_EVAL' | 'PROCUREMENT_EVAL' | 'QUALITY_EVAL' | 'DESIGN_EVAL' | 'CS_EVAL';
+            reviewerUserId: number;
+          }[],
+        },
+        currentUserId,
+      );
+    }
+
+    return { successMessage: `Excel 파일이 성공적으로 업로드되었습니다. ${importDataList.length}개의 평가 대상이 추가되었습니다.` };
+  } catch (error) {
+    console.error('Error in Importing Evaluation Targets from Excel:', error);
+    return { errorMessage: 'Excel 파일 처리 중 오류가 발생했습니다.' };
+  }
+}
diff --git a/lib/evaluation-target-list/table/evaluation-targets-columns.tsx b/lib/evaluation-target-list/table/evaluation-targets-columns.tsx
index 7b6754c1..f00738c7 100644
--- a/lib/evaluation-target-list/table/evaluation-targets-columns.tsx
+++ b/lib/evaluation-target-list/table/evaluation-targets-columns.tsx
@@ -37,8 +37,8 @@ const getConsensusBadge = (consensusStatus: boolean | null) => {
 
 const getDivisionBadge = (division: string) => {
   return (
-    <Badge variant={division === "OCEAN" ? "default" : "secondary"}>
-      {division === "OCEAN" ? "해양" : "조선"}
+    <Badge variant={division === "PLANT" ? "default" : "secondary"}>
+      {division === "PLANT" ? "해양" : "조선"}
     </Badge>
   );
 };
diff --git a/lib/evaluation-target-list/table/evaluation-targets-toolbar-actions.tsx b/lib/evaluation-target-list/table/evaluation-targets-toolbar-actions.tsx
index 714f96c3..66a61912 100644
--- a/lib/evaluation-target-list/table/evaluation-targets-toolbar-actions.tsx
+++ b/lib/evaluation-target-list/table/evaluation-targets-toolbar-actions.tsx
@@ -1,104 +1,112 @@
-"use client"
+'use client';
 
-import * as React from "react"
-import { type Table } from "@tanstack/react-table"
+/* IMPORT */
+import {
+  autoGenerateEvaluationTargets,
+  generateEvalTargetTemplate,
+  importEvalTargetExcel
+} from '../service';
+import { Button } from '@/components/ui/button';
+import { 
+  ConfirmTargetsDialog,
+  ExcludeTargetsDialog,
+  RequestReviewDialog,
+} from './evaluation-target-action-dialogs';
 import { 
-  Plus, 
   Check, 
-  MessageSquare, 
-  X, 
   Download,
-  Upload,
+  FileInput,
+  LoaderCircle,
+  MessageSquare, 
+  Plus, 
   RefreshCw,
-  Settings,
-  Trash2
-} from "lucide-react"
-import { toast } from "sonner"
-import { useRouter } from "next/navigation"
-import { useSession } from "next-auth/react"
-
-import { Button } from "@/components/ui/button"
+  Trash2,
+  Upload,
+  X,
+} from 'lucide-react';
+import { DeleteTargetsDialog } from './delete-targets-dialog';
 import {
   DropdownMenu,
   DropdownMenuContent,
   DropdownMenuItem,
-  DropdownMenuSeparator,
   DropdownMenuTrigger,
-} from "@/components/ui/dropdown-menu"
-import { ManualCreateEvaluationTargetDialog } from "./manual-create-evaluation-target-dialog"
-import { 
-  ConfirmTargetsDialog,
-  ExcludeTargetsDialog,
-  RequestReviewDialog
-} from "./evaluation-target-action-dialogs"
-import { DeleteTargetsDialog } from "./delete-targets-dialog"
-import { EvaluationTargetWithDepartments } from "@/db/schema"
-import { exportTableToExcel } from "@/lib/export"
-import { autoGenerateEvaluationTargets } from "../service" // 서버 액션 import
-import { useAuthRole } from "@/hooks/use-auth-role"
-
+} from '@/components/ui/dropdown-menu';
+import { exportTableToExcel } from '@/lib/export';
+import { ManualCreateEvaluationTargetDialog } from './manual-create-evaluation-target-dialog';
+import { toast } from 'sonner';
+import { type ChangeEvent, useCallback, useMemo, useRef, useState } from 'react';
+import { type EvaluationTargetWithDepartments } from '@/db/schema';
+import { type Table } from '@tanstack/react-table';
+import { useAuthRole } from '@/hooks/use-auth-role';
+import { useRouter } from 'next/navigation';
+import { useSession } from 'next-auth/react';
+
+// ----------------------------------------------------------------------------------------------------
+
+/* TYPES */
 interface EvaluationTargetsTableToolbarActionsProps {
-  table: Table<EvaluationTargetWithDepartments>
-  onRefresh?: () => void
+  table: Table<EvaluationTargetWithDepartments>;
+  onRefresh?: () => void;
 }
 
-export function EvaluationTargetsTableToolbarActions({ 
-  table, 
-  onRefresh 
-}: EvaluationTargetsTableToolbarActionsProps) {
-  const [isLoading, setIsLoading] = React.useState(false)
-  const [manualCreateDialogOpen, setManualCreateDialogOpen] = React.useState(false)
-  const [confirmDialogOpen, setConfirmDialogOpen] = React.useState(false)
-  const [excludeDialogOpen, setExcludeDialogOpen] = React.useState(false)
-  const [reviewDialogOpen, setReviewDialogOpen] = React.useState(false)
-  const [deleteDialogOpen, setDeleteDialogOpen] = React.useState(false)
-  const router = useRouter()
-  const { data: session } = useSession()
+// ----------------------------------------------------------------------------------------------------
+
+export function EvaluationTargetsTableToolbarActions(props: EvaluationTargetsTableToolbarActionsProps) {
+  const { table, onRefresh } = props;
+  const [isLoading, setIsLoading] = useState(false);
+  const [manualCreateDialogOpen, setManualCreateDialogOpen] = useState(false);
+  const [confirmDialogOpen, setConfirmDialogOpen] = useState(false);
+  const [excludeDialogOpen, setExcludeDialogOpen] = useState(false);
+  const [reviewDialogOpen, setReviewDialogOpen] = useState(false);
+  const [deleteDialogOpen, setDeleteDialogOpen] = useState(false);
+  const router = useRouter();
+  const { data: session } = useSession();
+  const fileInputRef = useRef<HTMLInputElement>(null);
 
   // 권한 체크
-  const { hasRole, isLoading: roleLoading } = useAuthRole()
-  const canManageEvaluations = hasRole('정기평가') || hasRole('admin')
+  const { hasRole, isLoading: roleLoading } = useAuthRole();
+  const canManageEvaluations = hasRole('정기평가') || hasRole('admin');
 
   // 사용자 ID 가져오기
-  const userId = React.useMemo(() => {
+  const userId = useMemo(() => {
     return session?.user?.id ? Number(session.user.id) : 1;
   }, [session]);
 
   // 선택된 행들
-  const selectedRows = table.getFilteredSelectedRowModel().rows
-  const hasSelection = selectedRows.length > 0
+  const selectedRows = table.getFilteredSelectedRowModel().rows;
+  const hasSelection = selectedRows.length > 0;
 
   // ✅ selectedTargets를 useMemo로 안정화 (VendorsTable 방식과 동일)
-  const selectedTargets = React.useMemo(() => {
+  const selectedTargets = useMemo(() => {
     return selectedRows.map(row => row.original)
-  }, [selectedRows])
+  }, [selectedRows]);
 
   // ✅ 각 상태별 타겟들을 개별적으로 메모이제이션 (VendorsTable 방식과 동일)
-  const pendingTargets = React.useMemo(() => {
+  const pendingTargets = useMemo(() => {
     return table
       .getFilteredSelectedRowModel()
       .rows
       .map(row => row.original)
-      .filter(t => t.status === "PENDING");
+      .filter(t => t.status === 'PENDING');
   }, [table.getFilteredSelectedRowModel().rows]);
 
-  const confirmedTargets = React.useMemo(() => {
+  const confirmedTargets = useMemo(() => {
     return table
       .getFilteredSelectedRowModel()
       .rows
       .map(row => row.original)
-      .filter(t => t.status === "CONFIRMED");
+      .filter(t => t.status === 'CONFIRMED');
   }, [table.getFilteredSelectedRowModel().rows]);
 
-  const excludedTargets = React.useMemo(() => {
+  const excludedTargets = useMemo(() => {
     return table
       .getFilteredSelectedRowModel()
       .rows
       .map(row => row.original)
-      .filter(t => t.status === "EXCLUDED");
+      .filter(t => t.status === 'EXCLUDED');
   }, [table.getFilteredSelectedRowModel().rows]);
 
-  const consensusTrueTargets = React.useMemo(() => {
+  const consensusTrueTargets = useMemo(() => {
     return table
       .getFilteredSelectedRowModel()
       .rows
@@ -106,7 +114,7 @@ export function EvaluationTargetsTableToolbarActions({
       .filter(t => t.consensusStatus === true);
   }, [table.getFilteredSelectedRowModel().rows]);
 
-  const consensusFalseTargets = React.useMemo(() => {
+  const consensusFalseTargets = useMemo(() => {
     return table
       .getFilteredSelectedRowModel()
       .rows
@@ -114,7 +122,7 @@ export function EvaluationTargetsTableToolbarActions({
       .filter(t => t.consensusStatus === false);
   }, [table.getFilteredSelectedRowModel().rows]);
 
-  const consensusNullTargets = React.useMemo(() => {
+  const consensusNullTargets = useMemo(() => {
     return table
       .getFilteredSelectedRowModel()
       .rows
@@ -123,7 +131,7 @@ export function EvaluationTargetsTableToolbarActions({
   }, [table.getFilteredSelectedRowModel().rows]);
 
   // ✅ 선택된 항목들의 상태 분석 - 안정화된 개별 배열들 사용
-  const selectedStats = React.useMemo(() => {
+  const selectedStats = useMemo(() => {
     const pending = pendingTargets.length
     const confirmed = confirmedTargets.length
     const excluded = excludedTargets.length
@@ -155,12 +163,12 @@ export function EvaluationTargetsTableToolbarActions({
   // ----------------------------------------------------------------
   // 신규 평가 대상 생성 (자동)
   // ----------------------------------------------------------------
-  const handleAutoGenerate = React.useCallback(async () => {
-    setIsLoading(true)
+  const handleAutoGenerate = useCallback(async () => {
+    setIsLoading(true);
     try {
       // 현재 년도를 기준으로 평가 대상 자동 생성
-      const currentYear = new Date().getFullYear()
-      const result = await autoGenerateEvaluationTargets(currentYear, userId)
+      const currentYear = new Date().getFullYear();
+      const result = await autoGenerateEvaluationTargets(currentYear, userId);
       
       if (result.success) {
         if (result.generatedCount === 0) {
@@ -168,52 +176,112 @@ export function EvaluationTargetsTableToolbarActions({
             description: result.skippedCount 
               ? `이미 존재하는 평가 대상: ${result.skippedCount}개`
               : undefined
-          })
+          });
         } else {
           toast.success(result.message, {
             description: result.details 
               ? `해양: ${result.details.shipTargets}개, 조선: ${result.details.plantTargets}개 생성${result.details.duplicateSkipped > 0 ? `, 중복 건너뜀: ${result.details.duplicateSkipped}개` : ''}`
               : undefined
-          })
+          });
         }
-        onRefresh?.()
-        router.refresh()
+        onRefresh?.();
+        router.refresh();
       } else {
-        toast.error(result.error || "자동 생성 중 오류가 발생했습니다.")
+        toast.error(result.error || '자동 생성 중 오류가 발생했습니다.');
       }
     } catch (error) {
-      console.error('Error auto generating targets:', error)
-      toast.error("자동 생성 중 오류가 발생했습니다.")
+      console.error('Error auto generating targets:', error);
+      toast.error("자동 생성 중 오류가 발생했습니다.");
     } finally {
-      setIsLoading(false)
+      setIsLoading(false);
     }
-  }, [router, onRefresh, userId])
+  }, [router, onRefresh, userId]);
 
   // ----------------------------------------------------------------
   // 신규 평가 대상 생성 (수동)
   // ----------------------------------------------------------------
-  const handleManualCreate = React.useCallback(() => {
+  const handleManualCreate = useCallback(() => {
     setManualCreateDialogOpen(true)
-  }, [])
+  }, []);
 
   // ----------------------------------------------------------------
   // 다이얼로그 성공 핸들러
   // ----------------------------------------------------------------
-  const handleActionSuccess = React.useCallback(() => {
+  const handleActionSuccess = useCallback(() => {
     table.resetRowSelection()
     onRefresh?.()
     router.refresh()
-  }, [table, onRefresh, router])
+  }, [table, onRefresh, router]);
+
+
+  // EXCEL IMPORT
+  const handleImport = useCallback(() => {
+    fileInputRef.current?.click();
+  }, [table]);
+  async function onFileChange(event: ChangeEvent<HTMLInputElement>) {
+    const file = event.target.files?.[0];
+    if (!file) {
+      toast.error('가져올 파일을 선택해주십시오.');
+      return;
+    }
+    if (!file.name.endsWith('.xlsx') && !file.name.endsWith('.xls')) {
+      toast.error('.xlsx 또는 .xls 확장자인 Excel 파일만 업로드 가능합니다.');
+      return;
+    }
+    event.target.value = '';
 
-  // ----------------------------------------------------------------
-  // 내보내기 핸들러
-  // ----------------------------------------------------------------
-  const handleExport = React.useCallback(() => {
+    try {
+      const { errorFile, errorMessage, successMessage } = await importEvalTargetExcel(file);
+
+      if (errorMessage) {
+        toast.error(errorMessage);
+ 
+        if (errorFile) {
+          const url = URL.createObjectURL(errorFile);
+          const link = document.createElement('a');
+          link.href = url;
+          link.download = 'errors.xlsx';
+          link.click();
+          URL.revokeObjectURL(url);
+        }
+      } else {
+        toast.success(successMessage || 'Excel 파일이 성공적으로 업로드되었습니다.');
+      }
+    } catch (error) {
+      toast.error('Excel 파일을 업로드하는 중 오류가 발생했습니다.');
+      console.error('Error in Excel File Upload: ', error);
+    } finally {
+      onRefresh?.();
+    }
+  };
+
+  // EXCEL EXPORT
+  const handleExport = useCallback(() => {
     exportTableToExcel(table, {
-      filename: "vendor-target-list",
-      excludeColumns: ["select", "actions"],
+      filename: 'vendor-target-list',
+      excludeColumns: ['select', 'actions'],
     })
-  }, [table])
+  }, [table]);
+
+  // EXCEL TEMPLATE DOWNLOAD
+  const handleTemplateDownload = useCallback(async () => {
+    try {
+      const buffer = await generateEvalTargetTemplate();
+      const blob = new Blob([buffer], {
+        type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
+      });
+      const url = URL.createObjectURL(blob);
+      const link = document.createElement('a');
+      link.href = url;
+      link.download = "협력업체_평가_대상_관리_템플릿.xlsx";
+      link.click();
+      URL.revokeObjectURL(url);
+      toast.success('템플릿 파일이 다운로드되었습니다.');
+    } catch (error) {
+      console.error('Error in Template Download: ', error);
+      toast.error('템플릿 다운로드 중 오류가 발생했습니다.');
+    }
+  }, [table]);
 
   // 권한이 없거나 로딩 중인 경우 내보내기 버튼만 표시
   if (roleLoading) {
@@ -226,10 +294,10 @@ export function EvaluationTargetsTableToolbarActions({
             disabled
             className="gap-2"
           >
-            <Download className="size-4 animate-spin" aria-hidden="true" />
-            <span className="hidden sm:inline">로딩중...</span>
+            <LoaderCircle className="size-4 animate-spin" aria-hidden="true" />
+            <span className="hidden sm:inline">로딩 중...</span>
           </Button>
-        </div>
+        </div>S
       </div>
     )
   }
@@ -266,6 +334,29 @@ export function EvaluationTargetsTableToolbarActions({
 
         {/* 유틸리티 버튼들 */}
         <div className="flex items-center gap-1 border-l pl-2 ml-2">
+          {/* 가져오기 버튼 */}
+          {canManageEvaluations && (
+            <>
+              <Button
+                variant="outline"
+                size="sm"
+                onClick={handleImport}
+                className="gap-2"
+              >
+                <Upload className="size-4" aria-hidden="true" />
+                <span className="hidden sm:inline">가져오기</span>
+              </Button>
+              <input
+                ref={fileInputRef}
+                type="file"
+                accept=".xlsx,.xls"
+                className="hidden"
+                onChange={onFileChange}
+              />
+            </>
+          )}
+
+          {/* 내보내기 버튼 */}
           <Button
             variant="outline"
             size="sm"
@@ -275,6 +366,19 @@ export function EvaluationTargetsTableToolbarActions({
             <Download className="size-4" aria-hidden="true" />
             <span className="hidden sm:inline">내보내기</span>
           </Button>
+
+          {/* 템플릿 다운로드 버튼 */}
+          {canManageEvaluations && (
+            <Button
+              variant="outline"
+              size="sm"
+              onClick={handleTemplateDownload}
+              className="gap-2"
+            >
+              <FileInput className="size-4" aria-hidden="true" />
+              <span className="hidden sm:inline">템플릿 다운로드</span>
+            </Button>
+          )}
         </div>
 
         {/* 선택된 항목 액션 버튼들 - 정기평가 권한이 있는 경우만 표시 */}
diff --git a/lib/risk-management/service.ts b/lib/risk-management/service.ts
index d87e8231..7037b5cc 100644
--- a/lib/risk-management/service.ts
+++ b/lib/risk-management/service.ts
@@ -365,7 +365,7 @@ async function generateRiskEventsTemplate(): Promise<ArrayBuffer> {
   try {

     const workbook = new ExcelJS.Workbook();

     const worksheet = workbook.addWorksheet('협력업체 리스크 입력 템플릿');

-    workbook.creator = 'EVCP System';

+    workbook.creator = 'eVCP System';

     workbook.created = new Date();

 

     const templateHeaders = [

diff --git a/lib/risk-management/table/risks-table-toolbar-actions.tsx b/lib/risk-management/table/risks-table-toolbar-actions.tsx
index a55634b5..821ad49f 100644
--- a/lib/risk-management/table/risks-table-toolbar-actions.tsx
+++ b/lib/risk-management/table/risks-table-toolbar-actions.tsx
@@ -7,7 +7,6 @@ import { Download, FileInput, Mail, Upload } from 'lucide-react';
 import { exportTableToExcel } from '@/lib/export';
 import { generateRiskEventsTemplate, importRiskEventsExcel } from '../service';
 import { toast } from 'sonner';
-import { type DataTableRowAction } from '@/types/table';
 import { type RisksView } from '@/db/schema';
 import { type Table } from '@tanstack/react-table';
 
@@ -89,12 +88,12 @@ function RisksTableToolbarActions(props: RisksTableToolbarActionsProps) {
     try {
       const buffer = await generateRiskEventsTemplate();
       const blob = new Blob([buffer], {
-        type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
+        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
       });
       const url = URL.createObjectURL(blob);
       const link = document.createElement('a');
       link.href = url;
-      link.download = "협력업체_리스크_템플릿.xlsx";
+      link.download = '협력업체_리스크_템플릿.xlsx';
       link.click();
       URL.revokeObjectURL(url);
       toast.success('템플릿 파일이 다운로드되었습니다.');
diff --git a/types/evaluation.ts b/types/evaluation.ts
index 9cf1f2c0..321cb57c 100644
--- a/types/evaluation.ts
+++ b/types/evaluation.ts
@@ -8,22 +8,22 @@ export const DEPARTMENT_CODE_LABELS = {
 
 
   export const vendortypeMap = {
-    EQUIPMENT: "기자재",
+    EQUIPMENT: "장비재",
     BULK: "벌크",
-    EQUIPMENT_BULK: "기자재/벌크"
+    EQUIPMENT_BULK: "장비재/벌크"
   };
 
 
   export const divisionMap = {
-      PLANT: "해양",
-      SHIP: "조선"
+    PLANT: "해양",
+    SHIP: "조선"
   };
 
 
   export const domesticForeignMap = {
-      DOMESTIC: "D",
-      FOREIGN: "F"
-};
+    DOMESTIC: "D",
+    FOREIGN: "F"
+  };
 
   export type EvaluationTargetStatus = "PENDING" | "CONFIRMED" | "EXCLUDED";
   export type Division = "PLANT" | "SHIP";