diff options
Diffstat (limited to 'components/form-data/import-excel-form.tsx')
| -rw-r--r-- | components/form-data/import-excel-form.tsx | 284 |
1 files changed, 233 insertions, 51 deletions
diff --git a/components/form-data/import-excel-form.tsx b/components/form-data/import-excel-form.tsx index 6f0828b0..82c7afc8 100644 --- a/components/form-data/import-excel-form.tsx +++ b/components/form-data/import-excel-form.tsx @@ -5,6 +5,18 @@ import { DataTableColumnJSON } from "./form-data-table-columns"; import { updateFormDataInDB } from "@/lib/forms/services"; import { decryptWithServerAction } from "../drm/drmUtils"; +// Define error structure for import +export interface ImportError { + tagNo: string; + rowIndex: number; + columnKey: string; + columnLabel: string; + errorType: string; + errorMessage: string; + currentValue?: any; + expectedFormat?: string; +} + // Simplified options interface without editableFieldsMap export interface ImportExcelOptions { file: File; @@ -22,6 +34,8 @@ export interface ImportExcelResult { error?: any; message?: string; skippedFields?: { tagNo: string, fields: string[] }[]; // 건너뛴 필드 정보 + errorCount?: number; + hasErrors?: boolean; } export interface ExportExcelOptions { @@ -35,6 +49,133 @@ interface GenericData { [key: string]: any; } +/** + * Create error sheet with import validation results + */ +function createImportErrorSheet(workbook: ExcelJS.Workbook, errors: ImportError[], headerErrors?: string[]) { + const errorSheet = workbook.addWorksheet("Import_Errors"); + + // Add header error section if exists + if (headerErrors && headerErrors.length > 0) { + errorSheet.addRow(["HEADER VALIDATION ERRORS"]); + const headerErrorTitleRow = errorSheet.getRow(1); + headerErrorTitleRow.font = { bold: true, size: 14, color: { argb: "FFFFFFFF" } }; + headerErrorTitleRow.getCell(1).fill = { + type: "pattern", + pattern: "solid", + fgColor: { argb: "FFDC143C" }, + }; + + headerErrors.forEach((error, index) => { + const errorRow = errorSheet.addRow([`${index + 1}. ${error}`]); + errorRow.getCell(1).fill = { + type: "pattern", + pattern: "solid", + fgColor: { argb: "FFFFCCCC" }, + }; + }); + + errorSheet.addRow([]); // Empty row for separation + } + + // Data validation errors section + const startRow = errorSheet.rowCount + 1; + + // Summary row + errorSheet.addRow([`DATA VALIDATION ERRORS: ${errors.length} errors found`]); + const summaryRow = errorSheet.getRow(startRow); + summaryRow.font = { bold: true, size: 12 }; + if (errors.length > 0) { + summaryRow.getCell(1).fill = { + type: "pattern", + pattern: "solid", + fgColor: { argb: "FFFFC0C0" }, + }; + } + + if (errors.length > 0) { + // Error data headers + const errorHeaders = [ + "TAG NO", + "Row Number", + "Column", + "Error Type", + "Error Message", + "Current Value", + "Expected Format", + ]; + + errorSheet.addRow(errorHeaders); + const headerRow = errorSheet.getRow(errorSheet.rowCount); + headerRow.font = { bold: true, color: { argb: "FFFFFFFF" } }; + headerRow.alignment = { horizontal: "center" }; + + headerRow.eachCell((cell) => { + cell.fill = { + type: "pattern", + pattern: "solid", + fgColor: { argb: "FFDC143C" }, + }; + }); + + // 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) => { + let bgColor = "FFFFFFFF"; // Default white + + switch (error.errorType) { + case "MISSING_TAG_NO": + bgColor = "FFFFCCCC"; // Light red + break; + case "TAG_NOT_FOUND": + bgColor = "FFFFDDDD"; // Very light red + break; + case "TYPE_MISMATCH": + bgColor = "FFFFEECC"; // Light orange + break; + case "INVALID_OPTION": + bgColor = "FFFFFFE0"; // Light yellow + break; + case "HEADER_MISMATCH": + 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); + }); + + return errorSheet; +} + export async function importExcelData({ file, tableData, @@ -80,13 +221,13 @@ export async function importExcelData({ } // Validate headers - let headerErrorMessage = ""; + const headerErrors: string[] = []; // Check for missing required columns columnsJSON.forEach((col) => { const label = col.label; if (!headerToIndexMap.has(label)) { - headerErrorMessage += `Column "${label}" is missing. `; + headerErrors.push(`Column "${label}" is missing from Excel file`); } }); @@ -94,23 +235,24 @@ export async function importExcelData({ headerToIndexMap.forEach((index, headerLabel) => { const found = columnsJSON.some((col) => col.label === headerLabel); if (!found) { - headerErrorMessage += `Unexpected column "${headerLabel}" found in Excel. `; + headerErrors.push(`Unexpected column "${headerLabel}" found in Excel file`); } }); - // Add error column - const lastColIndex = worksheet.columnCount + 1; - worksheet.getRow(1).getCell(lastColIndex).value = "Error"; - - // If header validation fails, download error report and exit - if (headerErrorMessage) { - headerRow.getCell(lastColIndex).value = headerErrorMessage.trim(); + // If header validation fails, create error report and exit + if (headerErrors.length > 0) { + createImportErrorSheet(workbook, [], headerErrors); const outBuffer = await workbook.xlsx.writeBuffer(); - saveAs(new Blob([outBuffer]), `import-check-result_${Date.now()}.xlsx`); + saveAs(new Blob([outBuffer]), `import-error-report_${Date.now()}.xlsx`); - toast.error(`Header mismatch found. Please check downloaded file.`); - return { success: false, error: "Header mismatch" }; + toast.error(`Header validation failed. ${headerErrors.length} errors found. Check downloaded error report.`); + return { + success: false, + error: "Header validation errors", + errorCount: headerErrors.length, + hasErrors: true + }; } // Create column key to Excel index mapping @@ -124,8 +266,8 @@ export async function importExcelData({ // Parse and validate data rows const importedData: GenericData[] = []; + const validationErrors: ImportError[] = []; const lastRowNumber = worksheet.lastRow?.number || 1; - let errorCount = 0; const skippedFieldsLog: { tagNo: string, fields: string[] }[] = []; // 건너뛴 필드 로그 // Process each data row @@ -134,16 +276,40 @@ export async function importExcelData({ const rowValues = row.values as ExcelJS.CellValue[]; if (!rowValues || rowValues.length <= 1) continue; // Skip empty rows - let errorMessage = ""; - let warningMessage = ""; const rowObj: Record<string, any> = {}; const skippedFields: string[] = []; // 현재 행에서 건너뛴 필드들 + let hasErrors = false; // Get the TAG_NO first to identify existing data const tagNoColIndex = keyToIndexMap.get("TAG_NO"); const tagNo = tagNoColIndex ? String(rowValues[tagNoColIndex] ?? "").trim() : ""; const existingRowData = existingDataMap.get(tagNo); + // Validate TAG_NO first + if (!tagNo) { + validationErrors.push({ + tagNo: `Row-${rowNum}`, + rowIndex: rowNum, + columnKey: "TAG_NO", + columnLabel: "TAG NO", + errorType: "MISSING_TAG_NO", + errorMessage: "TAG_NO is empty or missing", + currentValue: tagNo, + }); + hasErrors = true; + } else if (!existingTagNumbers.has(tagNo)) { + validationErrors.push({ + tagNo: tagNo, + rowIndex: rowNum, + columnKey: "TAG_NO", + columnLabel: "TAG NO", + errorType: "TAG_NOT_FOUND", + errorMessage: "TAG_NO not found in current data", + currentValue: tagNo, + }); + hasErrors = true; + } + // Process each column columnsJSON.forEach((col) => { const colIndex = keyToIndexMap.get(col.key); @@ -179,9 +345,6 @@ export async function importExcelData({ // Type-specific validation switch (col.type) { case "STRING": - if (!stringVal && col.key === "TAG_NO") { - errorMessage += `[${col.label}] is empty. `; - } rowObj[col.key] = stringVal; break; @@ -189,7 +352,17 @@ export async function importExcelData({ if (stringVal) { const num = parseFloat(stringVal); if (isNaN(num)) { - errorMessage += `[${col.label}] '${stringVal}' is not a valid number. `; + validationErrors.push({ + tagNo: tagNo || `Row-${rowNum}`, + rowIndex: rowNum, + columnKey: col.key, + columnLabel: col.label, + errorType: "TYPE_MISMATCH", + errorMessage: "Value is not a valid number", + currentValue: stringVal, + expectedFormat: "Number", + }); + hasErrors = true; } else { rowObj[col.key] = num; } @@ -199,14 +372,18 @@ export async function importExcelData({ break; case "LIST": - if ( - stringVal && - col.options && - !col.options.includes(stringVal) - ) { - errorMessage += `[${ - col.label - }] '${stringVal}' not in ${col.options.join(", ")}. `; + if (stringVal && col.options && !col.options.includes(stringVal)) { + validationErrors.push({ + tagNo: tagNo || `Row-${rowNum}`, + rowIndex: rowNum, + columnKey: col.key, + columnLabel: col.label, + errorType: "INVALID_OPTION", + errorMessage: "Value is not in the allowed options list", + currentValue: stringVal, + expectedFormat: col.options.join(", "), + }); + hasErrors = true; } rowObj[col.key] = stringVal; break; @@ -223,26 +400,10 @@ export async function importExcelData({ tagNo: tagNo, fields: skippedFields }); - warningMessage += `Skipped ${skippedFields.length} SHI-only fields. `; } - // Validate TAG_NO - const tagNum = rowObj["TAG_NO"]; - if (!tagNum) { - errorMessage += `No TAG_NO found. `; - } else if (!existingTagNumbers.has(tagNum)) { - errorMessage += `TagNumber '${tagNum}' is not in current data. `; - } - - // Record errors or add to valid data - if (errorMessage) { - row.getCell(lastColIndex).value = errorMessage.trim(); - errorCount++; - } else { - // Add warning message to Excel if there are skipped fields - if (warningMessage) { - row.getCell(lastColIndex).value = `WARNING: ${warningMessage.trim()}`; - } + // Add to valid data only if no errors + if (!hasErrors) { importedData.push(rowObj); } } @@ -256,16 +417,22 @@ export async function importExcelData({ ); } - // If there are validation errors, download error report and exit - if (errorCount > 0) { + // If there are validation errors, create error report and exit + if (validationErrors.length > 0) { + createImportErrorSheet(workbook, validationErrors); + const outBuffer = await workbook.xlsx.writeBuffer(); - saveAs(new Blob([outBuffer]), `import-check-result_${Date.now()}.xlsx`); + saveAs(new Blob([outBuffer]), `import-error-report_${Date.now()}.xlsx`); + toast.error( - `There are ${errorCount} error row(s). Please check downloaded file.` + `Data validation failed. ${validationErrors.length} errors found across ${new Set(validationErrors.map(e => e.tagNo)).size} TAG(s). Check downloaded error report.` ); + return { success: false, error: "Data validation errors", + errorCount: validationErrors.length, + hasErrors: true, skippedFields: skippedFieldsLog }; } @@ -342,6 +509,8 @@ export async function importExcelData({ success: true, importedCount: successCount, message: `Partially successful: ${successCount} rows updated, ${errorCount} errors`, + errorCount: errorCount, + hasErrors: errorCount > 0, skippedFields: skippedFieldsLog }; } else { @@ -349,6 +518,8 @@ export async function importExcelData({ success: false, error: "All updates failed", message: errors.join("\n"), + errorCount: errorCount, + hasErrors: true, skippedFields: skippedFieldsLog }; } @@ -368,6 +539,8 @@ export async function importExcelData({ success: true, importedCount: successCount, message: "All data imported and saved to database", + errorCount: 0, + hasErrors: false, skippedFields: skippedFieldsLog }; } catch (saveError) { @@ -376,6 +549,8 @@ export async function importExcelData({ return { success: false, error: saveError, + errorCount: 1, + hasErrors: true, skippedFields: skippedFieldsLog }; } @@ -393,6 +568,8 @@ export async function importExcelData({ return { success: true, importedCount: importedData.length, + errorCount: 0, + hasErrors: false, skippedFields: skippedFieldsLog }; } @@ -400,7 +577,12 @@ export async function importExcelData({ } catch (err) { console.error("Excel import error:", err); toast.error("Excel import failed."); - return { success: false, error: err }; + return { + success: false, + error: err, + errorCount: 1, + hasErrors: true + }; } finally { if (onPendingChange) onPendingChange(false); } |