Custom page Excel import and export

创建自定义页面 | 钉钉宜搭·帮助中心

能力免费版轻享版专业版专属版自定义页面不支持不支持支持支持未升级到新版信息架构的组织，请 点此查看 使用手册1. 操作步骤在应用管理界面，提供了两个创建自定义页面的入口1.1 通过【新建页面】下拉菜单创建左上角新建页面，在下拉菜单中选择【新建自定义页面(Beta)】，开始创建新建自定义页面1....

https://docs.aliwork.com/docs/yida_support/ui648w/ynk2lo

This case is from Jing, a three-party developer 」

1. Usage scenarios

This import and export is implemented based on the front-end method and is currently not supported on mobile terminals.

This example can be used when you need to export the data of the current custom page table to Excel or import it to a custom page table through Excel.

2. Implement functions

2.1 create a custom page

2.2 configure custom pages

(1) load xlsx.js

The code is as follows (the code needs to be called in didMount):

this.utils.loadScript('https://g.alicdn.com/code/lib/xlsx/0.18.5/xlsx.full.min.js').then(res => {
    console.log('success');
	}).catch(error => {
    this.utils.toast({
      title: '加载错误',
      type: 'error'
    });
  });

(2) configure upload Excel to import to a table

The code is as follows:

// 从上传的文件中解析EXCEL内容并回调处理函数
export function readWorkbookFromRemoteFile(url) {
  let xhr = new XMLHttpRequest();
  xhr.open('get', url, true);
  xhr.responseType = 'arraybuffer';
  xhr.onload = (e) => {
    if (xhr.status == 200) {
      let data = new Uint8Array(xhr.response)
      let workbook = XLSX.read(data, { type: 'array' });
      this.excelImportToTable(workbook);
    }
  };
  xhr.send();
}

// 导入到表格处理
export function excelImportToTable(workbook) {
  let sheetNames = workbook.SheetNames; // 工作表名称集合
  let worksheet = workbook.Sheets[sheetNames[0]]; // 只读取第一张sheet
  let tableJson = XLSX.utils.sheet_to_json(worksheet);
  const data = tableJson.map((item, index) => {
    return {
      text1: item['单行文本1'], // 表格对应组件标识：item[excel对应的列名称]
      text2: item['单行文本2'],
      text3: item['单行文本3'],
      id: index
    };
  });
  // 写入表格
  this.$("tablePc_lckakub0").set('data', {
    totalCount: data.length,
    data,
  });
  this.utils.toast({
    title: '数据导入完成',
    type: 'success'
  });
}

export function onSuccess(file, value) {
  if (value && value.length) {
    this.readWorkbookFromRemoteFile(value[0].downloadURL);
  } else {
    this.$("tablePc_lckakub0").set('data', { totalCount: 0, data: [] });
  };
}

(3) configure the selected data of the table to export to Excel

The code is as follows:

export function onExportData() {
  const { records } = this.state;
  if (!records.length || !records) {
    this.utils.toast({
      title: '当前未选中任何数据',
      type: 'warning'
    });
    return;
  };
  // 导出 excel 列名称
  const sheetHeader = [{
    title: '单行文本1',
    value: 'text1'
  }, {
    title: '单行文本2',
    value: 'text2'
  }, {
    title: '单行文本3',
    value: 'text3'
  }];
  const result = [];
  for (let i = 0; i < records.length; i++) {
    let item = {};
    for (let j = 0; j < sheetHeader.length; j++) {
      item[sheetHeader[j].title] = records[i][sheetHeader[j].value];
    };
    result.push(item);
  };
  // 新建空workbook，然后加入worksheet
  const ws = XLSX.utils.json_to_sheet(result);
  // 设置每列的列宽，10代表10个字符，注意中文占2个字符
  ws['!cols'] = [
    { wch: 10 },
    { wch: 10 },
    { wch: 10 }
  ];
  // 新建book
  const wb = XLSX.utils.book_new();
  // 生成xlsx文件(book,sheet数据,sheet命名)
  XLSX.utils.book_append_sheet(wb, ws, 'sheet1');
  // 写文件
  const workbookBlob = workbook2blob(wb);
  openDownloadDialog(workbookBlob, `${this.utils.formatter('date', Date.now(), 'YYYYMMDDhhmmss')}.xlsx`);
}

function openDownloadDialog(blob, fileName) {
  if (typeof blob == "object" && blob instanceof Blob) {
    blob = URL.createObjectURL(blob); // 创建blob地址
  }
  var aLink = document.createElement("a");
  aLink.href = blob;
  // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，有时候 file:///模式下不会生效
  aLink.download = fileName || "";
  var event;
  event = document.createEvent("MouseEvents");
  event.initMouseEvent("click", true, false, window, 0, 0, 0, 0, 0, false,
    false, false, false, 0, null);
  aLink.dispatchEvent(event);
}

function workbook2blob(workbook) {
  // 生成excel的配置项
  var wopts = {
    // 要生成的文件类型
    bookType: "xlsx",
    bookSST: false,
    type: "binary"
  };
  var wbout = XLSX.write(workbook, wopts);
  // 将字符串转ArrayBuffer
  function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
    return buf;
  }
  var blob = new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  });
  return blob;
}

3. Achieve results

3.1 import

3.2 Export

4. Try it online

示例中心 | 开发者赋能平台

AI智能体时代，全新宜搭。业务创新，一搭就行。

https://www.aliwork.com/o/coc?tplUuid=TPL_W3JB1K2U64H83ARH2BQS&from=yuque_subject