使用js将多个相关联的excel数据文件整合到一起并筛选出需要的数据的整理

87 阅读 0 评论 58 点赞

我是靠谱客的博主背后火龙果，最近开发中收集的这篇文章主要介绍使用js将多个相关联的excel数据文件整合到一起并筛选出需要的数据的整理，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

最近，因为业务需要遇到一个整合筛选excel数据的项目。
两个excel文件，一个是按获奖人名整理的获奖数据（分老师和学生两种身份，获奖类型分一、二、三等奖），另一个是按报送学校整理的数据（学校类型分为高职和中职），报送的学校要比获奖的学校个数多，业务需求是按学校类型统计获奖学校的老师和学生每一个获奖类型的个数。
首先推荐一个把excel转为json的在线网站 http://www.bejson.com/json/col2json/
举例说明两个json数据：
var data= [ {"id":"1","school_name":"***学院","address":"北京","获奖用户姓名":"张三","level":"一等奖","type":"高职","zw":"老师"}，
{"id":"2","school_name":"***学院","address":"北京","获奖用户姓名":"赵四","level":"二等奖","type":"高职","zw":"学生"}]
var s_all=[ {"id":"1","adress":"北京,"school_name":"***学院","type":"中职"},{"id":"2","adress":"北京,"school_name":"***学院","type":"高职"}]
1、创建两个数组分别存放老师和学生的数据
var info_list=[];
var info_student_list=[];
2、循环s_all
for( var j=0;j<s_all.length;j++) {
var info={"school_name":" ","address":" ","type":" ","teacher_one":"","teacher_two":"","teacher_three":"","teacher_four":" “};//按老师身份声明对象
var info_student={"school_name":"","address":" ","type":" ",student_one:" ",student_two:" ",student_three:" ",student_four:" "};//按学生身份申明对象
var school1=[];//存放筛选出的获奖学校
var school1_t=[];//存放获奖学校的老师数据
var school1_t_one=[];//存放获得一等奖的老师
var school1_t_two=[];
var school1_t_three=[];
var school1_t_four=[];
var school1_s=[];//存放获奖学校的学生数据
var school1_s_one=[];//存放获得一等奖的学生
var school1_s_two=[];
var school1_s_three=[];
var school1_s_four=[];

info.school_name = s_all[j].school_name;
info.address = s_all[j].adress;
info.type = s_all[j].type;
info_student.school_name = s_all[j].school_name;
info_student.address = s_all[j].adress;
info_student.type = s_all[j].type;
//筛选获奖学校
for (var i = 0; i < data.length; i++) {
if (data[i].school_name == s_all[j].school_name) {//筛选出获奖学校
school1.push(data[i]);
}
}
//老师
for (var i = 0; i < school1.length; i++) {
if (school1[i].zw == "老师") {
school1_t.push(school1[i]);
}
}
for (var i = 0; i < school1_t.length; i++) {
if (school1_t[i].level == "一等奖") {
school1_t_one.push(school1_t[i]);
}
}
for (var i = 0; i < school1_t.length; i++) {
if (school1_t[i].level == "二等奖") {
school1_t_two.push(school1_t[i]);
}
}
for (var i = 0; i < school1_t.length; i++) {
if (school1_t[i].level == "三等奖") {
school1_t_three.push(school1_t[i]);
}
}
for (var i = 0; i < school1_t.length; i++) {
if (school1_t[i].level == "优秀奖") {
school1_t_four.push(school1_t[i]);
}
}
//学生
for (var i = 0; i < school1.length; i++) {
if (school1[i].zw == "学生") {
school1_s.push(school1[i]);
}
}
for (var i = 0; i < school1_s.length; i++) {
if (school1_s[i].level == "一等奖") {
school1_s_one.push(school1_t[i]);
}
}
for (var i = 0; i < school1_s.length; i++) {
if (school1_s[i].level == "二等奖") {
school1_s_two.push(school1_t[i]);
}
}
for (var i = 0; i < school1_s.length; i++) {
if (school1_s[i].level == "三等奖") {
school1_s_three.push(school1_t[i]);
}
}
for (var i = 0; i < school1_s.length; i++) {
if (school1_s[i].level == "优秀奖") {
school1_s_four.push(school1_t[i]);
}
}
//通过数组的长度得到获奖个数
info.teacher_one = school1_t_one.length;
info.teacher_two = school1_t_two.length;
info.teacher_three = school1_t_three.length;
info.teacher_four = school1_t_four.length;
info_list.push(info);
//通过数组的长度得到获奖个数
info_student.student_one = school1_s_one.length;
info_student.student_two = school1_s_two.length;
info_student.student_three = school1_s_three.length;
info_student.student_four = school1_s_four.length;
info_student_list.push(info_student);
}
//循环数组刷到前端界面然后拷贝到excel表里
for(var i=0;i<info_list.length;i++){
var info=info_list[i];
var html=`
<tbody>
<tr>
<td>${info.address}</td>
<td>${info.type}</td>
<td>${info.school_name}</td>
<td>${info.teacher_one}</td>
<td>${info.teacher_two}</td>
<td>${info.teacher_three}</td>
<td>${info.teacher_four}</td>
</tr>
</tbody>>
`;
$("#table").append(html);
}
for(var i=0;i<info_student_list.length;i++){
var info_student=info_student_list[i];
var html=`
<tbody>
<tr>
<td>${info_student.address}</td>
<td>${info_student.type}</td>
<td>${info_student.school_name}</td>
<td>${info_student.student_one}</td>
<td>${info_student.student_two}</td>
<td>${info_student.student_three}</td>
<td>${info_student.student_four}</td>
</tr>
</tbody>>
`;
$("#table_student").append(html);
}
说明：本代码拼接字符串用的是ECMA6的小撇号，本方法在IE下不能解析出来，可以通过http://babeljs.io/repl/ 转码实现，
如果不想转码的话，可以考虑用引号拼接字符串。
本代码使用jquery来减少代码量
转载请声明出处！