summaryrefslogtreecommitdiff
path: root/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts
diff options
context:
space:
mode:
Diffstat (limited to 'lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts')
-rw-r--r--lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts303
1 files changed, 303 insertions, 0 deletions
diff --git a/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts b/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts
new file mode 100644
index 00000000..b4254b80
--- /dev/null
+++ b/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts
@@ -0,0 +1,303 @@
+import { type Table } from "@tanstack/react-table";
+import ExcelJS from "exceljs";
+import { type RegEvalCriteriaView } from "@/db/schema";
+import {
+ REG_EVAL_CRITERIA_CATEGORY,
+ REG_EVAL_CRITERIA_CATEGORY2,
+ REG_EVAL_CRITERIA_ITEM,
+} from "@/db/schema";
+
+/**
+ * 평가 기준 데이터를 Excel로 내보내기
+ */
+export async function exportRegEvalCriteriaToExcel<TData extends RegEvalCriteriaView>(
+ table: Table<TData>,
+ {
+ filename = "Regular_Evaluation_Criteria",
+ excludeColumns = ["select", "actions"],
+ sheetName = "평가 기준",
+ }: {
+ filename?: string;
+ excludeColumns?: string[];
+ sheetName?: string;
+ } = {}
+): Promise<void> {
+ // 워크북 생성
+ const workbook = new ExcelJS.Workbook();
+ workbook.creator = "EVCP System";
+ workbook.created = new Date();
+
+ // 메인 워크시트 생성
+ const worksheet = workbook.addWorksheet(sheetName);
+
+ // 한글 헤더 매핑
+ const columnHeaders = [
+ { key: "category", header: "평가부문", width: 15 },
+ { key: "category2", header: "점수구분", width: 15 },
+ { key: "item", header: "항목", width: 15 },
+ { key: "classification", header: "구분", width: 20 },
+ { key: "range", header: "범위", width: 20 },
+ { key: "detail", header: "평가내용", width: 30 },
+ { key: "scoreEquipShip", header: "장비-조선 점수", width: 18 },
+ { key: "scoreEquipMarine", header: "장비-해양 점수", width: 18 },
+ { key: "scoreBulkShip", header: "벌크-조선 점수", width: 18 },
+ { key: "scoreBulkMarine", header: "벌크-해양 점수", width: 18 },
+ { key: "remarks", header: "비고", width: 20 },
+ { key: "id", header: "ID", width: 10 },
+ { key: "criteriaId", header: "기준 ID", width: 12 },
+ { key: "orderIndex", header: "정렬 순서", width: 12 },
+ ].filter(col => !excludeColumns.includes(col.key));
+
+ // 컬럼 설정
+ worksheet.columns = columnHeaders;
+
+ // 헤더 스타일 적용
+ const headerRow = worksheet.getRow(1);
+ headerRow.font = { bold: true };
+ headerRow.fill = {
+ type: "pattern",
+ pattern: "solid",
+ fgColor: { argb: "FFCCCCCC" },
+ };
+ headerRow.alignment = { horizontal: "center", vertical: "middle" };
+
+ // 데이터 행 추가
+ const rowModel = table.getRowModel();
+ rowModel.rows.forEach((row) => {
+ const rowData: Record<string, any> = {};
+ columnHeaders.forEach((col) => {
+ let value = row.original[col.key as keyof RegEvalCriteriaView];
+
+ // 특정 컬럼들에 대해 한글 라벨로 변환
+ if (col.key === "category") {
+ value = REG_EVAL_CRITERIA_CATEGORY.find(item => item.value === value)?.label || value;
+ } else if (col.key === "category2") {
+ value = REG_EVAL_CRITERIA_CATEGORY2.find(item => item.value === value)?.label || value;
+ } else if (col.key === "item") {
+ value = REG_EVAL_CRITERIA_ITEM.find(item => item.value === value)?.label || value;
+ }
+
+ rowData[col.key] = value || "";
+ });
+ worksheet.addRow(rowData);
+ });
+
+ // 셀 스타일 적용
+ worksheet.eachRow((row, rowNumber) => {
+ row.eachCell((cell) => {
+ cell.border = {
+ top: { style: "thin" },
+ left: { style: "thin" },
+ bottom: { style: "thin" },
+ right: { style: "thin" },
+ };
+
+ // 데이터 행 가운데 정렬
+ if (rowNumber > 1) {
+ cell.alignment = { horizontal: "center", vertical: "middle" };
+ }
+ });
+ });
+
+ // 유효성 검사 시트 생성 (풀다운용)
+ const validationSheet = workbook.addWorksheet("ValidationData");
+ validationSheet.state = "hidden";
+
+ // 풀다운 옵션 데이터 추가
+ const categoryOptions = REG_EVAL_CRITERIA_CATEGORY.map(item => item.label);
+ const category2Options = REG_EVAL_CRITERIA_CATEGORY2.map(item => item.label);
+ const itemOptions = REG_EVAL_CRITERIA_ITEM.map(item => item.label);
+
+ validationSheet.getColumn(1).values = ["평가부문", ...categoryOptions];
+ validationSheet.getColumn(2).values = ["점수구분", ...category2Options];
+ validationSheet.getColumn(3).values = ["항목", ...itemOptions];
+
+ // 메인 시트에 데이터 유효성 검사 적용
+ const categoryColIndex = columnHeaders.findIndex(col => col.key === "category") + 1;
+ const category2ColIndex = columnHeaders.findIndex(col => col.key === "category2") + 1;
+ const itemColIndex = columnHeaders.findIndex(col => col.key === "item") + 1;
+
+ if (categoryColIndex > 0) {
+ (worksheet as any).dataValidations.add(`${worksheet.getColumn(categoryColIndex).letter}2:${worksheet.getColumn(categoryColIndex).letter}1000`, {
+ type: "list",
+ allowBlank: false,
+ formulae: [`ValidationData!$A$2:$A$${categoryOptions.length + 1}`],
+ });
+ }
+
+ if (category2ColIndex > 0) {
+ (worksheet as any).dataValidations.add(`${worksheet.getColumn(category2ColIndex).letter}2:${worksheet.getColumn(category2ColIndex).letter}1000`, {
+ type: "list",
+ allowBlank: false,
+ formulae: [`ValidationData!$B$2:$B$${category2Options.length + 1}`],
+ });
+ }
+
+ if (itemColIndex > 0) {
+ (worksheet as any).dataValidations.add(`${worksheet.getColumn(itemColIndex).letter}2:${worksheet.getColumn(itemColIndex).letter}1000`, {
+ type: "list",
+ allowBlank: false,
+ formulae: [`ValidationData!$C$2:$C$${itemOptions.length + 1}`],
+ });
+ }
+
+ // 파일 다운로드
+ const buffer = await workbook.xlsx.writeBuffer();
+ 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 = `${filename}.xlsx`;
+ link.click();
+ URL.revokeObjectURL(url);
+}
+
+/**
+ * 평가 기준 템플릿 다운로드
+ */
+export async function exportRegEvalCriteriaTemplate(): Promise<void> {
+ // 워크북 생성
+ const workbook = new ExcelJS.Workbook();
+ workbook.creator = "EVCP System";
+ workbook.created = new Date();
+
+ // 템플릿 워크시트 생성
+ const worksheet = workbook.addWorksheet("평가 기준 템플릿");
+
+ // 한글 헤더 설정
+ const templateHeaders = [
+ { key: "category", header: "평가부문", width: 15 },
+ { key: "category2", header: "점수구분", width: 15 },
+ { key: "item", header: "항목", width: 15 },
+ { key: "classification", header: "구분", width: 20 },
+ { key: "range", header: "범위", width: 20 },
+ { key: "detail", header: "평가내용", width: 30 },
+ { key: "scoreEquipShip", header: "장비-조선 점수", width: 18 },
+ { key: "scoreEquipMarine", header: "장비-해양 점수", width: 18 },
+ { key: "scoreBulkShip", header: "벌크-조선 점수", width: 18 },
+ { key: "scoreBulkMarine", header: "벌크-해양 점수", width: 18 },
+ { key: "remarks", header: "비고", width: 20 },
+ { key: "orderIndex", header: "정렬 순서", width: 12 },
+ ];
+
+ // 컬럼 설정
+ worksheet.columns = templateHeaders;
+
+ // 헤더 스타일 적용
+ const headerRow = worksheet.getRow(1);
+ headerRow.font = { bold: true };
+ headerRow.fill = {
+ type: "pattern",
+ pattern: "solid",
+ fgColor: { argb: "FFCCCCCC" },
+ };
+ headerRow.alignment = { horizontal: "center", vertical: "middle" };
+
+ // 유효성 검사 시트 생성
+ const validationSheet = workbook.addWorksheet("ValidationData");
+ validationSheet.state = "hidden";
+
+ // 풀다운 옵션 데이터 추가
+ const categoryOptions = REG_EVAL_CRITERIA_CATEGORY.map(item => item.label);
+ const category2Options = REG_EVAL_CRITERIA_CATEGORY2.map(item => item.label);
+ const itemOptions = REG_EVAL_CRITERIA_ITEM.map(item => item.label);
+
+ validationSheet.getColumn(1).values = ["평가부문", ...categoryOptions];
+ validationSheet.getColumn(2).values = ["점수구분", ...category2Options];
+ validationSheet.getColumn(3).values = ["항목", ...itemOptions];
+
+ // 데이터 유효성 검사 적용
+ (worksheet as any).dataValidations.add("A2:A1000", {
+ type: "list",
+ allowBlank: false,
+ formulae: [`ValidationData!$A$2:$A$${categoryOptions.length + 1}`],
+ });
+
+ (worksheet as any).dataValidations.add("B2:B1000", {
+ type: "list",
+ allowBlank: false,
+ formulae: [`ValidationData!$B$2:$B$${category2Options.length + 1}`],
+ });
+
+ (worksheet as any).dataValidations.add("C2:C1000", {
+ type: "list",
+ allowBlank: false,
+ formulae: [`ValidationData!$C$2:$C$${itemOptions.length + 1}`],
+ });
+
+ // 테두리 스타일 적용
+ headerRow.eachCell((cell) => {
+ cell.border = {
+ top: { style: "thin" },
+ left: { style: "thin" },
+ bottom: { style: "thin" },
+ right: { style: "thin" },
+ };
+ });
+
+ // 샘플 데이터 추가 (2-3줄)
+ worksheet.addRow([
+ "품질",
+ "공정",
+ "품질",
+ "품질시스템",
+ "ISO 9001 인증",
+ "품질경영시스템 운영 현황",
+ "10",
+ "10",
+ "10",
+ "10",
+ "품질시스템 운영",
+ "1"
+ ]);
+
+ worksheet.addRow([
+ "관리자",
+ "가격",
+ "납기",
+ "납기준수",
+ "최근 3년",
+ "납기준수율 90% 이상",
+ "5",
+ "5",
+ "5",
+ "5",
+ "납기준수 실적",
+ "2"
+ ]);
+
+ // 샘플 데이터 행에 테두리 적용
+ worksheet.getRow(2).eachCell((cell) => {
+ cell.border = {
+ top: { style: "thin" },
+ left: { style: "thin" },
+ bottom: { style: "thin" },
+ right: { style: "thin" },
+ };
+ cell.alignment = { horizontal: "center", vertical: "middle" };
+ });
+
+ worksheet.getRow(3).eachCell((cell) => {
+ cell.border = {
+ top: { style: "thin" },
+ left: { style: "thin" },
+ bottom: { style: "thin" },
+ right: { style: "thin" },
+ };
+ cell.alignment = { horizontal: "center", vertical: "middle" };
+ });
+
+ // 파일 다운로드
+ const buffer = await workbook.xlsx.writeBuffer();
+ 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);
+} \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 15:10:14 +0000