关于流式计算和Excel的练习题

关于流式计算和Excel的练习题

一、题干

一、给定一组以json文件形式存储的学生数据（具体字段可以参考Student类），完成以下需求

1. 读取json数据
2. 将读取到的学生数据按以下要求进行分类汇总
   （1）计算年龄在20岁以下(不含20岁)的学生的数量
   （2）计算女学生中语文成绩最好的学生的姓名
   （3）计算所有男学生数学成绩的平均分，保留两位有效数字
   （4）计算哪个老师的学生英语平均分最高
   （5）将本次考试学生分数榜，按总分降序排序，并存入excel文件中，至少包含字段：学生姓名、语文成绩、数学成绩、英语成绩、总成绩

二、文件

1.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

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);

    }
}