四、个人项目系统搭建

一、python写的后端代码

  后端代码使用Flask编写，如下所示：

# app.py
from flask import Flask, render_template, request, jsonify, g
import sqlite3
import os
from datetime import datetimeapp = 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 maxidexcept Exception as e:return jsonify({"message": "maxID发生错误"}), 500finally:# 确保关闭连接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 projectTableSET 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}), 200except Exception as e:# 如果有任何异常，回滚事务conn.rollback()print("发生错误:", e)return jsonify({"message": "处理数据时发生错误"}), 500finally:# 确保关闭连接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}), 200except Exception as e:# 如果有任何异常，回滚事务conn.rollback()print("发生错误:", e)return jsonify({"message": "处理数据时发生错误"}), 500finally:# 确保关闭连接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是0ids = max_id + 1insertData = {'id': ids, 'projectName': '在这里填写专案名称', 'submitPersonName': '','process': '', 'startTime': '2023-11-10', 'endTime': '2023-11-10', 'projectNeirong': '在这里填写专案内容'}insertData["endTime"] = formatted_timeprint(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": "处理数据时发生错误"}), 500finally:# 确保关闭连接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 = falseconsole.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 = SelectedProjectnameconst 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 = datatableHeader.innerHTML = ''tableBody.innerHTML = ''// 显示表格populateTable(queryResult);}).catch(error => {console.error('发送数据时发生错误:', error);  // 错误处理});})}elseconsole.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 + Sif (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);
}