Testcraft Studio frontend 18: src/excel.js

import { parseCsv } from './store.jsx';

const decoder = new TextDecoder();
export async function readZip(buffer) {
  const bytes = new Uint8Array(buffer), view = new DataView(buffer), entries = new Map();
  let end = bytes.length - 22;
  while (end >= Math.max(0, bytes.length - 65557) && view.getUint32(end, true) !== 0x06054b50) end--;
  if (end < 0 || end < bytes.length - 65557) throw new Error('This is not a valid Excel .xlsx workbook.');
  const count = view.getUint16(end + 10, true);
  if (count > 500) throw new Error('Use a smaller Excel workbook.');
  let offset = view.getUint32(end + 16, true), totalSize = 0;
  for (let index = 0; index < count; index++) {
    if (offset + 46 > bytes.length || view.getUint32(offset, true) !== 0x02014b50) throw new Error('The Excel workbook is damaged.');
    const flags = view.getUint16(offset + 8, true), method = view.getUint16(offset + 10, true), compressedSize = view.getUint32(offset + 20, true), size = view.getUint32(offset + 24, true), nameSize = view.getUint16(offset + 28, true), extraSize = view.getUint16(offset + 30, true), commentSize = view.getUint16(offset + 32, true), local = view.getUint32(offset + 42, true);
    totalSize += size;
    if (flags & 1) throw new Error('Use an Excel workbook without password protection.');
    if (totalSize > 12 * 1024 * 1024) throw new Error('Use an Excel workbook smaller than 12 MB when expanded.');
    const name = decoder.decode(bytes.slice(offset + 46, offset + 46 + nameSize));
    if (local + 30 > bytes.length || view.getUint32(local, true) !== 0x04034b50) throw new Error('The Excel workbook is damaged.');
    const start = local + 30 + view.getUint16(local + 26, true) + view.getUint16(local + 28, true);
    if (start + compressedSize > bytes.length) throw new Error('The Excel workbook is incomplete.');
    entries.set(name, async () => {
      const raw = bytes.slice(start, start + compressedSize);
      if (method === 0) return decoder.decode(raw);
      if (method !== 8) throw new Error('This Excel compression format is not supported. Save the file as .xlsx again.');
      const reader = new Blob([raw]).stream().pipeThrough(new DecompressionStream('deflate-raw')).getReader();
      const chunks = []; let length = 0;
      for (;;) { const { done, value } = await reader.read(); if (done) break; length += value.length; if (length > size || length > 12 * 1024 * 1024) { await reader.cancel(); throw new Error('The Excel workbook exceeds the size limit.'); } chunks.push(value); }
      const result = new Uint8Array(length); let position = 0; chunks.forEach(chunk => { result.set(chunk, position); position += chunk.length; });
      return decoder.decode(result);
    });
    offset += 46 + nameSize + extraSize + commentSize;
  }
  return entries;
}
function xml(text) {
  if (!text || /<!DOCTYPE|<!ENTITY/i.test(text)) throw new Error('The Excel workbook contains unsupported XML.');
  const document = new DOMParser().parseFromString(text, 'application/xml');
  if (document.getElementsByTagName('parsererror').length) throw new Error('The Excel workbook contains invalid XML.');
  return document;
}
const elements = (node, tag) => [...node.getElementsByTagNameNS('*', tag)];
const text = (node, tag) => elements(node, tag).map(item => item.textContent || '').join('');
function pathWithin(base, relative) { const parts = relative.startsWith('/') ? [] : base.split('/'); for (const part of relative.split('/')) { if (part === '..') parts.pop(); else if (part && part !== '.') parts.push(part); } return parts.join('/'); }
export async function readTemplate(file) {
  if (file.size > 2 * 1024 * 1024) throw new Error('Use a template smaller than 2 MB.');
  let rows, sourceSheet = '';
  if (/\.csv$/i.test(file.name)) rows = parseCsv(await file.text());
  else if (/\.xlsx$/i.test(file.name)) {
    const zip = await readZip(await file.arrayBuffer());
    const workbook = xml(await zip.get('xl/workbook.xml')?.());
    const sheet = elements(workbook, 'sheet')[0];
    if (!sheet) throw new Error('The Excel workbook has no worksheets.');
    sourceSheet = sheet.getAttribute('name') || 'First worksheet';
    const relationships = xml(await zip.get('xl/_rels/workbook.xml.rels')?.());
    const relationId = sheet.getAttributeNS('http://schemas.openxmlformats.org/officeDocument/2006/relationships', 'id');
    const relation = elements(relationships, 'Relationship').find(item => item.getAttribute('Id') === relationId);
    if (!relation || relation.getAttribute('TargetMode') === 'External') throw new Error('The worksheet is not stored in this workbook.');
    const worksheetPath = pathWithin('xl', relation.getAttribute('Target'));
    const shared = zip.has('xl/sharedStrings.xml') ? elements(xml(await zip.get('xl/sharedStrings.xml')()), 'si').map(item => text(item, 't')) : [];
    const worksheet = xml(await zip.get(worksheetPath)?.());
    rows = elements(worksheet, 'row').slice(0, 101).map(row => {
      const values = [];
      for (const cell of elements(row, 'c')) {
        const reference = cell.getAttribute('r')?.match(/^[A-Z]+/i)?.[0] || 'A';
        const column = [...reference.toUpperCase()].reduce((value, letter) => value * 26 + letter.charCodeAt(0) - 64, 0) - 1;
        if (column > 199) throw new Error('Use a template with no more than 200 columns.');
        const type = cell.getAttribute('t'), value = text(cell, 'v');
        values[column] = type === 's' ? shared[Number(value)] || '' : type === 'inlineStr' ? text(cell, 't') : value;
      }
      return Array.from({ length: values.length }, (_, index) => values[index] || '');
    });
  } else throw new Error('Upload an Excel .xlsx or CSV template.');
  if (!rows.length || rows[0].filter(value => value.trim()).length < 2) throw new Error('Put at least two named columns in the first worksheet row.');
  const fields = rows[0].map((field, index) => field.trim() || `Column ${index + 1}`);
  if (new Set(fields.map(field => field.toLowerCase())).size !== fields.length) throw new Error('Use a unique name for each template column.');
  return { name: file.name, fields, rows: rows.slice(1, 101), sourceSheet };
}

export function saveTemplateImport(state, imported, workspaceId = '', id = `template-${crypto.randomUUID()}`) {
  const template = { id, name: imported.name.replace(/\.(xlsx|csv)$/i, ''), description: `Columns from ${imported.name}${imported.sourceSheet ? ` · ${imported.sourceSheet}` : ''}`, fields: [...imported.fields], team: state.session.team, visibility: 'team', version: 1 };
  state.templates.push(template);
  const workspace = state.workspaces.find(item => item.id === workspaceId && item.team === state.session.team);
  if (workspace) workspace.templateId = id;
  return template;
}