Spaces:
Sleeping
Sleeping
Download frontend/src/utils/fileParser.ts from shaun07/grid-backtest: direct link, hf CLI and curl.
- Browser
- Download file 24.5 kB
-
https://huggingface.co/spaces/shaun07/grid-backtest/resolve/main/frontend/src/utils/fileParser.ts
- Command line
-
hf download hf://spaces/shaun07/grid-backtest/frontend/src/utils/fileParser.ts
-
curl -L -o fileParser.ts https://huggingface.co/spaces/shaun07/grid-backtest/resolve/main/frontend/src/utils/fileParser.ts
24.5 kB
| /** | |
| * 文件解析工具 | |
| * 支持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<ParseResult> { | |
| 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<number, number> = {} | |
| 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<string, number> = {}; | |
| 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<ParseResult> { | |
| 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<ParseResult> { | |
| 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<ParseResult> { | |
| 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<ParseResult> { | |
| 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<string, number> { | |
| const columnMap: Record<string, number> = {}; | |
| 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; // 返回原始字符串 | |
| } | |