/** * 文件解析工具 * 支持Excel、CSV、JSON格式的股票数据文件解析 */ import { convertMinuteToDaily } from './technicalIndicators'; export interface StockData { symbol: string; // 股票代码 name: string; // 股票名称 date: string; // 日期 open: number; // 开盘价 high: number; // 最高价 low: number; // 最低价 close: number; // 收盘价 volume: number; // 成交量 amount?: number; // 成交额 } export interface ParseResult { success: boolean; data?: StockData[]; symbol?: string; symbolName?: string; error?: string; totalRecords?: number; dateRange?: { start: string; end: string; }; dataType?: 'minute' | 'daily'; // 数据类型:分钟线或日线 } function inferSymbolFromFilename(fileName: string): string { try { const base = String(fileName || '').split(/[\\/]/).pop() || '' const name0 = base.toLowerCase().replace(/\.day$/i, '') const digits = (name0.match(/\d{6}/g) || []).pop() return String(digits || '').trim() } catch { return '' } } export async function parseTDXDay(file: File): Promise { try { const buf = await file.arrayBuffer() const dv = new DataView(buf) const recordSize = 32 const n = Math.floor(dv.byteLength / recordSize) if (n <= 0) { return { success: false, error: '文件中没有数据' } } const candidateScales = [10, 100, 1000, 10000] const scores: Record = {} for (const s of candidateScales) scores[s] = 0 const sampleSize = Math.min(80, n) for (let i = 0; i < sampleSize; i++) { const off = i * recordSize const closeRaw = dv.getUint32(off + 16, true) const amount = dv.getFloat32(off + 20, true) const volRaw = dv.getUint32(off + 24, true) if (!closeRaw || !amount || !volRaw) continue const priceImp = amount / volRaw for (const scale of candidateScales) { const close = closeRaw / scale if (close <= 0) continue const diffRatio = Math.abs(priceImp - close) / close if (diffRatio <= 0.5) scores[scale] += 1 } } let scale = 100 const allZero = candidateScales.every((s) => (scores[s] || 0) === 0) if (!allZero) { const prefOrder = [100, 10, 1000, 10000] scale = candidateScales .slice() .sort((a, b) => { const sa = scores[a] || 0 const sb = scores[b] || 0 if (sa !== sb) return sb - sa return prefOrder.indexOf(a) - prefOrder.indexOf(b) })[0] } const symbol = inferSymbolFromFilename(file.name) const data: StockData[] = [] for (let i = 0; i < n; i++) { const off = i * recordSize const dateInt = dv.getUint32(off + 0, true) const openRaw = dv.getUint32(off + 4, true) const highRaw = dv.getUint32(off + 8, true) const lowRaw = dv.getUint32(off + 12, true) const closeRaw = dv.getUint32(off + 16, true) const amount = dv.getFloat32(off + 20, true) const volRaw = dv.getUint32(off + 24, true) if (!dateInt || !closeRaw) continue const ds = String(dateInt) if (ds.length !== 8) continue const date = `${ds.slice(0, 4)}-${ds.slice(4, 6)}-${ds.slice(6, 8)}` const open = Math.round((openRaw / scale) * 10000) / 10000 const high = Math.round((highRaw / scale) * 10000) / 10000 const low = Math.round((lowRaw / scale) * 10000) / 10000 const close = Math.round((closeRaw / scale) * 10000) / 10000 if (!(close > 0)) continue data.push({ symbol, name: '', date, open, high, low, close, volume: Number(volRaw || 0), amount: Number(amount || 0), }) } if (data.length === 0) { return { success: false, error: '未找到有效的股票数据,请检查数据格式' } } data.sort((a, b) => new Date(a.date).getTime() - new Date(b.date).getTime()) return { success: true, data, symbol, symbolName: '', totalRecords: data.length, dateRange: { start: data[0].date, end: data[data.length - 1].date, }, dataType: 'daily', } } catch (error) { return { success: false, error: `DAY解析失败: ${error}` } } } /** * 检测数据是否为分钟线数据 * @param data 股票数据数组 * @returns 如果是分钟线数据返回true,否则返回false */ function detectMinuteData(data: StockData[]): boolean { if (!data || data.length < 2) return false; // 检查日期格式是否包含时间部分 const hasTimeInDate = data.some(item => { const dateStr = item.date; return dateStr.includes(' ') || dateStr.includes(':') || dateStr.includes('T'); }); if (hasTimeInDate) { return true; } // 检查同一天是否有多个数据点 const dateCount: Record = {}; data.forEach(item => { const dateOnly = item.date.split(' ')[0] || item.date; dateCount[dateOnly] = (dateCount[dateOnly] || 0) + 1; }); // 如果同一天的数据点超过1个,则认为是分钟线数据 return Object.values(dateCount).some(count => count > 1); } function convertMinuteStockToDaily(data: StockData[]): StockData[] { const symbol = data?.[0]?.symbol || '' const name = data?.[0]?.name || '' return convertMinuteToDaily(data).map(d => ({ symbol, name, date: d.date, open: d.open, high: d.high, low: d.low, close: d.close, volume: d.volume || 0, })) } /** * 解析CSV文件 */ export async function parseCSV(file: File): Promise { try { const text = await file.text(); // 尝试不同的分隔符 const separators = [',', '\t', '|', ';']; let bestSeparator = ','; let maxColumns = 0; for (const sep of separators) { const testLine = text.split('\n')[0]; const columns = testLine.split(sep).length; if (columns > maxColumns) { maxColumns = columns; bestSeparator = sep; } } const lines = text.split('\n').filter(line => line.trim()); if (lines.length < 2) { return { success: false, error: '文件内容不足,至少需要标题行和一行数据' }; } // 自动定位标题行(有些导出会在前几行加说明/空行) let headerLineIndex = 0 for (let i = 0; i < Math.min(10, lines.length); i++) { const hs = lines[i].split(bestSeparator).map(h => String(h || '').trim()) const joined = hs.join(' ').toLowerCase() if (joined.includes('日期') && (joined.includes('收盘') || joined.includes('close'))) { headerLineIndex = i break } if (joined.includes('date') && (joined.includes('close') || joined.includes('收盘'))) { headerLineIndex = i break } } // 解析标题行 const headers = lines[headerLineIndex].split(bestSeparator).map(h => h.trim()); // 查找关键列的索引 const columnMap = findColumnIndices(headers); // 如果没有找到标准列名,尝试按位置推断(适用于您的数据格式) if (columnMap.symbol === undefined && headers.length >= 8) { // 根据您的数据格式:代码、名称、日期、开盘价、最高价、最低价、收盘价、成交量、成交额 columnMap.symbol = 0; // 代码 columnMap.name = 1; // 名称 columnMap.date = 2; // 日期 columnMap.open = 3; // 开盘价 columnMap.high = 4; // 最高价 columnMap.low = 5; // 最低价 columnMap.close = 6; // 收盘价 columnMap.volume = 7; // 成交量 if (headers.length > 8) { columnMap.amount = 8; // 成交额 } } if (columnMap.symbol === undefined) { return { success: false, error: '未找到股票代码列,请确保文件包含股票代码信息' }; } if (columnMap.close !== undefined) { if (columnMap.open === undefined) columnMap.open = columnMap.close if (columnMap.high === undefined) columnMap.high = columnMap.close if (columnMap.low === undefined) columnMap.low = columnMap.close } const data: StockData[] = []; let symbol = ''; let symbolName = ''; // 解析数据行 for (let i = headerLineIndex + 1; i < lines.length; i++) { const values = lines[i].split(bestSeparator).map(v => v.trim()); if (values.length < Math.max(...Object.values(columnMap)) + 1) continue; const record: StockData = { symbol: values[columnMap.symbol] || '', name: values[columnMap.name || 0] || '', date: formatDate(values[columnMap.date || 0] || ''), open: parseFloat(values[columnMap.open || 0] || '0') || 0, high: parseFloat(values[columnMap.high || 0] || '0') || 0, low: parseFloat(values[columnMap.low || 0] || '0') || 0, close: parseFloat(values[columnMap.close || 0] || '0') || 0, volume: parseFloat(values[columnMap.volume || 0] || '0') || 0, amount: columnMap.amount ? parseFloat(values[columnMap.amount] || '0') : undefined }; if (record.symbol && record.date && record.open > 0) { data.push(record); // 获取股票代码和名称 if (!symbol && record.symbol) { symbol = record.symbol; symbolName = record.name; } } } if (data.length === 0) { return { success: false, error: '未找到有效的股票数据,请检查数据格式' }; } // 按日期排序 data.sort((a, b) => new Date(a.date).getTime() - new Date(b.date).getTime()); // 检测是否为分钟线数据,如果是则转换为日线数据 let finalData = data; let dataType: 'minute' | 'daily' = 'daily'; if (detectMinuteData(data)) { finalData = convertMinuteStockToDaily(data); dataType = 'minute'; } return { success: true, data: finalData, symbol, symbolName, totalRecords: finalData.length, dateRange: { start: finalData[0].date, end: finalData[finalData.length - 1].date }, dataType }; } catch (error) { return { success: false, error: `CSV解析失败: ${error}` }; } } /** * 解析Excel文件 */ export async function parseExcel(file: File): Promise { try { // 动态导入xlsx库 const XLSX = await import('xlsx'); const arrayBuffer = await file.arrayBuffer(); // 使用raw: true禁用自动日期转换 const workbook = XLSX.read(arrayBuffer, { type: 'array', raw: true }); // 获取第一个工作表 const sheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[sheetName]; // 转换为JSON格式,使用raw: true保持原始格式 const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1, raw: true }); if (jsonData.length < 2) { return { success: false, error: 'Excel文件内容不足,至少需要标题行和一行数据' }; } // 自动定位标题行(有些 Excel 会在前几行有说明/空行/合并单元格) let headerRowIndex = 0 for (let i = 0; i < Math.min(20, jsonData.length); i++) { const row = (jsonData[i] as any[]) || [] const cells = row.map((c) => String(c ?? '').trim()).filter(Boolean) if (cells.length < 4) continue const joined = cells.join(' ').toLowerCase() if (joined.includes('日期') && (joined.includes('收盘') || joined.includes('close'))) { headerRowIndex = i break } if (joined.includes('date') && (joined.includes('close') || joined.includes('收盘'))) { headerRowIndex = i break } } const headers = ((jsonData[headerRowIndex] as any[]) || []).map(h => String(h || '').trim()); const rows = jsonData.slice(headerRowIndex + 1) as any[][]; // 调试日志:打印前几行数据 console.log('Excel解析 - 列名:', headers); if (rows.length > 0) { console.log('Excel解析 - 第一行数据:', rows[0]); }; const result = parseDataRows(headers, rows); // 如果解析成功,检测是否为分钟线数据,如果是则转换为日线数据 if (result.success && result.data) { let finalData = result.data; let dataType: 'minute' | 'daily' = 'daily'; if (detectMinuteData(result.data)) { finalData = convertMinuteStockToDaily(result.data); dataType = 'minute'; } return { ...result, data: finalData, totalRecords: finalData.length, dateRange: { start: finalData[0].date, end: finalData[finalData.length - 1].date }, dataType }; } return result; } catch (error) { return { success: false, error: `Excel解析失败: ${error}。请确保文件格式正确。` }; } } /** * 解析JSON文件 */ export async function parseJSON(file: File): Promise { try { const text = await file.text(); const jsonData = JSON.parse(text); // 支持多种JSON格式 let records: any[] = []; if (Array.isArray(jsonData)) { records = jsonData; } else if (jsonData.data && Array.isArray(jsonData.data)) { records = jsonData.data; } else if (jsonData.records && Array.isArray(jsonData.records)) { records = jsonData.records; } else { return { success: false, error: 'JSON格式不正确,需要包含数组格式的股票数据' }; } if (records.length === 0) { return { success: false, error: 'JSON文件中没有数据' }; } const data: StockData[] = []; let symbol = ''; let symbolName = ''; for (const record of records) { const stockData: StockData = { symbol: record.symbol || record.code || record.股票代码 || record.代码 || '', name: record.name || record.股票名称 || record.名称 || '', date: formatDate(record.date || record.日期 || record.时间 || ''), open: parseFloat(record.open || record.开盘价 || record.开盘 || '0') || 0, high: parseFloat(record.high || record.最高价 || record.最高 || '0') || 0, low: parseFloat(record.low || record.最低价 || record.最低 || '0') || 0, close: parseFloat(record.close || record.收盘价 || record.收盘 || '0') || 0, volume: parseFloat(record.volume || record.成交量 || '0') || 0, amount: parseFloat(record.amount || record.成交额 || '0') || undefined }; if (stockData.symbol && stockData.date) { data.push(stockData); if (!symbol && stockData.symbol) { symbol = stockData.symbol; symbolName = stockData.name; } } } if (data.length === 0) { return { success: false, error: '未找到有效的股票数据' }; } // 按日期排序 data.sort((a, b) => new Date(a.date).getTime() - new Date(b.date).getTime()); // 检测是否为分钟线数据,如果是则转换为日线数据 let finalData = data; let dataType: 'minute' | 'daily' = 'daily'; if (detectMinuteData(data)) { finalData = convertMinuteStockToDaily(data); dataType = 'minute'; } return { success: true, data: finalData, symbol, symbolName, totalRecords: finalData.length, dateRange: { start: finalData[0].date, end: finalData[finalData.length - 1].date }, dataType }; } catch (error) { return { success: false, error: `JSON解析失败: ${error}` }; } } /** * 通用数据行解析函数 */ function parseDataRows(headers: string[], rows: any[][]): ParseResult { try { // 查找关键列的索引 const columnMap = findColumnIndices(headers) console.log('parseDataRows - columnMap:', columnMap); // 仅在完全没有找到任何列时,才按位置推断 if (Object.keys(columnMap).length === 0 && headers.length >= 8) { columnMap.symbol = 0; // 代码 columnMap.name = 1; // 名称 columnMap.date = 2; // 日期 // 跳过第3列(时间列) columnMap.open = 4; // 开盘价 columnMap.high = 5; // 最高价 columnMap.low = 6; // 最低价 columnMap.close = 7; // 收盘价 columnMap.volume = 8; // 成交量 if (headers.length > 9) { columnMap.amount = 9; // 成交额 } } if (columnMap.symbol === undefined) { return { success: false, error: '未找到股票代码列,请确保文件包含股票代码信息' }; } const data: StockData[] = []; let symbol = ''; let symbolName = ''; // 解析数据行 for (const values of rows) { if (values.length < Math.max(...Object.values(columnMap).filter(v => v !== undefined)) + 1) continue; const record: StockData = { symbol: String(values[columnMap.symbol] || '').trim(), name: String(values[columnMap.name || 0] || '').trim(), date: formatDate(values[columnMap.date || 0]), open: parseNumericValue(values[columnMap.open || 0]), high: parseNumericValue(values[columnMap.high || 0]), low: parseNumericValue(values[columnMap.low || 0]), close: parseNumericValue(values[columnMap.close || 0]), volume: parseNumericValue(values[columnMap.volume || 0]), amount: columnMap.amount ? parseNumericValue(values[columnMap.amount]) : undefined }; if (record.symbol && record.date && record.open > 0) { data.push(record); // 获取股票代码和名称 if (!symbol && record.symbol) { symbol = record.symbol; symbolName = record.name; } } } if (data.length === 0) { return { success: false, error: '未找到有效的股票数据,请检查数据格式' }; } // 按日期排序 data.sort((a, b) => new Date(a.date).getTime() - new Date(b.date).getTime()); return { success: true, data, symbol, symbolName, totalRecords: data.length, dateRange: { start: data[0].date, end: data[data.length - 1].date } }; } catch (error) { return { success: false, error: `数据解析失败: ${error}` }; } } /** * 根据文件类型选择解析器 */ export async function parseStockDataFile(file: File): Promise { const fileName = file.name.toLowerCase(); if (fileName.endsWith('.csv')) { return parseCSV(file); } else if (fileName.endsWith('.xlsx') || fileName.endsWith('.xls')) { return parseExcel(file); } else if (fileName.endsWith('.json')) { return parseJSON(file); } else if (fileName.endsWith('.day')) { return parseTDXDay(file); } else { return { success: false, error: '不支持的文件格式,请上传股票数据文件 (CSV、Excel、JSON、DAY)' }; } } /** * 查找列索引 */ function findColumnIndices(headers: string[]): Record { const columnMap: Record = {}; headers.forEach((header, index) => { const h = header.toLowerCase().trim(); // 股票代码 if (h.includes('市场代码') || h.includes('交易所') || h.includes('market')) { // ignore } else if (h.includes('股票代码') || h.includes('证券代码') || h === '代码' || h === 'symbol' || h === 'code') { // 强匹配:优先锁定真正的证券代码列 if (columnMap.symbol === undefined || h.includes('股票代码') || h.includes('证券代码') || h === '代码') { columnMap.symbol = index; } } else if ((h.includes('代码') || h.includes('symbol') || h.includes('code')) && columnMap.symbol === undefined) { // 弱匹配:仅在还没找到 symbol 时才使用 columnMap.symbol = index; } // 股票名称 else if (h.includes('名称') || h.includes('name') || h.includes('股票')) { columnMap.name = index; } // 日期 - 强匹配优先,避免把“上市日期”等误当成交易日期 else if (h === '日期' || h === 'date') { columnMap.date = index; } else if (h.includes('上市日期') || h.includes('listing') || h.includes('list_date')) { // ignore } else if ((h.includes('日期') || h.includes('date')) && columnMap.date === undefined) { // 弱匹配:仅在还没找到 date 时才使用 columnMap.date = index; } // 时间 - 如果没有找到日期列,才使用时间列 else if (h.includes('时间') && columnMap.date === undefined) { columnMap.date = index; } // 开盘价 - 精确匹配,避免误匹配 else if (h === '开盘价' || h === '开盘' || h === 'open' || h.includes('open') || h.includes('开盘')) { columnMap.open = index; } // 最高价 else if (h.includes('最高') || h.includes('high')) { columnMap.high = index; } // 最低价 else if (h.includes('最低') || h.includes('low')) { columnMap.low = index; } // 收盘价 else if (h === '收盘价' || h === '收盘' || h.includes('收盘') || h.includes('close')) { columnMap.close = index; } // 成交量 else if (h.includes('成交量') || h.includes('volume') || h.includes('vol')) { columnMap.volume = index; } // 成交额 else if (h.includes('成交额') || h.includes('amount') || h.includes('turnover')) { columnMap.amount = index; } }); return columnMap; } /** * 安全地将值转换为数字 * @param value 可以是字符串、数字或null/undefined * @returns 转换后的数字,如果无效则返回0 */ function parseNumericValue(value: any): number { if (value === null || value === undefined) { return 0; } if (typeof value === 'number') { return isNaN(value) ? 0 : value; } if (typeof value === 'string') { const cleaned = value .trim() .replace(/,/g, '') .replace(/%/g, '') const parsed = parseFloat(cleaned); return isNaN(parsed) ? 0 : parsed; } return 0; } /** * 格式化日期 */ function formatDate(dateInput: any): string { if (!dateInput) return ''; // 如果是Excel的序列号日期 if (typeof dateInput === 'number' && dateInput > 1 && dateInput < 100000) { // Excel's epoch starts on 1900-01-01, but it's bugged and thinks 1900 is a leap year. // So we subtract 25569 to get days since 1970-01-01 const excelEpoch = new Date(1899, 11, 30); const date = new Date(excelEpoch.getTime() + dateInput * 86400000); return date.toISOString().split('T')[0]; } const dateStr = String(dateInput); const date = new Date(dateStr); if (!isNaN(date.getTime())) { return date.toISOString().split('T')[0]; } // 尝试其他格式 const formats = [ { regex: /(\d{4})-(\d{1,2})-(\d{1,2})/, y: 1, m: 2, d: 3 }, { regex: /(\d{4})\/(\d{1,2})\/(\d{1,2})/, y: 1, m: 2, d: 3 }, { regex: /(\d{4})(\d{2})(\d{2})/, y: 1, m: 2, d: 3 }, ]; for (const format of formats) { const match = dateStr.match(format.regex); if (match) { const year = parseInt(match[format.y], 10); const month = parseInt(match[format.m], 10); const day = parseInt(match[format.d], 10); if (year > 1900 && month > 0 && month <= 12 && day > 0 && day <= 31) { return `${year}-${String(month).padStart(2, '0')}-${String(day).padStart(2, '0')}`; } } } return dateStr; // 返回原始字符串 }