需求内容
前后分离后台管理的web项目,需要导入一个excel文件到数据库
处理方案
首先想到的是formdata进行提交,后端实现是采用的python flask,功能实现后,发现接口采用微服务进行注册的,当前只支持application/json格式的参数,采用formdata上传的话,找不到对应的服务,不可用。
后面想到把文件文件转成base64字符串进行上传,记录实现的过程中如下:
前端vue上传excel文件代码
<template>
<el-dialog
title="模板上传"
:visible.sync="dialogVisible"
width="70%"
@close="dialogVisible = false"
>
<el-upload
class="upload-demo"
drag
action="#"
:headers="uploadHeaders"
:limit="1"
:http-request="uploadByJson"
>
<i class="el-icon-upload" />
<div class="el-upload__text">将文件拖到此处,或<em>点击上传</em></div>
<div slot="tip" class="el-upload__tip">只能上传固定模板的excel文件</div>
<div />
</el-upload>
<span slot="footer">
<el-button @click="dialogVisible = false">取消</el-button>
<el-button type="primary" @click="dialogVisible = false">确认</el-button>
</span>
</el-dialog>
</template>
<script>
import { uploadOrderListExcel } from '@/api/nxOrder'
export default {
props: {
dialogVisible: Boolean
},
data() {
return {
// dialogVisible: false,
uploadHeaders: {
'POMX-Svc': 'nx_rpc',
'POMX-Call': 'order_list_upload'
},
uploadData: {
'file': 'haha'
},
base64string: ''
}
},
watch: {
dialogVisible(val, oldval) {
console.log(val)
if (val === false) {
this.$emit('closeModal', false)
}
}
},
created() {
},
methods: {
handleClose(done) {
},
beforeUpload(file) {
console.log(file)
},
/**
* 采用element的http-request属性,覆盖默认的上传行为,可以自定义上传的实现
*/
uploadByJson(params) {
// 读取文件Blob, 获取base64字符串
const reader = new FileReader()
let fileBase64String = ''
reader.readAsDataURL(params.file)
reader.onload = () => {
fileBase64String = reader.result
}
reader.onloadend = () => {
/**
* 调用接口上传文件base64数据
*/
uploadOrderListExcel({
'file': fileBase64String
}).then((res) => {
console.log(res)
}).catch(err => {
console.log(err)
})
}
}
}
}
</script>
后端flask接收base64字符串转excel文件
import base64
from flask import Blueprint
from openpyxl import load_workbook
@app.method('nx_rpc.order_list_upload')
def order_list_upload(file):
try:
# 解决Incorrect padding的问题
missing_padding = 4 - len(file) % 4
if missing_padding:
file += '=' * missing_padding
# 处理保存的excel无法打开的问题
fileString = file.split(',', 1)[1]
# 保存文件
textData = base64.b64decode(fileString)
with open('模板.xlsx', 'wb') as f:
f.write(textData)
f.close()
# 读取excel文件
workbook = load_workbook('模板.xlsx')
detail_sheet = workbook['明细']
detail_sheet_json = []
for i in range(2, detail_sheet.max_row + 1):
sub_data = (detail_sheet.cell(i, 2).value,
str(detail_sheet.cell(i, 3).value),
detail_sheet.cell(i, 4).value,
detail_sheet.cell(i, 5).value,
detail_sheet.cell(i, 6).value,
detail_sheet.cell(i, 7).value,
detail_sheet.cell(i, 8).value,
detail_sheet.cell(i, 9).value,
str(detail_sheet.cell(i, 10).value),
str(detail_sheet.cell(i, 11).value))
detail_sheet_json.append(sub_data)
print('明细表的数据:', detail_sheet_json)
except Exception as e:
return {'message': '虽然你报错了,但爸爸还是爱你'}
return {
'message': '成功了哦',
'code': 20000,
'data': 'Success'
}