纯前端导入导出excel文件

最新推荐文章于 2024-05-08 17:29:19 发布

今天吃了嘛o

最新推荐文章于 2024-05-08 17:29:19 发布

阅读量144

点赞数 2

分类专栏：导入导出excel文件文章标签： javascript

本文链接：https://blog.csdn.net/weixin_45535843/article/details/120784651

版权

导入导出excel文件专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.下载xlsx插件（此插件可以读取excel文件）

cnpm install xlsx --save

2.html部分

<template>
  <div class="hello">
    <el-upload action="/" :before-upload="beforeUpload" :show-file-list="false" :limit="1" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel" :auto-upload="true"><el-button size="small" icon="el-icon-upload" type="primary">导入数据</el-button></el-upload><el-button size="small" icon="el-icon-upload" type="primary" @click="downExcle">导出数据</el-button>
  </div>
</template>

3.js部分

<script>
var arr = [];
var newArr = [];
import Xlsx from 'xlsx'
export default {
  name: "HelloWorld",
  props: {
    msg: String,
  },
  data() {
    return {
      xlxsData: [
        // 源数据
        {
          type: "颜色",
          ID: "34234",
          name: "Light Pink",
        },
      ],
      xlscList: [],
      xlscTitle: {
        "类型": "type",
        "ID": "id",
        "名称": "name"
      },
    };
  },
  methods: {
  //上传
    beforeUpload(file) {
      console.log(file)
      let self = this;
      const types = file.name.split('.')[1];
      const fileType = ['xlsx','xls'].some(item => {
        return item === types
      });
      if (!fileType) {
        this.$message.error('文件格式错误，请重新选择文件！')
      }
      if (file.size / 1024 / 1024 > 1) {
          this.$message.error('上传文件大小不能超过 1M !');
          return false
      }


      this.file2Xce(file).then(tab => {
        if (tab && tab.length > 0) {
          tab[0].sheet.forEach(item => {
            let obj = {};
            for (let key in item) {
              obj[self.xlscTitle[key]] = item[key];
            }
            self.xlscList.push(obj);
          });
          // console.log(self.xlscList)

          if (self.xlscList.length) {
            this.$message.success('上传成功')
          } else {
            this.$message.error('空文件或数据缺失，请重新选择文件！')
          }
        }
      })
    },
    //读取文件
    file2Xce(file){
      return new Promise(function(resolve) {
        const reader = new FileReader();
        reader.onload = function(e) {
          const data = e.target.result;
          this.wb = Xlsx.read(data, {
            type: "binary"
          });
          const result = [];
          
          this.wb.SheetNames.forEach(sheetName => {
          result.push({
              sheetName: sheetName,
              sheet: Xlsx.utils.sheet_to_json(this.wb.Sheets[sheetName])
            })
          })
          console.log(result,'data')
        
          result.forEach(item=>{
            item.sheet.forEach(i=>{
              arr.push(i)
            })
          })
          arr.forEach(item=>{
            for(var i in item){
              newArr.push(item[i])
            }
          })
          resolve(result);
        }
        reader.readAsBinaryString(file);
      })
    },
    //导出
    downExcle() {
    
      let jsonData = newArr
      console.log(jsonData,'wordList')
      let str = '<tr><td>关键字</td></tr>';
      //循环遍历，每行加入tr标签，每个单元格加td标签
      for(let i = 0 ; i < jsonData.length ; i++ ){
        str+='<tr>';
        str+=`<td>${ jsonData[i] + '\t'}</td>`;    
        str+='</tr>';
      }
      //Worksheet名
      let worksheet = 'Sheet1'
      let uri = 'data:application/vnd.ms-excel;base64,';
      //模板
      let template = `<html xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel"
      xmlns="http://www.w3.org/TR/REC-html40">
      <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
      <x:Name>${worksheet}</x:Name>
      <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
      </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
      </head><body><table>${str}</table></body></html>`;
      let a = document.createElement('a');
      a.href =  uri + this.base64(template);
      a.download = '关键字导出'+new Date().valueOf()+'.xlsx'
      document.body.appendChild(a);
      a.click();
      document.body.removeChild(a);
      arr =[];
      newArr = [];
    }, 
    base64 (s) { return window.btoa(unescape(encodeURIComponent(s))) },

  },
};
</script>