public static function exportAllToFile()
{
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
$date = date("Y.m");
$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
//$objPHPExcel->getDefaultStyle()->applyFromArray($styleArray);
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle($date . '在校生名单');
$data = array('序号', '类别', '国籍', '学号', '中文姓名', '英文姓名', 'CSC登记号', '护照号码', '性别', '出生日期', '学院', '专业', '入学日期', '原班级', '现班级', '授课语言', '费用', '住址', '联系方式', 'E-MAIL地址', '导师', '备注1', '备注2');
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$stu = Student::where("stu_type", "在校生")->orWhere("stu_type", "新生")->get();
$data = array();
$num = 1;
foreach ($stu as $item) {
$data[$num - 1][0] = $num;
$data[$num - 1][1] = $item['type'];
$data[$num - 1][2] = $item['nationality'];
$data[$num - 1][3] = $item['id'];
$data[$num - 1][4] = $item['name_cn'];
$data[$num - 1][5] = $item['name_en'];
$data[$num - 1][6] = $item['csc_no'];
$data[$num - 1][7] = $item['passport_id'];
$data[$num - 1][8] = $item['gender'];
$data[$num - 1][9] = $item['birthday'];
$data[$num - 1][10] = $item['school'];
$data[$num - 1][11] = $item['major'];
$data[$num - 1][12] = $item['admission_date'];
$data[$num - 1][13] = $item['old_class'];
$data[$num - 1][14] = $item['new_class'];
$data[$num - 1][15] = $item['language'];
$data[$num - 1][16] = $item['fee_type'];
$data[$num - 1][17] = $item['inschool_address'];
$data[$num - 1][18] = $item['telephone'];
$data[$num - 1][19] = $item['email'];
$data[$num - 1][20] = $item['tutor'];
$data[$num - 1][21] = $item['inschool_note1'];
$data[$num - 1][22] = $item['inschool_note2'];
$num++;
}
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A2');
//self::setMyStyle($objPHPExcel, 0, 22, $num);
$myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $date . '新生名单');
$objPHPExcel->addSheet($myWorkSheet);
$objPHPExcel->setActiveSheetIndex(1);
$data = array('序号', '类别', '国籍', '学号', '中文姓名', '英文姓名', 'CSC登记号', '护照号码', '性别', '出生日期', '学院', '专业', '入学日期', '原班级', '现班级', '授课语言', '费用', '住址', '联系方式', 'E-MAIL地址', '导师', '备注1', '备注2');
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$stu = Student::where('stu_type', '新生')->get();
$data = array();
$num = 1;
foreach ($stu as $item) {
$data[$num - 1][0] = $num;
$data[$num - 1][1] = $item['type'];
$data[$num - 1][2] = $item['nationality'];
$data[$num - 1][3] = $item['id'];
$data[$num - 1][4] = $item['name_cn'];
$data[$num - 1][5] = $item['name_en'];
$data[$num - 1][6] = $item['csc_no'];
$data[$num - 1][7] = $item['passport_id'];
$data[$num - 1][8] = $item['gender'];
$data[$num - 1][9] = $item['birthday'];
$data[$num - 1][10] = $item['school'];
$data[$num - 1][11] = $item['major'];
$data[$num - 1][12] = $item['admission_date'];
$data[$num - 1][13] = $item['old_class'];
$data[$num - 1][14] = $item['new_class'];
$data[$num - 1][15] = $item['language'];
$data[$num - 1][16] = $item['fee_type'];
$data[$num - 1][17] = $item['inschool_address'];
$data[$num - 1][18] = $item['telephone'];
$data[$num - 1][19] = $item['email'];
$data[$num - 1][20] = $item['tutor'];
$data[$num - 1][21] = $item['inschool_note1'];
$data[$num - 1][22] = $item['inschool_note2'];
$num++;
}
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A2');
//self::setMyStyle($objPHPExcel, 1, 22, $num);
$myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $date . '休学名单');
$objPHPExcel->addSheet($myWorkSheet);
$objPHPExcel->setActiveSheetIndex(2);
$data = array('序号', '类别', '国籍', '学号', '中文姓名', '英文姓名', 'CSC登记号', '护照号码', '性别', '出生日期', '学院', '专业', '入学日期', '原班级', '现班级', '休学日期', '导师', '备注', '备注1');
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$stu = Student::where('stu_type', '休学')->get();
$data = array();
$num = 1;
foreach ($stu as $item) {
$data[$num - 1][0] = $num;
$data[$num - 1][1] = $item['type'];
$data[$num - 1][2] = $item['nationality'];
$data[$num - 1][3] = $item['id'];
$data[$num - 1][4] = $item['name_cn'];
$data[$num - 1][5] = $item['name_en'];
$data[$num - 1][6] = $item['csc_no'];
$data[$num - 1][7] = $item['passport_id'];
$data[$num - 1][8] = $item['gender'];
$data[$num - 1][9] = $item['birthday'];
$data[$num - 1][10] = $item['school'];
$data[$num - 1][11] = $item['major'];
$data[$num - 1][12] = $item['admission_date'];
$data[$num - 1][13] = $item['old_class'];
$data[$num - 1][14] = $item['new_class'];
$data[$num - 1][15] = $item['suspend_date'];
$data[$num - 1][16] = $item['tutor'];
$data[$num - 1][17] = $item['suspend_note'];
$data[$num - 1][18] = $item['suspend_note1'];
$num++;
}
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A2');
//self::setMyStyle($objPHPExcel, 2, 18, $num);
$myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $date . '国别统计');
$objPHPExcel->addSheet($myWorkSheet);
$objPHPExcel->setActiveSheetIndex(3);
//self::setMyStyle($objPHPExcel, 3, 12, 0);
$data = array('序号', '洲别', '国别', '本科', '硕研', '博研', '语言生', '普进', '高进', '人数小计', '男生', '女生');
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$data = Country::GetStuArrayByCountry();
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A2');
$myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $date . '毕业结业名单');
$objPHPExcel->addSheet($myWorkSheet);
$objPHPExcel->setActiveSheetIndex(4);
$data = array('序号', '类别', '国籍', '学号', '中文姓名', '英文姓名', 'CSC登记号', '护照号码', '性别', '出生日期', '学院', '专业', '入学日期', '毕业日期', '发证日期', '所获证书', '毕业证书号码', '学位证书号码', '进修证书号码', '学习证书号码', '联系地址', '导师', '备注');
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$stu = Student::where('stu_type', '毕业结业')->get();
$data = array();
$num = 1;
foreach ($stu as $item) {
$data[$num - 1][0] = $num;
$data[$num - 1][1] = $item['type'];
$data[$num - 1][2] = $item['nationality'];
$data[$num - 1][3] = $item['id'];
$data[$num - 1][4] = $item['name_cn'];
$data[$num - 1][5] = $item['name_en'];
$data[$num - 1][6] = $item['csc_no'];
$data[$num - 1][7] = $item['passport_id'];
$data[$num - 1][8] = $item['gender'];
$data[$num - 1][9] = $item['birthday'];
$data[$num - 1][10] = $item['school'];
$data[$num - 1][11] = $item['major'];
$data[$num - 1][12] = $item['admission_date'];
$data[$num - 1][13] = $item['graduate_date'];
$data[$num - 1][14] = $item['certificate_date'];
$data[$num - 1][15] = $item['certificate_type'];
$data[$num - 1][16] = $item['graduation_certificate'];
$data[$num - 1][17] = $item['degree_certificate'];
$data[$num - 1][18] = $item['further_certificate'];
$data[$num - 1][19] = $item['learn_certificate'];
$data[$num - 1][20] = $item['graduated_address'];
$data[$num - 1][21] = $item['tutor'];
$data[$num - 1][22] = $item['graduated_note'];
$num++;
}
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A2');
//self::setMyStyle($objPHPExcel, 4, 22, $num);
$myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $date . '退学转学名单');
$objPHPExcel->addSheet($myWorkSheet);
$objPHPExcel->setActiveSheetIndex(5);
$data = array('序号', '类别', '国籍', '学号', '中文姓名', '英文姓名', 'CSC登记号', '护照号码', '性别', '出生日期', '学院', '专业', '入学日期', '原班级', '现班级', '退学、转学日期', '转学学校', '导师', '备注');
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$stu = Student::where('stu_type', '退学转学')->get();
$data = array();
$num = 1;
foreach ($stu as $item) {
$data[$num - 1][0] = $num;
$data[$num - 1][1] = $item['type'];
$data[$num - 1][2] = $item['nationality'];
$data[$num - 1][3] = $item['id'];
$data[$num - 1][4] = $item['name_cn'];
$data[$num - 1][5] = $item['name_en'];
$data[$num - 1][6] = $item['csc_no'];
$data[$num - 1][7] = $item['passport_id'];
$data[$num - 1][8] = $item['gender'];
$data[$num - 1][9] = $item['birthday'];
$data[$num - 1][10] = $item['school'];
$data[$num - 1][11] = $item['major'];
$data[$num - 1][12] = $item['admission_date'];
$data[$num - 1][13] = $item['old_class'];
$data[$num - 1][14] = $item['new_class'];
$data[$num - 1][15] = $item['leave_date'];
$data[$num - 1][16] = $item['leave_to'];
$data[$num - 1][17] = $item['tutor'];
$data[$num - 1][18] = $item['leave_note'];
$num++;
}
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A2');
//self::setMyStyle($objPHPExcel, 5, 18, $num);
$myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $date . '未报到名单');
$objPHPExcel->addSheet($myWorkSheet);
$objPHPExcel->setActiveSheetIndex(6);
$data = array('序号', '类别', '国籍', '学号', '中文姓名', '英文姓名', 'CSC登记号', '护照号码', '性别', '出生日期', '学院', '专业', '入学日期', '原班级', '现班级', '授课语言', '费用', '住址', '联系方式', 'E-MAIL地址', '导师', '备注1', '备注2');
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$stu = Student::where('stu_type', '未报到')->get();
$data = array();
$num = 1;
foreach ($stu as $item) {
$data[$num - 1][0] = $num;
$data[$num - 1][1] = $item['type'];
$data[$num - 1][2] = $item['nationality'];
$data[$num - 1][3] = $item['id'];
$data[$num - 1][4] = $item['name_cn'];
$data[$num - 1][5] = $item['name_en'];
$data[$num - 1][6] = $item['csc_no'];
$data[$num - 1][7] = $item['passport_id'];
$data[$num - 1][8] = $item['gender'];
$data[$num - 1][9] = $item['birthday'];
$data[$num - 1][10] = $item['school'];
$data[$num - 1][11] = $item['major'];
$data[$num - 1][12] = $item['admission_date'];
$data[$num - 1][13] = $item['old_class'];
$data[$num - 1][14] = $item['new_class'];
$data[$num - 1][15] = $item['language'];
$data[$num - 1][16] = $item['fee_type'];
$data[$num - 1][17] = $item['inschool_address'];
$data[$num - 1][18] = $item['telephone'];
$data[$num - 1][19] = $item['email'];
$data[$num - 1][20] = $item['tutor'];
$data[$num - 1][21] = $item['inschool_note1'];
$data[$num - 1][22] = $item['inschool_note2'];
$num++;
}
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A2');
//self::setMyStyle($objPHPExcel, 6, 22, $num);
$objPHPExcel->setActiveSheetIndex(0);
$filename = date("YmdHis", time()) . floor((microtime(true) - time()) * 1000) . ".xls";
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(storage_path() . "/app/backup/" . $filename);
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
return "backup/" . $filename;
}