EXCEL can import subforms when submitting anonymously.

1. Case Background

When sharing forms to external customers, subforms are often supported to import Excel. However, due to case reasons, subforms cannot be directly imported into files.

2. Procedure

2.1 form design

File upload limit: only one file can be uploaded. Only Excel is supported.

2.2 EXCEL template formulation

Note:This document case only supports a single row header and does not support merging columns. The header field of the template must at least overwrite the required fields of the subform.

2.2 Introduction of js-excel and related code configuration

2.2.1 introduce js-excel package

const src = "https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js";
const script = document.createElement('script');
script.setAttribute('type', 'text/javascript');
script.src = src;
document.body.appendChild(script);

2.2.2 read uploaded file content

// 从上传的文件中解析EXCEL内容并回调处理函数
// 这里增加一个this的参数传递，用于对页面元素进行定位修改
function readWorkbookFromRemoteFile(_this,url, callback) {
  let xhr = new XMLHttpRequest();
  xhr.open('get', url, true);
  xhr.responseType = 'arraybuffer';
  xhr.onload = function (e) {
    if (xhr.status == 200) {
      let data = new Uint8Array(xhr.response)
      let workbook = XLSX.read(data, { type: 'array' });
      if (callback) callback(workbook, _this);
    }
  };
  xhr.send();
}

2.2.3 fill in the subform

//填写导入到子表单处理
function excelImportToTable(workbook,_this)
{
  let sheetNames = workbook.SheetNames; // 工作表名称集合
  let worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
  let tableJson = XLSX.utils.sheet_to_json(worksheet);
  let tableValue = [];
  // 循环读取Json对表单数据进行赋值
  tableJson.forEach(function (item, $index, arr) {
    let tmpValue = {
      // 时间 -如果有时间字段，要这样处理一下，因为EXCEL上传来都是4986这种数字
      dateField_l1vygf2d: new Date((item.时间 - 1) * 24 * 3600000 + 1),
      // 发货仓
      子表单的字段: item.EXCE表头上的字段,
      //其它字段写到这里
    }
    tableValue.push(tmpValue);
  })
  // 写入子表单
  const acitivityTable = _this.$("子表单的唯一ID");
  async function setTableValue() {
    const symbol = await acitivityTable.setValue(tableValue);
  }
  setTableValue().then(function (result) {   
    _this.utils.dialog({
      type: 'info',
      title: '数据导入完成',
      content: "请仔细复核后提交!",
      messageProps: {
        type: 'notice',
      }
    })
  });
}

2.2.4 set the upload success event of the upload component

export function onExclUploadSuccess(file, value) {
  console.log('onSuccess', file, value);
  const uploadFileUrl=value[0].downloadURL
  const fileContent = readWorkbookFromRemoteFile(this,uploadFileUrl, excelImportToTable) 
}

3. Effect display

4. Try it online

https://www.aliwork.com/bench/coe? tplUuid=TPL_W3JB1K2U64H83ARH2BQS

-------------------- Get the latest information YIDA, welcome to follow US--------------------