Java计算加权平均成绩

记一次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();
        }
    }
}