diff options
Diffstat (limited to 'components/form-data-plant/export-excel-form.tsx')
| -rw-r--r-- | components/form-data-plant/export-excel-form.tsx | 674 |
1 files changed, 674 insertions, 0 deletions
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 |