diff options
Diffstat (limited to 'components/form-data/export-excel-form.tsx')
| -rw-r--r-- | components/form-data/export-excel-form.tsx | 321 |
1 files changed, 304 insertions, 17 deletions
diff --git a/components/form-data/export-excel-form.tsx b/components/form-data/export-excel-form.tsx index d0ccf980..07d3c447 100644 --- a/components/form-data/export-excel-form.tsx +++ b/components/form-data/export-excel-form.tsx @@ -13,6 +13,7 @@ export interface DataTableColumnJSON { type: ColumnType; options?: string[]; shi?: boolean; // SHI-only field indicator + required?: boolean; // Required field indicator // Add any other properties that might be in columnsJSON } @@ -22,18 +23,249 @@ export interface GenericData { 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; 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; +} + +/** + * 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; } /** @@ -45,11 +277,15 @@ export async function exportExcelData({ tableData, columnsJSON, formCode, - onPendingChange + 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(); @@ -92,7 +328,13 @@ export async function exportExcelData({ }); // 2. 데이터 시트에 헤더 추가 - const headers = columnsJSON.map((col) => col.label); + const headers = columnsJSON.map((col) => { + let headerLabel = col.label; + if (col.required) { + headerLabel += " *"; // Required fields marked with asterisk + } + return headerLabel; + }); worksheet.addRow(headers); // 헤더 스타일 적용 @@ -106,13 +348,21 @@ export async function exportExcelData({ const column = columnsJSON[columnIndex]; if (column?.shi === true) { - // SHI-only 필드는 더 진한 음영으로 표시 (헤더 라벨은 원본 유지) + // 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 = { @@ -131,20 +381,34 @@ export async function exportExcelData({ }); const dataRow = worksheet.addRow(rowValues); + // Get errors for this row + const rowErrors = errors.filter(err => err.rowIndex === rowIndex + 2); + const hasErrors = rowErrors.length > 0; + // SHI-only 컬럼의 데이터 셀에도 음영 적용 dataRow.eachCell((cell, colNumber) => { const columnIndex = colNumber - 1; const column = columnsJSON[columnIndex]; + // Check if this cell has errors + const cellHasError = rowErrors.some(err => err.columnKey === column.key); + if (column?.shi === true) { // SHI-only 필드의 데이터 셀에 연한 음영 적용 cell.fill = { type: "pattern", pattern: "solid", - fgColor: { argb: "FFFFCCCC" }, // 매우 연한 빨간색 배경 + fgColor: { argb: cellHasError ? "FFFF6666" : "FFFFCCCC" }, // 에러가 있으면 더 진한 빨간색 }; - // 읽기 전용임을 나타내기 위해 이탤릭 적용 cell.font = { italic: true, color: { argb: "FF666666" } }; + } else if (cellHasError) { + // 에러가 있는 셀은 연한 빨간색 배경 + cell.fill = { + type: "pattern", + pattern: "solid", + fgColor: { argb: "FFFFDDDD" }, + }; + cell.font = { color: { argb: "FFCC0000" } }; } }); }); @@ -162,7 +426,7 @@ export async function exportExcelData({ // 유효성 검사 정의 const validation = { type: "list" as const, - allowBlank: true, + allowBlank: !col.required, formulae: [validationRange], showErrorMessage: true, errorStyle: "warning" as const, @@ -227,15 +491,28 @@ export async function exportExcelData({ column.width = Math.min(Math.max(maxLength + 2, 10), 50); }); - // 6. 범례 추가 (별도 시트) + // 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(["Gray background header", "Regular editable fields"]); - legendSheet.addRow(["Light red background cells", "Data in SHI-only fields (read-only)"]); - legendSheet.addRow(["Red text color", "SHI-only field headers"]); + 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"]); + legendSheet.addRow(["Light red data cells", "Data in SHI-only fields (read-only)"]); + + 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" } }; + } // 범례 스타일 적용 const legendHeaderRow = legendSheet.getRow(1); @@ -251,15 +528,25 @@ export async function exportExcelData({ }; }); - // 7. 파일 다운로드 + // 8. 파일 다운로드 const buffer = await workbook.xlsx.writeBuffer(); - saveAs( - new Blob([buffer]), - `${formCode}_data_${new Date().toISOString().slice(0, 10)}.xlsx` - ); + 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); - toast.success("Excel 내보내기 완료!"); - return { success: true }; + 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 내보내기 실패."); |