daydayup---->Poi操作
学生的成绩统计在excel中，给出按学分绩排名的名次（学分绩等于各科成绩的加权平均，为了解决教师判分标准不统一的问题，其中单科成绩折算公式为：单科成绩=（个人单科成绩/本课程最高成绩）*100）。
所用知识
hibernate编程式事务
poi访问excel表
一、新建项目，导入mysql，poi，hibernate，junit，mysql驱动的jar包
代码具体如下：
1、Score.java
package com.wsz.entity;
public class Score {
private Integer id; // 主键
private String studentNumber; // 学号
private String name; // 姓名
private String courseName; // 课程名
private Double score; // 课程成绩
private Double highestScore; // 课程最高成绩
private Double creditPoints; // 学分
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getStudentNumber() {
return studentNumber;
}
public void setStudentNumber(String studentNumber) {
this.studentNumber = studentNumber;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCourseName() {
return courseName;
}
public void setCourseName(String courseName) {
this.courseName = courseName;
}
public Double getScore() {
return score;
}
public void setScore(Double score) {
this.score = score;
}
public Double getHighestScore() {
return highestScore;
}
public void setHighestScore(Double highestScore) {
this.highestScore = highestScore;
}
public Double getCreditPoints() {
return creditPoints;
}
public void setCreditPoints(Double creditPoints) {
this.creditPoints = creditPoints;
}
}
2、Score.hbm.xml映射文件
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="com.wsz.entity.Score"> <id name="id"> <generator class="identity" /> </id> <property name="studentNumber" /> <property name="name" /> <property name="courseName" /> <property name="score" /> <property name="highestScore" /> <property name="creditPoints" /> </class> </hibernate-mapping>
3、hibernate配置文件 hibernate.cfg.xml
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <!--一个数据库一个session-factory定义--> <session-factory> <property name="hibernate.connection.url">jdbc:mysql://localhost/hibernate_score</property> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> <property name="hibernate.connection.username">root</property> <property name="hibernate.connection.password">admin</property> <property name="hibernate.current_session_context_class">thread</property> <!--数据库方言,不同数据库的方言不同--> <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property> <!--执行时会输出sql语句,方便调试--> <property name="hibernate.show_sql">true</property> <!--所有的实体对象的配置文件都要在这里关联起来--> <mapping resource="com/wsz/entity/Score.hbm.xml"/> </session-factory> </hibernate-configuration>
4、ScoreDao.java
package com.wsz.entity;
import org.hibernate.Session;
public class ScoreDao {
public void addScore(Score score) {
Session session = null;
try {
session = HibernateUtils.getSessionFactory().getCurrentSession();
session.beginTransaction();
session.save(score);
session.getTransaction().commit();
}catch(Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}
}
}
5、初始化数据
package com.wsz.entity;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import junit.framework.TestCase;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class InitDate extends TestCase {
String strFilePath = "C:\\";// 设置路径
String strFileName = "控计学院" + ".xls";// 设置名称
ScoreDao scoreDao=new ScoreDao();
// 导入excel表数据到数据库
public void importScore() {
try {
File fSource = new File(strFilePath + strFileName);
FileInputStream in = new FileInputStream(fSource);
HSSFWorkbook workbook = new HSSFWorkbook(in);
HSSFSheet sheet = workbook.getSheetAt(0); // 默认读取第一个工作簿
int count = sheet.getLastRowNum();
System.out.println(count); // 数据的行数
HSSFRow temprow = sheet.getRow(0);
System.out.println(temprow.getCell(0).getStringCellValue());
System.out.println(temprow.getCell(1).getStringCellValue());
System.out.println(temprow.getCell(2).getStringCellValue());
System.out.println(temprow.getCell(3).getStringCellValue());
System.out.println(temprow.getCell(4).getStringCellValue());
System.out.println(temprow.getCell(5).getStringCellValue());
for (int i = 1; i < count + 1; i++) {
Score score = new Score();
HSSFRow row = sheet.getRow(i);
score.setStudentNumber(row.getCell(0).getStringCellValue());
score.setName(row.getCell(1).getStringCellValue());
score.setCourseName(row.getCell(2).getStringCellValue());
score.setCreditPoints(Double.valueOf(row.getCell(3).getStringCellValue()));
score.setScore(Double.valueOf(row.getCell(4).getStringCellValue()));
score.setHighestScore(Double.valueOf(row.getCell(5).getStringCellValue()));
// 保存数据
scoreDao.addScore(score);
System.out.println(score.getId());
System.out.println(score.getStudentNumber());
System.out.println(score.getName());
System.out.println(score.getCourseName());
System.out.println(score.getScore());
System.out.println(score.getHighestScore());
System.out.println(score.getCreditPoints());
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
6、ExportDB.java
package com.wsz.entity;
import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
public class ExportDB {
public static void main(String[] args) {
//读取hibernate.cfg.xml文件
Configuration cfg = new Configuration().configure();
SchemaExport export = new SchemaExport(cfg);
export.create(true, true);
}
}
7、HibernateUtils.java
package com.wsz.entity;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class HibernateUtils {
private static SessionFactory factory;
static {
try {
Configuration cfg = new Configuration().configure();
factory = cfg.buildSessionFactory();
} catch (Exception e) {
e.printStackTrace();
}
}
public static SessionFactory getSessionFactory() {
return factory;
}
// factory.getCurrentSession()可以用于同一线程的多个方法,以保证使用同一个Session
public static Session getSession() {
return factory.openSession();
}
public static void closeSession(Session session) {
if (session != null) {
if (session.isOpen()) {
session.close();
}
}
}
}
8、查看排名的sql语句(没写client)
select name ,sum(score/highestScore*creditPoints*100)/sum(creditPoints) as “成绩” from score group by name order by sum(score/highestScore*creditPoints*100)/sum(creditPoints) desc;
9、结果：
