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、结果：