国民体质测定标准手册及标准解析成JSON文件计算分数，java解析excel文件
【摘要】 大家好，我是雄雄，欢迎关注微信公众号：雄雄的小课堂 前言现在是：2022年6月14日10:07:27最近在做体质测评的功能，需要依据《国民体质测定标准手册及标准》，根据用户的个人信息，从而计算出各个指标的的分；大致思路就是先将国家提供的标准，整理成JSON文件，然后用java代码调用JSON数据，从而计算出得分。 涉及技能点java解析excel文件。封装json格式的数据。 实现思路将国家...
大家好，我是雄雄，欢迎关注微信公众号：雄雄的小课堂
前言
现在是：2022年6月14日10:07:27
最近在做体质测评的功能，需要依据《国民体质测定标准手册及标准》，根据用户的个人信息，从而计算出各个指标的的分；大致思路就是先将国家提供的标准，整理成JSON文件，然后用java代码调用JSON数据，从而计算出得分。
涉及技能点
java解析excel文件。- 封装
json格式的数据。
实现思路
- 将国家提供的标准表格文件复制到
excel电子表格中。
- 解析excel表格，然后生成
json文件。 - 根据个人信息，从
JSON中查询对应的分数
代码实现
-
直接将标准表格复制到
excel中,复制完成之后简单的修改修改，不然解析起来麻烦，如下所示： -
解析
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数据，计算对应的分数。
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）