Spaces:
Sleeping
Sleeping
File size: 5,494 Bytes
425a907 | 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 142 143 144 145 146 | import * as XLSX from 'xlsx';
const _nrm = v => String(v || '').trim();
const _nrl = v => _nrm(v).toLowerCase();
const _nrk = v => _nrl(v).replace(/[_\s]/g, '');
function xlsRows(wb, name) {
if (!name) return [];
return XLSX.utils.sheet_to_json(wb.Sheets[name], { defval: '' });
}
export function parsePastXLSX(arrayBuffer) {
try {
const wb = XLSX.read(arrayBuffer, { type: 'array' });
const result = { panels: null, vars: null, contributions: null, mapping: null, fixedVars: null, config: null };
// Sheet 1 — Panel Selection
const s1n = wb.SheetNames.find(n => ['page 1','panel'].every(k => n.toLowerCase().includes(k)));
if (s1n) {
const rows = xlsRows(wb, s1n);
if (rows.length) {
result.panels = rows.map(r => Object.fromEntries(Object.entries(r).map(([k,v]) => [_nrm(k), _nrm(v)])));
}
}
// Sheet 2 — Dictionary / selected vars (PRIMARY_METRICS = Y)
const s2n = wb.SheetNames.find(n => ['page 2','dict'].every(k => n.toLowerCase().includes(k)));
if (s2n) {
const rows = xlsRows(wb, s2n);
if (rows.length) {
const keys = Object.keys(rows[0]);
const varKey = keys.find(k => _nrl(k) === 'variable');
const pmKey = keys.find(k => _nrl(k) === 'primary_metrics');
if (varKey && pmKey) {
result.vars = new Set();
rows.forEach(r => {
const v = _nrm(r[varKey]);
if (v && _nrm(r[pmKey]).toUpperCase() === 'Y') result.vars.add(v);
});
}
}
}
// Sheet 3 — Contributions
const s3n = wb.SheetNames.find(n => ['page 3','contrib'].every(k => n.toLowerCase().includes(k)));
if (s3n) {
const rows = xlsRows(wb, s3n);
if (rows.length) {
const keys = Object.keys(rows[0]);
const actKey = keys.find(k => _nrk(k).includes('activitylevel') || _nrk(k).includes('activity1'));
const mnKey = keys.find(k => _nrl(k).includes('min'));
const mxKey = keys.find(k => _nrl(k).includes('max'));
if (actKey && mnKey && mxKey) {
result.contributions = {};
rows.forEach(r => {
const act = _nrm(r[actKey]); if (!act) return;
const mn = parseFloat(r[mnKey]) || 0;
const mx = parseFloat(r[mxKey]) || 50;
if (!result.contributions[act]) result.contributions[act] = { min: mn, max: mx };
});
}
}
}
// Sheet 4 — Panel Mapping
const s4n = wb.SheetNames.find(n => ['page 4','mapping'].every(k => n.toLowerCase().includes(k)));
if (s4n) {
const rows = xlsRows(wb, s4n);
if (rows.length) {
const keys = Object.keys(rows[0]);
const rmKey = keys.find(k => _nrk(k).includes('removevar'));
if (rmKey) {
result.mapping = rows.map(r => {
const vars = _nrm(r[rmKey]).split(',').map(v => v.trim()).filter(Boolean);
const pd = {};
keys.forEach(k => { if (k !== rmKey) pd[_nrm(k)] = _nrm(r[k]); });
return { panelData: pd, removedVars: vars };
});
}
}
}
// Sheet 5 — Fixed Variables
const s5n = wb.SheetNames.find(n => ['page 5','fixed'].every(k => n.toLowerCase().includes(k)));
if (s5n) {
const rows = xlsRows(wb, s5n);
if (rows.length) {
const keys = Object.keys(rows[0]);
const fvKey = keys.find(k => _nrk(k).includes('fixvar'));
if (fvKey) {
result.fixedVars = rows.map(r => {
const vars = _nrm(r[fvKey]).split(',').map(v => v.trim()).filter(Boolean);
const pd = {};
keys.forEach(k => { if (k !== fvKey) pd[_nrm(k)] = _nrm(r[k]); });
return { panelData: pd, vars };
});
} else {
result.fixedVars = [];
}
}
}
// Config_Params sheet
const cfgSheet = wb.SheetNames.find(n => n.toLowerCase().replace(/[\s_]/g,'').includes('configparam'));
if (cfgSheet) {
try {
const cfgRows = XLSX.utils.sheet_to_json(wb.Sheets[cfgSheet], { header: 1, defval: '' });
const jsonStr = cfgRows[1] && cfgRows[1][0] ? String(cfgRows[1][0]).trim() : '';
if (jsonStr) result.config = JSON.parse(jsonStr);
} catch (e) { /* ignore */ }
}
// Build synthesised panelCSV from Sheet 1
if (result.panels && result.panels.length) {
const pHeaders = Object.keys(result.panels[0]);
const parsedPanelRows = result.panels.map((r, i) => {
const o = { __id: i + 1 };
pHeaders.forEach(h => { o[h] = _nrm(r[h]); });
return o;
});
result.synthPanelCSV = { headers: pHeaders, rows: parsedPanelRows };
}
// Build synthesised dictCSV from Sheet 2 (only selected variables)
const s2nRaw = wb.SheetNames.find(n => ['page 2','dict'].every(k => n.toLowerCase().includes(k)));
if (s2nRaw) {
const rawRows = XLSX.utils.sheet_to_json(wb.Sheets[s2nRaw], { defval: '' });
if (rawRows.length) {
const dHeaders = Object.keys(rawRows[0]);
const parsedDictRows = rawRows.map((r, i) => {
const o = { __id: i + 1 };
dHeaders.forEach(h => { o[h] = String(r[h] ?? '').trim(); });
o._sel = (o['PRIMARY_METRICS'] || '').toUpperCase() === 'Y';
return o;
});
result.synthDictCSV = { headers: dHeaders, rows: parsedDictRows };
}
}
return result;
} catch (e) {
console.error('[PastResult] parse error', e);
return null;
}
}
|