<?php
namespace app\admin\controller;
use app\admin\controller\Common;
use think\Db;
use think\Request;
class Excel extends Common
{
//导入Excel
public function import(){
//具体上传文件处理方法可以看另一个上传代码
/*PHPEXCEL使用方法
下载地址：https://github.com/PHPOffice/PHPExcel/releases
关于原生引入问题，下载最新的phpexcel类，下载解压缩后有一个classes文件夹，里面包含了PHPExcel.php和PHPExcel的文件夹，这个类文件和文件夹是我们需要的，把classes解压到你项目的根目录中，重名名为PHPExcel
require_once '/PHPExcel/PHPExcel.php';
首先实例化PHPExcel类 PHPExcel object
$objPHPExcel = new \PHPExcel();
------------
TP框架引入则是和上面差不多，把classes解压到你项目的vendor目录中，重名名为PHPExcel
然后 vendor("PHPExcel.PHPExcel");
*/
if (!empty($_FILES)) {
$path=$_SERVER['DOCUMENT_ROOT'];//网站相对服务器的根目录
$savepath=$path.'/Uploads/';
$file = request()->file('file');
// 移动到框架应用根目录/uploads/ 目录下
$info = $file->move($savepath);
if($info){
// 成功上传后 获取上传信息
$extension= $info->getExtension(); //文件后缀
$pathname= $info->getSaveName(); //加时间文件夹的文件名
$filename= $info->getFilename(); //文件名
vendor("PHPExcel.PHPExcel");
$Excel_name = $savepath.$pathname;
if ($extension =='xlsx') {
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader ->load($Excel_name);
} else if ($extension =='xls') {
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader ->load($Excel_name);
} else if ($extension=='csv') {
$PHPReader = new \PHPExcel_Reader_CSV();
//默认输入字符集
$PHPReader->setInputEncoding('GBK');
//默认的分隔符
$PHPReader->setDelimiter(',');
//载入文件
$objPHPExcel = $PHPReader->load($Excel_name);
}else{
returnApidata(1,'文件类型错误,请核对！','');die;
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
//第3行开始
for($i=3;$i<=$highestRow;$i++)
{
if(!empty($objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue())){ //判断教师姓名为空则不读取
$data=array();
$data['admin_name']=null;
$data['admin_realname'] = trim($objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue());//姓名
if(trim($objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue())=="男"){
$data['admin_sex']=1;
}elseif(trim($objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue())=="女"){
$data['admin_sex']=2;
}else{
$data['admin_sex']=null;
}//性别
$data['admin_nationality'] = trim($objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue()); //民族
$data['admin_idcard'] = strtr(trim($objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue()),array(' '=>'')); //身份证号,去除中间空格
$a1=substr($data['admin_idcard'],6,8);
$a2=strtotime($a1);
$data['admin_birthday']=$a2; //出生日期
$data['admin_politics_status'] = trim($objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue()); //政治面貌
$data['admin_start_worktime'] = trim($objPHPExcel->getActiveSheet()->getCell("J".$i)->getValue()); //参加工作时间
$data['admin_staff_state'] = trim($objPHPExcel->getActiveSheet()->getCell("K".$i)->getValue()); //编制情况
$data['admin_phone'] = strtr(trim($objPHPExcel->getActiveSheet()->getCell("L".$i)->getValue()),array(' '=>'')); //手机号码，去除中间空格
$res=Db::name('admin')->insert($data);
}
}
returnApidata(0,'数据导入成功','');die;
}else{
// 上传失败获取错误信息
returnApidata(1,'文件上传错误','');die;
}
}else{
returnApidata(1,'请选择文件再提交','');die;
}
}
//导出Excel
public function export(){
if(Request::instance()->isPost()){
$sql="select admin_realname,admin_sex,admin_nationality,admin_idcard,admin_politics_status,admin_start_worktime,admin_staff_state,admin_phone,admin_education_one,admin_education_one_detail,admin_education_highest,admin_education_highest_detail,admin_teach_kind,admin_teach_number,admin_major_course,admin_part_course,admin_teach_qualification,admin_teach_qualification_gettime,admin_engage_level,admin_engage_level_gettime,admin_remark from hb_admin where admin_id!=1 and admin_id!=2 order by admin_id asc";
$arr=Db::query($sql);
$date=date('Y');
$idarr=array();
foreach ($arr as $k => $v) {
$arr[$k]=$v; //对已有字段直接赋值了，不用单独循环
//性别防止为空，不能用? :
if($v['admin_sex']==1){
$arr[$k]['sex']="男";
}elseif($v['admin_sex']==2){
$arr[$k]['sex']="女";
}else{
$arr[$k]['sex']="";
}
$arr[$k]['id']=$k+1;
$arr[$k]['workplace']="灰埠中心小学";
//身份证取出生日期，防止为空
if(empty($v['admin_idcard'])){
$arr[$k]['admin_birthday']="";
$arr[$k]['admin_years']="";
}else{
$year=substr($v['admin_idcard'],6,4);
$month=substr($v['admin_idcard'],10,2);
//$day=substr($v['admin_idcard'],12,2);
$arr[$k]['admin_birthday']=$year.".".$month;
$arr[$k]['admin_years']=$date-$year;
}
}
$xlsData = $arr; //赋值$xlsData
$xlsName = "教师信息表"; //赋值$xlsName
$xlsCell = array(
array('id','序号'),
array('workplace','工作单位'),
array('admin_realname','姓名'),
array('sex','性别'),
array('admin_nationality','民族'),
array('admin_idcard','身份证号'),
array('admin_birthday','出生年月'),
array('admin_years','周岁'),
array('admin_politics_status','政治面貌'),
array('admin_start_worktime','参加工作时间'),
array('admin_staff_state','编制情况'),
array('admin_education_one','第一学历'),
array('admin_education_one_detail','第一学历毕业学校时间及专业'),
array('admin_education_highest','最高学历'),
array('admin_education_highest_detail','最高学历毕业学校时间及专业'),
array('admin_teach_kind','教师资格证种类'),
array('admin_teach_number','教师资格证号码'),
array('admin_major_course','现专任学科'),
array('admin_part_course','现兼任学科'),
array('admin_teach_qualification','职称资格'),
array('admin_teach_qualification_gettime','取得时间'),
array('admin_engage_level','现聘级位等级'),
array('admin_engage_level_gettime','现聘岗位等级时间'),
array('admin_remark','备注'),
);
$this->exportExcel($xlsName,$xlsCell,$xlsData); //文件名，对应栏目，数据数组
}
}
public function exportExcel($expTitle,$expCellName,$expTableData){
//$xlsTitle = iconv('gbk','utf-8', $expTitle);
//$xlsTitle = iconv('gb2312','utf-8', $expTitle);
//$xlsTitle = iconv('utf-8','gb2312', $expTitle);
$xlsTitle = $expTitle; //看导出的excel是否乱码，乱码需要测试以上转码
$fileName = $xlsTitle." ".date('Y-m-d'); // 文件名称可根据自己情况设定
$cellNum = count($expCellName);
$dataNum = count($expTableData);
vendor("PHPExcel.PHPExcel"); //引入PHPExcel
$objPHPExcel = new \PHPExcel(); //实例化PHPExcel类， 等同于在桌面上新建一个excel
$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
$objSheet=$objPHPExcel->getActiveSheet(); //获得当前活动sheet
$objSheet->setTitle("教师基本信息表"); //给当前活动sheet起个名称
$objSheet->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中,注意PHPExcel前不能少\
$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1'); //合并第一行单元格,减1是因为数组键值从0开始
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '灰埠中心小学-教师信息采集表'); //设置第一行单元格标题
//设置最后一行行数：
$Num=$dataNum+2; //+2 是因为去除第1行大标题和第2行栏目标题
//设置F G H J Q U W列字体：Arial (数字用这个字体)
$objPHPExcel->getActiveSheet(0)->getStyle("F3:F$Num")->getFont()->setName('Arial')->setSize(12);
$objPHPExcel->getActiveSheet(0)->getStyle("G3:G$Num")->getFont()->setName('Arial')->setSize(12);
$objPHPExcel->getActiveSheet(0)->getStyle("H3:H$Num")->getFont()->setName('Arial')->setSize(12);
$objPHPExcel->getActiveSheet(0)->getStyle("J3:J$Num")->getFont()->setName('Arial')->setSize(12);
$objPHPExcel->getActiveSheet(0)->getStyle("Q3:Q$Num")->getFont()->setName('Arial')->setSize(12);
$objPHPExcel->getActiveSheet(0)->getStyle("U3:U$Num")->getFont()->setName('Arial')->setSize(12);
$objPHPExcel->getActiveSheet(0)->getStyle("W3:W$Num")->getFont()->setName('Arial')->setSize(12);
//设置第1行大标题字体：红色
//$objPHPExcel->getActiveSheet()->getStyle('A1:'.$cellName[$cellNum-1].'1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
//设置第1行大标题字体：12px 黑体
$objPHPExcel->getActiveSheet(0)->getStyle('A1:'.$cellName[$cellNum-1].'1')->getFont()->setSize(14)->setBold(true);
//设置第1行大标题行高
$objPHPExcel->getActiveSheet(0)->getRowDimension('1')->setRowHeight(50);
//设置第2行栏目标题行高
$objPHPExcel->getActiveSheet(0)->getRowDimension('2')->setRowHeight(30);
//设置第3行开始 正文数据行高
$objPHPExcel->getActiveSheet(0)->getRowDimension('3:$num')->setRowHeight(24);
//设置第2行栏目标题字体：12px 黑体
$objPHPExcel->getActiveSheet(0)->getStyle('A2:'.$cellName[$cellNum-1].'2')->getFont()->setSize(12)->setBold(true);
//设置第2行栏目标题填充的样式和背景色(不能设置背景色，边框会被覆盖)
//$objPHPExcel->getActiveSheet(0)->getStyle('A2:'.$cellName[$cellNum-1].'2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('00f0f0f0');
//设置下面3行开始内容栏字体属性
$objPHPExcel->getActiveSheet(0)->getStyle("3:$Num")->getFont()->setSize(12);
//设置单元格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(23);
$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(20);
//设置栏目标题，从第2行开始
for($i=0;$i<$cellNum;$i++){
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
}
//设置数据，从第3行开始
for($i=0;$i<$dataNum;$i++){
for($j=0;$j<$cellNum;$j++){
//技术点，通过在数据前面加空格，再用setCellValueExplicit可以解决科学计数法的问题
if($j==5 || $j==16){
$objPHPExcel->getActiveSheet(0)->setCellValueExplicit($cellName[$j].($i+3), ' '.$expTableData[$i][$expCellName[$j][0]],\PHPExcel_Cell_DataType::TYPE_STRING);
}else{
$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
}
}
}
/* 导出excel2003格式
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"'); //告诉浏览器输出excel文件
header("Content-Disposition:attachment;filename=$fileName.xls"); //告诉浏览器将输出文件的名称
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //生成excel文件
$objWriter->save('php://output'); //保存文件
exit;*/
/* 导出excel2007格式 */
header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8;name="'.$xlsTitle.'.xls"');
header("Content-Disposition:attachment;filename=$fileName.xlsx");
header('Cache-Control: max-age=0');//使用缓存前进行验证
$objWriter = \PHPExcel_IOFactory:: createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
}
04-09
522
522
12-19
“相关推荐”对你有帮助么？
-
非常没帮助 -
没帮助 -
一般 -
有帮助 -
非常有帮助
提交
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
