关于流式计算和Excel的练习题
关于流式计算和Excel的练习题
一、题干
一、给定一组以json文件形式存储的学生数据（具体字段可以参考Student类），完成以下需求
- 读取json数据
- 将读取到的学生数据按以下要求进行分类汇总
（1）计算年龄在20岁以下(不含20岁)的学生的数量
（2）计算女学生中语文成绩最好的学生的姓名
（3）计算所有男学生数学成绩的平均分，保留两位有效数字
（4）计算哪个老师的学生英语平均分最高
（5）将本次考试学生分数榜，按总分降序排序，并存入excel文件中，至少包含字段：学生姓名、语文成绩、数学成绩、英语成绩、总成绩
二、文件
1.json文件
文件名:students.json
点击查看students.json
[
{
"age": 19,
"chineseScore": 55,
"englishScore": 31,
"gender": 0,
"id": 1,
"mathScore": 75,
"name": "赵洋洋",
"teacherName": "王老师"
},
{
"age": 19,
"chineseScore": 42,
"englishScore": 80,
"gender": 1,
"id": 2,
"mathScore": 48,
"name": "吴化腾",
"teacherName": "钱老师"
},
{
"age": 19,
"chineseScore": 89,
"englishScore": 80,
"gender": 0,
"id": 3,
"mathScore": 53,
"name": "郑韵韵",
"teacherName": "钱老师"
},
{
"age": 19,
"chineseScore": 50,
"englishScore": 44,
"gender": 0,
"id": 4,
"mathScore": 50,
"name": "马双",
"teacherName": "吴老师"
},
{
"age": 19,
"chineseScore": 35,
"englishScore": 81,
"gender": 0,
"id": 5,
"mathScore": 67,
"name": "郑娇娇",
"teacherName": "李老师"
},
{
"age": 18,
"chineseScore": 83,
"englishScore": 96,
"gender": 0,
"id": 6,
"mathScore": 96,
"name": "王菲菲",
"teacherName": "王老师"
},
{
"age": 20,
"chineseScore": 66,
"englishScore": 35,
"gender": 0,
"id": 7,
"mathScore": 68,
"name": "周娜",
"teacherName": "钱老师"
},
{
"age": 22,
"chineseScore": 61,
"englishScore": 20,
"gender": 0,
"id": 8,
"mathScore": 53,
"name": "周欣",
"teacherName": "郑老师"
},
{
"age": 22,
"chineseScore": 85,
"englishScore": 97,
"gender": 1,
"id": 9,
"mathScore": 80,
"name": "周鸿志",
"teacherName": "郑老师"
},
{
"age": 20,
"chineseScore": 45,
"englishScore": 61,
"gender": 1,
"id": 10,
"mathScore": 42,
"name": "孙俊杰",
"teacherName": "吴老师"
},
{
"age": 19,
"chineseScore": 99,
"englishScore": 91,
"gender": 0,
"id": 11,
"mathScore": 64,
"name": "马欣",
"teacherName": "吴老师"
},
{
"age": 20,
"chineseScore": 74,
"englishScore": 39,
"gender": 0,
"id": 12,
"mathScore": 75,
"name": "李菲菲",
"teacherName": "周老师"
},
{
"age": 18,
"chineseScore": 35,
"englishScore": 89,
"gender": 0,
"id": 13,
"mathScore": 20,
"name": "马佳妮",
"teacherName": "孙老师"
},
{
"age": 18,
"chineseScore": 22,
"englishScore": 21,
"gender": 1,
"id": 14,
"mathScore": 72,
"name": "赵俊杰",
"teacherName": "钱老师"
},
{
"age": 21,
"chineseScore": 56,
"englishScore": 72,
"gender": 0,
"id": 15,
"mathScore": 93,
"name": "马晓芸",
"teacherName": "马老师"
},
{
"age": 18,
"chineseScore": 94,
"englishScore": 30,
"gender": 1,
"id": 16,
"mathScore": 51,
"name": "吴明",
"teacherName": "周老师"
},
{
"age": 19,
"chineseScore": 66,
"englishScore": 32,
"gender": 1,
"id": 17,
"mathScore": 61,
"name": "李正雄",
"teacherName": "孙老师"
},
{
"age": 20,
"chineseScore": 40,
"englishScore": 30,
"gender": 1,
"id": 18,
"mathScore": 72,
"name": "吴封",
"teacherName": "马老师"
},
{
"age": 19,
"chineseScore": 43,
"englishScore": 33,
"gender": 1,
"id": 19,
"mathScore": 46,
"name": "赵强",
"teacherName": "马老师"
},
{
"age": 18,
"chineseScore": 76,
"englishScore": 86,
"gender": 0,
"id": 20,
"mathScore": 38,
"name": "孙可",
"teacherName": "赵老师"
},
{
"age": 20,
"chineseScore": 28,
"englishScore": 47,
"gender": 1,
"id": 21,
"mathScore": 77,
"name": "马鸿志",
"teacherName": "马老师"
},
{
"age": 22,
"chineseScore": 25,
"englishScore": 31,
"gender": 1,
"id": 22,
"mathScore": 33,
"name": "李强",
"teacherName": "吴老师"
},
{
"age": 18,
"chineseScore": 62,
"englishScore": 43,
"gender": 0,
"id": 23,
"mathScore": 88,
"name": "孙楠",
"teacherName": "李老师"
},
{
"age": 19,
"chineseScore": 43,
"englishScore": 25,
"gender": 0,
"id": 24,
"mathScore": 29,
"name": "王婷婷",
"teacherName": "吴老师"
},
{
"age": 22,
"chineseScore": 87,
"englishScore": 67,
"gender": 0,
"id": 25,
"mathScore": 93,
"name": "孙可",
"teacherName": "赵老师"
},
{
"age": 18,
"chineseScore": 36,
"englishScore": 82,
"gender": 1,
"id": 26,
"mathScore": 57,
"name": "王明",
"teacherName": "周老师"
},
{
"age": 22,
"chineseScore": 66,
"englishScore": 61,
"gender": 1,
"id": 27,
"mathScore": 88,
"name": "王成功",
"teacherName": "吴老师"
},
{
"age": 22,
"chineseScore": 58,
"englishScore": 23,
"gender": 1,
"id": 28,
"mathScore": 27,
"name": "孙化腾",
"teacherName": "孙老师"
},
{
"age": 18,
"chineseScore": 50,
"englishScore": 28,
"gender": 0,
"id": 29,
"mathScore": 96,
"name": "钱晓芸",
"teacherName": "王老师"
},
{
"age": 19,
"chineseScore": 39,
"englishScore": 81,
"gender": 1,
"id": 30,
"mathScore": 30,
"name": "郑俊杰",
"teacherName": "周老师"
},
{
"age": 22,
"chineseScore": 94,
"englishScore": 42,
"gender": 0,
"id": 31,
"mathScore": 69,
"name": "赵娇娇",
"teacherName": "赵老师"
},
{
"age": 21,
"chineseScore": 37,
"englishScore": 55,
"gender": 1,
"id": 32,
"mathScore": 93,
"name": "吴伟",
"teacherName": "周老师"
},
{
"age": 20,
"chineseScore": 79,
"englishScore": 69,
"gender": 0,
"id": 33,
"mathScore": 47,
"name": "王晓芸",
"teacherName": "郑老师"
},
{
"age": 21,
"chineseScore": 29,
"englishScore": 73,
"gender": 0,
"id": 34,
"mathScore": 96,
"name": "孙欣",
"teacherName": "周老师"
},
{
"age": 21,
"chineseScore": 48,
"englishScore": 60,
"gender": 1,
"id": 35,
"mathScore": 46,
"name": "赵俊豪",
"teacherName": "李老师"
},
{
"age": 22,
"chineseScore": 70,
"englishScore": 50,
"gender": 0,
"id": 36,
"mathScore": 22,
"name": "吴佳妮",
"teacherName": "钱老师"
},
{
"age": 21,
"chineseScore": 84,
"englishScore": 35,
"gender": 1,
"id": 37,
"mathScore": 30,
"name": "吴正雄",
"teacherName": "马老师"
},
{
"age": 22,
"chineseScore": 73,
"englishScore": 69,
"gender": 0,
"id": 38,
"mathScore": 43,
"name": "郑娇娇",
"teacherName": "李老师"
},
{
"age": 20,
"chineseScore": 40,
"englishScore": 71,
"gender": 0,
"id": 39,
"mathScore": 63,
"name": "吴菲菲",
"teacherName": "钱老师"
},
{
"age": 19,
"chineseScore": 40,
"englishScore": 57,
"gender": 1,
"id": 40,
"mathScore": 58,
"name": "钱浚伟",
"teacherName": "马老师"
},
{
"age": 20,
"chineseScore": 44,
"englishScore": 88,
"gender": 1,
"id": 41,
"mathScore": 50,
"name": "郑成功",
"teacherName": "王老师"
},
{
"age": 18,
"chineseScore": 80,
"englishScore": 69,
"gender": 1,
"id": 42,
"mathScore": 79,
"name": "周刚",
"teacherName": "王老师"
},
{
"age": 19,
"chineseScore": 63,
"englishScore": 20,
"gender": 1,
"id": 43,
"mathScore": 20,
"name": "李军",
"teacherName": "吴老师"
},
{
"age": 19,
"chineseScore": 36,
"englishScore": 73,
"gender": 0,
"id": 44,
"mathScore": 59,
"name": "郑柔",
"teacherName": "周老师"
},
{
"age": 22,
"chineseScore": 93,
"englishScore": 43,
"gender": 1,
"id": 45,
"mathScore": 40,
"name": "孙俊杰",
"teacherName": "周老师"
},
{
"age": 20,
"chineseScore": 89,
"englishScore": 66,
"gender": 0,
"id": 46,
"mathScore": 71,
"name": "周婷婷",
"teacherName": "马老师"
},
{
"age": 18,
"chineseScore": 36,
"englishScore": 70,
"gender": 1,
"id": 47,
"mathScore": 72,
"name": "郑军",
"teacherName": "马老师"
},
{
"age": 22,
"chineseScore": 25,
"englishScore": 62,
"gender": 0,
"id": 48,
"mathScore": 64,
"name": "吴欣",
"teacherName": "王老师"
},
{
"age": 22,
"chineseScore": 53,
"englishScore": 95,
"gender": 0,
"id": 49,
"mathScore": 91,
"name": "周娇娇",
"teacherName": "周老师"
},
{
"age": 20,
"chineseScore": 24,
"englishScore": 59,
"gender": 1,
"id": 50,
"mathScore": 70,
"name": "吴刚",
"teacherName": "吴老师"
},
{
"age": 20,
"chineseScore": 24,
"englishScore": 77,
"gender": 1,
"id": 51,
"mathScore": 80,
"name": "钱明",
"teacherName": "周老师"
},
{
"age": 20,
"chineseScore": 81,
"englishScore": 92,
"gender": 1,
"id": 52,
"mathScore": 27,
"name": "周伟",
"teacherName": "马老师"
},
{
"age": 18,
"chineseScore": 91,
"englishScore": 88,
"gender": 0,
"id": 53,
"mathScore": 79,
"name": "王欣",
"teacherName": "李老师"
},
{
"age": 20,
"chineseScore": 63,
"englishScore": 65,
"gender": 0,
"id": 54,
"mathScore": 90,
"name": "李胜男",
"teacherName": "马老师"
},
{
"age": 20,
"chineseScore": 59,
"englishScore": 70,
"gender": 0,
"id": 55,
"mathScore": 63,
"name": "李菁菁",
"teacherName": "王老师"
},
{
"age": 18,
"chineseScore": 82,
"englishScore": 20,
"gender": 1,
"id": 56,
"mathScore": 95,
"name": "钱致远",
"teacherName": "郑老师"
},
{
"age": 22,
"chineseScore": 79,
"englishScore": 72,
"gender": 1,
"id": 57,
"mathScore": 24,
"name": "马封",
"teacherName": "钱老师"
},
{
"age": 20,
"chineseScore": 66,
"englishScore": 32,
"gender": 1,
"id": 58,
"mathScore": 41,
"name": "吴致远",
"teacherName": "钱老师"
},
{
"age": 21,
"chineseScore": 63,
"englishScore": 30,
"gender": 1,
"id": 59,
"mathScore": 73,
"name": "钱刚",
"teacherName": "李老师"
},
{
"age": 18,
"chineseScore": 38,
"englishScore": 21,
"gender": 1,
"id": 60,
"mathScore": 29,
"name": "李俊豪",
"teacherName": "王老师"
},
{
"age": 22,
"chineseScore": 55,
"englishScore": 96,
"gender": 0,
"id": 61,
"mathScore": 87,
"name": "赵可",
"teacherName": "孙老师"
},
{
"age": 19,
"chineseScore": 70,
"englishScore": 55,
"gender": 1,
"id": 62,
"mathScore": 49,
"name": "吴俊杰",
"teacherName": "赵老师"
},
{
"age": 22,
"chineseScore": 60,
"englishScore": 89,
"gender": 0,
"id": 63,
"mathScore": 63,
"name": "周娜",
"teacherName": "赵老师"
},
{
"age": 20,
"chineseScore": 40,
"englishScore": 26,
"gender": 0,
"id": 64,
"mathScore": 42,
"name": "李娇娇",
"teacherName": "赵老师"
},
{
"age": 19,
"chineseScore": 66,
"englishScore": 29,
"gender": 1,
"id": 65,
"mathScore": 99,
"name": "钱俊豪",
"teacherName": "钱老师"
},
{
"age": 22,
"chineseScore": 30,
"englishScore": 61,
"gender": 0,
"id": 66,
"mathScore": 56,
"name": "赵菁菁",
"teacherName": "马老师"
},
{
"age": 21,
"chineseScore": 25,
"englishScore": 89,
"gender": 0,
"id": 67,
"mathScore": 56,
"name": "郑胜男",
"teacherName": "李老师"
},
{
"age": 22,
"chineseScore": 95,
"englishScore": 63,
"gender": 0,
"id": 68,
"mathScore": 54,
"name": "孙柔",
"teacherName": "孙老师"
},
{
"age": 22,
"chineseScore": 54,
"englishScore": 64,
"gender": 0,
"id": 69,
"mathScore": 78,
"name": "吴可",
"teacherName": "钱老师"
},
{
"age": 18,
"chineseScore": 35,
"englishScore": 95,
"gender": 0,
"id": 70,
"mathScore": 57,
"name": "马娜",
"teacherName": "钱老师"
},
{
"age": 21,
"chineseScore": 83,
"englishScore": 62,
"gender": 0,
"id": 71,
"mathScore": 49,
"name": "马欣",
"teacherName": "钱老师"
},
{
"age": 19,
"chineseScore": 91,
"englishScore": 35,
"gender": 1,
"id": 72,
"mathScore": 99,
"name": "钱俊豪",
"teacherName": "吴老师"
},
{
"age": 19,
"chineseScore": 91,
"englishScore": 59,
"gender": 1,
"id": 73,
"mathScore": 62,
"name": "马化腾",
"teacherName": "钱老师"
},
{
"age": 21,
"chineseScore": 90,
"englishScore": 80,
"gender": 0,
"id": 74,
"mathScore": 98,
"name": "钱佳妮",
"teacherName": "周老师"
},
{
"age": 19,
"chineseScore": 64,
"englishScore": 50,
"gender": 0,
"id": 75,
"mathScore": 88,
"name": "王可",
"teacherName": "马老师"
},
{
"age": 19,
"chineseScore": 64,
"englishScore": 64,
"gender": 1,
"id": 76,
"mathScore": 25,
"name": "王强",
"teacherName": "赵老师"
},
{
"age": 22,
"chineseScore": 26,
"englishScore": 27,
"gender": 1,
"id": 77,
"mathScore": 22,
"name": "郑俊杰",
"teacherName": "郑老师"
},
{
"age": 20,
"chineseScore": 92,
"englishScore": 67,
"gender": 0,
"id": 78,
"mathScore": 25,
"name": "钱欣",
"teacherName": "周老师"
},
{
"age": 18,
"chineseScore": 27,
"englishScore": 51,
"gender": 0,
"id": 79,
"mathScore": 21,
"name": "李柔",
"teacherName": "郑老师"
},
{
"age": 18,
"chineseScore": 78,
"englishScore": 99,
"gender": 1,
"id": 80,
"mathScore": 60,
"name": "周封",
"teacherName": "赵老师"
},
{
"age": 20,
"chineseScore": 63,
"englishScore": 99,
"gender": 0,
"id": 81,
"mathScore": 57,
"name": "赵胜男",
"teacherName": "赵老师"
},
{
"age": 18,
"chineseScore": 87,
"englishScore": 71,
"gender": 0,
"id": 82,
"mathScore": 94,
"name": "钱婷婷",
"teacherName": "李老师"
},
{
"age": 18,
"chineseScore": 31,
"englishScore": 36,
"gender": 1,
"id": 83,
"mathScore": 70,
"name": "吴浚伟",
"teacherName": "马老师"
},
{
"age": 18,
"chineseScore": 30,
"englishScore": 54,
"gender": 1,
"id": 84,
"mathScore": 26,
"name": "马化腾",
"teacherName": "马老师"
},
{
"age": 18,
"chineseScore": 76,
"englishScore": 20,
"gender": 1,
"id": 85,
"mathScore": 69,
"name": "吴封",
"teacherName": "李老师"
},
{
"age": 21,
"chineseScore": 31,
"englishScore": 69,
"gender": 0,
"id": 86,
"mathScore": 23,
"name": "吴可",
"teacherName": "郑老师"
},
{
"age": 21,
"chineseScore": 82,
"englishScore": 87,
"gender": 0,
"id": 87,
"mathScore": 24,
"name": "郑欣",
"teacherName": "钱老师"
},
{
"age": 18,
"chineseScore": 44,
"englishScore": 46,
"gender": 1,
"id": 88,
"mathScore": 99,
"name": "李成功",
"teacherName": "吴老师"
},
{
"age": 22,
"chineseScore": 43,
"englishScore": 72,
"gender": 0,
"id": 89,
"mathScore": 39,
"name": "吴菲菲",
"teacherName": "李老师"
},
{
"age": 22,
"chineseScore": 92,
"englishScore": 77,
"gender": 1,
"id": 90,
"mathScore": 74,
"name": "李明",
"teacherName": "孙老师"
},
{
"age": 19,
"chineseScore": 26,
"englishScore": 61,
"gender": 1,
"id": 91,
"mathScore": 71,
"name": "郑明",
"teacherName": "马老师"
},
{
"age": 18,
"chineseScore": 61,
"englishScore": 80,
"gender": 0,
"id": 92,
"mathScore": 30,
"name": "周菲菲",
"teacherName": "赵老师"
},
{
"age": 21,
"chineseScore": 54,
"englishScore": 75,
"gender": 0,
"id": 93,
"mathScore": 35,
"name": "马楠",
"teacherName": "吴老师"
},
{
"age": 18,
"chineseScore": 37,
"englishScore": 73,
"gender": 1,
"id": 94,
"mathScore": 98,
"name": "周化腾",
"teacherName": "王老师"
},
{
"age": 22,
"chineseScore": 52,
"englishScore": 52,
"gender": 0,
"id": 95,
"mathScore": 31,
"name": "孙洋洋",
"teacherName": "王老师"
},
{
"age": 22,
"chineseScore": 31,
"englishScore": 80,
"gender": 1,
"id": 96,
"mathScore": 22,
"name": "钱俊豪",
"teacherName": "王老师"
},
{
"age": 21,
"chineseScore": 71,
"englishScore": 98,
"gender": 0,
"id": 97,
"mathScore": 53,
"name": "郑娜",
"teacherName": "王老师"
},
{
"age": 18,
"chineseScore": 53,
"englishScore": 24,
"gender": 1,
"id": 98,
"mathScore": 84,
"name": "赵一鸣",
"teacherName": "马老师"
},
{
"age": 20,
"chineseScore": 63,
"englishScore": 71,
"gender": 1,
"id": 99,
"mathScore": 76,
"name": "赵成功",
"teacherName": "钱老师"
},
{
"age": 19,
"chineseScore": 23,
"englishScore": 65,
"gender": 0,
"id": 100,
"mathScore": 53,
"name": "王楠",
"teacherName": "赵老师"
}
]
2.实体类
实体名称：Student.java
点击Student.java
package com.ccjiuhong.assessment;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 学生类
*
* @author G. Seinfeld
* @since 2020-09-22
*/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Student {
/**
* 主键 唯一标识
*/
private Long id;
/**
* 学生姓名（学生可能重名，以主键作为区分的标识）
*/
private String name;
/**
* 年龄
*/
private Integer age;
/**
* 性别 0-女 1-男
*/
private Integer gender;
/**
* 老师姓名，认为姓名相同的老师为同一老师
*/
private String teacherName;
/**
* 数学成绩（0-100）
*/
private Integer mathScore;
/**
* 语文成绩（0-100）
*/
private Integer chineseScore;
/**
* 英语成绩（0-100）
*/
private Integer englishScore;
}
三、答案
package com.ccjiuhong.assessment;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.OptionalDouble;
import java.util.stream.Collectors;
/**
* 给定一组以json文件形式存储的学生数据（具体字段可以参考Student类），完成以下需求
* 1. 读取json数据
* 2. 将读取到的学生数据按以下要求进行分类汇总
* （1）计算年龄在20岁以下(不含20岁)的学生的数量
* （2）计算女学生中语文成绩最好的学生的姓名
* （3）计算所有男学生数学成绩的平均分，保留两位有效数字
* （4）计算哪个老师的学生英语平均分最高
* （5）将本次考试学生分数榜，按总分降序排序，并存入excel文件中，至少包含字段：学生姓名、语文成绩、数学成绩、英语成绩、总成绩
*
*/
public class JsonHandler {
public static void main(String[] args) throws IOException {
/**
* 随笔：
* 对于zip文件，有专门自己的流：zipInputStream
* byteArrayInputStream ————> 转成 byte数组
*/
// 读取json文件，转为java和list
// 参数是json的路径
Path path = Paths.get("src", "main", "resources", "students.json");
// readAllBytes是一下的读出来，针对的是相对来说比较小的文件，文件大的话用使用：bufferedreader
byte[] bytes = Files.readAllBytes(path);
// 得到字符串，不写UTF-8，则默认有UTF-8
String jsonString = new String(bytes);
// 字符串转List，parseArray是fastjson的方法
JSONArray jsonArray = JSONArray.parseArray(jsonString);
List<Student> students = jsonArray.toJavaList(Student.class);
/**
* 流式运算和sql语句有时候很像
* 比如SQL 语句一样， select username from user where id = 1，你只要说明：
* 我需要 id 是 1 （id = 1）的用户（user）的用户名（username ）”，那么就可以
* 得到自己想要的数据，而不需要自己亲自去数据库里面循环遍历查找。
*
* 而SQL可以操作的，一般流式计算也可以操作。
*
* 流式计算分为3个步骤：
* 1.把集合转成流； 如果是数组，则Stream of（数组）
* 2.对流的串联操作，跳过：skip 。 map：映射，从学生映射到学生姓名，（流是不可变的对象，流的特点是只能用一次，每个操作都要重新点Strem一次）
* 3.流的归并计算，比如 .count
*/
//（1）计算年龄在20岁以下(不含20岁)的学生的数量
long count = students.stream().filter(student -> student.getAge() < 20).count();
System.out.println(count);
//（2）计算女学生中语文成绩最好的学生的姓名
String name = students.stream().filter(student -> student.getGender() == 0).max(Comparator.comparing(Student::getChineseScore)).map(Student::getName).orElse("");
System.out.println(name);
/*
注意：
student -> student.getGender() == 0，如果写成equals，则0要写在前面。
如果student.getGender()是null，和0双等比较，则报 空指针异常
Interger 127以内 是相等的，以外是不等的
/Comparator.comparing 里面传的是需要比较的字段
*/
//（3）计算所有男学生数学成绩的平均分
DecimalFormat decimalFormat = new DecimalFormat("#.00");// 保留2位小数： DecimalFormat("#.00")
OptionalDouble average = students.stream().filter(student -> student.getGender() == 1).map(Student::getMathScore).mapToInt(i -> i).average();
double v = average.orElse(0);
System.out.println(decimalFormat.format(v));
//（4）计算哪个老师的学生英语平均分最高
Map<String, Double> collect = students.stream()
.collect(Collectors.groupingBy(Student::getTeacherName, Collectors.averagingDouble(Student::getEnglishScore))
);
String teacherName = collect.entrySet().stream().max(Map.Entry.comparingByValue()).map(Map.Entry::getKey).orElse("");
System.out.println(teacherName);
//Entry 是Map的内部静态类
//外部类不能是静态类，静态的东西是类的东西，非静态的是对象的东西
// (5) 打印本次考试学生分数榜，按总分降序排序，打印字段 学生姓名、语文成绩、数学成绩、英语成绩、总成绩
//1.创建Excel工作簿 HSSF（03版本）、XSSF（07版本） 2种
Workbook workbook = new XSSFWorkbook();
//2.根据工作簿来创建工作表
Sheet sheet = workbook.createSheet();
//3.再用工作表创建行，传一个行号，从0开始
Row row = sheet.createRow(0);
//4.根据行创建列，列号从0开始
Cell cell = row.createCell(0);
cell.setCellValue("学生姓名");
cell = row.createCell(1);
cell.setCellValue("语文成绩");
cell = row.createCell(2);
cell.setCellValue("数学成绩");
cell = row.createCell(3);
cell.setCellValue("英语成绩");
cell = row.createCell(4);
cell.setCellValue("总成绩");
int[] i = {1};
//数据的准备：
//sorted：排序 Comparator.comparing 按照负的总分来排序，即按照总成绩降序
students.stream().sorted(Comparator.comparing(s -> -(s.getChineseScore() + s.getEnglishScore() + s.getMathScore())))
.forEach(
s -> {
Row newRow = sheet.createRow(i[0]++);
Cell newCell = newRow.createCell(0);
newCell.setCellValue(s.getName());
newCell = newRow.createCell(1);
newCell.setCellValue(s.getChineseScore());
newCell = newRow.createCell(2);
newCell.setCellValue(s.getMathScore());
newCell = newRow.createCell(3);
newCell.setCellValue(s.getEnglishScore());
newCell = newRow.createCell(4);
newCell.setCellValue(s.getChineseScore() + s.getEnglishScore() + s.getMathScore());
}
);
Path outputPath = Paths.get("src", "main", "resources", "out.xlsx");
OutputStream outputStream = Files.newOutputStream(outputPath);
workbook.write(outputStream);
}
}
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 单线程的Redis速度为什么快？
· 展开说说关于C#中ORM框架的用法！
· Pantheons：用 TypeScript 打造主流大模型对话的一站式集成库
· SQL Server 2025 AI相关能力初探
· 为什么 退出登录 或 修改密码 无法使 token 失效