import * as XLSX from 'xlsx'
|
import type { ConsoleDataType } from '../api/consoleData'
|
import { parseTestDuration } from './testDataDuration.ts'
|
|
export const consoleImportColumns = {
|
REALTIME: { recordNo: '记录编号', batteryNo: '电池', cycleNo: '循环序号', stepNo: '工步序号', stepName: '工步名称', sequenceId: '记录序号', relativeTimeSeconds: '记录时间(h:min:s.ms)', voltage: '电压(V)', current: '电流(mA)', capacity: '容量(mAh)', energy: '能量(mWh)', recordedAt: '绝对时间', temperature: '温度(℃)', positivePotential: '正电位(V)', negativePotential: '负电位(V)' },
|
STEP: { recordNo: '记录编号', batteryNo: '电池', cycleNo: '循环序号', stepNo: '工步序号', stepName: '工步名称', stepDuration: '工步时间(h:min:s.ms)', capacity: '容量(mAh)', energy: '能量(mWh)', startVoltage: '起始电压(V)', endVoltage: '终止电压(V)', chargeMedianVoltage: '充电中值电压(V)', dischargeMedianVoltage: '放电中值电压(V)' },
|
CYCLE: { recordNo: '记录编号', batteryNo: '电池', cycleNo: '循环序号', chargeCapacity: '充电容量(mAh)', dischargeCapacity: '放电容量(mAh)', efficiency: '充放电效率(%)', chargeEnergy: '充电能量(mWh)', dischargeEnergy: '放电能量(mWh)' },
|
} satisfies Record<ConsoleDataType, Record<string, string>>
|
|
export interface ValidatedImportRow {
|
sourceRow: number
|
raw: Record<string, unknown>
|
errors: string[]
|
data: Record<string, string | number | null>
|
}
|
|
const sheetNames = { REALTIME: ['实时', '实时模板', 'record'], STEP: ['工步', '工步模板', 'step'], CYCLE: ['循环', '循环模板', 'cycle'] }
|
export const consoleSheetLabels: Record<ConsoleDataType, string> = { REALTIME: '实时', STEP: '工步', CYCLE: '循环' }
|
export interface ValidatedImportSheet {
|
type: ConsoleDataType
|
rows: ValidatedImportRow[]
|
errors: string[]
|
}
|
|
/** 创建包含三个标准列头的空工作簿，不生成测量数据。 */
|
export function createConsoleImportWorkbook(selectedType?: ConsoleDataType): XLSX.WorkBook {
|
const workbook = XLSX.utils.book_new()
|
for (const type of selectedType ? [selectedType] : Object.keys(consoleSheetLabels) as ConsoleDataType[]) {
|
XLSX.utils.book_append_sheet(workbook, XLSX.utils.aoa_to_sheet([Object.values(consoleImportColumns[type])]), consoleSheetLabels[type])
|
}
|
// 单页汇总没有绝对时间，辅助页只供匹配，不隐式导入实时数据。
|
if (selectedType && selectedType !== 'REALTIME') XLSX.utils.book_append_sheet(workbook, XLSX.utils.aoa_to_sheet([Object.values(consoleImportColumns.REALTIME)]), 'record')
|
return workbook
|
}
|
|
/** 三表独立校验并汇总错误；一键导入不使用单页的任意名称回退规则。 */
|
export function parseConsoleImportWorkbook(workbook: XLSX.WorkBook, batteries: string[], selectedBattery = ''): ValidatedImportSheet[] {
|
const sheets = (Object.keys(consoleSheetLabels) as ConsoleDataType[]).map(type => {
|
const result: ValidatedImportSheet = { type, rows: [], errors: [] }
|
const names = workbook.SheetNames.filter(name => sheetNames[type].includes(name) && workbook.Sheets[name])
|
try {
|
if (!names.length) throw new Error(`缺少${consoleSheetLabels[type]}工作表`)
|
if (names.length > 1) throw new Error(`存在多个${consoleSheetLabels[type]}工作表，无法确定导入来源`)
|
result.rows = parseConsoleImport(workbook, type, batteries, selectedBattery)
|
if (!result.rows.length) result.errors.push('工作表没有可导入的数据')
|
} catch (error) { result.errors.push(error instanceof Error ? error.message : '工作表解析失败') }
|
return result
|
})
|
const batterySets = sheets.map(sheet => [...new Set(sheet.rows.map(row => row.data.batteryNo))].sort().join('\u0000'))
|
sheets.forEach((sheet, index) => {
|
if (sheet.rows.length && sheets[0]!.rows.length && batterySets[index] !== batterySets[0]) sheet.errors.push('列「电池号」：三个 Sheet 的电池号集合不一致')
|
})
|
return sheets
|
}
|
const partyHeaders: Record<string, string> = {
|
recordedAt: '绝对时间', stepName: '工步名称', current: '电流(mA)', capacity: '容量(mAh)', energy: '能量(mWh)',
|
stepDuration: '工步时间(h:min:s.ms)', chargeCapacity: '充电容量(mAh)', dischargeCapacity: '放电容量(mAh)',
|
chargeEnergy: '充电能量(mWh)', dischargeEnergy: '放电能量(mWh)',
|
}
|
|
// 无时区文本与 Excel 日期都按香港设备时区解释，禁止依赖浏览器所在时区。
|
function deviceTime(value: unknown, date1904 = false): number {
|
if (typeof value === 'number' && value > 0 && value < 2958466) {
|
const text = XLSX.utils.format_cell({ t: 'n', v: value + (date1904 ? 1462 : 0), z: 'yyyy-mm-dd hh:mm:ss.000' })
|
return deviceTime(text)
|
}
|
const text = String(value ?? '').trim()
|
if (/^\d{13}$/.test(text)) return Number(text)
|
const match = /^(\d{4})[-/](\d{1,2})[-/](\d{1,2})[ T](\d{1,2}):(\d{2}):(\d{2})(?:\.(\d{1,3}))?$/.exec(text)
|
if (!match) return NaN
|
const [year, month, day, hour, minute, second] = match.slice(1, 7).map(Number) as [number, number, number, number, number, number]
|
const local = new Date(Date.UTC(year, month - 1, day, hour, minute, second, Number((match[7] || '').padEnd(3, '0'))))
|
if (local.getUTCFullYear() !== year || local.getUTCMonth() !== month - 1 || local.getUTCDate() !== day || hour > 23 || minute > 59 || second > 59) return NaN
|
return local.getTime() - 8 * 3600000
|
}
|
|
/** 将当前子页工作表转换为待提交记录；错误保留真实 Excel 行号和列名。 */
|
export function parseConsoleImport(workbook: XLSX.WorkBook, type: ConsoleDataType, batteries: string[], selectedBattery = ''): ValidatedImportRow[] {
|
const name = workbook.SheetNames.find(name => sheetNames[type].includes(name))
|
|| (workbook.SheetNames.length === 1 ? workbook.SheetNames[0] : undefined)
|
if (!name || !workbook.Sheets[name]) throw new Error(`缺少${sheetNames[type][0]}工作表`)
|
const sheet = workbook.Sheets[name]!
|
const table = XLSX.utils.sheet_to_json<unknown[]>(sheet, { header: 1, raw: true, defval: '', blankrows: true })
|
const headers = (table[0] || []).map(value => String(value).trim())
|
const party = name === sheetNames[type][2]
|
const legacyRealtime = type === 'REALTIME' && !headers.includes('绝对时间') && (headers.includes('设备时间') || headers.includes('记录时间(h:min:s.ms)'))
|
const legacySourceNumber = headers.includes('电池号') && (headers.includes('设备时间') || legacyRealtime)
|
const date1904 = !!workbook.Workbook?.WBProps?.date1904
|
const endTimes = new Map<string, unknown>()
|
const invalidEndTimes = new Set<string>()
|
const batteryFor = (row: Record<string, unknown>) => String(row['电池'] || row['电池号'] || selectedBattery || (batteries.length === 1 ? batteries[0] : '')).trim()
|
const timeKey = (row: Record<string, unknown>) => JSON.stringify([batteryFor(row), String(row['循环序号']), ...(type === 'STEP' ? [String(row['工步序号'])] : [])])
|
const recordNames = workbook.SheetNames.filter(name => sheetNames.REALTIME.includes(name) && workbook.Sheets[name])
|
if (type !== 'REALTIME' && recordNames.length > 1) throw new Error('存在多个实时辅助工作表，无法确定时间来源')
|
if (type !== 'REALTIME' && recordNames.length === 1) {
|
// 汇总表没有绝对时间，只借用匹配实时记录的末次时间，不导入其他 Sheet 的行。
|
const records = XLSX.utils.sheet_to_json<Record<string, unknown>>(workbook.Sheets[recordNames[0]!]!, { raw: true })
|
for (const record of records) {
|
const key = timeKey(record)
|
const time = deviceTime(record['绝对时间'], date1904)
|
if (!Number.isFinite(time)) invalidEndTimes.add(key)
|
if (Number.isFinite(time) && (!endTimes.has(key) || time > deviceTime(endTimes.get(key), date1904))) endTimes.set(key, record['绝对时间'])
|
}
|
// 组内有未知时间就无法可靠确定末次记录，不能静默退到更早的有效行。
|
for (const key of invalidEndTimes) endTimes.delete(key)
|
}
|
// 旧标准模板按原列头单位读取，避免对 A/Ah/Wh 再次缩放。
|
const headerFor = (field: string, standardHeader: string) => {
|
if (field === 'recordedAt' && legacyRealtime) return headers.includes('设备时间') ? '设备时间' : '记录时间(h:min:s.ms)'
|
if (party) return partyHeaders[field] || standardHeader
|
if (field === 'batteryNo' && headers.includes('电池号')) return '电池号'
|
if (field === 'stepName' && headers.includes('工步')) return '工步'
|
if (field === 'stepDuration' && headers.includes('工步时间')) return '工步时间'
|
if (field === 'recordedAt' && headers.includes('设备时间')) return '设备时间'
|
const legacyHeader = standardHeader.replace(/\(m(A|Ah|Wh)\)/, '($1)')
|
return headers.includes(standardHeader) ? standardHeader : headers.includes(legacyHeader) ? legacyHeader : standardHeader
|
}
|
const columns: Record<string, string> = { ...consoleImportColumns[type], ...(type !== 'REALTIME' ? { recordedAt: '绝对时间' } : {}) }
|
const missing = Object.entries(columns).filter(([field, header]) => {
|
// 中值只读取对应列；缺列可为空，不借用旧峰值或谷值。
|
if (['recordNo', 'batteryNo'].includes(field)) return false
|
if (field === 'relativeTimeSeconds' && legacyRealtime) return false
|
if (party && ['temperature', 'positivePotential', 'negativePotential', 'chargeMedianVoltage', 'dischargeMedianVoltage'].includes(field)) return false
|
if (type !== 'REALTIME' && field === 'recordedAt') return false
|
return !headers.includes(headerFor(field, header))
|
}).map(([field, header]) => headerFor(field, header))
|
if (missing.length) throw new Error(`第1行，列「${missing.join('、')}」：模板字段缺失`)
|
return table.slice(1).flatMap((cells, index) => {
|
if (cells.every(value => value === '' || value === null)) return []
|
const sourceRow = index + 2 + XLSX.utils.decode_range(sheet['!ref'] || 'A1').s.r
|
const raw = Object.fromEntries(headers.map((header, index) => [header, cells[index] ?? '']))
|
const data: ValidatedImportRow['data'] = { sourceRow }
|
const errors: string[] = []
|
for (const [field, standardHeader] of Object.entries(columns)) {
|
const header = headerFor(field, standardHeader)
|
let value: unknown = raw[header]
|
if (field === 'batteryNo') value = batteryFor(raw)
|
// 系统记录编号只供定位修订，导入不能拿它代替设备/文件的来源编号。
|
if (field === 'recordNo') value = type === 'REALTIME' ? raw['记录序号'] : type === 'STEP' ? `step:${raw['循环序号']}:${raw['工步序号']}` : `cycle:${raw['循环序号']}`
|
// 旧标准模板的编号曾表示来源，保留其重复导入身份；新模板编号不参与去重。
|
if (field === 'recordNo' && legacySourceNumber && raw['记录编号']) value = raw['记录编号']
|
if (type !== 'REALTIME' && field === 'recordedAt' && !value) value = endTimes.get(timeKey(raw))
|
if (field === 'stepDuration' || field === 'relativeTimeSeconds') {
|
const cell = sheet[XLSX.utils.encode_cell({ r: sourceRow - 1, c: headers.indexOf(header) })]
|
if (typeof value === 'number' && cell?.z && /[hs]/i.test(cell.z)) value = XLSX.utils.format_cell(cell)
|
value = field === 'relativeTimeSeconds' && legacyRealtime ? null : parseTestDuration(value)
|
}
|
const text = String(value ?? '').trim()
|
const fail = (reason: string) => errors.push(`第${sourceRow}行，列「${header}」：${reason}`)
|
if (['recordNo', 'batteryNo', 'stepName'].includes(field)) {
|
data[field] = text
|
if (!text) fail('不能为空')
|
else if (field === 'batteryNo' && !batteries.includes(text)) fail('不属于当前测试')
|
} else if (field === 'recordedAt') {
|
const time = deviceTime(value, date1904)
|
data.recordedAt = Number.isFinite(time) ? time : null
|
data.deviceTimeRaw = text
|
data.adapterTimezoneId = 'Asia/Hong_Kong'
|
if (!Number.isFinite(time)) fail('绝对设备时间无效')
|
} else if (['cycleNo', 'stepNo', 'sequenceId'].includes(field)) {
|
data[field] = Number(text)
|
if (!text || !Number.isSafeInteger(data[field]) || Number(data[field]) < 1) fail('必须是正整数')
|
} else {
|
// 测量值仅来自文件；缺列、空格或占位符保持空值，不生成温度或电位等测试值。
|
const number = !text || text === '-' ? null : Number(text)
|
data[field] = number
|
if (number !== null && !Number.isFinite(number)) fail('必须是数字或空值')
|
else if (number !== null && /\(mA\)|\(mAh\)|\(mWh\)/.test(header)) data[field] = Number((number / 1000).toFixed(12))
|
}
|
}
|
return [{ sourceRow, raw, data, errors }]
|
})
|
}
|
