diff options
Diffstat (limited to 'lib/export-to-excel.ts')
| -rw-r--r-- | lib/export-to-excel.ts | 316 |
1 files changed, 316 insertions, 0 deletions
diff --git a/lib/export-to-excel.ts b/lib/export-to-excel.ts new file mode 100644 index 00000000..b35c18d6 --- /dev/null +++ b/lib/export-to-excel.ts @@ -0,0 +1,316 @@ +// lib/utils/export-to-excel.ts + +import ExcelJS from 'exceljs' + +interface ExportToExcelOptions { + filename?: string + sheetName?: string + headers?: string[] + dateFormat?: string + autoFilter?: boolean + freezeHeader?: boolean +} + +/** + * 데이터 배열을 Excel 파일로 내보내기 (ExcelJS 사용) + * @param data - 내보낼 데이터 배열 + * @param options - 내보내기 옵션 + */ +export async function exportDataToExcel( + data: Record<string, any>[], + options: ExportToExcelOptions = {} +) { + const { + filename = 'export', + sheetName = 'Sheet1', + headers, + dateFormat = 'yyyy-mm-dd', + autoFilter = true, + freezeHeader = true + } = options + + try { + // 데이터가 없으면 반환 + if (!data || data.length === 0) { + console.warn('No data to export') + return false + } + + // 워크북 생성 + const workbook = new ExcelJS.Workbook() + workbook.creator = 'TBE System' + workbook.created = new Date() + + // 워크시트 추가 + const worksheet = workbook.addWorksheet(sheetName, { + properties: { + defaultRowHeight: 20 + } + }) + + // 헤더 처리 + const finalHeaders = headers || Object.keys(data[0]) + + // 컬럼 정의 + const columns = finalHeaders.map(header => ({ + header, + key: header, + width: Math.min( + Math.max( + header.length, + ...data.map(row => { + const value = row[header] + if (value === null || value === undefined) return 0 + return String(value).length + }) + ) + 2, + 50 + ) + })) + + worksheet.columns = columns + + // 데이터 추가 + data.forEach(row => { + const rowData: Record<string, any> = {} + + finalHeaders.forEach(header => { + const value = row[header] + + // null/undefined 처리 + if (value === null || value === undefined) { + rowData[header] = '' + } + // Date 객체 처리 + else if (value instanceof Date) { + rowData[header] = value + } + // boolean 처리 + else if (typeof value === 'boolean') { + rowData[header] = value ? 'Yes' : 'No' + } + // 숫자 처리 + else if (typeof value === 'number') { + rowData[header] = value + } + // 기타 (문자열 등) + else { + rowData[header] = String(value) + } + }) + + worksheet.addRow(rowData) + }) + + // 헤더 스타일링 + const headerRow = worksheet.getRow(1) + headerRow.font = { bold: true } + headerRow.fill = { + type: 'pattern', + pattern: 'solid', + fgColor: { argb: 'FFE0E0E0' } + } + headerRow.alignment = { vertical: 'middle', horizontal: 'center' } + headerRow.height = 25 + + // 헤더 테두리 + headerRow.eachCell((cell) => { + cell.border = { + top: { style: 'thin' }, + left: { style: 'thin' }, + bottom: { style: 'thin' }, + right: { style: 'thin' } + } + }) + + // 데이터 행 스타일링 + worksheet.eachRow((row, rowNumber) => { + if (rowNumber > 1) { + row.alignment = { vertical: 'middle' } + row.eachCell((cell) => { + cell.border = { + top: { style: 'thin' }, + left: { style: 'thin' }, + bottom: { style: 'thin' }, + right: { style: 'thin' } + } + }) + } + }) + + // 자동 필터 추가 + if (autoFilter) { + worksheet.autoFilter = { + from: { row: 1, column: 1 }, + to: { row: data.length + 1, column: columns.length } + } + } + + // 헤더 고정 + if (freezeHeader) { + worksheet.views = [ + { state: 'frozen', ySplit: 1 } + ] + } + + // 날짜 포맷 적용 + worksheet.eachRow((row, rowNumber) => { + if (rowNumber > 1) { + row.eachCell((cell, colNumber) => { + const header = finalHeaders[colNumber - 1] + const value = data[rowNumber - 2][header] + + if (value instanceof Date) { + cell.numFmt = dateFormat === 'yyyy-mm-dd' + ? 'yyyy-mm-dd' + : 'mm/dd/yyyy' + } + // 숫자 포맷 (천단위 구분) + else if (typeof value === 'number' && header.toLowerCase().includes('quantity')) { + cell.numFmt = '#,##0' + } + }) + } + }) + + // 파일 다운로드 + 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') + const timestamp = new Date().toISOString().slice(0, 10) + const finalFilename = `${filename}_${timestamp}.xlsx` + + link.href = url + link.download = finalFilename + link.style.display = 'none' + + document.body.appendChild(link) + link.click() + document.body.removeChild(link) + + URL.revokeObjectURL(url) + + return true + } catch (error) { + console.error('Excel export error:', error) + throw new Error('Failed to export Excel file') + } +} + +/** + * Date 객체를 Excel 형식으로 포맷팅 + */ +function formatDateForExcel(date: Date, format: string): string { + const year = date.getFullYear() + const month = String(date.getMonth() + 1).padStart(2, '0') + const day = String(date.getDate()).padStart(2, '0') + + switch (format) { + case 'yyyy-mm-dd': + return `${year}-${month}-${day}` + case 'dd/mm/yyyy': + return `${day}/${month}/${year}` + case 'mm/dd/yyyy': + return `${month}/${day}/${year}` + default: + return date.toLocaleDateString() + } +} + +/** + * CSV 파일로 내보내기 (대안 옵션) + */ +export function exportDataToCSV( + data: Record<string, any>[], + filename: string = 'export' +) { + try { + if (!data || data.length === 0) { + console.warn('No data to export') + return + } + + // 헤더 추출 + const headers = Object.keys(data[0]) + + // CSV 문자열 생성 + let csvContent = headers.join(',') + '\n' + + data.forEach(row => { + const values = headers.map(header => { + const value = row[header] + + // null/undefined 처리 + if (value === null || value === undefined) return '' + + // 콤마나 줄바꿈이 있으면 따옴표로 감싸기 + const stringValue = String(value) + if (stringValue.includes(',') || stringValue.includes('\n')) { + return `"${stringValue.replace(/"/g, '""')}"` + } + + return stringValue + }) + + csvContent += values.join(',') + '\n' + }) + + // BOM 추가 (Excel에서 UTF-8 인식) + const BOM = '\uFEFF' + const blob = new Blob([BOM + csvContent], { type: 'text/csv;charset=utf-8;' }) + + // 다운로드 링크 생성 + const link = document.createElement('a') + const url = URL.createObjectURL(blob) + + link.setAttribute('href', url) + link.setAttribute('download', `${filename}_${new Date().toISOString().slice(0, 10)}.csv`) + link.style.visibility = 'hidden' + + document.body.appendChild(link) + link.click() + document.body.removeChild(link) + + return true + } catch (error) { + console.error('CSV export error:', error) + throw new Error('Failed to export CSV file') + } +} + +/** + * 간단한 데이터 내보내기 헬퍼 + * Excel이 안되면 CSV로 fallback + */ +export async function exportData( + data: Record<string, any>[], + options: ExportToExcelOptions & { format?: 'excel' | 'csv' } = {} +) { + const { format = 'excel', ...exportOptions } = options + + try { + if (format === 'csv') { + return exportDataToCSV(data, exportOptions.filename) + } else { + return exportDataToExcel(data, exportOptions) + } + } catch (error) { + console.error(`Failed to export as ${format}, trying CSV as fallback`) + + // Excel 실패 시 CSV로 시도 + if (format === 'excel') { + try { + return exportDataToCSV(data, exportOptions.filename) + } catch (csvError) { + console.error('Both Excel and CSV export failed') + throw csvError + } + } + + throw error + } +}
\ No newline at end of file |