200 lines
6.5 KiB
Python
200 lines
6.5 KiB
Python
import os
|
||
from werkzeug.utils import secure_filename
|
||
import datetime
|
||
import random
|
||
from openpyxl import load_workbook, Workbook
|
||
from db import insertKeyword, getAllKeywordList
|
||
|
||
# 获取本文件目录地址
|
||
basedir = os.path.abspath(os.path.dirname(__file__))
|
||
# 设置允许的文件尾缀
|
||
ALLOWED_EXTENSIONS = set(['xlsx'])
|
||
# 判断允许的文件尾缀
|
||
def allowed_file(filename):
|
||
return '.' in filename and filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS
|
||
|
||
#生成唯一的名称字符串，防止重名问题
|
||
def create_uuid():
|
||
# 生成当前时间
|
||
nowTime = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
|
||
# 生成的随机整数n，其中0<=n<=100
|
||
randomNum = random.randint(0, 100)
|
||
if randomNum <= 10:
|
||
randomNum = str(0) + str(randomNum)
|
||
uniqueNum = str(nowTime) + str(randomNum)
|
||
return uniqueNum
|
||
|
||
# 接受前端传来的文件
|
||
def upload_excel(request):
|
||
# 文件夹地址
|
||
file_dir = os.path.join(basedir, '../upload')
|
||
# 文件夹不存在就创建
|
||
if not os.path.exists(file_dir):
|
||
os.makedirs(file_dir)
|
||
# 获取文件
|
||
f = request.files['excel']
|
||
# 文件格式判断拦截器
|
||
if f and allowed_file(f.filename):
|
||
fname = secure_filename(f.filename)
|
||
ext = fname.rsplit('.', 1)[1]
|
||
# 生成新文件名
|
||
new_filename = create_uuid() + '.' + ext
|
||
# 保存文件
|
||
f.save(os.path.join(file_dir, new_filename))
|
||
return {'errcode': 200, 'filename': new_filename}
|
||
else:
|
||
return {'errcode': 700, 'errmsg': '文件格式不符'}
|
||
|
||
|
||
# 函数用途：用于将含有“ ”的字符串，以“ ”分隔成数组形式
|
||
# 参数：str: string
|
||
# 返回值：arr: list
|
||
def strToArr(str):
|
||
return str.split(" ")
|
||
|
||
# 加载excel文件
|
||
def loadData(file_name, sheet_index):
|
||
# 根据文件名加载文件
|
||
addr_cache = '/app/upload/' + file_name
|
||
wb = load_workbook(addr_cache)
|
||
# 根据参数获取sheet
|
||
ws = wb[wb.sheetnames[sheet_index]]
|
||
# 计数器
|
||
index = 0
|
||
# 数据表
|
||
data_list = []
|
||
for row in ws.rows:
|
||
# 排除表头
|
||
if index != 0:
|
||
data = {}
|
||
# 关键词
|
||
data['keywords'] = row[0].value
|
||
# 内容
|
||
data['content'] = row[1].value
|
||
# 浏览量
|
||
data['num'] = row[2].value
|
||
# 日期
|
||
data['date'] = row[3].value
|
||
# 标题
|
||
data["news"] = {}
|
||
data["news"]["title"] = row[4].value
|
||
# 摘要
|
||
data["news"]["digest"] = row[5].value
|
||
# 更新时间
|
||
data["news"]["update_time"] = row[6].value
|
||
# 封面图链接
|
||
data["news"]["cover"] = row[7].value
|
||
# 图文链接
|
||
data["news"]["url"] = row[8].value
|
||
# 图文类型
|
||
data["news"]["type"] = strToArr(row[9].value) if isinstance(row[9].value,str) else []
|
||
data_list.append(data)
|
||
index += 1
|
||
return data_list
|
||
|
||
# 组织记录内容
|
||
def packageData(data):
|
||
return {
|
||
'keywords': data['keywords'],
|
||
'content': data['content'],
|
||
'num': data['num'],
|
||
'date': data['date'],
|
||
'news': {
|
||
"title": data["news"]["title"],
|
||
"digest": data["news"]["digest"],
|
||
"update_time": data["news"]["update_time"],
|
||
"cover": data["news"]["cover"],
|
||
"url": data["news"]["url"],
|
||
"type": data["news"]["type"],
|
||
}
|
||
}
|
||
|
||
# 插入到数据库中
|
||
def insertToDB(data_list):
|
||
# 插入错误列表
|
||
err_list = []
|
||
for data in data_list:
|
||
ins_content = packageData(data)
|
||
res = insertKeyword(ins_content)
|
||
# 插入失败处理
|
||
if res['errcode'] != 200:
|
||
res['keywords'] = ins_content['keywords']
|
||
res['content'] = ins_content['content']
|
||
err_list.append(res)
|
||
if len(err_list) != 0:
|
||
return {'errcode': 701, 'errmsg': 'keyword插入失败', 'err_list': err_list}
|
||
return {'errcode': 200, 'errmsg': 'ok'}
|
||
|
||
# 创建工作簿
|
||
def createWorkBook(data_list):
|
||
try:
|
||
# 实例化
|
||
wb = Workbook()
|
||
# 激活 worksheet
|
||
ws = wb.active
|
||
index = 1
|
||
# 加上表头
|
||
data_list.insert(0, packageTitle())
|
||
# 遍历数据表
|
||
for keywords in data_list:
|
||
# 第一列放关键词
|
||
ws.cell(row=index, column= 1).value = keywords['keywords']
|
||
# 第二列放内容
|
||
ws.cell(row=index, column= 2).value = keywords['content']
|
||
# 浏览量
|
||
ws.cell(row=index, column= 3).value = keywords["num"]
|
||
# 日期
|
||
ws.cell(row=index, column= 4).value = keywords["date"]
|
||
# 标题
|
||
ws.cell(row=index, column= 5).value = keywords["news"]["title"]
|
||
# 摘要
|
||
ws.cell(row=index, column= 6).value = keywords["news"]["digest"]
|
||
# 更新时间
|
||
ws.cell(row=index, column= 7).value = keywords["news"]["update_time"]
|
||
# 封面图
|
||
ws.cell(row=index, column= 8).value = keywords["news"]["cover"]
|
||
# 链接
|
||
ws.cell(row=index, column= 9).value = keywords["news"]["url"]
|
||
# 类型
|
||
ws.cell(row=index, column= 10).value = ' '.join(keywords["news"]["type"])
|
||
index += 1
|
||
# 获取一个唯一的文件名
|
||
new_filename = create_uuid() + '.xlsx'
|
||
addr_cache = '/app/saveExcel/' + new_filename
|
||
# 保存到指定地址，用nginx暴露这个文件夹映射到/excel/get
|
||
wb.save(addr_cache)
|
||
# 返回给前端的相对地址
|
||
addr = '/excel/get/' + new_filename
|
||
except Exception as e:
|
||
print(e)
|
||
return {'errcode': 900, 'errmsg': '创建工作簿失败'}
|
||
return {'errcode': 200, 'errmsg': 'ok', 'addr': addr}
|
||
|
||
# 获取当前数据库中的信息
|
||
def getkeywordsList():
|
||
# 获取所有记录
|
||
res_list = getAllKeywordList()
|
||
# 获取失败拦截器
|
||
if res_list['errcode'] != 200:
|
||
return res_list
|
||
# 获取到记录集合
|
||
arr = res_list['arr']
|
||
|
||
return {'errcode': 200, 'errmsg': 'ok', 'data_list': arr}
|
||
|
||
# 组织表头
|
||
def packageTitle():
|
||
return {
|
||
'keywords': '关键词（多个关键词以空格隔开）',
|
||
'content': '内容',
|
||
'num': '浏览量',
|
||
'date': '日期',
|
||
'news': {
|
||
"title": '图文标题',
|
||
"digest": '图文摘要',
|
||
"update_time": '图文更新时间',
|
||
"cover": '图文封面链接',
|
||
"url": '图文链接',
|
||
"type": '图文类型',
|
||
}
|
||
} |