js
String.prototype.format = function (args) {
var result = this;
if (arguments.length > 0) {
if (arguments.length === 1 && typeof (args) == "object") {
for (var key in args) {
if (args[key] !== undefined) {
var reg = new RegExp("({" + key + "})", "g");
result = result.replace(reg, args[key]);
}
}
} else {
for (var i = 0; i < arguments.length; i++) {
if (arguments[i] !== undefined) {
var reg = new RegExp("({)" + i + "(})", "g");
result = result.replace(reg, arguments[i]);
}
}
}
}
return result;
};
//新增
window.onload = function appendrow() {
var tbdDom = document.getElementById('tbd');
var tbnDom = document.getElementById('tbn');
var x = "";
tbnDom.onclick = function () {
for (i = 0; i < 6; i++) {
x += "<td style=\"text-align: center\"><input style=\"width:100%\" id={ddd} type=\"text\" ></td>".format({"ddd": i});
}
x += "<td style=\"text-align: center\"><input class=\"btn btn-info\" style='background-color:green' type=\"button\" onclick='delettr(this)' value=\"取消\"></td><td style=\"text-align: center\" ><input class=\"btn btn-info\" style='background-color:red' type=\"button\" onclick='save_row(this)' value=\"保存\"></td></tr>";
tbdDom.innerHTML = "<tr id='tr'>" + x;
x = ""
}
};
function delettr(obj) {
var tbdom = document.getElementById('tbd');
var trDom = document.getElementById('tr');
tbdom.removeChild(trDom)
}
function save_row(obj) {
var valuedic = [];
for (i = 0; i < 6; i++) {
valuedic[i] = document.getElementById(i).value;
}
$.ajax({
type: "POST",
url: '/edit/appendrow/',
data: JSON.stringify({valuedic: valuedic},),
success: function (args) {
alert(args.msg);
delettr(obj);
}
});
}
// 查询
function query_result() {
var tbodyDom = document.getElementById('tbd');
var x = "{% for row in rowlist %}<tr>{% for val in row %}<td>{{ val }}</td>{% endfor %}</tr>{% endfor %}";
var select_text = [];
for (i = 0; i < 6; i++) {
var options = $('#selected{id} option:selected'.format({'id': i + 1}));
values = options.val();
// console.log(values);
select_text[i] += values;
}
// console.log(select_text);
$.ajax({
type: "POST",
url: "/edit/",
data: JSON.stringify({selected_titles: select_text},),
success: function (data) {
// alert(data.msg[2]);
var value_list = data.msg;
// alert(data.msg.length);
var z = '';
for (j = 0; j < data.msg.length; j++) {
var y = "<tr>";
for (k = 0; k < 6; k++) {
y += "<td >" + value_list[j][k] + "</td>"
}
y += "<td style=\"display:none;\">" + value_list[j][6] + "</td><td style=\"text-align: center\" ><input class=\"btn btn-info\" style='background-color:forestgreen' type=\"button\" onclick='change_row(this)' value=\"修改\"></td><td style=\"text-align: center\"><input class=\"btn btn-info\" style='background-color:red' type=\"button\" onclick='delet_row(this)' value=\"删除\"></td></tr>";
z += y;
}
tbodyDom.innerHTML = z;
}
});
}
var change_num = 0;
var trHtml = '';
//修改某一行
function change_row(obj) {
if (change_num === 0) {
var htdDom = obj.parentElement.previousSibling;
var trDom = obj.parentElement.parentElement;
console.log(htdDom.tagName);
console.log(trDom.innerHTML);
trHtml = trDom.innerHTML;
var x = "";
for (i = 0; i < 6; i++) {
x += "<td style=\"text-align: center\"><input style=\"width:100%\" type=\"text\" value='{value}' ></td>".format({value: trDom.childNodes[i].innerText});
}
x = x + "<td style=\"display:none;\">" + htdDom.innerText + "</td>";
trDom.innerHTML = x + "<td style=\"text-align: center\"><input class=\"btn btn-info\" style='background-color:green' type=\"button\" onclick='cenceltr(this)' value=\"取消\"></td><td style=\"text-align: center\" ><input class=\"btn btn-info\" style='background-color:red' type=\"button\" onclick='save_change(this)' value=\"保存\"></td></tr>";
change_num += 1;
} else {
alert('请逐个条修改！')
}
}
function cenceltr(obj) {
var trDom = obj.parentElement.parentElement;
//使用正则删除多出来的font标签
trDom.innerHTML = trHtml.replace(/<\/?font[^>]*>/gi, "");
change_num = 0;
}
function save_change(obj) {
change_num = 0;
var htdDom = obj.parentElement.previousSibling.previousSibling;
var trDom = obj.parentElement.parentElement;
console.log(htdDom.tagName);
console.log(trDom.getElementsByTagName('input')[1].value);
console.log(htdDom.innerText);
var row_value = [];
for (i = 0; i < 6; i++) {
row_value[i] = trDom.getElementsByTagName('input')[i].value
}
row_value[6] = htdDom.innerText;
console.log(row_value);
if (confirm('确定保存修改吗？')) {
$.ajax({
type: 'POST',
url: '/edit/changerow/',
data: JSON.stringify({row_value: row_value}),
success: function (data) {
alert(data.msg);
var x = '';
for (i = 0; i < 6; i++) {
x += "<td>{value}</td>".format({value: trDom.getElementsByTagName('input')[i].value})
}
x = x + "<td style=\"display:none;\">" + htdDom.innerText + "</td>";
x += "<td style=\"text-align: center\" ><input class=\"btn btn-info\" style='background-color:forestgreen' type=\"button\" onclick='change_row(this)' value=\"修改\"></td><td style=\"text-align: center\"><input class=\"btn btn-info\" style='background-color:red' type=\"button\" onclick='delet_row(this)' value=\"删除\"></td>";
trDom.innerHTML = x;
}
})
}
}
//删除一行
function delet_row(obj) {
var htdDom = obj.parentElement.previousSibling.previousSibling;
var trDom = obj.parentElement.parentElement;
console.log(htdDom.tagName);
console.log(trDom.innerHTML);
trHtml = trDom.innerHTML;
var row_value = [];
for (i = 0; i < 6; i++) {
row_value[i] = trDom.childNodes[i].innerText;
}
row_value[6] = htdDom.innerText;
console.log(row_value);
if (confirm('确定删除吗？')) {
$.ajax({
type: "POST",
url: "/edit/deleterow/",
data: JSON.stringify({row_value: row_value}),
success: function (data) {
alert(data.msg);
trDom.remove()
}
})
}
}
base.html
<!DOCTYPE html>
<html lang="en">
{% load staticfiles %}
<head>
<meta charset="UTF-8">
<title>表格文档管理系统</title>
<script src="{% static 'js/editjs.js' %} " type="text/javascript "></script>
<link href="{% static 'css/bootstrap.min.css' %}" rel="stylesheet">
<link href="{% static 'css/font-awesome.min.css' %}" rel="stylesheet">
<style type="text/css">
</style>
</head>
<body>
{% block content %}
{% endblock %}
<script src="{% static 'js/jquery-3.4.1.min.js' %}" type="text/javascript"></script>
<script type="text/javascript" src="{% static 'js/bootstable.js' %}"></script>
<script>
</script>
</body>
</html>
index.html
{% extends 'base.html' %}
{% block content %}
<div class="container">
<div class="row" >
<div>
<h1>物料管理系统</h1>
<a href="{% url 'edit:addpage' %}"><button class="btn btn-info" id="addbtn"> 新增</button></a>
</div>
<div class="col-md-12" style="padding:2em 0;">
<div class="table-responsive">
<table class="table table-bordered tab-content" id="mytable">
<thead>
<tr valign="middle">
{% for title in titles %}
<th style="text-align: center">{{ title }}</th>
{% endfor %}
<td rowspan="2" style="text-align:center;vertical-align:middle;">
<button class="btn btn-info" id="btn" onclick="query_result() "> 查询</button>
</td>
<td rowspan="2" style="text-align:center;vertical-align:middle;">
<button class="btn btn-info" id="tbn"> 新增</button>
</td>
</tr>
<tr >
{% for cols in titles_list %}
<td style="text-align: center">
<select style="width:100%" name="title" id="selected{{ forloop.counter }}">
<option value='' selected=""></option>
{% for col in cols %}
<option value={{ col }}>{{ col }}</option>
{% endfor %}
</select>
</td>
{% endfor %}
</tr>
</thead>
<tbody id="tbd">
</tbody>
</table>
</div>
</div>
</div>
</div>
{% endblock %}
view.py
import json
import os
import time
import pythoncom
import xlwings as xw
import xlrd
from django.http import JsonResponse
from django.shortcuts import render
# Create your tests here.
from utils import util
from utils.util import equals, Myapp, stablequal
path = 'C:\\Users\Administrator\Desktop\\first_project\ExcelOnline\storybook.xlsx'
sheet_name = 'story'
def index(request):
'''
查询首页
:param request:
:return:
'''
print('-----------------------------------')
if request.method == 'GET':
start_time = time.time()
xbook = xlrd.open_workbook(path)
xtable = xbook.sheet_by_name(sheet_name)
cols_list = []
for i in range(xtable.ncols):
cols = xtable.col_values(i, start_rowx=1)
col_values = []
for j in cols:
if j not in col_values and j != '':
col_values.append(j)
cols_list.append(col_values)
titles = xtable.row_values(0)
end_time = time.time()
print('加载耗时：', end_time - start_time)
return render(request, 'index.html', {'titles_list': cols_list, 'titles': titles})
if request.method == 'POST':
selected_titles = json.loads(request.body.decode('utf-8'))['selected_titles']
for i in range(len(selected_titles)):
if 'undefined' in selected_titles[i]:
selected_titles[i] = selected_titles[i].replace('undefined', '')
print(selected_titles)
start_time = time.time()
xbook = xlrd.open_workbook(path)
xtable = xbook.sheet_by_name(sheet_name)
row_list = []
for i in range(1, xtable.nrows):
row_values = [str(j) for j in xtable.row_values(i, start_colx=0, end_colx=None)]
if equals(row_values, selected_titles):
row_values.append(i + 1)
row_list.append(row_values)
end_time = time.time()
print('查询耗时：', end_time - start_time)
print(row_list)
return JsonResponse(data={'msg': row_list})
def add_page(request):
if request.method == 'GET':
start_time = time.time()
xbook = xlrd.open_workbook(path)
xtable = xbook.sheet_by_name(sheet_name)
cols_list = []
for i in range(2):
cols = xtable.col_values(i, start_rowx=1)
col_values = []
for j in cols:
if j not in col_values and j != '':
col_values.append(j)
cols_list.append(col_values)
titles = xtable.row_values(0)
end_time = time.time()
print('加载耗时：', end_time - start_time)
return render(request, 'addpage.html', {'titles_list': cols_list, 'titles': titles})
def append_row(request):
'''
新增一行
:param request:
:return:
'''
if request.method == 'POST':
print('过来')
# print(request.POST)
# print(type(request.POST))
values_lsit = json.loads(request.body.decode('utf-8'))['valuedic']
print(values_lsit)
result = [False if i == '' else True for i in values_lsit]
if not True in result:
data = {'msg': '没有写入内容！'}
else:
pythoncom.CoInitialize()
app = Myapp()
start_time = time.time()
xbook = app.books.open(path)
xtable = xbook.sheets(sheet_name)
info = xtable.used_range
last_row = str(info.last_cell.row + 1)
print('last_row:', last_row)
xtable.range('a' + last_row).value = values_lsit
xbook.save()
xbook.close()
app.kill()
util.app_list = []
end_time = time.time()
print('添加耗时：', end_time - start_time)
data = {'msg': '添加成功！'}
return JsonResponse(data)
def change_row(request):
'''
修改某一行
:param request:
:return:
'''
if request.method == "POST":
row_value = json.loads(request.body.decode('utf-8'))['row_value']
pythoncom.CoInitialize()
app = Myapp()
start_time = time.time()
xbook = app.books.open(path)
xtable = xbook.sheets(sheet_name)
row_num = row_value[-1]
rows = xtable.range('a' + row_num + ':f' + row_num).value
row_list = ['' if not rows[i] else str(rows[i]) for i in range(len(rows))]
print('row_value:', row_value[:-1])
print('row_list:', row_list)
if stablequal(row_list, row_value[:-1]):
print(stablequal(row_list, row_value[:-1]))
xbook.save()
xbook.close()
app.kill()
util.app_list = []
end_time = time.time()
print('修改耗时：', end_time - start_time)
return JsonResponse({'msg': '没有任何改动！'})
else:
xtable.range('a' + row_num + ':f' + row_num).value = row_value[:-1]
xbook.save()
xbook.close()
app.kill()
util.app_list = []
end_time = time.time()
print('修改耗时：', end_time - start_time)
return JsonResponse({'msg': '更新成功！'})
def delete_row(request):
'''
删除某一行
:param request:
:return:
'''
if request.method == "POST":
row_value = json.loads(request.body.decode('utf-8'))['row_value']
pythoncom.CoInitialize()
app = Myapp()
start_time = time.time()
xbook = app.books.open(path)
xtable = xbook.sheets(sheet_name)
row_num = row_value[-1]
print(xtable.range('a' + row_num + ':f' + row_num).value)
xtable.range('a' + row_num + ':f' + row_num).clear()
xbook.save()
xbook.close()
app.kill()
util.app_list = []
end_time = time.time()
print('删除耗时：', end_time - start_time)
return JsonResponse({'msg': '删除成功！'})
url.py
import Edit
from Edit import views
app_name = 'edit'
urlpatterns = [
path('', views.index, name='index'),
path('appendrow/', Edit.views.append_row, name='appendrow'),
path('changerow/', Edit.views.change_row, name='changerow'),
path('deleterow/', Edit.views.delete_row, name='deleterow'),
path('addpage/', Edit.views.add_page, name='addpage')
]
setting.py
# Internationalization
# https://docs.djangoproject.com/en/2.2/topics/i18n/
LANGUAGE_CODE = 'zh-hans'
TIME_ZONE = 'Asia/Shanghai'
USE_I18N = True
USE_L10N = True
USE_TZ = False
# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/2.2/howto/static-files/
STATIC_URL = '/static/'
STATICFILES_DIRS = [os.path.join(BASE_DIR, 'static')]
# 发送邮件的设置
EMAIL_HOST = 'smtp.126.com'
EMAIL_HOST_USER = 'zhangpengv@126.com'
EMAIL_HOST_PASSWORD = '581321pengv'
EMAIL_PORT = 25
EMAIL_USE_TLS = True
EMAIL_USE_SSL = False # 126,QQ: 465 163:454
util.py
import pythoncom
from ExcelOnline.settings import EMAIL_HOST_USER
def equals(a, b):
'''
搜索逻辑
:param a:
:param b:
:return:
'''
c = {}
for i in range(len(b)):
if b[i] != '': # b是查询关键字列表
c[i] = b[i]
d = []
for key, value in c.items():
if a[key] == value:
d.append(True)
else:
d.append(False)
return False if False in d else True
def stablequal(a, b):
'''
判恒等
:param a:
:param b:
:return:
'''
c = []
for i in range(len(a)):
if len(a) == len(b) and a[i] == b[i]:
c.append(True)
else:
c.append(False)
return False if False in c else True
import xlwings as xw
app_list = []
class Myapp():
def __new__(cls, *args, **kwargs):
print(app_list)
if len(app_list):
app = app_list.pop()
app.kill()
app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = True
app_list.append(app)
return app
else:
app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = True
app_list.append(app)
return app
import uuid
from django.core.mail import send_mail
# 找回密码发送邮件
# def send_email(email, request):
# subject = '找回密码'
# user1 = User.objects.filter(email=email).first()
# ran_code = uuid.uuid4()
# ran_code = str(ran_code)
# ran_code = ran_code.replace('-', '')
# request.session['ran_code'] = user1.id
# message = '''
# 可爱的用户:
# <br>
# 亲爱的%s,
# 点击 <a href='http://127.0.0.1:8000/user/update_psd'>这里</a>可以重置你的密码，
# 如果无法点击，你也可以将下面的地址复制到浏览器中打开:
# <br>
# http://127.0.0.1:8000/user/update_psd
# ''' % (user1.nickname,)
# # 发送邮件send_mail
# result = send_mail(subject, "", EMAIL_HOST_USER, [email, ], html_message=message)
# return result
网友评论