visualization-design/src/services/excel.ts

import ExcelJS from 'exceljs';
import { saveAs } from 'file-saver';
import { MessagePlugin } from 'tdesign-vue-next';

export async function importExcel(columns: any[]) {
  return new Promise((resolve, reject) => {
    const input = document.createElement('input');
    input.type = 'file';
    input.accept =
      '.csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel,application/zip';
    input.onchange = (event) => {
      const elem: any = event.target;

      if (elem.files && elem.files[0].name.indexOf('.xlsx') > 0) {
        const workbook = new ExcelJS.Workbook();
        const reader = new FileReader();
        reader.readAsArrayBuffer(elem.files[0]);
        reader.onload = async () => {
          const buffer: any = reader.result;
          await workbook.xlsx.load(buffer);
          // 默认只解析第一个sheet
          const worksheet = workbook.worksheets[0];
          // 获取sheet1的所有rows
          const rows = worksheet.getSheetValues();
          if (rows.length === 0) {
            MessagePlugin.warning('导入的excel文件不可为空！');
            return;
          }
          let data: any = [];
          let indexKeyMap: any = {};
          worksheet.eachRow((row, rowNumber) => {
            let _data: any = {};
            row.eachCell((cell, colNumber) => {
              if (rowNumber === 1) {
                let _index = columns.findIndex(
                  (item) => item.header === cell.value
                );
                if (_index !== -1) {
                  indexKeyMap[colNumber] = columns[_index].key;
                }
              } else {
                _data[indexKeyMap[colNumber]] = cell.value;
              }
            });
            if (rowNumber !== 1) {
              data.push(_data);
            }
          });
          resolve(data);
          MessagePlugin.success('导入成功!');
        };
      }
    };
    input.click();
  });
}

export function saveAsExcel(
  name: string,
  columns: { header: string; key: string }[],
  data: any[]
) {
  const fileName = `${name}.xlsx`;
  const workbook = new ExcelJS.Workbook();
  // workbook.creator = 'Me';
  // workbook.lastModifiedBy = 'Her';
  workbook.created = new Date();
  // workbook.modified = new Date();
  // workbook.lastPrinted = new Date();
  const worksheet = workbook.addWorksheet(`${name}`);
  worksheet.columns = columns;

  worksheet.addRows(data);
  workbook.xlsx.writeBuffer().then((data) => {
    const blob = new Blob([data], {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8',
    });
    saveAs(blob, fileName);
    MessagePlugin.success('导出成功!');
  });
}