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.