首先声明没有用java,因为不会。在网上找了很久,在github上找到了基于pywss的web框架。地址在下面,第一个地址是pywss介绍,轻量级的python web框架,pywss 通过WebSocketUpgrade
完成通讯升级,第二个地址是luckysheet项目。
GitHub - czasg/pywss: 一个轻量级的 Python Web 框架
GitHub - czasg/LuckySheetServer: luckysheet 多人在线协同编辑 python 实现
在此的基础上我要做两件事,第一件事在打开网页的时候导入数据库,通过“celldata”:jsonData,将数据导入luckysheet,代码如下。
def DataToJson():
conn = pymysql.Connect(host='127.0.0.1', port=3306, user='*****', passwd='******', charset='utf8', db='unicom')
# 设置自己的mysql信息
cur = conn.cursor()
sql = "select * from testlog" # 选择具体的数据库db='unicom'下的testlog
cur.execute(sql)
data = cur.fetchall()
cur.close()
conn.close()
jsonData = []
for i, cell in enumerate(data):
for j, value in enumerate(cell):
result = {}
result['r'] = int(i)
result['c'] = int(j)
result['v'] = str(value)
jsonData.append(result)
return (jsonData)
# luckysheet的要求导出格式
def load(ctx: pywss.Context):
jsonData = DataToJson()
data = json.dumps([
{
"name": "Cell",
"index": "sheet_01",
"order": 0,
"status": 1,
"celldata": jsonData,
}]) # json.dumps将一个Python数据结构转换为JSON
# json.dumps()用于将dict类型的数据转成str
ctx.write(data) # 写入json的数据
第二件事是在关闭网页的时候,将数据导出到数据库。前端采用jquery下的$.ajax,后端用python flask接收数据。注意luckysheet采用的js,是jquery,已经包含在了luckysheet.umd.js。数据已经给到了python后端,数据导出为test.xlsx,最后test.xlsx导出到mysql。
数据先导出到.xlsx是因为我觉得luckysheet.getallsheets()的数据和openpyxl的数据很像,然后通过sqlalchemy和pandas将.xlsx导入到mysql数据库。index.html和**.py代码如下。
**.py
import openpyxl
from openpyxl import load_workbook
import os ,time
from urllib.parse import quote_plus as urlquote
from sqlalchemy import create_engine
import pandas as pd
import json
from flask import Flask, request
app = Flask(__name__)
def excel_to_mysql():
userName = "root"
password = "*******"
dbHost = "127.0.0.1"
dbPort = 3306
dbName = "unicom"
engine = create_engine(f'mysql+pymysql://{userName}:{urlquote(password)}@{dbHost}:{dbPort}/{dbName}?charset=utf8')
data_frame = pd.read_excel('test.xlsx')
data_frame = data_frame.where(data_frame.notnull(), '')
data_frame.to_sql(name='testlog', con=engine, index=False, if_exists='replace')
@app.route('/test',methods=['GET','POST'])
def index():
if request.method == "POST":
a = request.form.get('exportdata')
b = json.loads(a)
for key, data in b.items():
if key == 'celldata':
list1 = []
for i in data:
for j in i['v']:
if j == 'm':
row = i['r'] + 2
col = i['c'] + 1
value = i['v']['m']
list1.extend([[row, col, value]])
#print(list1)
if os.path.exists('test.xlsx') == False:
workbook = openpyxl.Workbook()
sheet = workbook.active
for i in list1:
sheet.cell(i[0], i[1]).value = i[2]
workbook.save('test.xlsx')
time.sleep(0.1)
excel_to_mysql()
if os.path.exists('test.xlsx') == True:
os.remove('test.xlsx')
workbook = openpyxl.Workbook()
sheet = workbook.active
for i in list1:
sheet.cell(i[0], i[1]).value = i[2]
workbook.save('test.xlsx')
time.sleep(0.1)
excel_to_mysql()
if __name__ == '__main__':
app.run(host="127.0.0.1", port=5000)
index.html(html上的日志按钮,是我在浏览器后台看数据的时候加的console.log())
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link rel='stylesheet' href='/static/css/pluginsCss.css'/>
<link rel='stylesheet' href='/static/css/plugins.css'/>
<link rel='stylesheet' href='/static/css/luckysheet.css'/>
<link rel='stylesheet' href='/static/css/iconfont.css'/>
<script src="/static/js/plugin.js"></script>
<script src="/static/js/luckysheet.umd.js"></script>
</head>
<body>
<div id="lucky" style="margin:0px;padding:0px;position:absolute;width:100%;height:100%;left: 0px;top: 20px;"></div>
</body>
<script>
var options = {
container: 'lucky',
title: "Pywss",
lang: 'zh',
allowUpdate: true,
loadUrl: "/luckysheet/api/loadUrl",
updateUrl: `ws://${document.location.host}/luckysheet/api/updateUrl`,
functionButton: '<button id="" class="btn btn-primary btn-danger" style=" padding:3px 6px; font-size: 12px; margin-right: 85px;" onclick="testlog()">日志</button>',
}
$(function () {luckysheet.create(options)})
function testlog() {console.log(luckysheet.getAllSheets()[0])}
$(window).bind('unload', function() {
$.ajax({
url: 'http://127.0.0.1:5000/test',
method: 'POST',
data: {exportdata: JSON.stringify(luckysheet.getAllSheets()[0])},
});
})
</script>
</html>
基于此,实现了局域网下的多人协同办公表格,和微信上的在线表格类似的使用感受,但是是局域网的。注意luckysheet的js文件和css文件均从本地导入。使用的web框架不同,js,css文件存放的位置不同,比如static和templates。最后把mysql(navicat for mysql)也放一下,部分截图。