java教学管理系统设计_JAVA课程设计——多源教学数据管理系统

团队简介

团队名称：

419圣斗士

团队成员

姓名

成员介绍

任务分配

周炳辉(组长)

来自网络的一个大佬，穿女装很合适

poi与servlet

徐宏伟

网络中一个具有强大隐藏实力的大哥

css,部分jsp与html

李金谣

旭宝的忠实粉丝，来自格兰迪的挖矿者

部分jsp与echarts

龚顺德

常规划水，一个默默无闻的人

团队博客，Student类

项目git地址

项目git提交记录

项目功能架构图与主要功能流程图

主要功能图

项目运行截图或屏幕录制

项目关键代码

public class ExcelUtil {

//默认单元格内容为数字时格式

private static DecimalFormat df = new DecimalFormat("0");

// 默认单元格格式化日期字符串

private static SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");

// 格式化数字

private static DecimalFormat nf = new DecimalFormat("0.00");

public static ArrayList> readExcel(File file){

if(file == null){

return null;

}

if(file.getName().endsWith("xlsx")){

//处理ecxel2007

return readExcel2007(file);

}else{

//处理ecxel2003

return readExcel2003(file);

}

}

/*

* @return 将返回结果存储在ArrayList内，存储结构与二位数组类似

* lists.get(0).get(0)表示过去Excel中0行0列单元格

*/

public static ArrayList> readExcel2003(File file){

try{

ArrayList> rowList = new ArrayList>();

ArrayList colList;

HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));

int length = wb.getNumberOfSheets();

int index = 0;

for(int i = 0; i < length; i++) {

HSSFSheet tempSheet = wb.getSheetAt(i);

HSSFRow row = tempSheet.getRow(0);

HSSFCell cell = row.getCell(0);

String value = cell.getStringCellValue();

if(value.contains("网络17")) {

index = i;

break;

}

}

HSSFSheet sheet = wb.getSheetAt(index);

HSSFRow row;

HSSFCell cell;

Object value;

for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){

row = sheet.getRow(i);

colList = new ArrayList();

if(row == null){

//当读取行为空时

if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行

rowList.add(colList);

}

continue;

}else{

rowCount++;

}

for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){

cell = row.getCell(j);

if(cell == null || cell.getCellType() == CellType.BLANK){

//当该单元格为空

if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格

colList.add("");

}

continue;

}

switch(cell.getCellType()){

case STRING:

value = cell.getStringCellValue();

break;

case NUMERIC:

value = df.format(cell.getNumericCellValue());

break;

case BOOLEAN:

value = Boolean.valueOf(cell.getBooleanCellValue());

break;

case BLANK:

value = "";

break;

default:

value = cell.toString();

}// end switch

colList.add(value);

}//end for j

rowList.add(colList);

}//end for i

return rowList;

}catch(Exception e){

return null;

}

}

public static ArrayList> readExcel2007(File file){

try{

ArrayList> rowList = new ArrayList>();

ArrayList colList;

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));

int length = wb.getNumberOfSheets();

int index = 0;

for(int i = 0; i < length; i++) {

XSSFSheet tempSheet = wb.getSheetAt(i);

XSSFRow row = tempSheet.getRow(0);

XSSFCell cell = row.getCell(0);

if(cell!=null&&cell.getCellType()!=CellType.BLANK) {

String value = cell.getStringCellValue();

if(value.contains("网络17")) {

index = i;

break;

}

}

}

XSSFSheet sheet = wb.getSheetAt(index);

XSSFRow row;

XSSFCell cell;

Object value;

for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){

row = sheet.getRow(i);

colList = new ArrayList();

if(row == null){

//当读取行为空时

if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行

rowList.add(colList);

}

continue;

}else{

rowCount++;

}

for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){

cell = row.getCell(j);

if(cell == null || cell.getCellType() == CellType.BLANK){

//当该单元格为空

if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格

colList.add("");

}

continue;

}

switch(cell.getCellType()){

case STRING:

value = cell.getStringCellValue();

break;

case NUMERIC:

value = df.format(cell.getNumericCellValue());

break;

case BOOLEAN:

value = Boolean.valueOf(cell.getBooleanCellValue());

break;

case BLANK:

value = "";

break;

default:

value = cell.toString();

}// end switch

colList.add(value);

}//end for j

rowList.add(colList);

}//end for i

return rowList;

}catch(Exception e){

e.printStackTrace();

System.out.println("exception");

return null;

}

}

public static void writeExcel(ArrayList> result,String path){

if(result == null){

return;

}

XSSFWorkbook wb = new XSSFWorkbook();

XSSFSheet sheet = wb.createSheet("sheet1");

for(int i = 0 ;i < result.size() ; i++){

XSSFRow row = sheet.createRow(i);

if(result.get(i) != null){

for(int j = 0; j < result.get(i).size() ; j ++){

XSSFCell cell = row.createCell(j);

cell.setCellValue(result.get(i).get(j).toString());

}

}

}

ByteArrayOutputStream os = new ByteArrayOutputStream();

try

{

wb.write(os);

} catch (IOException e){

e.printStackTrace();

}

byte[] content = os.toByteArray();

File file = new File(path);//Excel文件生成后存储的位置。

OutputStream fos = null;

try

{

fos = new FileOutputStream(file);

fos.write(content);

os.close();

fos.close();

}catch (Exception e){

e.printStackTrace();

}

}

public static DecimalFormat getDf() {

return df;

}

public static void setDf(DecimalFormat df) {

ExcelUtil.df = df;

}

public static SimpleDateFormat getSdf() {

return sdf;

}

public static void setSdf(SimpleDateFormat sdf) {

ExcelUtil.sdf = sdf;

}

public static DecimalFormat getNf() {

return nf;

}

public static void setNf(DecimalFormat nf) {

ExcelUtil.nf = nf;

}

}

public class POIUtil {

public static void buildStandardScale(String path) {

Connection con = null;

PreparedStatement pStatement = null;

Statement stmt = null;

ResultSet rs = null;

ArrayList> ans;

String sql = "insert into study(id,name,sex,class,major,faculty) values(?,?,?,?,?,?)";

try {

con = JDBCUtil.getConnection();

con.setAutoCommit(false);

ans = new ArrayList>();

ans = TxtUtil.readFile(path);

//ans = TxtUtil.readFile("C:\\Users\\你们走啊\\Desktop\\study\\学生名单.txt");

stmt = con.createStatement();

pStatement = con.prepareStatement(sql);

for(int i = 1; i < ans.size(); i++) {

String id = ans.get(i).get(0).toString();

String newSql = "select * from study where id = "+id;

rs = stmt.executeQuery(newSql);

String c = "0";

while (rs.next()) {

c = rs.getString(1);

}

if(c.equals("0")) {

for(int j = 1; j <= ans.get(i).size(); j++) {

pStatement.setString(j, ans.get(i).get(j-1).toString());

}

pStatement.addBatch();

pStatement.executeBatch();

con.commit();

}

}

}catch (SQLException sqle) {

sqle.printStackTrace();

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtil.realeaseAll(rs, stmt, con, pStatement);

}

}

public static void mergeExcel(String path) {

Connection con = null;

PreparedStatement pStatement = null;

Statement stmt = null;

ResultSet rs = null;

ArrayList> ans;

ArrayList cowList;

ArrayList> errorAns = new ArrayList>();

ArrayList temp = new ArrayList();

temp.add("学号");

temp.add("姓名");

temp.add("该信息所在的文件夹");

errorAns.add(temp);

String sql = "insert into study(id,name,sex,class,major,faculty) values(?,?,?,?,?,?)";

try {

con = JDBCUtil.getConnection();

con.setAutoCommit(false);

stmt = con.createStatement();

//String Path = "C:\\Users\\你们走啊\\Desktop\\新建文件夹";

File file = new File(path);

File[] tempList = file.listFiles();

String tempNameList [] = new String [tempList.length];

for(int i = 0; i < tempList.length; i++) {

if (tempList[i].isFile()) {

tempNameList[i] = tempList[i].toString();

}

}

int begin = 0, index = 0;

ArrayList> result;

for(int l = 0; l < tempNameList.length; l++) {

begin = 0;

index = 0;

if(tempNameList[l].contains("txt")) {

continue;

}

if(tempNameList[l].contains("错误")) {

continue;

}

if(tempNameList[l].contains("网络汇总")) {

continue;

}

String name;

File newFile = new File(tempNameList[l]);

if(newFile.getName().contains("期中")) {

name = "midterm";

}else if(newFile.getName().contains("期末")) {

name = "terminal";

}else if(newFile.getName().contains("语言程序设计")) {

name = "MOOC";

}else {

name = "pta";

}

result = ExcelUtil.readExcel(newFile);

for(int i = 0 ;i < result.size() ;i++){

for(int j = 0;j

if(result.get(i).get(j).toString().equals("学号")) begin = i+1;

if(result.get(i).get(j).toString().contains("总分")) {

index = j;

break;

}

}

}

boolean flag;

for(int i = begin; i < result.size(); i++) {

temp = new ArrayList();

String id = result.get(i).get(0).toString();

flag = false;

if(result.get(i).get(0)==null||result.get(i).get(0).toString().equals("")||result.get(i).get(1)==null||result.get(i).get(1).toString().equals("")) {

flag = false;

}

else {

String newSql2 = "select * from study where id = "+result.get(i).get(0)+" and name = '"+result.get(i).get(1)+"'";

rs = stmt.executeQuery(newSql2);

while(rs.next()) {

flag = true;

}

}

if(!flag) {

temp.add(result.get(i).get(0));

temp.add(result.get(i).get(1));

temp.add(tempNameList[l]);

errorAns.add(temp);

}

String newSql = "update study set "+name+" = (?) where id = "+id;

//String newSql = "update study set "+name.toString()+" = "+result.get(i).get(index).toString()+" where id = "+id;

if(NumberUtil.isNumber(id)){

pStatement = con.prepareStatement(newSql);

pStatement.setString(1, result.get(i).get(index).toString());

pStatement.addBatch();

pStatement.executeBatch();

con.commit();

}

}

}

//将合并excel文件写入新的excel

ans = new ArrayList>();

sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = 'study'";

rs = stmt.executeQuery(sql);

cowList = new ArrayList();

while(rs.next()) {

cowList.add(rs.getString(1));

}

ans.add(cowList);

rs.close();

String newSql="select * from study";

rs = stmt.executeQuery(newSql);

while(rs.next()) {

cowList = new ArrayList();

for(int i = 1; i <= 11; i++)

if(rs.getString(i)==null) {

cowList.add("");

}

else cowList.add(rs.getString(i));

ans.add(cowList);

}

ExcelUtil.writeExcel(ans, path+"/java网络汇总.xlsx");

ExcelUtil.writeExcel(errorAns, path+"/错误汇总表.xlsx");

}catch (SQLException sqle) {

sqle.printStackTrace();

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtil.realeaseAll(rs, stmt, con, pStatement);

}

}

项目代码扫描结果及改正

根据提示的错误，修改相关的错误。

尚待改进

界面美化，数据库远程连接等等。