文章目录
- 一、python写的后端代码
- 二、html代码
- 三、index.css
- 四、js代码
效果图:
一、python写的后端代码
后端代码使用Flask编写,如下所示:
# app.py
from flask import Flask, render_template, request, jsonify, g
import sqlite3
import os
from datetime import datetime
app = Flask(__name__)
# ----------------------------------------------------------------------------
# 默认配置与默认函数
# ----------------------------------------------------------------------------
connection = 'example.db' # 连接数据库
@app.route('/')
def index():
return render_template('index.html')
def get_db():
if not hasattr(g, 'db'):
g.db = sqlite3.connect(connection)
g.db.row_factory = sqlite3.Row # 将查询结果以字典形式返回
return g.db
@app.teardown_appcontext
def close_db(error=None):
"""请求结束时关闭数据库连接"""
if hasattr(g, 'db'):
g.db.close()
# 返回当前数据库的最大ID,离线函数
def maxID():
conn1 = get_db()
cursor = conn1.cursor()
try:
cursor.execute("SELECT * FROM projectTable")
rows = cursor.fetchall()
ID = []
for row in rows:
ID.append(int(row[0]))
if ID == []:
ID = [0]
maxid = max(ID)
return maxid
except Exception as e:
return jsonify({"message": "maxID发生错误"}), 500
finally:
# 确保关闭连接
conn1.close()
# ----------------------------------------------------------------------------
# 获得当前表中所有的项目名称(去重过的)
# ----------------------------------------------------------------------------
@app.route('/projectNameOptions', methods=['POST'])
def projectNameOptions():
conn = get_db()
cursor = conn.cursor()
# 表格如果不存在就创建
print("projectNameOptions正在运行……")
cursor.execute("SELECT DISTINCT(projectName) FROM projectTable")
rows = cursor.fetchall()
results = []
for row in rows:
results.append(row['projectName'])
return jsonify({"projects": results})
# ----------------------------------------------------------------------------
# 前端页面默认返回的数据
# ----------------------------------------------------------------------------
@app.route('/default', methods=['POST'])
def default():
conn = get_db()
cursor = conn.cursor()
# 表格如果不存在就创建
print("default正在运行……")
if os.path.exists('example.db'):
print("Database file exists!")
else:
print("Database file does not exist.")
cursor.execute('''
CREATE TABLE IF NOT EXISTS projectTable (
id INTEGER PRIMARY KEY AUTOINCREMENT,
projectName TEXT,
submitPersonName TEXT,
Process TEXT,
StartTime TEXT,
EndTime TEXT,
neirong TEXT
)
''')
cursor.execute("SELECT * FROM projectTable")
rows = cursor.fetchall()
results = []
for row in rows:
results.append({
'id': row['id'],
'projectName': row['projectName'],
'submitPersonName': row['submitPersonName'],
'Process': row['Process'],
'StartTime': row['StartTime'],
'EndTime': row['EndTime'],
"neirong" : row['neirong']
})
print(results)
return jsonify(results)
# ----------------------------------------------------------------------------
# 根据所给条件(projectname专案名称, personname提交人, projectquery专案进度)查询数据
# ----------------------------------------------------------------------------
@app.route('/searchfor', methods=['POST'])
def searchfor():
conn = get_db()
cursor = conn.cursor()
# 返回的数据
data = request.get_json()
print("查询条件 = ", data)
if data["projectname"] == "":
projectquery = "projectName LIKE '%'"
else:
projectquery = "projectName LIKE '%{}%'".format(data["projectname"])
if data["personname"] == "":
personNamequery = "submitPersonName LIKE '%'"
else:
personNamequery = "submitPersonName LIKE '%{}%'".format(data["personname"])
Processquery = "Process LIKE '%{}%'".format(data["stateSelectionValue"])
print(Processquery)
query = "SELECT * FROM projectTable WHERE {} AND {} AND {}".format(projectquery, personNamequery, Processquery)
print("searchFor query:", query)
cursor.execute(query)
rows = cursor.fetchall()
results = []
for row in rows:
results.append({
'id': row['id'],
'projectName': row['projectName'],
'submitPersonName': row['submitPersonName'],
'Process': row['Process'],
'StartTime': row['StartTime'],
'EndTime': row['EndTime'],
"neirong" : row['neirong']
})
print("查询结果:", results)
return jsonify(results)
# ----------------------------------------------------------------------------
# 保存数据
# ----------------------------------------------------------------------------
@app.route('/saveData', methods=['POST'])
def receive_data():
try:
conn = get_db()
cursor = conn.cursor()
data = request.get_json()
# 当前更新的时间
currenttime = datetime.now()
formatted_time = currenttime.strftime("%Y-%m-%d")
if data:
print("收到的表格数据:", data)
# 启动事务
for updated_data in data:
query = '''
UPDATE projectTable
SET
projectName = ?,
neirong = ?,
submitPersonName = ?,
process = ?,
startTime = ?,
endTime = ?
WHERE id = ?
'''
cursor.execute(query, (
updated_data['projectName'],
updated_data['projectNeirong'],
updated_data['submitPersonName'],
updated_data['process'],
updated_data['startTime'],
# formatted_time,
updated_data['endTime'],
updated_data['id']
))
conn.commit()
cursor.execute("SELECT * FROM projectTable")
rows = cursor.fetchall()
print("查询结果==========================================================")
for row in rows:
print(
f"ID: {row[0]}, Project Name: {row[1]}, Submitter: {row[2]}, Process: {row[3]}, Start Time: {row[4]}, End Time: {row[5]}, Neirong: {row[6]}")
print("已更改!")
return jsonify({"message": "数据接收成功", "receivedData": data}), 200
except Exception as e:
# 如果有任何异常,回滚事务
conn.rollback()
print("发生错误:", e)
return jsonify({"message": "处理数据时发生错误"}), 500
finally:
# 确保关闭连接
conn.close()
# ----------------------------------------------------------------------------
# 删除指定的一行
# ----------------------------------------------------------------------------
@app.route('/delectable', methods=['POST'])
def delete_data():
conn = get_db()
cursor = conn.cursor()
# 返回数据示例:{'id': 1, 'flag': True}
data = request.get_json()
if data:
print("收到的表格数据:", data)
try:
if data["flag"]:
query = '''
DELETE FROM projectTable WHERE id = ?
'''
# 使用参数化查询
cursor.execute(query, (data['id'], ))
conn.commit()
cursor.execute("SELECT * FROM projectTable")
rows = cursor.fetchall()
print("查询结果==========================================================")
for row in rows:
print(
f"ID: {row[0]}, Project Name: {row[1]}, Submitter: {row[2]}, Process: {row[3]}, Start Time: {row[4]}, End Time: {row[5]}, Neirong: {row[6]}")
print("已删除!")
return jsonify({"message": "数据接收成功", "receivedData": data}), 200
except Exception as e:
# 如果有任何异常,回滚事务
conn.rollback()
print("发生错误:", e)
return jsonify({"message": "处理数据时发生错误"}), 500
finally:
# 确保关闭连接
conn.close()
else:
return jsonify({"message": "无效的数据"}), 400
# ----------------------------------------------------------------------------
# 插入一行
# ----------------------------------------------------------------------------
@app.route('/insertTable', methods=['POST'])
def insert_data():
print("insertTable正在运行!")
conn = get_db()
cursor = conn.cursor()
data = request.get_json()
print("data = ", data)
# 获得当前时间
currenttime = datetime.now()
formatted_time = currenttime.strftime("%Y-%m-%d")
try:
cursor.execute("SELECT MAX(id) FROM projectTable")
max_id = cursor.fetchone()[0] # 获取查询结果的第一列
if max_id is None:
max_id = 0 # 如果表中没有记录,则最大ID是0
ids = max_id + 1
insertData = {'id': ids, 'projectName': '在这里填写专案名称', 'submitPersonName': '',
'process': '', 'startTime': '2023-11-10', 'endTime': '2023-11-10', 'projectNeirong': '在这里填写专案内容'}
insertData["endTime"] = formatted_time
print(insertData)
query = '''
INSERT INTO projectTable (projectName, neirong, submitPersonName, process, startTime, endTime, id)
VALUES (?, ?, ?, ?, ?, ?, ?)
'''
cursor.execute(query, (
insertData['projectName'],
insertData['projectNeirong'],
insertData['submitPersonName'],
insertData['process'],
insertData['startTime'],
insertData['endTime'],
insertData['id']
))
conn.commit()
cursor.execute("SELECT * FROM projectTable")
rows = cursor.fetchall()
show_data = []
for row in rows:
show_data.append({
'id': row[0], 'projectName': row[1], 'submitPersonName': row[2],
'Process': row[3], 'StartTime': row[4], 'EndTime': row[5],
'neirong': row[6]
})
print("插入操作已执行!")
return jsonify({"message": "数据插入成功", "data": show_data})
except Exception as e:
return jsonify({"message": "处理数据时发生错误"}), 500
finally:
# 确保关闭连接
conn.close()
# ----------------------------------------------------------------------------
# 主函数
# ----------------------------------------------------------------------------
if __name__ == '__main__':
app.run(debug=True)
二、html代码
<!DOCTYPE html>
<html lang="zh">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>专案管理页面</title>
<link rel="stylesheet" href="{{ url_for('static', filename='index.css') }}">
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.min.css">
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
</head>
<div class="header">
<h1>专案管理系统</h1>
<span id="clock"></span>
</div>
<script>
function updateClock() {
const now = new Date();
const hours = String(now.getHours()).padStart(2, '0');
const minutes = String(now.getMinutes()).padStart(2, '0');
const seconds = String(now.getSeconds()).padStart(2, '0');
const currentTime = `${hours}:${minutes}:${seconds}`;
document.getElementById('clock').textContent = currentTime;
}
setInterval(updateClock, 1000);
updateClock(); // Initialize the clock immediately
</script>
<div class="container">
<div class="form-container" id="login-form">
<form id="projectForm" action="/" method="POST">
<!-- 把专案名称更换为 -->
<label for="projectname">专案名称:</label>
<select id="projectname" name="projectname">
<!-- 选项由JS动态添加 -->
</select>
<!-- <label for="projectname">专案名称:</label>
<input type="text" id="projectname" name="projectname"> -->
<label for="personname">提交人:</label>
<input type="text" id="personname" name="personname">
<label for="stateSelection">专案状态:</label>
<select id="stateSelection" name="stateSelection">
<option value=""></option>
<option value="Ongoing">正在进行</option>
<option value="shunyan">顺延(本周无进度)</option>
<option value="achieved">已经完成</option>
</select>
<button id="addRowBtn" type="button">增加一行</button> <!-- 添加按钮-->
<button type="submit">查询</button>
<button type="button" onclick="clearFormat()">清除格式</button>
</form>
</div>
<div class="assiantFre_container">
<table id="projectTable">
<thead id="tableHeader">
<!-- 动态生成的表头将插入这里 -->
</thead>
<tbody id="tableBody">
<!-- 动态生成的表格数据将插入这里 -->
</tbody>
</table>
</div>
<script src="{{ url_for('static', filename='index.js') }}"></script>
</div>
</html>
三、index.css
/* styles.css */
/* 基本样式重置 */
* {
margin: 0;
padding: 0;
box-sizing: border-box;
}
/* h1 {
text-align: center;
margin-bottom: 20px;
color: #444;
} */
.header {
display: flex;
justify-content: space-between;
/* 将内容两端对齐 */
align-items: center;
/* 垂直居中对齐 */
padding: 20px;
/* 添加内边距 */
background-color: #f8f8f8;
/* 背景颜色 */
}
.header h1 {
margin: 0 auto;
/* 使标题居中 */
}
.header #clock {
font-size: 24px;
margin-left: 120px;
color: white; /* 设置文字颜色 */
background-color: #343a40; /* 设置背景颜色 */
padding: 10px 20px; /* 添加内边距 */
border-radius: 5px; /* 添加圆角 */
box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2); /* 添加阴影 */
background: linear-gradient(135deg, #4285f4, #673ab7);
animation: blink 1s infinite alternate; /* 添加闪烁动画 */
}
@keyframes blink {
from {
opacity: 1;
}
to {
opacity: 0.5;
}
}
.container {
font-family: Arial, sans-serif;
background-color: #f4f4f4;
color: #333;
line-height: 1.6;
padding: 20px;
border-radius: 8px;
box-shadow: 0 0 1px rgba(0, 0, 0, 0.1);
}
.form-container {
display: flex;
/* 使用 Flexbox 布局 */
align-items: center;
/* 垂直居中对齐 */
justify-content: flex-start;
/* 控制子元素在容器内的水平排列方式 */
gap: 20px;
/* 控制每个控件之间的间距 */
}
form {
display: flex;
/* 使表单内的控件排列在一行 */
gap: 20px;
/* 控制控件之间的间距 */
align-items: center;
/* 垂直对齐控件 */
}
#projectname {
padding: 10px;
/* 设置内边距,让内容不与边框紧贴 */
border: 1px solid #ccc;
/* 设置边框颜色和样式 */
border-radius: 4px;
/* 设置圆角效果 */
font-size: 16px;
/* 设置字体大小 */
width: 200px;
/* 设置宽度为 200px */
height: 40px;
/* 设置高度为 40px */
background-color: #fff;
/* 设置背景颜色为白色 */
}
#projectname option {
padding: 8px;
/* 设置选项的内边距,避免文字与边缘过于紧密 */
}
/* #projectname {
width: 200px; /* 设置宽度 */
/* height: 40px; 设置高度} */
*/ label {
font-weight: bold;
color: #555;
margin: 0;
/* 去掉默认的 margin */
}
input[type="text"] {
padding: 10px;
border: 1px solid #ccc;
border-radius: 4px;
font-size: 16px;
width: 200px;
/* 你可以调整输入框的宽度 */
}
#stateSelection {
padding: 10px;
/* 设置内边距,让内容不与边框紧贴 */
border: 1px solid #ccc;
/* 设置边框颜色和样式 */
border-radius: 4px;
/* 设置圆角效果 */
font-size: 16px;
/* 设置字体大小 */
width: 200px;
/* 设置宽度为 200px */
height: 40px;
/* 设置高度为 40px */
background-color: #fff;
/* 设置背景颜色为白色 */
}
#stateSelection option {
padding: 8px;
/* 设置选项的内边距,避免文字与边缘过于紧密 */
}
button {
width: 100px;
padding: 10px;
background-color: #007bff;
color: #fff;
border: none;
border-radius: 4px;
font-size: 16px;
cursor: pointer;
transition: background-color 0.3s ease;
}
.assiantFre_container {
color: #333;
padding: 20px;
/* 上下之间的距离 */
border-radius: 8px;
font-size: 20px;
max-width: 100%;
/* 修改最大宽度为100% */
width: 100%;
}
table {
width: 100%;
/* 设置表格占据100%宽度 */
border-collapse: collapse;
/* 合并表格边框 */
margin-top: 30px;
/* 设置表格与上方元素的间距 */
}
table th,
table td {
padding: 12px 15px;
text-align: left;
border: 1px solid #ddd;
/* 为表头和单元格添加边框 */
word-wrap: break-word;
/* 自动换行,避免内容溢出 */
white-space: normal;
/* 允许正常换行 */
}
table th {
background-color: #f2f2f2;
font-weight: bold;
color: #444;
}
table tr:nth-child(even) {
background-color: #f9f9f9;
}
table tr:hover {
background-color: #e9e9e9;
}
四、js代码
console.log("index.js 文件已加载");
document.addEventListener('DOMContentLoaded', function () {
/*********************************************
获取表单和表格元素
*********************************************/
const form = document.getElementById('projectForm');
const tableHeader = document.getElementById('tableHeader');
const tableBody = document.getElementById('tableBody');
const addRowbtn = document.getElementById('addRowBtn');
/*********************************************
创建单元格的函数
*********************************************/
function createTableCell(content) {
const td = document.createElement('td');
td.textContent = content; // 设置单元格的文本内容
td.setAttribute('contenteditable', 'true'); // 设置单元格可编辑
return td;
}
/*********************************************
控制显示表格的函数
*********************************************/
function populateTable(data) {
tableHeader.innerHTML = `
<tr>
<th>ID</th>
<th>专案名称</th>
<th>专案内容</th>
<th>提交人</th>
<th>状态</th>
<th>项目起始日期</th>
<th>更改时间</th>
<th>操作</th> <!-- 用于显示删除按钮 -->
</tr>
`;
data.forEach(function (row) {
const tr = document.createElement('tr'); // 创建一行
// console.log("row_neirong", row.neirong)
const createTableCell = (content) => {
const td = document.createElement('td');
td.innerHTML = content; // 使用 innerHTML 插入带有 HTML 标签的内容
td.contentEditable = true; // 确保单元格是可编辑的
return td;
};
tr.appendChild(createTableCell(row.id));
tr.appendChild(createTableCell(row.projectName));
tr.appendChild(createTableCell(row.neirong));
tr.appendChild(createTableCell(row.submitPersonName));
tr.appendChild(createTableCell(row.Process));
tr.appendChild(createTableCell(row.StartTime));
tr.appendChild(createTableCell(row.EndTime));
// 创建删除按钮的单元格
const deleteCell = document.createElement('td');
const deleteButton = document.createElement('button');
deleteButton.textContent = '删除'; // 设置按钮文本
deleteButton.classList.add('delete-btn'); // 添加样式类,可以自定义按钮样式
// 为删除按钮绑定点击事件,删除当前行
deleteButton.addEventListener('click', function (event) {
event.preventDefault();
const confirmDelete = window.confirm('是否确定删除这一行?');
if (confirmDelete) {
const tr = deleteButton.closest('tr'); // 获取当前按钮所在的行
const id = tr.querySelector('td:nth-child(1)').textContent.trim()
console.log(id)
const deleteData = { 'flag': true, 'id': id }
tr.remove(); // 删除该行
fetch('/delectable', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify(deleteData)
})
.then(response => response.json())
.then(data => {
console.log('成功发送数据到delete接口:', data);
})
.catch(error => {
console.error('失败发送数据到delete接口:', error);
});
}
});
deleteCell.appendChild(deleteButton); // 将按钮添加到单元格中
tr.appendChild(deleteCell); // 将单元格添加到行中
tableBody.appendChild(tr); // 将完整的行添加到表格主体
});
}
/*********************************************
默认显示当前表中的数据
*********************************************/
fetch('/default', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
})
.then(response => response.json()) // 解析返回的 JSON 数据
.then(data => {
const defaultData = data;
// 初始化表格
console.log(defaultData)
populateTable(defaultData);
}).catch(error => {
console.error('Error:', error);
});
/*********************************************
筛选条件接口
*********************************************/
fetch('/projectNameOptions', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
})
.then(response => {
if (!response.ok) {
throw new Error('网络响应不正常');
}
return response.json();
})
.then(data => {
let exeuted = false
console.log("正在查询")
const selectElement = document.querySelector('select[id="projectname"]'); // 使用 id 属性选择器
// 添加默认选项
const defaultOption = document.createElement('option');
defaultOption.value = "";
defaultOption.textContent = "请选择项目";
selectElement.appendChild(defaultOption);
data.projects.forEach(projectName => {
const option = document.createElement('option');
option.value = projectName;
option.textContent = projectName; //
selectElement.appendChild(option); //
});
// 监听 select 元素的 change 事件
selectElement.addEventListener('change', function () {
const SelectedProjectname = selectElement.value; // 获取当前选中的值
console.log('当前选中的项目名称:', SelectedProjectname);
search(SelectedProjectname); // 调用 search 函数并传递选中的值
exeuted = true
});
if (!exeuted){
search(defaultOption.value)
}
})
.catch(error => {
console.error(error);
});
function search(SelectedProjectname) {
// 查询
if (form) {
form.addEventListener('submit', function (event) {
event.preventDefault();
console.log("查询表单已提交", SelectedProjectname);
const projectName = SelectedProjectname
const personname = document.getElementById('personname').value;
const stateSelectionValue = document.getElementById('stateSelection').value;
const formData = {
projectname: projectName, // 获取专案名称
personname: personname, // 获取提交人
stateSelectionValue: stateSelectionValue // 获取专案进度
};
// 将对象转换为 JSON 字符串
const JsonData = JSON.stringify(formData);
console.log("JsonData:", JsonData);
fetch('/searchfor', {
method: 'POST',
headers: {
'Content-Type': 'application/json', // 设置请求的内容类型为 JSON
},
body: JsonData
})
.then(response => response.json()) // 解析响应的 JSON 数据
.then(data => {
console.log("searchfor被触发!!"); // 处理接收到的数据
console.log('searchfordata:', data); // 处理接收到的数据
// 模拟查询结果
const queryResult = data
tableHeader.innerHTML = ''
tableBody.innerHTML = ''
// 显示表格
populateTable(queryResult);
})
.catch(error => {
console.error('发送数据时发生错误:', error); // 错误处理
});
})
}
else
console.log("不存在form!")
}
/*********************************************
插入一行
*********************************************/
addRowbtn.addEventListener('click', function () {
event.preventDefault();
console.log("click提交事件被触发了");
fetch('/insertTable', {
method: 'POST',
headers: {
'Content-Type': 'application/json', // 设置请求的内容类型为 JSON
},
body: JSON.stringify({
name: 'John Doe',
age: 30,
})
})
.then(response => response.json()) // 解析响应的 JSON 数据
.then(data => {
const newData = data['data']; // 想要插入的新数据
console.log('newdata:', newData); // 处理接收到的数据
tableHeader.innerHTML = ''
tableBody.innerHTML = ''
// 填充表格
populateTable(newData);
})
.catch(error => {
console.error('发送数据时发生错误:', error); // 错误处理
});
});
/*********************************************
保存当前表格到数据库
*********************************************/
window.addEventListener('keydown', function (event) {
// 检查是否按下了 Ctrl + S
if (event.ctrlKey && event.key === 's') {
// 阻止默认行为(防止浏览器弹出保存对话框)
event.preventDefault();
console.log("表格信息已保存!");
const jsonData = getTableDataAsJson(tableBody) // 从页面上获得当前所有的项目
console.log(jsonData)
if (jsonData === '[]' || jsonData.length === 0) {
console.log("jsonData 是空的");
} else {
console.log("jsonData 不是空的");
fetch('/saveData', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: jsonData
})
.then(response => response.json())
.then(data => {
console.log('成功发送数据到后端:', data);
})
.catch(error => {
console.error('发送数据失败:', error);
});
}
}
});
function getTableDataAsJson(tableBody) {
const rows = tableBody.querySelectorAll('tr'); // 获取所有的行
const tableData = [];
rows.forEach(function (row) {
const cells = row.querySelectorAll('td'); // 获取每一行的所有单元格
if (cells.length === 0) return; // 如果这一行是空的,则跳过
const rowData = {
id: cells[0].textContent.trim(),
projectName: cells[1].textContent.trim(),
projectNeirong: cells[2].innerHTML.trim(),
submitPersonName: cells[3].textContent.trim(),
process: cells[4].textContent.trim(),
startTime: cells[5].textContent.trim(),
endTime: cells[6].textContent.trim()
};
tableData.push(rowData); // 将每一行的数据添加到数组中
});
// 将数组转换为 JSON 格式并返回
return JSON.stringify(tableData);
}
});
function clearFormat() {
var table = document.getElementById('projectTable');
var selection = window.getSelection();
var selectedText = selection.toString();
if (selectedText.length === 0) {
alert("请先选择要清除格式的文本。");
return;
}
// 获取选中的范围
var range = selection.getRangeAt(0);
var startContainer = range.startContainer;
var endContainer = range.endContainer;
// 创建一个新的文本节点,替换选中的内容
var plainText = selectedText.replace(/<[^>]*>/g, '');
var textNode = document.createTextNode(plainText);
// 替换选中的内容
range.deleteContents();
range.insertNode(textNode);
// 重新设置选中的范围
range.setStart(textNode, 0);
range.setEnd(textNode, plainText.length);
selection.removeAllRanges();
selection.addRange(range);
}