1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
import { NextRequest, NextResponse } from 'next/server'
import ExcelJS from 'exceljs'
import db from '@/db/db'
import { ocrRows, users } from '@/db/schema'
import { eq, desc } from 'drizzle-orm'
import { PassThrough } from 'stream'
export async function GET(request: NextRequest) {
try {
// ExcelJS 워크북 생성 (스트리밍 모드)
const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
stream: new PassThrough(),
useStyles: true,
useSharedStrings: true
})
// 워크시트 추가
const worksheet = workbook.addWorksheet('OCR Data', {
properties: { defaultColWidth: 15 }
})
// 헤더 정의
worksheet.columns = [
{ header: 'ID', key: 'id', width: 10 },
{ header: 'Table ID', key: 'tableId', width: 15 },
{ header: 'Session ID', key: 'sessionId', width: 20 },
{ header: 'Row Index', key: 'rowIndex', width: 12 },
{ header: 'Report No', key: 'reportNo', width: 15 },
{ header: 'File Name', key: 'fileName', width: 30 },
{ header: 'Inspection Date', key: 'inspectionDate', width: 15 },
{ header: 'No', key: 'no', width: 10 },
{ header: 'Identification No', key: 'identificationNo', width: 20 },
{ header: 'Tag No', key: 'tagNo', width: 15 },
{ header: 'Joint No', key: 'jointNo', width: 15 },
{ header: 'Joint Type', key: 'jointType', width: 15 },
{ header: 'Welding Date', key: 'weldingDate', width: 15 },
{ header: 'Confidence', key: 'confidence', width: 12 },
{ header: 'Source Table', key: 'sourceTable', width: 15 },
{ header: 'Source Row', key: 'sourceRow', width: 12 },
{ header: 'User Name', key: 'userName', width: 20 },
{ header: 'User Email', key: 'userEmail', width: 25 },
{ header: 'Created At', key: 'createdAt', width: 20 },
]
// 헤더 스타일 적용
worksheet.getRow(1).font = { bold: true }
worksheet.getRow(1).fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFE0E0E0' }
}
// 청크 단위로 데이터 가져와서 스트리밍
const CHUNK_SIZE = 1000
let offset = 0
let hasMore = true
while (hasMore) {
const chunk = await db
.select({
id: ocrRows.id,
tableId: ocrRows.tableId,
sessionId: ocrRows.sessionId,
rowIndex: ocrRows.rowIndex,
reportNo: ocrRows.reportNo,
fileName: ocrRows.fileName,
inspectionDate: ocrRows.inspectionDate,
no: ocrRows.no,
identificationNo: ocrRows.identificationNo,
tagNo: ocrRows.tagNo,
jointNo: ocrRows.jointNo,
jointType: ocrRows.jointType,
weldingDate: ocrRows.weldingDate,
confidence: ocrRows.confidence,
sourceTable: ocrRows.sourceTable,
sourceRow: ocrRows.sourceRow,
userId: ocrRows.userId,
createdAt: ocrRows.createdAt,
userName: users.name,
userEmail: users.email,
})
.from(ocrRows)
.leftJoin(users, eq(ocrRows.userId, users.id))
.orderBy(desc(ocrRows.createdAt))
.limit(CHUNK_SIZE)
.offset(offset)
if (chunk.length === 0) {
hasMore = false
} else {
// 각 행을 워크시트에 추가
for (const row of chunk) {
worksheet.addRow({
...row,
createdAt: row.createdAt ? new Date(row.createdAt).toLocaleString('ko-KR') : '',
inspectionDate: row.inspectionDate ? new Date(row.inspectionDate).toLocaleDateString('ko-KR') : '',
weldingDate: row.weldingDate ? new Date(row.weldingDate).toLocaleDateString('ko-KR') : '',
}).commit() // commit()으로 메모리 즉시 해제
}
offset += chunk.length
if (chunk.length < CHUNK_SIZE) {
hasMore = false
}
}
}
// 워크시트 커밋
worksheet.commit()
// 스트림을 버퍼로 변환
const buffer = await streamToBuffer(workbook)
// 응답 헤더 설정
const headers = new Headers()
headers.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
headers.set('Content-Disposition', `attachment; filename="OCR_Export_${new Date().toISOString().split('T')[0]}.xlsx"`)
return new NextResponse(buffer, { headers })
} catch (error) {
console.error('Export error:', error)
return NextResponse.json({ error: 'Export failed' }, { status: 500 })
}
}
// 스트림을 버퍼로 변환하는 헬퍼 함수
async function streamToBuffer(workbook: ExcelJS.stream.xlsx.WorkbookWriter): Promise<Buffer> {
return new Promise((resolve, reject) => {
const chunks: Buffer[] = []
const stream = (workbook as any).stream as PassThrough
stream.on('data', (chunk: Buffer) => chunks.push(chunk))
stream.on('end', () => resolve(Buffer.concat(chunks)))
stream.on('error', reject)
workbook.commit()
})
}
|
