Blog.csdn.net/qq_38391187…
use
A simple demo that uses only ONE HTML and does not require any other configuration
< link rel = "stylesheet" href = "https://unpkg.com/[email protected]/dist/xspreadsheet.css" > < script SRC = "https://unpkg.com/[email protected]/dist/xspreadsheet.js" > < / script > < script SRC = "https://unpkg.com/[email protected]/dist/locale/zh-cn.js" > < / script > < div id = "X-ray spreadsheet - demo" > < script > x_spreadsheet.locale('zh-cn'); Htmlout = document.getelementById (' spreadsheet-demo') var xs = x_spreadsheet(htmlOut </script> </body> </div>)Copy the code
The problem now is how to load the JSON data from the plug-in and handle the saving. It should be ok to export the data to Excel.
Data is read from the database and parsed into tables
Let’s look at an example of loading JSON
< link rel = "stylesheet" href = "https://unpkg.com/[email protected]/dist/xspreadsheet.css" > < script SRC = "https://unpkg.com/[email protected]/dist/xspreadsheet.js" > < / script > < script SRC = "https://unpkg.com/[email protected]/dist/locale/zh-cn.js" > < / script > < div id = "X-ray spreadsheet - demo" > < script > x_spreadsheet.locale('zh-cn'); Var htmlOut = document.getElementById(' spreadsheet-demo') data = [{"name":"Sheet1","freeze":"A1","styles":[],"merges":[], "rows":{ "0":{"cells":{"0":{"text":"id"},"1":{"text":"name"}}}, "1":{"cells":{"0":{"text":"1"},"1":{"text":"Tom"}}}, "2":{"cells":{"0":{"text":"2"},"1":{"text":"Hall"}}}, "3":{"cells":{"0":{"text":"3"},"1":{"text":"Sure"}}}, "len":5}, "cols":{"len":6}, "validations":[], "Autofilter ":{}}] var xs = x_spreadsheet(htmlOut). LoadData (data) console.log(" Autofilter ":{}}] var xs = x_spreadsheet(htmlout). LoadData (data) console.log(" autofilter":{}}] var xs = X_spreadsheet (htmlout). \n", xs.getData()) // getData gets an object, to convert it to JSON use json.stringify () console.log("json string format: \n", JSON.stringify(xs.getData())) </script> </div>Copy the code
In the example above, the json format is different from that of the database, so if we want to display the direct JSON style from the database, we need to write a conversion function of our own. We can do this on the back end or the front end, so we’ll do it on the back end.
Suppose you get a DF object from your database and now concatenate it into a JSON string of this form.
We find that the rows and columns specified in JSON are added first, so we specify one more row than the database data.
Now use Python to implement the format conversion: dataframe -> dict -> dict2 -> list
The format of the dict2
This is a four-dimensional dictionarydict2['0']['cells']['0']['text']
You can access the first data, which is the ID, but the data is actually two-dimensional, two and four dimensions are fixed ‘cells’ and’ text ‘.
Scheme 1: Use string concatenation directly
Def df2xspreadsheetjson(df) -> STR: "" df object into x- Spreadsheet format json string :param df: dataframe from database :return: Cols = [] # for col in df.columns: if col == "": Elif col is None: cols.append("NULL") # else: cols.append("N/A") # Cols.append (col) # print(cols) df.columns = cols if df.columns [0] < 1 or df.columns [1] < 1: Info += '\"0\":{\"cells\":{' for I in range(len(metrics)): if i ! = len(metrics)-1: info += '\"' + str(i) + '\":' + '{\"text\":\"' + str(metrics[i]) + "\"}," else: info += '\"' + str(i) + '\":' + '{\"text\":\"' + str(metrics[i]) + "\"}" info += '}},' for index, row in df.iterrows(): info += '\"'+str(index+1) + '\":' for j in range(len(metrics)): if j == 0: info += "{\"cells\":{" # print(row[col]) if j ! = len(metrics)-1: info += '\"' + str(j)+'\":' + '{\"text\":\"' + str(row[metrics[j]]) + "\"}," else: info += '\"' + str(j) + '\":' + '{\"text\":\"' + str(row[metrics[j]]) + "\"}" info += '}},' rows = '{' + info + "len:" + STR (df.shape[0]+2) + '}' # Coupled with a line of best + 1 + 1 = 'cols cols \ ": {\" len \ ":" + STR (df) shape [1] + 1) +'}} 'return rows # test data = {' fruit' : [' apple ', 'pear', 'strawberry'], 'number: [3, 2, 2], and' price ': [10, 9, None]} data2 = {" 0 ": {" cells" : {" 0 ": {" text" : "id"}, "1" : {" text ": "name"}}}, "1": {"cells": {"0": {"text": "1"}, "1": {"text": "Tom"}}}, "2": {"cells": {"0": {"text": "2"}, "1": {"text": "Hall"}}}, "3": {"cells": {"0": {"text": "3"}, "1": {"text": "Sure"}}}} df = pd.DataFrame(data) print(df) s = df2xspreadsheetjson(df) print(s)Copy the code
Plan two: Use multidimensional dict
TODO
Copy the code
Try using the result of the Python script we just created:
< link rel = "stylesheet" href = "https://unpkg.com/[email protected]/dist/xspreadsheet.css" > < script SRC = "https://unpkg.com/[email protected]/dist/xspreadsheet.js" > < / script > < script SRC = "https://unpkg.com/[email protected]/dist/locale/zh-cn.js" > < / script > < div id = "X-ray spreadsheet - demo" > < script > x_spreadsheet.locale('zh-cn'); Var htmlOut = document.getElementById(' spreadsheet-demo') data = [{"name":"Sheet1","freeze":"A1","styles":[],"merges":[], "rows":{ "0":{"cells":{"0":{"text":"id"},"1":{"text":"name"}}}, "1":{"cells":{"0":{"text":"1"},"1":{"text":"Tom"}}}, "2":{"cells":{"0":{"text":"2"},"1":{"text":"Hall"}}}, "3":{"cells":{"0":{"text":"3"},"1":{"text":"Sure"}}}, "len":5 }, "cols":{"len":6}, "validations":[], "autofilter":{}}] rows2 = {" 0 ": {" cells" : {" 0 ": {" text", "fruit"}, "1" : {" text ", "number"}, "2" : {" text ", "price"}}}, "1" : {" cells ": {" 0" : {" text ":" apple "}, "1" : {" text ", "3"}, "2" : {" text ", "10.0"}}}, "2" : {" cells ": {" 0" : {" text ", "pear"}, "1" : {" text ":" 2 "}, "2" : {" text ":" 9.0 "}}}, "3" : {" cells ": {" 0" : {" text ", "strawberry "},"1":{"text":"2"},"2":{"text":"nan"}}},len:5} data2 = {"rows":rows2, "Cols ":{"len":4}} var xs = x_spreadsheet(htmlout).loadData(data2) console.log(" \n", xs.getData()) // getData gets an object, to convert it to JSON use json.stringify () console.log("json string format: \n", JSON.stringify(xs.getData())) </script> </div>Copy the code
Open the HTML file directly and find that you can generate charts smoothly.
Use xspread-sheet in flask
Create a New Flask project
pip install flask, flask_script
mkdir flaskSheet
cd flaskSheet
flask run
Copy the code
This is a standard Flask demo. Create utils/trans.py in the app folder
Import pandas as pd def df2xspreadsheetjson(df) -> STR: "" Dataframe :return: STR '' cols = [] # for col in df.columns: if col == "": Elif col is None: cols.append("NULL") # else: cols.append("N/A") # Cols.append (col) # print(cols) df.columns = cols if df.columns [0] < 1 or df.columns [1] < 1: Info += '\"0\":{\"cells\":{' for I in range(len(metrics)): if i ! = len(metrics)-1: info += '\"' + str(i) + '\":' + '{\"text\":\"' + str(metrics[i]) + "\"}," else: info += '\"' + str(i) + '\":' + '{\"text\":\"' + str(metrics[i]) + "\"}" info += '}},' for index, row in df.iterrows(): info += '\"'+str(index+1) + '\":' for j in range(len(metrics)): if j == 0: info += "{\"cells\":{" # print(row[col]) if j ! = len(metrics)-1: info += '\"' + str(j)+'\":' + '{\"text\":\"' + str(row[metrics[j]]) + "\"}," else: info += '\"' + str(j) + '\":' + '{\"text\":\"' + str(row[metrics[j]]) + "\"}" info += '}},' rows = '{' + info + "len:" + STR (df) shape [0] + 2) + '} '# add title length, coupled with a line of best + 1 + 1 =' cols cols \ ": {\" len \ ":" + STR (df) shape [1] + 1) + '}} 'return rowsCopy the code
Create a new __init__.py file here as well. Create demo.py in the app folder
from flask import Flask, render_template
from flask import request
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate,MigrateCommand
from flask_script import Shell,Manager
import json
import pandas as pd
from utils.trans import df2xspreadsheetjson
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql://XXXX:XXXXXXX@localhost:3306/mydb"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
app.config['WTF_CSRF_ENABLED'] = False
db = SQLAlchemy(app)
migrate = Migrate(app,db)
manager = Manager(app)
manager.add_command('db',MigrateCommand)
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True, index=True)
age = db.Column(db.Integer, default=18)
def __repr__(self):
return 'User:%s'%self.name
@app.route('/show')
def demo():
# user = User.query.all()
# name = user.name
# age = user.age
# data = {
# "name": name,
# "age": age
# }
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql://user:passwd@localhost:3306/mydb?charset=utf8mb4")
conn = engine.connect()
data = pd.read_sql("select * from users limit 100",conn)
data = df2xspreadsheetjson(data)
return render_template("index.html", data=data)
@app.route('/login', methods=["GET", "POST"])
def login():
if request.method == "POST":
username = request.form.get("username")
userage = request.form.get("userage")
user = User(name=username, age=userage)
db.session.add(user)
db.session.commit()
return render_template("login.html")
if __name__ == '__main__':
db.create_all()
app.run(debug=True)
manager.run()
Copy the code
Go to the templates folder and create index.html
<! DOCTYPE HTML > < HTML lang="en"> <head> <meta charset="UTF-8"> <title> homepage </title> </head> <link rel="stylesheet" Href = "https://unpkg.com/[email protected]/dist/xspreadsheet.css" > < script SRC = "https://unpkg.com/[email protected]/dist/xspreadsheet.js" > < / script > < script SRC = "https://unpkg.com/[email protected]/dist/locale/zh-cn.js" > < / script > < body > < h1 > x - spreadsheet < / h1 > <! To enable JS to read data, Put it in a container --> <div id=" datAID "d ="{{data}}" style="display:none"></div> <div id="x-spreadsheet-demo"> <script> var daer= document.getElementById('dataid').getAttribute('d') console.log(daer) x_spreadsheet.locale('zh-cn'); Var htmlOut = document.getelementById (' spreadsheet-demo') rows2= eval('(' + daer+ ')'); Data2 = [{"name":" table ", "rows":rows2, Var xs = x_spreadsheet(htmlout). LoadData (data2) console.log(" cols":{"len":20}}] \n", xs.getData()) // getData gets an object, to convert it to JSON use json.stringify () console.log("json string format: \n", JSON.stringify(xs.getData())) </script> </div> </body> </html>Copy the code
A new login. HTML
<! DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Xiaotaotao</title> </head> <body> <form </label><input type="text" name="username" placeholder=" placeholder "><br/> <label> </label><input type="password" name="userage" placeholder=" placeholder "><br/> < form> </body> </html>Copy the code
Finally, run Python demo.py
In the browser, open localhost:5000/login and write data to the database. Localhost :5000/show displays the data. The effect is as follows:
Loading and rendering 10000 rows of data takes about 3 seconds, not bad. 1000 rows with x without y delay. Since the X spreadsheet is editable, we will continue to study how to save the modified data.