Excel导入数据库(vue+py)vue部分

1. 根据网站的用户的权限  获取对应的数据库模块

2. 在模块中增加数据

3. 上传Excel数据文件

主要为自己工作内容记录,十分笼统,欢迎私信交流~~

vue(运用Ant框架)

html:

<template>
  <page-header-wrapper>
    <a-card :bordered="false">
      <a-row>
        <a-col :span="6">
          <a-button type="primary" size="large" @click="downloadExcel()" :loading="downloadLoading">
            下载Excel数据模板 </a-button>
        </a-col>
        <a-col :span="12" :offset="6" style="text-align: right;">
          <a-radio-group v-model:value="processValue" size="large" style="margin-right:10px">
            <a-radio-button value="before">导入前去重</a-radio-button>
            <a-radio-button value="after">导入后去重</a-radio-button>
          </a-radio-group>
          <a-upload name="file" accept=".xlsx" :multiple="true" :action="uploadURL" :headers="uploadHeaders" :showUploadList="false" @change="uploadChange">
            <a-button type="primary" size="large" :loading="uploadLoading">
              <a-icon type="upload" /> 上传Excel数据文件
            </a-button>
          </a-upload>
        </a-col>
      </a-row>
      <a-row v-show="progressPercent != 0" style="margin-top: 1vh">
        <a-col>
          <a-progress :stroke-color="{
            '0%': '#87d068',
            '100%': '#108ee9',
          }" :percent="progressPercent" />
        </a-col>
      </a-row>
    </a-card>
    <a-card v-show="uploadCardVisible" style="margin-top: 1vh">
      <a-row>
        <a-col>
          <b style="font-size: 20px" @click="initPageConfig()">导入过程详情：</b>
        </a-col>
      </a-row>
      <a-row style="margin-top: 1vh">
        <div style="width: 100%; height: 50vh; overflow-y: auto" id="log-div">
          <a-alert :message="log.message" :type="log.type" v-for="log in importLogArray" v-bind:key="log.message" style="margin-bottom: 5px" />
        </div>
      </a-row>
    </a-card>
    <a-card :bordered="false" style="margin-top: 1vh">
      <a-row>
        <a-col>
          <b style="font-size: 20px">通过Excel文件方式上传数据步骤说明：</b>
        </a-col>
      </a-row>
      <a-row style="margin-top: 1vh">
        <a-col>
          <a-descriptions :column="1" bordered>
            <a-descriptions-item label="1. 下载Excel模板">
              点击“下载Excel模板”按钮下载此模块的数据模板，该模板包含所有可上传的列名和数据类型<br />
              *注1：Excel模板中的列名等已有信息不可修改，否则会导致数据上传失败<br />
              *注2：您下载的模板和其他账号下载的模板结构可能不同，请不要混用
            </a-descriptions-item>
            <a-descriptions-item label="2. 填写Excel模板">
              按照规范填写Excel模板中的数据<br />
              *注3：请不要在数据中间留空行<br />
              *注4：日期格式统一为yyyy-MM-dd HH:mm:ss，例如：2020-01-01 00:00:00。如果日期为空，请设置为0000-00-00 00:00:00，不接受空字符串，否则报错
            </a-descriptions-item>
            <a-descriptions-item label="3. 上传Excel文件">
              点击“上传Excel数据”按钮上传填写完毕的Excel文件<br />
            </a-descriptions-item>
            <a-descriptions-item label="*报错说明">
              当填写的Excel文件中的数据不符合规范或有其他问题时，程序会提示导入失败，并提示大致相关问题原因。本次导入随即停止并回滚本次已导入的数据，请修改Excel后重新导入。
            </a-descriptions-item>
          </a-descriptions>
        </a-col>
      </a-row>
    </a-card>
  </page-header-wrapper>
</template>

js:

<script>
import { GenerateExcelTemplate, ImportExcelData, QueryImportExcelDataProgress } from '@/api/authority/excel'
import moment from 'moment'

export default {
  name: 'Excelimport',
  data () {
    return {
      processValue: 'before',
      progressPercent: 0,
      downloadLoading: false,
      uploadHeaders: {
        'Access-Token': localStorage.getItem('Access-Token'),
        'User-ID': localStorage.getItem('User-ID'),
        'User-Name': localStorage.getItem('User-Name'),
        'Roles': localStorage.getItem('Roles'),
      },
      uploadURL: process.env.VUE_APP_API_BASE_URL + '/authority/UploadExcelFile/',
      uploadCardVisible: false,
      importLogArray: [],
      importingFilePath: '',
      uploadLoading: false,
      importInterval: null,
    }
  },
  mounted () {
    console.log(this.uploadURL)
  },
  directives: {
    resize: {
      // 指令的名称
      bind (el, binding) {
        // el为绑定的元素，binding为绑定给指令的对象
        // console.log(el, '绑定', binding)
        let width = '',
          height = ''
        function isReize () {
          const style = document.defaultView.getComputedStyle(el)
          if (width !== style.width || height !== style.height) {
            binding.value({ width: style.width, height: style.height }) // 关键(这传入的是函数,所以执行此函数)
          }
          width = style.width
          height = style.height
        }
        el.__vueSetInterval__ = setInterval(isReize, 300)
      },
      unbind (el) {
        // console.log(el, '解绑')
        clearInterval(el.__vueSetInterval__)
      },
    },
  },
  methods: {
    // 下载模块
    downloadExcel () {
      this.downloadLoading = true
      this.progressPercent = 0.0
      let downloadInterval = setInterval(() => {
        this.progressPercent += 1
        if (this.progressPercent >= 99) {
          this.progressPercent = 99
          clearInterval(downloadInterval)
        }
      }, 100)
      let _this = this
      _this.$message.info('正在生成模板，请稍后')
      GenerateExcelTemplate()
        .then(response => {
          console.log(response);
          var fileURL = window.URL.createObjectURL(new Blob([response]));
          var fileLink = document.createElement("a");

          fileLink.href = fileURL;
          fileLink.setAttribute(
            "download",
            "数据导入模板_" + moment().format("YYYYMMDD_hhmmss") + ".xlsx"
          );
          document.body.appendChild(fileLink);
          fileLink.click();
        })
        .catch(error => {
          _this.progressPercent = 0.0
          console.log(error)
        }).finally(() => {
          _this.downloadLoading = false
          clearInterval(downloadInterval)
          _this.progressPercent = 100.0
        })
    },
    uploadExcel () {
      this.progressPercent = 0.0
      let _this = this
      let progressInterval = setInterval(function () {
        if (_this.progressPercent < 100) {
          console.log(_this.progressPercent)
          _this.progressPercent = _this.progressPercent + 1
        } else {
          _this.$message.info('文件上传成功，开始导入')
          clearInterval(progressInterval)
        }
      }, 20)
    },
    uploadChange (info) {
      console.log(this.processValue);
      this.uploadLoading = true
      this.uploadCardVisible = true
      if (info.file.status !== 'uploading') {
        this.importLogArray = []
        this.importLogArray.push({
          message: '[1/5] 上传过程开始启动',
          type: 'info',
        })
        console.log(info.file, info.fileList);
        this.importLogArray.push({
          message: '[2/5] 正在上传文件，请稍后……',
          type: 'info',
        })
      }
      if (info.file.status === 'done') {
        if (info.file.response.code == 20000) {
          this.$message.info(`${info.file.name} 文件上传成功，开始导入数据`);
          this.importLogArray.push({
            message: '[3/5] 文件上传成功√',
            type: 'success',
          })
          this.importLogArray.push({
            message: '[4/5] 请牢记您本次上传的数据批次号：[' + info.file.response.version.version_now + ']',
            type: 'info',
          })
          this.importingFilePath = info.file.response.file_path
          this.importData(info.file.response.file_path, info.file.response.version);
        } else if (info.file.response.code == 50001) {
          this.uploadLoading = false;
          this.$message.error(`${info.file.name} 上传失败`);
          this.importLogArray.push({
            message: '[3/5] 文件上传失败×，' + info.file.response.message,
            type: 'error',
          })
        } else {
          this.uploadLoading = false;
          this.$message.error(`${info.file.name} 上传失败`);
          this.importLogArray.push({
            message: '[3/5] 文件上传失败×，发生内部错误：' + info.file.response.message,
            type: 'error',
          })
        }
      } else if (info.file.status === 'error') {
        console.log(info.file)
        this.uploadLoading = false;
        this.$message.error(`${info.file.name} 上传失败`);
        this.importLogArray.push({
          message: '[3/5] 文件上传失败×，上传过程发生错误.',
          type: 'error',
        })
      }
    },
    importData (file_path, version) {
      this.importLogArray.push({
        message: '[4/5] 开始导入上传文件内容到数据库，请稍后……',
        type: 'info',
      })
      this.importLogArray.push({
        message: '[4/5] 根据数据量大小，导入过程可能持续数分钟，请耐心等待……',
        type: 'info',
      })
      this.progressPercent = 1
      this.importInterval = setInterval(() => {
        QueryImportExcelDataProgress({ version_id: version.version_id }).then(res => {
          if (res.code == 20000) {
            this.importLogArray.push({
              message: '[4/5] 数据导入进度' + res.progress + '%',
              type: 'info',
            })
            this.progressPercent = parseInt(res.progress)
            if (res.progress == "100") {
              clearInterval(this.importInterval)
              this.importLogArray.push({
                message: '[5/5] 数据导入进度100%',
                type: 'success',
              })
            }
          } else {
            console.error(res)
          }
        })
      }, 2000)
      ImportExcelData({ file_path: file_path, version_id: version.version_id, version_now: version.version_now, mode: this.processValue })
        .then(response => {
          console.log(response)
          if (response.code == 20000) {
            this.importLogArray.push({
              message: '[5/5] 数据导入完毕√',
              type: 'success',
            })
            this.progressPercent = 100
          } else {
            this.$message.error('导入失败');
            this.importLogArray.push({
              message: '[4/5] 导入失败×，发生内部错误：' + response.message,
              type: 'error',
            })
            this.importLogArray.push({
              message: '[4/5] 错误提示：' + response.data,
              type: 'error',
            })
          }
        }).catch(error => {
          this.$message.error('导入失败');
          this.importLogArray.push({
            message: '[4/5] 导入失败×，发生内部错误：' + error,
            type: 'error',
          })
        }).finally(() => {
          this.uploadLoading = false;
          clearInterval(this.importInterval)
        })
    },
    checkboxRollbackChange (e) {
      console.log(`checked = ${e.target.checked}`);
    }
  },
}
</script>

ajax:

import request from '@/utils/request'
import requestFile from '@/utils/requestFile'

const api = {
    generate_excel_template: '/authority/GenerateExcelTemplate/',
    upload_excel_file: '/authority/UploadExcelFile/',
    import_excel_data: '/authority/ImportExcelData/',
    query_import_excel_data_progress: '/authority/QueryImportExcelDataProgress/',
}
export function GenerateExcelTemplate (parameter) {
    return requestFile({
        url: api.generate_excel_template,
        method: 'post',
        data: {
            ...parameter
        }
    })
}
export function UploadExcelFile (parameter) {
    return requestFile({
        url: api.upload_excel_files,
        method: 'get',
        params: parameter
    })
}
export function ImportExcelData (parameter) {
    return request({
        url: api.import_excel_data,
        method: 'post',
        data: {
            ...parameter
        }
    })
}
export function QueryImportExcelDataProgress (parameter) {
    return request({
        url: api.query_import_excel_data_progress,
        method: 'post',
        data: {
            ...parameter
        }
    })
}

效果展示: