多源教学数据管理系统之团队课设博客

 一、团队简介

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分明确区分出来

扫描代码之后还有一点小错误未更正

posted @ 2019-01-18 12:20  咿呀!  阅读(396)  评论(0编辑  收藏  举报