多源教学数据管理系统之团队课设博客
一、团队简介
1.团队名称：青龙学习小组
2.团队成员：
| 名字 | 学号 | 班级 |
| 黄倩 | 201721123100 | 网络1714 |
| 黄卿卿 | 201721123096 | 网络1714 |
| 唐欢 | 201721123095 | 网络1714 |
3.任务分配：
model：黄卿卿（主攻）、唐欢（辅助）
gui：黄倩、唐欢
团队博客：黄倩、唐欢
4.团队成员课程设计博客链接
| 名字 | 个人博客链接 | |
| 组长 | 黄倩 | https://www.cnblogs.com/hq9-/p/10287153.html |
| 组员 | 黄卿卿 | https://www.cnblogs.com/huangqingqing/p/10285411.html |
| 组员 | 唐欢 | https://www.cnblogs.com/tanghuan/p/10284765.html |
二、项目简介
1.项目简介：
老师使用多种教学平台（雨课堂、PTA、超星）开展教学活动，每个平台中的教学活动数据均可以导出为Excel数据文件，但内部的结构均不一样。还有其他数据文件：学生学号、姓名、班级信息文件（标准参考数据，比较准确，.txt格式）、学生最终成绩表。尝试编写一个程序实现。
2.采用技术：数据库、POI、文件、Java、GUI、数据库连接池、Git
3.功能需求分析：
1）导入标准名单文件需要连接数据库及文件相关操作
2）选择合并成绩Excel文件需要用到POI与文件相关操作
4.项目亮点：贴合实际，未来可能发展为一个可被使用的程序
5.团队成员负责模块：
黄卿卿：将txt文件导入数据库（可以有不同的txt）,将excel文件写到数据库（支持两种格式）,完善将数据库从excel导出，浏览txt文件的界面代码，添加回撤功能及相关监听器代码。
唐欢：实现浏览文件、选择文件功能，实现显示txt文件在gui界面，导出excel界面，最初导出excel文件的业务代码及图形界面等
黄倩：初始化界面、登录界面、权重功能实现及图形界面等，代码管理
三、项目git地址及提交记录
1.项目git地址
https://gitee.com/hq19991209/multisource_teaching_data.git
2.项目git提交记录截图
黄卿卿：
唐欢：
黄倩：
四、项目功能架构图与主要功能流程图
1.项目功能架构图
2.主要功能流程图
五、项目运行截图
六、项目关键代码（按模块讲解，不要太多）
导入txt标准名单文件到数据库中
public static String WriteToDB(String filename) throws IOException { Connection conn = null; PreparedStatement ps = null; PreparedStatement ps2 = null; ResultSet rs = null; StringBuilder sb1 = new StringBuilder(); StringBuilder sb2 = new StringBuilder(); StringBuilder sb3 = new StringBuilder(); sb3.append(") value("); File f = new File(filename); String t = f.getName(); String table = t.substring(0, t.lastIndexOf(".")); System.out.println(table); sb2.append("insert into " + table + "(stuNo,name"); sb1.append("create table " + table + "(id int not null primary key auto_increment,stuNo varchar(20) not null"); // 表名固定了 try { conn = JDBCUtil.getConnection(); File file = new File(filename); FileInputStream is = new FileInputStream(file); InputStreamReader isr = new InputStreamReader(is, "UTF8"); BufferedReader bf = new BufferedReader(isr); String str; int flag = 0, cou = 0;// cou标记提供列数 String[] titles = null; while ((str = bf.readLine()) != null) { // 初次建表 if (flag == 0) { titles = str.split("\\s+"); for (String string : titles) { // System.out.println(string+ " 第"+cou); if (cou > 0) { sb1.append("," + string + " varchar(20) not null"); } if (cou == 0) { sb3.append("?"); } else { sb3.append(",?"); } if (cou >= 2) { // sb2.append(string); sb2.append("," + string); } cou++; } flag = 1; sb1.append(");"); sb3.append(");"); String createsql = sb1 + ""; String sss; sss = sb3 + ""; String insertsql = sb2 + sss; ps2 = conn.prepareStatement(createsql); ps2.executeUpdate(); } else { String sss; sss = sb3 + ""; String insertsql = sb2 + sss; ps = conn.prepareStatement(insertsql); String[] s = str.split("\\s"); for (int i = 0; i < s.length; i++) { ps.setString(i + 1, s[i]); // System.out.println(); } ps.executeUpdate(); } } System.out.println("成功向数据库插入文本 "); // return table; } catch (SQLException e) { System.out.println("SQLException!"); e.printStackTrace(); } catch (FileNotFoundException e) { System.out.println("FileNotFound"); e.printStackTrace(); } finally { JDBCUtil.realeaseAll(rs, ps, conn); } return table; }
public static void deletetable(String tablename) throws IOException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String deletesql = "drop table " + tablename;
try {
conn = JDBCUtil.getConnection();
ps = conn.prepareStatement(deletesql);
ps.executeUpdate();
} catch (SQLException e) {
System.out.println("SQLException!");
e.printStackTrace();
} finally {
JDBCUtil.realeaseAll(rs, ps, conn);
}
}
}
将.xls与.xlsx的Excel文件导入数据库
public static String WriteExcl(String tablename, String setPos, String filename, String newrowName) throws IOException { Connection conn = null; PreparedStatement ps = null; PreparedStatement ps2 = null; ResultSet rs = null; String insertsql = "update " + tablename + " set " + setPos + "= ? where stuNo = ?"; String altersql = "alter table " + tablename + " add " + newrowName + " varchar(20)"; try { StringBuilder sb = new StringBuilder(); conn = JDBCUtil.getConnection(); ps = conn.prepareStatement(insertsql); ps2 = conn.prepareStatement(altersql); ps2.executeUpdate(); File file = new File(filename); WriteExcelToDB excelReader = new WriteExcelToDB(); InputStream is2 = new FileInputStream(filename); Map<Integer, String> map = excelReader.readExcelContent(is2, file); for (int i = 3; i <= map.size(); i++) { String[] s = map.get(i).split("\\s+"); ps.setString(1, s[2]); ps.setString(2, s[0]); System.out.println(insertsql); int result = ps.executeUpdate(); if (result == 0) { sb.append("学号 " + s[0] + "姓名 " + s[1] + " 成绩 " + s[2] + " 插入有异常" + "\n"); } } String re = sb + ""; return re; } catch (SQLException e) { System.out.println("SQLException!"); e.printStackTrace(); } catch (FileNotFoundException e) { System.out.println("FileNotFound"); e.printStackTrace(); } finally { JDBCUtil.realeaseAll(rs, ps, conn); } return "插入完毕"; } public static String findErr(String tablename, String finPos) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; String findsql = "select stuNo,name, " + finPos + " from " + tablename + " where " + finPos + " is NULL or " + finPos + " ='缺考'"; ; try { StringBuilder sb = new StringBuilder(); conn = JDBCUtil.getConnection(); ps = conn.prepareStatement(findsql); rs = ps.executeQuery(); int flag = 0; while (rs.next()) { if (flag == 0) { sb.append("下列在名单中的学生没有成绩\n" + "学号" + "\t" + "姓名" + "\t" + "成绩" + "\t\n"); flag = 1; } sb.append(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\n"); } return sb + ""; } catch (SQLException e) { System.out.println("SQLException!"); e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtil.realeaseAll(rs, ps, conn); } return "查找完毕"; } public static void accuAccordPercent(String tablename, String cname, Map<String, Double> per) { Connection conn = null; PreparedStatement ps = null; PreparedStatement ps2 = null; PreparedStatement ps3 = null; ResultSet rs = null; StringBuilder sb = new StringBuilder(); String altersql = "alter table " + tablename + " add " + cname + " varchar(20)"; sb.append("update " + tablename + " set " + cname + "="); try { conn = JDBCUtil.getConnection(); ps = conn.prepareStatement(altersql); ps.executeUpdate(); for (Map.Entry<String, Double> entry : per.entrySet()) { String clearsql = " UPDATE " + tablename + " set " + entry.getKey() + " =0 where " + entry.getKey() + "='缺考'or " + entry.getKey() + " is NULL; "; ps3 = conn.prepareStatement(clearsql); ps3.executeUpdate(); } int flag = 0; for (Map.Entry<String, Double> entry : per.entrySet()) { if (flag == 0) { sb.append(entry.getKey() + "*" + entry.getValue() + " "); flag = 1; } else { sb.append("+ " + entry.getKey() + "*" + entry.getValue() + " "); } } String updatesql = sb + " ;"; System.out.println(updatesql); ps2 = conn.prepareStatement(updatesql); ps2.executeUpdate(); System.out.println("成功设置权重写入总分"); } catch (SQLException e) { System.out.println("SQLException!"); e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtil.realeaseAll(rs, ps, conn); } }
将数据库中的表导出成Excel文件
public static void readExcel(String tablename, String outname) throws SQLException { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { // 连接数据库查询数据 Class.forName("com.mysql.cj.jdbc.Driver"); conn = DriverManager.getConnection(url, user, password); String sql = "select * from " + tablename; ps = conn.prepareStatement(sql); // 获取结果集 rs = ps.executeQuery(); // 用于获取字段的描述信息,比如字段名 ResultSetMetaData metaData = (ResultSetMetaData) rs.getMetaData(); // 创建workBook对象 @SuppressWarnings("resource") HSSFWorkbook workBook = new HSSFWorkbook(); // 在workBook对象中创建一张表格 HSSFSheet sheet = workBook.createSheet("学生成绩"); // 设置每一列的宽度 int colnum = metaData.getColumnCount(); for (int i = 0; i < colnum; i++) { sheet.setColumnWidth(i, 2000); } // 单元格样式对象 HSSFCellStyle cellStyle = workBook.createCellStyle(); // 设置文本居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 创建第0行,作为表格的表头 HSSFRow row = sheet.createRow(0); HSSFCell cell = null; for (int i = 0; i < colnum; i++) { cell = row.createCell(i); // 动态获取字段名 cell.setCellValue(metaData.getColumnLabel(i + 1)); cell.setCellStyle(cellStyle); } int rowIndex = 1; while (rs.next()) { // 循环将查询出来的数据封装到表格的一行中 row = sheet.createRow(rowIndex); for (int i = 0; i < colnum; i++) { cell = row.createCell(i); cell.setCellValue(rs.getString(i + 1)); cell.setCellStyle(cellStyle); } rowIndex++; } String outPutFile = "d:" + File.separator + outname + ".xls"; FileOutputStream fos = new FileOutputStream(outPutFile); // 输出流将文件写到硬盘 workBook.write(fos); fos.flush(); fos.close(); } catch (Exception e) { e.printStackTrace(); } finally { rs.close(); ps.close(); conn.close(); } }
七、项目代码扫描结果及改正
1.
更改命名即可
2.
添加@Override就可以解决
3.
七、尚待改进
数据分析与章节进度展示未实现
成绩为缺考与null的不能与0分明确区分出来
扫描代码之后还有一点小错误未更正
