记一次java计算加权平均成绩
思路很简单,用poi
读取单元格的数据,封装到实体类。这里为了计算方便,创建了课程实体类Classes
和学生Student
实体类。
Classes
主要记录:学生班级classes
,学生姓名name
,课程学分credit
,课程成绩grade
,学生姓名name
,学号id
,由于源文件单元格的格式是文本类型,因此在设置credit
和grade
时,要进行类型转换。
Student,主要记录:加权平均成绩grade
,学生姓名name
,学生班级classes
,,学号id
。
怎样记录和组织1w+的数据
采取,读取完一个学生的成绩,就计算一次的策略。把每一行成绩扔进栈中,然后判断,下一行的学生名字与栈顶学生名字是否一致,一致就入栈,不一致就进行计算,把栈内元素清空,存储下一个学生的信息。
每次计算完毕,把数据封装进Student
实体类,扔进List集合。
数据处理完毕,写数据到磁盘
这里只需要匹配学生班级,将各班的学生分开,然后写入磁盘。
目录结构
代码展示
Student
package com.henu.entity;
public class Student {
private String name;
private String classes;
private String id;
private Double grade;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getClasses() {
return classes;
}
public void setClasses(String classes) {
this.classes = classes;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public Double getGrade() {
return grade;
}
public void setGrade(Double grade) {
this.grade = grade;
}
@Override
public String toString() {
return "Student{" +
"name='" + name + '\'' +
", classes='" + classes + '\'' +
", id='" + id + '\'' +
", grade=" + grade +
'}';
}
}
Classes
package com.henu.entity;
public class Classes {
private String classes;
private String id;
private double credit;
private double grade;
private String name;
public String getClasses() {
return classes;
}
public void setClasses(String classes) {
this.classes = classes;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public double getCredit() {
return credit;
}
public void setCredit(String credit) {
this.credit = Double.parseDouble(credit);
}
public double getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = Double.parseDouble(grade);
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Classes{" +
"classes='" + classes + '\'' +
", id='" + id + '\'' +
", credit=" + credit +
", grade=" + grade +
", name='" + name + '\'' +
'}';
}
}
POIUtils
package com.henu.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Stack;
import com.henu.entity.Classes;
import com.henu.entity.Student;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class POIUtils {
public static final int ST_NAME = 6;
public static final int ST_ID = 5;
public static final int ST_CLASS = 4;
public static final int CL_CREDIT = 9;
public static final int CL_GRADE = 13;
public static void getExcelAsFile(String file) throws FileNotFoundException, IOException, InvalidFormatException {
InputStream ins = null;
Workbook wb = null;
ins = new FileInputStream(new File(file));
wb = WorkbookFactory.create(ins);
ins.close();
//得到Excel工作表对象
Sheet sheet = wb.getSheetAt(0);
//总行数
int trLength = sheet.getLastRowNum();
//得到Excel工作表的行
Row row = sheet.getRow(0);
//总列数
int tdLength = row.getLastCellNum();
Stack<Classes> stack = new Stack<Classes>();
Row rows = null;
HSSFCell cells = null;
List<Student> students = new ArrayList<Student>();
for (int i = 4; i < trLength; i++) {
rows = sheet.getRow(i);
Classes classes = new Classes();
//学生姓名
cells = (HSSFCell) rows.getCell(ST_NAME);
classes.setName(cells.getStringCellValue());
//学生课程学分
cells = (HSSFCell) rows.getCell(CL_CREDIT);
classes.setCredit(cells.getStringCellValue());
//学生该课取得的成绩
cells = (HSSFCell) rows.getCell(CL_GRADE);
classes.setGrade(cells.getStringCellValue());
//学生号
cells = (HSSFCell) rows.getCell(ST_ID);
classes.setId(cells.getStringCellValue());
//学生班级
cells = (HSSFCell) rows.getCell(ST_CLASS);
classes.setClasses(cells.getStringCellValue());
String str = stack.empty() ? "" : stack.peek().getName();
if (stack.empty() || str.equals(classes.getName())) {
stack.push(classes);
} else {
Student student = new Student();
student.setName(stack.peek().getName());
student.setClasses(stack.peek().getClasses());
student.setId(stack.peek().getId());
double credits = 0.0;
double grades = 0.0;
Classes temp = new Classes();
while (!stack.empty()) {
temp = stack.pop();
credits += temp.getCredit();
grades += temp.getGrade() * temp.getCredit();
}
stack.push(classes);
student.setGrade(grades / credits);
students.add(student);
}
}
//成绩计算完毕,创建excel存储数据
CreateExcel(students);
}
//写数据到excel
public static void CreateExcel(List<Student> list) {
String[] str = {"数据科学与大数据技术","数据科学与大数据技术(明德计划)","计算机科学与技术","信息安全","自动化","全部"};
for (String s : str) {
HSSFWorkbook wb = write(list,s);
try {
FileOutputStream fout = new FileOutputStream("out/"+s+".xls");
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
System.out.println("All done!");
}
public static HSSFWorkbook write(List<Student> list, String fileName) {
HSSFWorkbook write = new HSSFWorkbook();
HSSFSheet sheet = write.createSheet("new Sheet");
HSSFRow rowHeader = sheet.createRow(0);
rowHeader.createCell(0).setCellValue("班级");
rowHeader.createCell(1).setCellValue("姓名");
rowHeader.createCell(2).setCellValue("学号");
rowHeader.createCell(3).setCellValue("成绩");
int row_id = 1;
for (Student student : list) {
if (student.getClasses().equals(fileName)||fileName.equals("全部")){
HSSFRow row = sheet.createRow(row_id);
row_id++;
row.createCell(0).setCellValue(student.getClasses());
row.createCell(1).setCellValue(student.getName());
row.createCell(2).setCellValue(student.getId());
row.createCell(3).setCellValue(student.getGrade());
}
}
write.setSheetName(0, fileName);
return write;
}
public static void main(String[] args) {
try {
getExcelAsFile("in/test.xls");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}
}