vue3 + antd vue 纯前端 基于xlsx 实现导入excel 转 json，将json数据转换XLSX并下载（下载模版）

时间：2024-07-20 17:01:58

一、导入

0、关键代码

// 安装插件
npm i xlsx/yarn add xlsx
// 导入xlsx
import * as XLSX from 'xlsx';

点击提交的时候才整理数据。上传的时候文件保存在  state.form.file[0] 中的

// 定义字段映射关系
const fieldMap = {
  sheet2json: {
    技能名称: 'skill_name',
    技能等级: 'skill_level',
    技能描述: 'skill_desc',
    技能类型: 'skill_type',
    技能效果: 'skill_effect',
    技能消耗: 'skill_cost',
    技能持续时间: 'skill_duration',
    技能范围: 'skill_range',
    技能范围: 'skill_range',
    技能目标: 'skill_target'
  }
};

// 提交 --- 点击提交的时候才整理数据。上传的时候文件保存的  state.form.file[0] 中的
const handleSummit = () => {
  formRef.value.validate().then(async () => {
    try {
      const data = await state.form.file[0].arrayBuffer(); // 使用 arrayBuffer 避免中文乱码
      const workbook = XLSX.read(data, { type: 'buffer' });
      const outdata = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);

      // 映射字段名并过滤掉不符合预期的数据
      const mappedData = outdata
        .map(row => {
          return Object.keys(row).reduce((targetMap, key) => {
            const mappedKey = fieldMap.sheet2json[key];
            if (mappedKey) {
              targetMap[mappedKey] = row[key];
            }
            return targetMap;
          }, {});
        })
        .filter(item => Object.keys(item).length > 0); // 过滤空对象

      console.log('------- 导入的数据 -------', mappedData);

      emits('submit', mappedData);
      handleClose();
    } catch (error) {}
  });
};

1、template

<a-form :model="state.form" name="form" ref="formRef" :label-col="{ style: { width: '120px' } }" autocomplete="off" :rules="rules">
  <a-form-item label="导入文件上传" name="file" :rules="rules.file">
    <div class="file-warp" style="position: relative">
      <a-upload
        style="margin-left: 20px"
        :file-list="state.form.file"
        name="file"
        :customRequest="upload"
        :beforeUpload="beforeUpload"
        @remove="handleRemove"
        accept=".xlsx, .xls">
        <a-button type="primary">
          <upload-outlined></upload-outlined>
          上传文件
        </a-button>
      </a-upload>
      <a-button type="primary" ghost style="position: absolute; top: 0; left: 150px" @click="handleDownload">
        <VerticalAlignBottomOutlined></VerticalAlignBottomOutlined>
        模版下载
      </a-button>
    </div>
  </a-form-item>
</a-form>

2、script

import * as XLSX from 'xlsx';
import { reactive, ref } from 'vue';

const state = reactive({
  form: {
    file: []
  }
});
const formRef = ref(null);
const open = ref(true);

const rules = {
  file: [{ required: true, message: '请选择文件', trigger: ['blur', 'change'] }]
};

// 定义字段映射关系
const fieldMap = {
  sheet2json: {
    技能名称: 'skill_name',
    技能等级: 'skill_level',
    技能描述: 'skill_desc',
    技能类型: 'skill_type',
    技能效果: 'skill_effect',
    技能消耗: 'skill_cost',
    技能持续时间: 'skill_duration',
    技能范围: 'skill_range',
    技能范围: 'skill_range',
    技能目标: 'skill_target'
  },
  json2sheet: {
    skill_name: '技能名称',
    skill_level: '技能等级',
    skill_desc: '技能描述',
    skill_type: '技能类型',
    skill_effect: '技能效果',
    skill_cost: '技能消耗',
    skill_duration: '技能持续时间',
    skill_range: '技能范围',
    skill_target: '技能目标'
  }
};

// 上传文件之前检测
const beforeUpload = file => {
  const isXlsxOrXls = file.name.split('.')[1] == 'xlsx' || file.name.split('.')[1] == 'xls';
  if (!isXlsxOrXls) {
    message.error('只允许上传xlsx, xls格式的文件！');
    return false;
  }
  const isLt10M = file.size / 1024 / 1024 < 10;
  if (!isLt10M) {
    message.error('文件不得大于10MB!');
    return false;
  }
  return isXlsxOrXls && isLt10M;
};

// 选择文件
const upload = file => {
  // 原本调用接口上传的
  // uplaodFile(file.file).then(res => {
  //   fileList.value.push({ name: res.data.originalFilename, url: viteConfig.baseUrl + res.data.fileName, fileUrl: res.data.fileName });
  //   formRef.value.clearValidate();
  // });
  state.form.file = [file.file];
  formRef.value.clearValidate();
};

// 移除文件
const handleRemove = file => {
  state.form.file = [];
};

// 提交 转换数据
const handleSummit = () => {
  formRef.value.validate().then(async () => {
    try {
      const data = await state.form.file[0].arrayBuffer(); // 使用 arrayBuffer 避免中文乱码
      const workbook = XLSX.read(data, { type: 'buffer' });
      const outdata = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);

      // 映射字段名并过滤掉不符合预期的数据
      const mappedData = outdata
        .map(row => {
          return Object.keys(row).reduce((targetMap, key) => {
            const mappedKey = fieldMap.sheet2json[key];
            if (mappedKey) {
              targetMap[mappedKey] = row[key];
            }
            return targetMap;
          }, {});
        })
        .filter(item => Object.keys(item).length > 0); // 过滤空对象

      console.log('------- 导入的数据 -------', mappedData);

      emits('submit', mappedData);
      handleClose();
    } catch (error) {}
  });
};

二、模板下载

1、script

// 定义字段映射关系
const fieldMap = {
  json2sheet: {
    skill_name: '技能名称',
    skill_level: '技能等级',
    skill_desc: '技能描述',
    skill_type: '技能类型',
    skill_effect: '技能效果',
    skill_cost: '技能消耗',
    skill_duration: '技能持续时间',
    skill_range: '技能范围',
    skill_target: '技能目标'
  }
};

// 模板数据
let templateData = [
  {
    skill_name: '大刀斩',
    skill_level: '5',
    skill_desc: '技能描述',
    skill_type: '大招',
    skill_effect: '亚瑟王那样的大招',
    skill_cost: '10000',
    skill_duration: '10',
    skill_range: '500',
    skill_target: '目标：亚瑟王'
  }
];

// 模版下载
const handleDownload = () => {
  // 映射字段名并过滤掉不符合预期的数据
  const list = templateData
    .map(row => {
      return Object.keys(row).reduce((targetMap, key) => {
        const mappedKey = fieldMap.json2sheet[key];
        if (mappedKey) {
          targetMap[mappedKey] = row[key];
        }
        return targetMap;
      }, {});
    })
    .filter(item => Object.keys(item).length > 0); // 过滤空对象;

  const workSheet = XLSX.utils.json_to_sheet(list);
  const workBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workBook, workSheet, '技能表');

  // 生成Excel文件并下载
  XLSX.writeFile(workBook, '技能表模板.xlsx');
};

三、完整的文件

<!--
 * @Description: ------------ fileDescription -----------
 * @Author: snows_l snows_l@163.com
 * @Date: 2024-07-18 14:46:47
 * @LastEditors: snows_l snows_l@163.com
 * @LastEditTime: 2024-07-19 15:51:19
 * @FilePath: /digital-qiankun-you/cmdb/src/pages/ipSource/components/uploadFile.vue
-->
<template>
  <div class="upeate-field-warp">
    <a-modal width="800px" v-model:open="open" :z-index="10004" centered :title="'规划导入'">
      <template #footer>
        <a-button type="primary" @click="handleSummit">确认</a-button>
      </template>
      <div class="update-field-content-warp">
        <a-form :model="state.form" name="form" ref="formRef" :label-col="{ style: { width: '120px' } }" autocomplete="off" :rules="rules">
          <a-form-item label="导入文件上传" name="file" :rules="rules.file">
            <div class="file-warp" style="position: relative">
              <a-upload
                style="margin-left: 20px"
                :file-list="state.form.file"
                name="file"
                :customRequest="upload"
                :beforeUpload="beforeUpload"
                @remove="handleRemove"
                accept=".xlsx, .xls">
                <a-button type="primary">
                  <upload-outlined></upload-outlined>
                  上传文件
                </a-button>
              </a-upload>
              <a-button type="primary" ghost style="position: absolute; top: 0; left: 150px" @click="handleDownload">
                <VerticalAlignBottomOutlined></VerticalAlignBottomOutlined>
                模版下载
              </a-button>
            </div>
          </a-form-item>
        </a-form>
      </div>
    </a-modal>
  </div>
</template>

<script setup>
import { UploadOutlined, VerticalAlignBottomOutlined } from '@ant-design/icons-vue';
import { reactive, ref } from 'vue';
import * as XLSX from 'xlsx';

const emits = defineEmits(['submit']);

const state = reactive({
  form: {
    file: []
  }
});
const formRef = ref(null);
const open = ref(true);

const rules = {
  file: [{ required: true, message: '请选择文件', trigger: ['blur', 'change'] }]
};

// 上传文件之前检测
const beforeUpload = file => {
  const isXlsxOrXls = file.name.split('.')[1] == 'xlsx' || file.name.split('.')[1] == 'xls';
  if (!isXlsxOrXls) {
    message.error('只允许上传xlsx, xls格式的文件！');
    return false;
  }
  const isLt10M = file.size / 1024 / 1024 < 10;
  if (!isLt10M) {
    message.error('文件不得大于10MB!');
    return false;
  }
  return isXlsxOrXls && isLt10M;
};

// 选择文件
const upload = file => {
  // 原本调用接口上传的
  // uplaodFile(file.file).then(res => {
  //   fileList.value.push({ name: res.data.originalFilename, url: viteConfig.baseUrl + res.data.fileName, fileUrl: res.data.fileName });
  //   formRef.value.clearValidate();
  // });
  state.form.file = [file.file];
  formRef.value.clearValidate();
};

// 移除文件
const handleRemove = file => {
  state.form.file = [];
};

// 初始化
const init = () => {
  open.value = true;
};

// 关闭
const handleClose = () => {
  open.value = false;
};

// 定义字段映射关系
const fieldMap = {
  sheet2json: {
    技能名称: 'skill_name',
    技能等级: 'skill_level',
    技能描述: 'skill_desc',
    技能类型: 'skill_type',
    技能效果: 'skill_effect',
    技能消耗: 'skill_cost',
    技能持续时间: 'skill_duration',
    技能范围: 'skill_range',
    技能范围: 'skill_range',
    技能目标: 'skill_target'
  },
  json2sheet: {
    skill_name: '技能名称',
    skill_level: '技能等级',
    skill_desc: '技能描述',
    skill_type: '技能类型',
    skill_effect: '技能效果',
    skill_cost: '技能消耗',
    skill_duration: '技能持续时间',
    skill_range: '技能范围',
    skill_target: '技能目标'
  }
};

// 模板数据
let templateData = [
  {
    skill_name: '大刀斩',
    skill_level: '5',
    skill_desc: '技能描述',
    skill_type: '大招',
    skill_effect: '亚瑟王那样的大招',
    skill_cost: '10000',
    skill_duration: '10',
    skill_range: '500',
    skill_target: '目标：亚瑟王'
  }
];

// 提交
const handleSummit = () => {
  formRef.value.validate().then(async () => {
    try {
      const data = await state.form.file[0].arrayBuffer(); // 使用 arrayBuffer 避免中文乱码
      const workbook = XLSX.read(data, { type: 'buffer' });
      const outdata = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);

      // 映射字段名并过滤掉不符合预期的数据
      const mappedData = outdata
        .map(row => {
          return Object.keys(row).reduce((targetMap, key) => {
            const mappedKey = fieldMap.sheet2json[key];
            if (mappedKey) {
              targetMap[mappedKey] = row[key];
            }
            return targetMap;
          }, {});
        })
        .filter(item => Object.keys(item).length > 0); // 过滤空对象

      console.log('------- 导入的数据 -------', mappedData);

      emits('submit', mappedData);
      handleClose();
    } catch (error) {}
  });
};

// 模版下载
const handleDownload = () => {
  // 映射字段名并过滤掉不符合预期的数据
  const list = templateData
    .map(row => {
      return Object.keys(row).reduce((targetMap, key) => {
        const mappedKey = fieldMap.json2sheet[key];
        if (mappedKey) {
          targetMap[mappedKey] = row[key];
        }
        return targetMap;
      }, {});
    })
    .filter(item => Object.keys(item).length > 0); // 过滤空对象;

  const workSheet = XLSX.utils.json_to_sheet(list);
  const workBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workBook, workSheet, '技能表');
  // 生成Excel文件并下载
  XLSX.writeFile(workBook, '技能表模板.xlsx');
};

defineExpose({
  init
});
</script>

<style lang="less" scoped>
.upeate-field-warp {
  width: 100%;
}
</style>

<style lang="less">
.update-field-content-warp {
  padding: 40px 20px;
  .field-item {
    display: flex;
    align-items: center;
    label {
      min-width: 80px;
    }
  }
}
</style>

四、效果图：

原数据（图1）

导入组件（图2）

导出整理后的数据（图3）

模板下载（图4）

模板下载之后的文件（图5）

相关文章