国民体质测定标准手册及标准解析成JSON文件计算分数,java解析excel文件

 muxiongxiong   2022-06-14 10:46   190 人阅读  0 条评论

大家好,我是雄雄,欢迎关注微信公众号:雄雄的小课堂

前言

现在是:2022年6月14日10:07:27

最近在做体质测评的功能,需要依据《国民体质测定标准手册及标准》,根据用户的个人信息,从而计算出各个指标的的分;大致思路就是先将国家提供的标准,整理成JSON文件,然后用java代码调用JSON数据,从而计算出得分。

涉及技能点

  1. java解析excel文件。
  2. 封装json格式的数据。

实现思路

  1. 将国家提供的标准表格文件复制到excel电子表格中。
  2. 解析excel表格,然后生成json文件。
  3. 根据个人信息,从JSON中查询对应的分数

代码实现

  1. 直接将标准表格复制到excel中,复制完成之后简单的修改修改,不然解析起来麻烦,如下所示:

  2. 解析excel的代码如下:

	/**
	 * 20-29 岁成年人身高标准体重评分表(男)
	 */
	public static void adultsWithHeightWeightStandardScoreSheet() {
		String excelPath = System.getProperty("user.dir")
			+ "/src/main/excelFile/women-50-59height_and_weight_score.xlsx";
		try {
			//String encoding = "GBK";
			File excel = new File(excelPath);
			//判断文件是否存在
			if (excel.isFile() && excel.exists()) {
				//.是特殊字符,需要转义!!!!!
				String[] split = excel.getName().split("\\.");
				Workbook wb;
				//根据文件后缀(xls/xlsx)进行判断
				if ("xls".equals(split[1])) {
					//文件流对象
					FileInputStream fis = new FileInputStream(excel);
					wb = new HSSFWorkbook(fis);
				} else if ("xlsx".equals(split[1])) {
					wb = new XSSFWorkbook(excel);
				} else {
					System.out.println("文件类型错误!");
					return;
				}

				//开始解析
				//读取sheet 0
				Sheet sheet = wb.getSheetAt(0);

				//第一行
				int firstRowIndex = sheet.getFirstRowNum();
				//最后一行
				int lastRowIndex = sheet.getLastRowNum();
				System.out.println("一共有这么多行:"+lastRowIndex);
				System.out.println("**************开始执行**************");
				List<ScoreExcelVO>  scoreExcelVOList = new ArrayList<>();
				JSONArray jsonArrayResult = new JSONArray();
				//遍历行
				for (int rIndex = firstRowIndex; rIndex < lastRowIndex; rIndex++) {
					Row row = sheet.getRow(rIndex);
					if (row != null) {
						//身高
						Cell cell_height = row.getCell(0);
						String height = cell_height.toString();

						//1分
						Cell cell_one_score1 = row.getCell(1);
						String one_score1 = cell_one_score1.toString();

						//3分
						Cell cell_three_score = row.getCell(2);
						String three_score = cell_three_score.toString();

						//5分
						Cell cell_five_score = row.getCell(3);
						String five_score = cell_five_score.toString();

						//3分
						Cell cell_three_score2 = row.getCell(4);
						String three_score2 = cell_three_score2.toString();

						//1分
						Cell cell_one_score2 = row.getCell(5);
						String one_score2 = cell_one_score2.toString();

						//将值放在实体类中
						ScoreExcelVO scoreExcelVO = new ScoreExcelVO();
						scoreExcelVO.setHeight(height);
						scoreExcelVO.setFirstOneScore(one_score1);
						scoreExcelVO.setFirstThreeScore(three_score);
						scoreExcelVO.setFiveScore(five_score);
						scoreExcelVO.setSecondOneScore(one_score2);
						scoreExcelVO.setSecondThreeScore(three_score2);
						scoreExcelVOList.add(scoreExcelVO);
					}
				}

				JSONArray array = new JSONArray();
				//将集合转换成JSON格式的数据
				//JSONArray scoreExcelVOArray = JSONArray.parseArray(JSON.toJSONString(scoreExcelVOList));
				scoreExcelVOList.forEach(scoreExcelVO->{
					JSONObject obj = new JSONObject();
					obj.put(scoreExcelVO.getHeight(),scoreExcelVO);
					array.add(obj);
				});
				String str = "";
				for(Object o : array){
					String old = o.toString().substring(1,o.toString().length()-1);
					str+=old+",";
				}
				//将最后一个逗号截取掉
				str = str.substring(0,str.length()-1);
				str = "{"+str+"}";
				System.out.println(str);
			} else {
				System.out.println("找不到指定的文件");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

通过在main方法中调用,生成的json数据如下:

	{
  "144.0-144.9": {
    "firstOneScore": "<36.6",
    "firstThreeScore": "36.6-37.6",
    "fiveScore": "37.7-48.2",
    "height": "144.0-144.9",
    "secondOneScore": ">52.3",
    "secondThreeScore": "48.3-52.3"
  },
  "145.0-145.9": {
    "firstOneScore": "<37.1",
    "firstThreeScore": "37.1-38.1",
    "fiveScore": "38.2-49.0",
    "height": "145.0-145.9",
    "secondOneScore": ">53.0",
    "secondThreeScore": "49.1-53.0"
  },
  "146.0-146.9": {
    "firstOneScore": "<37.7",
    "firstThreeScore": "37.7-38.6",
    "fiveScore": "38.7-49.8",
    "height": "146.0-146.9",
    "secondOneScore": ">53.8",
    "secondThreeScore": "49.9-53.8"
  },
  "147.0-147.9": {
    "firstOneScore": "<38.3",
    "firstThreeScore": "38.3-39.2",
    "fiveScore": "39.3-50.6",
    "height": "147.0-147.9",
    "secondOneScore": ">54.6",
    "secondThreeScore": "50.7-54.6"
  },
  "148.0-148.9": {
    "firstOneScore": "<38.9",
    "firstThreeScore": "38.9-39.7",
    "fiveScore": "39.8-51.4",
    "height": "148.0-148.9",
    "secondOneScore": ">55.4",
    "secondThreeScore": "51.5-55.4"
  },
  "149.0-149.9": {
    "firstOneScore": "<39.9",
    "firstThreeScore": "39.9-40.4",
    "fiveScore": "40.5-52.1",
    "height": "149.0-149.9",
    "secondOneScore": ">56.2",
    "secondThreeScore": "52.2-56.2"
  },
  "150.0-150.9": {
    "firstOneScore": "<40.5",
    "firstThreeScore": "40.5-41.1",
    "fiveScore": "41.2-52.9",
    "height": "150.0-150.9",
    "secondOneScore": ">57.1",
    "secondThreeScore": "53.0-57.1"
  },
  "151.0-151.9": {
    "firstOneScore": "<41.0",
    "firstThreeScore": "41.0-41.7",
    "fiveScore": "41.8-53.8",
    "height": "151.0-151.9",
    "secondOneScore": ">58.0",
    "secondThreeScore": "53.9-58.0"
  },
  "152.0-152.9": {
    "firstOneScore": "<41.6",
    "firstThreeScore": "41.6-42.4",
    "fiveScore": "42.5-54.6",
    "height": "152.0-152.9",
    "secondOneScore": ">59.0",
    "secondThreeScore": "54.7-59.0"
  },
  "153.0-153.9": {
    "firstOneScore": "<42.2",
    "firstThreeScore": "42.2-43.2",
    "fiveScore": "43.3-55.6",
    "height": "153.0-153.9",
    "secondOneScore": ">59.8",
    "secondThreeScore": "55.7-59.8"
  },
  "154.0-154.9": {
    "firstOneScore": "<42.8",
    "firstThreeScore": "42.8-44.0",
    "fiveScore": "44.1-56.7",
    "height": "154.0-154.9",
    "secondOneScore": ">60.9",
    "secondThreeScore": "56.8-60.9"
  },
  "155.0-155.9": {
    "firstOneScore": "<43.4",
    "firstThreeScore": "43.4-44.7",
    "fiveScore": "44.8-57.8",
    "height": "155.0-155.9",
    "secondOneScore": ">61.9",
    "secondThreeScore": "57.9-61.9"
  },
  "156.0-156.9": {
    "firstOneScore": "<44.0",
    "firstThreeScore": "44.0-45.4",
    "fiveScore": "45.5-58.8",
    "height": "156.0-156.9",
    "secondOneScore": ">62.9",
    "secondThreeScore": "58.9-62.9"
  },
  "157.0-157.9": {
    "firstOneScore": "<44.5",
    "firstThreeScore": "44.5-46.0",
    "fiveScore": "46.1-59.7",
    "height": "157.0-157.9",
    "secondOneScore": ">64.0",
    "secondThreeScore": "59.8-64.0"
  },
  "158.0-158.9": {
    "firstOneScore": "<45.0",
    "firstThreeScore": "45.0-46.9",
    "fiveScore": "47.0-61.8",
    "height": "158.0-158.9",
    "secondOneScore": ">65.1",
    "secondThreeScore": "61.9-65.1"
  },
  "159.0-159.9": {
    "firstOneScore": "<45.5",
    "firstThreeScore": "45.5-47.6",
    "fiveScore": "47.7-61.9",
    "height": "159.0-159.9",
    "secondOneScore": ">66.1",
    "secondThreeScore": "62.0-66.1"
  },
  "160.0-160.9": {
    "firstOneScore": "<46.0",
    "firstThreeScore": "46.0-48.5",
    "fiveScore": "48.6-62.9",
    "height": "160.0-160.9",
    "secondOneScore": ">67.2",
    "secondThreeScore": "63.0-67.2"
  },
  "161.0-161.9": {
    "firstOneScore": "<46.7",
    "firstThreeScore": "46.7-49.2",
    "fiveScore": "49.3-63.8",
    "height": "161.0-161.9",
    "secondOneScore": ">68.2",
    "secondThreeScore": "63.9-68.2"
  },
  "162.0-162.9": {
    "firstOneScore": "<47.3",
    "firstThreeScore": "47.3-50.1",
    "fiveScore": "50.2-64.9",
    "height": "162.0-162.9",
    "secondOneScore": ">69.0",
    "secondThreeScore": "65.0-69.0"
  },
  "163.0-163.9": {
    "firstOneScore": "<47.8",
    "firstThreeScore": "47.8-51.0",
    "fiveScore": "51.1-65.9",
    "height": "163.0-163.9",
    "secondOneScore": ">70.1",
    "secondThreeScore": "66.0-70.1"
  },
  "164.0-164.9": {
    "firstOneScore": "<48.4",
    "firstThreeScore": "48.4-51.6",
    "fiveScore": "51.7-67.0",
    "height": "164.0-164.9",
    "secondOneScore": ">71.0",
    "secondThreeScore": "67.1-71.0"
  },
  "165.0-165.9": {
    "firstOneScore": "<48.9",
    "firstThreeScore": "48.9-52.2",
    "fiveScore": "52.3-67.8",
    "height": "165.0-165.9",
    "secondOneScore": ">72.1",
    "secondThreeScore": "67.9-72.1"
  },
  "166.0-166.9": {
    "firstOneScore": "<49.4",
    "firstThreeScore": "49.4-53.0",
    "fiveScore": "53.1-68.7",
    "height": "166.0-166.9",
    "secondOneScore": ">72.9",
    "secondThreeScore": "68.8-72.9"
  },
  "167.0-167.9": {
    "firstOneScore": "<49.9",
    "firstThreeScore": "49.9-53.6",
    "fiveScore": "53.7-69.6",
    "height": "167.0-167.9",
    "secondOneScore": ">73.8",
    "secondThreeScore": "69.7-73.8"
  },
  "168.0-168.9": {
    "firstOneScore": "<50.5",
    "firstThreeScore": "50.0-54.3",
    "fiveScore": "54.4-70.4",
    "height": "168.0-168.9",
    "secondOneScore": ">75.0",
    "secondThreeScore": "70.5-75.0"
  },
  "169.0-169.9": {
    "firstOneScore": "<51.2",
    "firstThreeScore": "51.2-55.0",
    "fiveScore": "55.1-71.2",
    "height": "169.0-169.9",
    "secondOneScore": ">75.9",
    "secondThreeScore": "71.3-75.9"
  },
  "170.0-170.9": {
    "firstOneScore": "<52.0",
    "firstThreeScore": "52.0-55.7",
    "fiveScore": "55.8-72.1",
    "height": "170.0-170.9",
    "secondOneScore": ">76.8",
    "secondThreeScore": "72.2-76.8"
  },
  "171.0-171.9": {
    "firstOneScore": "<52.7",
    "firstThreeScore": "52.7-56.6",
    "fiveScore": "56.7-73.1",
    "height": "171.0-171.9",
    "secondOneScore": ">77.9",
    "secondThreeScore": "73.2-77.9"
  },
  "172.0-172.9": {
    "firstOneScore": "<53.5",
    "firstThreeScore": "53.5-57.5",
    "fiveScore": "57.6-74.0",
    "height": "172.0-172.9",
    "secondOneScore": ">79.1",
    "secondThreeScore": "74.1-79.1"
  },
  "173.0-173.9": {
    "firstOneScore": "<54.1",
    "firstThreeScore": "54.1-58.3",
    "fiveScore": "58.4-75.0",
    "height": "173.0-173.9",
    "secondOneScore": ">80.0",
    "secondThreeScore": "75.1-80.0"
  },
  "174.0-174.9": {
    "firstOneScore": "<54.6",
    "firstThreeScore": "54.6-59.2",
    "fiveScore": "59.3-75.9",
    "height": "174.0-174.9",
    "secondOneScore": ">81.1",
    "secondThreeScore": "76.0-81.1"
  },
  "175.0-175.9": {
    "firstOneScore": "<55.2",
    "firstThreeScore": "55.2-60.0",
    "fiveScore": "60.1-76.9",
    "height": "175.0-175.9",
    "secondOneScore": ">82.0",
    "secondThreeScore": "77.0-82.0"
  },
  "176.0-176.9": {
    "firstOneScore": "<55.9",
    "firstThreeScore": "55.9-60.8",
    "fiveScore": "60.9-77.9",
    "height": "176.0-176.9",
    "secondOneScore": ">83.0",
    "secondThreeScore": "78.0-83.0"
  },
  "177.0-177.9": {
    "firstOneScore": "<56.5",
    "firstThreeScore": "56.5-61.3",
    "fiveScore": "61.4-78.9",
    "height": "177.0-177.9",
    "secondOneScore": ">84.1",
    "secondThreeScore": "79.0-84.1"
  },
  "178.0-178.9": {
    "firstOneScore": "<57.1",
    "firstThreeScore": "57.1-62.1",
    "fiveScore": "62.2-80.0",
    "height": "178.0-178.9",
    "secondOneScore": ">85.0",
    "secondThreeScore": "80.1-85.0"
  },
  "179.0-179.9": {
    "firstOneScore": "<57.7",
    "firstThreeScore": "57.7-62.7",
    "fiveScore": "62.8-81.2",
    "height": "179.0-179.9",
    "secondOneScore": ">86.1",
    "secondThreeScore": "81.3-86.1"
  },
  "180.0-180.9": {
    "firstOneScore": "<58.4",
    "firstThreeScore": "58.4-63.3",
    "fiveScore": "63.4-82.4",
    "height": "180.0-180.9",
    "secondOneScore": ">87.1",
    "secondThreeScore": "82.5-87.1"
  },
  "181.0-181.9": {
    "firstOneScore": "<58.9",
    "firstThreeScore": "58.9-64.2",
    "fiveScore": "64.3-83.5",
    "height": "181.0-181.9",
    "secondOneScore": ">88.1",
    "secondThreeScore": "83.6-88.1"
  },
  "182.0-182.9": {
    "firstOneScore": "<59.5",
    "firstThreeScore": "59.5-64.9",
    "fiveScore": "65.0-84.7",
    "height": "182.0-182.9",
    "secondOneScore": ">89.1",
    "secondThreeScore": "84.8-89.1"
  },
  "183.0-183.9": {
    "firstOneScore": "<60.2",
    "firstThreeScore": "60.2-65.7",
    "fiveScore": "65.8-85.7",
    "height": "183.0-183.9",
    "secondOneScore": ">90.2",
    "secondThreeScore": "85.8-90.2"
  },
  "184.0-184.9": {
    "firstOneScore": "<60.8",
    "firstThreeScore": "60.8-66.4",
    "fiveScore": "66.5-86.8",
    "height": "184.0-184.9",
    "secondOneScore": ">91.2",
    "secondThreeScore": "86.9-91.2"
  },
  "185.0-185.9": {
    "firstOneScore": "<61.4",
    "firstThreeScore": "61.4-67.1",
    "fiveScore": "67.2-87.7",
    "height": "185.0-185.9",
    "secondOneScore": ">92.2",
    "secondThreeScore": "87.8-92.2"
  },
  "186.0-186.9": {
    "firstOneScore": "<62.0",
    "firstThreeScore": "62.0-67.9",
    "fiveScore": "68.0-89.8",
    "height": "186.0-186.9",
    "secondOneScore": ">93.3",
    "secondThreeScore": "89.9-93.3"
  },
  "187.0-187.9": {
    "firstOneScore": "<62.7",
    "firstThreeScore": "62.7-68.7",
    "fiveScore": "68.8-89.7",
    "height": "187.0-187.9",
    "secondOneScore": ">94.4",
    "secondThreeScore": "89.8-94.4"
  },
  "188.0-188.9": {
    "firstOneScore": "<63.3",
    "firstThreeScore": "63.3-69.4",
    "fiveScore": "69.5-90.8",
    "height": "188.0-188.9",
    "secondOneScore": ">95.5",
    "secondThreeScore": "90.9-95.5"
  },
  "189.0-189.9": {
    "firstOneScore": "<64.0",
    "firstThreeScore": "64.0-70.4",
    "fiveScore": "70.5-91.7",
    "height": "189.0-189.9",
    "secondOneScore": ">96.6",
    "secondThreeScore": "91.8-96.6"
  },
  "190.0-190.9": {
    "firstOneScore": "<64.6",
    "firstThreeScore": "64.6-71.1",
    "fiveScore": "71.2-92.7",
    "height": "190.0-190.9",
    "secondOneScore": ">97.7",
    "secondThreeScore": "92.8-97.7"
  },
  "191.0-191.9": {
    "firstOneScore": "<65.2",
    "firstThreeScore": "65.2-71.9",
    "fiveScore": "72.0-93.8",
    "height": "191.0-191.9",
    "secondOneScore": ">98.7",
    "secondThreeScore": "93.9-98.7"
  },
  "192.0-192.9": {
    "firstOneScore": "<65.9",
    "firstThreeScore": "65.9-72.9",
    "fiveScore": "73.0-95.0",
    "height": "192.0-192.9",
    "secondOneScore": ">99.8",
    "secondThreeScore": "95.1-99.8"
  },
  "193.0-193.9": {
    "firstOneScore": "<66.6",
    "firstThreeScore": "66.6-73.6",
    "fiveScore": "73.7-96.2",
    "height": "193.0-193.9",
    "secondOneScore": ">101.0",
    "secondThreeScore": "96.3-101.0"
  },
  "194.0-194.9": {
    "firstOneScore": "<67.3",
    "firstThreeScore": "67.3-74.5",
    "fiveScore": "74.6-97.4",
    "height": "194.0-194.9",
    "secondOneScore": ">102.1",
    "secondThreeScore": "97.5-102.1"
  },
  "195.0-195.9": {
    "firstOneScore": "<67.9",
    "firstThreeScore": "67.9-75.3",
    "fiveScore": "75.4-98.5",
    "height": "195.0-195.9",
    "secondOneScore": ">103.3",
    "secondThreeScore": "98.6-103.3"
  },
  "196.0-196.9": {
    "firstOneScore": "<68.6",
    "firstThreeScore": "68.6-76.1",
    "fiveScore": "76.2-99.6",
    "height": "196.0-196.9",
    "secondOneScore": ">104.5",
    "secondThreeScore": "99.7-104.5"
  },
  "197.0-197.0": {
    "firstOneScore": "<69.3",
    "firstThreeScore": "69.3-77.1",
    "fiveScore": "77.2-100.7",
    "height": "197.0-197.0",
    "secondOneScore": ">105.7",
    "secondThreeScore": "100.8-105.7"
  },
  "198.0-198.9": {
    "firstOneScore": "<70.0",
    "firstThreeScore": "70.0-78.0",
    "fiveScore": "78.1-101.8",
    "height": "198.0-198.9",
    "secondOneScore": ">106.8",
    "secondThreeScore": "101.9-106.8"
  },
  "199.0-199.9": {
    "firstOneScore": "<71.8",
    "firstThreeScore": "71.8-79.1",
    "fiveScore": "79.2-102.6",
    "height": "199.0-199.9",
    "secondOneScore": ">107.8",
    "secondThreeScore": "102.7-107.8"
  }
}

至此,解析json文件完成,后期分享一下,如何根据json数据,计算对应的分数。

本文地址:http://xn--ypzn79ca.com/?id=1400
版权声明:本文为原创文章,版权归 muxiongxiong 所有,欢迎分享本文,转载请保留出处!

 发表评论


表情

还没有留言,还不快点抢沙发?