diff options
Diffstat (limited to 'components/form-data/export-excel-form.tsx')
| -rw-r--r-- | components/form-data/export-excel-form.tsx | 197 |
1 files changed, 197 insertions, 0 deletions
diff --git a/components/form-data/export-excel-form.tsx b/components/form-data/export-excel-form.tsx new file mode 100644 index 00000000..c4010df2 --- /dev/null +++ b/components/form-data/export-excel-form.tsx @@ -0,0 +1,197 @@ +// lib/excelUtils.ts +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[]; + // 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 the options interface for the export function +export interface ExportExcelOptions { + tableData: GenericData[]; + columnsJSON: DataTableColumnJSON[]; + formCode: string; + onPendingChange?: (isPending: boolean) => void; +} + +// Define the return type +export interface ExportExcelResult { + success: boolean; + error?: any; +} + +/** + * 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, + onPendingChange +}: ExportExcelOptions): Promise<ExportExcelResult> { + try { + if (onPendingChange) onPendingChange(true); + + // 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) => col.label); + worksheet.addRow(headers); + + // 헤더 스타일 적용 + const headerRow = worksheet.getRow(1); + headerRow.font = { bold: true }; + headerRow.alignment = { horizontal: "center" }; + headerRow.eachCell((cell) => { + cell.fill = { + type: "pattern", + pattern: "solid", + fgColor: { argb: "FFCCCCCC" }, + }; + }); + + // 3. 데이터 행 추가 + tableData.forEach((row) => { + const rowValues = columnsJSON.map((col) => { + const value = row[col.key]; + return value !== undefined && value !== null ? value : ""; + }); + worksheet.addRow(rowValues); + }); + + // 4. 데이터 유효성 검사 적용 + const maxRows = 5000; // 데이터 유효성 검사를 적용할 최대 행 수 + + columnsJSON.forEach((col, idx) => { + if (col.type === "LIST" && validationRanges.has(col.key)) { + const colLetter = worksheet.getColumn(idx + 1).letter; + const validationRange = validationRanges.get(col.key)!; + + // 유효성 검사 정의 + const validation = { + type: "list" as const, + allowBlank: true, + formulae: [validationRange], + showErrorMessage: true, + errorStyle: "warning" as const, + errorTitle: "유효하지 않은 값", + error: "목록에서 값을 선택해주세요.", + }; + + // 모든 데이터 행에 유효성 검사 적용 (최대 maxRows까지) + for ( + let rowIdx = 2; + rowIdx <= Math.min(tableData.length + 1, maxRows); + rowIdx++ + ) { + worksheet.getCell(`${colLetter}${rowIdx}`).dataValidation = + validation; + } + + // 빈 행에도 적용 (최대 maxRows까지) + if (tableData.length + 1 < maxRows) { + for ( + let rowIdx = tableData.length + 2; + rowIdx <= maxRows; + rowIdx++ + ) { + worksheet.getCell(`${colLetter}${rowIdx}`).dataValidation = + validation; + } + } + } + }); + + // 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. 파일 다운로드 + const buffer = await workbook.xlsx.writeBuffer(); + saveAs( + new Blob([buffer]), + `${formCode}_data_${new Date().toISOString().slice(0, 10)}.xlsx` + ); + + toast.success("Excel 내보내기 완료!"); + return { success: true }; + } 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 |