现实中,我们往往很多地方都需要这样批量导入数据,除了phpexcel还有csv等方法可以解决
下面详细介绍一下使用方法
首先在官方下载安装包解压到本地,然后复制保存到tp框架下的vendor目录下
html页面如下
下面是后端代码:
public function column(){
$grade=D('school')->where(array('parent_id'=>1))->select();
$school = D('schooll')->select();
$this->assign('school',$school);
$this->assign('grade',$grade);
if(IS_POST){
$data_p = I('post.');
if ($data_P['sno']==' ') {
$this->ajaxReturn(array('nr' => '学号已存在!', 'sf' => 0));
}elseif($data_P['sname']==' '){
$this->ajaxReturn(array('nr' => '姓名不为空!', 'sf' => 0));
}elseif($data_P['grade']==' '){
$this->ajaxReturn(array('nr'=> '不为空','sf'=>'0'));
}elseif($data_P['major']==' '){
$this->ajaxReturn(array('nr'=>'不为空','sf'=>'0'));
}elseif($data_P['classs']==' '){
$this->ajaxReturn(array('nr'=>'不为空','sf'=>'0'));
}elseif($data_P['school']==' '){
$this->ajaxReturn(array('nr'=>'不为空','sf'=>'0'));
}else{
$student = M('student')->add(array('sno'=>$data_p['sno'],'passwd'=>md5($data_p['passwd']),'sname'=>$data_p['sname'],'zt'=>$data_p['checkresult'],'majorname'=>$data_p['major'],'classname'=>$data_p['classs'],'gradename'=>$data_p['school'],'schoolname'=>$data_p['grade']));
}
if ($student) {
$this->ajaxReturn(array('nr' => '添加成功'.$data_P['major'], 'sf' => 0));
} else {
$this->ajaxReturn(array('nr' => '添加失敗!', 'sf' => 0));
}
}
$this->display('index/column');
}
public function upload() {
if (!empty($_FILES)) {
$config = array(
'exts' => array('xlsx','xls'),
'maxSize' => 3145728,
'rootPath' =>"./Excel/",
'subName' => array('date','Ymd'),
);
$upload = new \Think\Upload($config);
if (!$info = $upload->upload()){
$this->error($upload->getError());
}else{
//vendor('PHPExcel.PHPExcel');/////引入类库
//vendor('PHPExcel.Reader.Excel5');
import("vendor.PHPExcel");
import("vendor.PHPExcel.Reader.Excel5");
$objPHPExcel = new \PHPExcel();/////初始化引入的方法
$PHPReader=new \PHPExcel_Reader_Excel5();
$file_name=$upload->rootPath.$info['excel']['savepath'].$info['excel']['savename'];
}
$objReader = $PHPReader->load($file_name);
$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
//判断导入表格后缀格式
if ($extension == 'xlsx') {
$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
} elseif($extension == 'xls'){
$objReader =\PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
}
$sheet =$objPHPExcel->getSheet(0);//获取表中第一个工作表
$highestRow = $sheet->getHighestRow();//取得总行数
$highestColumn =$sheet->getHighestColumn(); //取得总列数
for($currentRow=1;$currentRow<=$highestRow;$currentRow++){
for($currentColumn='A';$currentColumn<=$highestColumn;$currentColumn++){
$address=$currentColumn.$currentRow;
$arr[$currentRow][$currentColumn]=$sheet->getCell($address)->getValue();
}
for ($i = 2; $i <= $highestRow; $i++) {
$data_p['sno'] =$objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
$passwd =$objPHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();
$data_p['passwd']=md5($passwd);
$data_p['sname'] =$objPHPExcel->getActiveSheet()->getCell("C" .$i)->getValue();
$data_p['zt'] = '0';
$data_p['majorname'] = $objPHPExcel->getActiveSheet()->getCell("D". $i)->getValue();
$data_p['classname'] =$objPHPExcel->getActiveSheet()->getCell("E" .$i)->getValue();
$data_p['gradename'] =$objPHPExcel->getActiveSheet()->getCell("F" . $i)->getValue();
$data_p['schoolname'] =$objPHPExcel->getActiveSheet()->getCell("G" . $i)->getValue();
$ex=M('Student')->add($data_p);
}
if($ex){
$this->success('导入成功!');
}else{
$this->error("请选择上传的文件");
}
}
}
好的具体的就是这些。哪里不懂可以留言1119702968@qq.com咨询。