import * as XLSX from 'xlsx'
|
import type { ConsoleDataType } from '../api/consoleData'
|
|
export const consoleImportColumns = {
|
REALTIME: { recordNo: '记录编号', batteryNo: '电池号', recordedAt: '设备时间', cycleNo: '循环序号', stepNo: '工步序号', stepName: '工步名称', sequenceId: '记录序号', voltage: '电压(V)', current: '电流(A)', capacity: '容量(Ah)', energy: '能量(Wh)', temperature: '温度(℃)', positivePotential: '正电位(V)', negativePotential: '负电位(V)' },
|
STEP: { recordNo: '记录编号', batteryNo: '电池号', recordedAt: '设备时间', cycleNo: '循环序号', stepNo: '工步序号', stepName: '工步', stepDuration: '工步时间', capacity: '容量(Ah)', energy: '能量(Wh)', startVoltage: '起始电压(V)', endVoltage: '终止电压(V)', chargePeakVoltage: '充电峰值电压(V)', dischargeValleyVoltage: '放电谷值电压(V)' },
|
CYCLE: { recordNo: '记录编号', batteryNo: '电池号', recordedAt: '设备时间', cycleNo: '循环序号', chargeCapacity: '充电容量(Ah)', dischargeCapacity: '放电容量(Ah)', efficiency: '充放电效率(%)', chargeEnergy: '充电能量(Wh)', dischargeEnergy: '放电能量(Wh)' },
|
} 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'] }
|
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 date1904 = !!workbook.Workbook?.WBProps?.date1904
|
const endTimes = new Map<string, unknown>()
|
if (party && type !== 'REALTIME' && workbook.Sheets.record) {
|
// 汇总表没有绝对时间，只借用匹配实时记录的末次时间，不导入其他 Sheet 的行。
|
const records = XLSX.utils.sheet_to_json<Record<string, unknown>>(workbook.Sheets.record, { raw: true })
|
for (const record of records) {
|
const key = type === 'STEP' ? `${record['循环序号']}:${record['工步序号']}` : String(record['循环序号'])
|
const time = deviceTime(record['绝对时间'], date1904)
|
if (Number.isFinite(time) && (!endTimes.has(key) || time > deviceTime(endTimes.get(key), date1904))) endTimes.set(key, record['绝对时间'])
|
}
|
}
|
const columns: Record<string, string> = consoleImportColumns[type]
|
const missing = Object.entries(columns).filter(([field, header]) => {
|
if (party && ['recordNo', 'batteryNo', 'temperature', 'positivePotential', 'negativePotential', 'chargePeakVoltage', 'dischargeValleyVoltage'].includes(field)) return false
|
if (party && type !== 'REALTIME' && field === 'recordedAt') return false
|
return !headers.includes(party ? partyHeaders[field] || header : header)
|
}).map(([field, header]) => party ? partyHeaders[field] || header : 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 = party ? partyHeaders[field] || standardHeader : standardHeader
|
let value: unknown = raw[header]
|
if (party && field === 'batteryNo') value = selectedBattery || (batteries.length === 1 ? batteries[0] : '')
|
if (party && field === 'recordNo') value = raw['记录编号'] || (type === 'REALTIME' ? raw['记录序号'] : type === 'STEP' ? `step:${raw['循环序号']}:${raw['工步序号']}` : `cycle:${raw['循环序号']}`)
|
if (party && type !== 'REALTIME' && field === 'recordedAt' && !value) value = endTimes.get(type === 'STEP' ? `${raw['循环序号']}:${raw['工步序号']}` : String(raw['循环序号']))
|
if (field === 'stepDuration' && typeof value === 'string' && value.includes(':')) {
|
const duration = /^(\d+):([0-5]\d):([0-5]\d)(?:\.(\d{1,3}))?$/.exec(value.trim())
|
value = duration ? Number(duration[1]) * 3600 + Number(duration[2]) * 60 + Number(duration[3]) + Number((duration[4] || '').padEnd(3, '0')) / 1000 : NaN
|
}
|
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 }]
|
})
|
}
|
