- 浏览: 31960 次
- 性别:
- 来自: 广州
最新评论
用phpexcel读出并导入数据库的代码 //导入
function actionimport() { //error_reporting(0); $form = new Form_Import(url('teamup/import')); $province = Province::find()->all()->query(); $form['province_id']->items = Helper_Array::toHashMap($province, 'province_id', 'name'); if ($this->_context->isPOST()) { $teamupclass_id = $_POST['teamupclass_id']; $province_id = $_POST['province_id']; $check = strrpos($_FILES['filecsv']['name'],"."); $check1 = substr($_FILES['filecsv']['name'],$check+1); if ($check1=='csv' || $check1=='txt' || $check1=='xls' ||$check1=='xlsx') { if($check1=='xls' || $check1=='xlsx'){ //header("Content-Type: text/html; charset=utf-8"); //error_reporting(0); //ini_set('memory_limit', '512M');//处理内存溢出 //ini_set('max_execution_time', '50'); $roodir=dirname(dirname(__FILE__)); set_include_path("{$roodir}/1.7.2/classes");//设置包含phpexcel包的路径 require_once 'PHPExcel.php';//包含类 require_once'PHPExcel/Reader/Excel2007.php';//包含读功能实现的2007格式的类 require_once'PHPExcel/Reader/Excel5.php';//包含excel5读功能的类 //$fileName = 'zzzz.xls'; //$path = 'E:\phpnow\htdocs\uniteone\\'; //$filePath = $path.$fileName; $filePath=$_FILES['filecsv']['tmp_name']; $PHPExcel = new PHPExcel(); $PHPReader = new PHPExcel_Reader_Excel2007(); //新建excel2007读对象 if(!$PHPReader->canRead($filePath)){ //如果读对象格式不合,新建excel5读对象 $PHPReader = new PHPExcel_Reader_Excel5(); if(!$PHPReader->canRead($filePath)){ //如果还不对,输出没有excel echo 'no Excel'; return ; } } $PHPExcel = $PHPReader->load($filePath); $currentSheet = $PHPExcel->getSheet(0); //取得excel工作“分页” /**取得一共有多少列*/ $allColumn = $currentSheet->getHighestColumn(); /**取得一共有多少行*/ $allRow = $currentSheet->getHighestRow(); for($currentRow = 1;$currentRow<=$allRow;$currentRow++){//获取excel文件数据到数组 for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){ $address = $currentColumn.$currentRow; //$abc=iconv("", "utf-8", $currentSheet->getCell($address)->getValue()); //echo $currentSheet->getCell($address)->getValue()."\t"; $arr[$currentColumn]=trim($currentSheet->getCell($address)->getValue()); //$lines[]=$currentSheet->getCell($address)->getValue(); } $lines[]=implode(",",$arr); } //print_r($lines); }else{ // 将一个文件读入数组。本例中通过 HTTP 从 URL 中取得 HTML 源文件。 $lines = file($_FILES['filecsv']['tmp_name']); //print_r($lines); } // 在数组中循环,显示 HTML 的源文件并加上行号。 foreach ($lines as $line_num => $line): print_r($line); if($line_num > 1) { $line = explode(",",$line); foreach( $line as $k=>$v) { if( !$v ) unset( $line[$k] ); } $line = array_flip(array_flip($line)); // print_r($line); if (count($line)==2){ $line = implode(",",$line); $line.=",$teamupclass_id,1,$province_id"; $address=explode(",", iconv("GB2312","UTF-8//IGNORE",$line)); // dump ($address); if(count($address)<5) { return $this->_redirectMessage( '文件格式错误', '导入失败。', url('teamup/import')); break; } $value=Teamup::meta()->props2fields; $vartext=$value['city_id'].','.$value['name'].','.$value['teamupclass_id'].','.$value['country_id'].','.$value['province_id']; $useraddress=explode(",", $vartext); $temp = new Teamup(array_combine($useraddress,$address)); $temp['order']=0; // $aaaa=array_combine($useraddress,$address); Teamup::meta()->deleteWhere('name=?',$address['0']);// Teamup::meta()->deleteWhere(array($aaaa)); $temp->save(); } if(count($line)==3){ $line = implode(",",$line); $line.=",$teamupclass_id,1"; if($check1!='xls' && $check1!='xlsx') {$address=explode(",", iconv("GB2312","UTF-8//IGNORE",$line));} else {$address=explode(",", $line);} //$address=explode(",", iconv("GB2312","UTF-8//IGNORE",$line)); // $bb=Province::meta()->find('name= ?',$address[1])->getOne(); // $address[1]=$bb['province_id']; /*转换省份为省份id对应数据库省份表,由于数据库存的只是简称,而要导入的数据有简称也有全称,因此不使用查询匹配*/ if ($address[1]=='北京' || $address[1]=='北京市') {$address[1]=2;} else if($address[1]=='上海' || $address[1]=='上海市') {$address[1]=3;} else if($address[1]=='天津' || $address[1]=='天津市') {$address[1]=4;} else if($address[1]=='重庆' || $address[1]=='重庆市') {$address[1]=5;} else if($address[1]=='河北' || $address[1]=='河北省') {$address[1]=6;} else if($address[1]=='山西' || $address[1]=='陕西省') {$address[1]=7;} else if($address[1]=='内蒙古' || $address[1]=='内蒙古自治区') {$address[1]=8;} else if($address[1]=='辽宁' || $address[1]=='辽宁省') {$address[1]=9;} else if($address[1]=='吉林' || $address[1]=='吉林省') {$address[1]=10;} else if($address[1]=='黑龙江' || $address[1]=='黑龙江') {$address[1]=11;} else if($address[1]=='江苏' || $address[1]=='江苏省') {$address[1]=12;} else if($address[1]=='浙江' || $address[1]=='浙江省') {$address[1]=13;} else if($address[1]=='安徽' || $address[1]=='安徽省') {$address[1]=14;} else if($address[1]=='福建' || $address[1]=='福建省') {$address[1]=15;} else if($address[1]=='江西' || $address[1]=='江西省') {$address[1]=16;} else if($address[1]=='山东' || $address[1]=='山东省') {$address[1]=17;} else if($address[1]=='河南' || $address[1]=='河南省') {$address[1]=18;} else if($address[1]=='湖北' || $address[1]=='湖北省') {$address[1]=19;} else if($address[1]=='湖南' || $address[1]=='湖南省') {$address[1]=20;} else if($address[1]=='广东' || $address[1]=='广东省') {$address[1]=21;} else if($address[1]=='广西' || $address[1]=='广西省') {$address[1]=22;} else if($address[1]=='南海' || $address[1]=='南海省') {$address[1]=23;} else if($address[1]=='四川' || $address[1]=='四川省') {$address[1]=24;} else if($address[1]=='贵州' || $address[1]=='贵州省') {$address[1]=25;} else if($address[1]=='云南' || $address[1]=='云南省') {$address[1]=26;} else if($address[1]=='西藏' || $address[1]=='西藏自治区') {$address[1]=27;} else if($address[1]=='陕西' || $address[1]=='陕西省') {$address[1]=28;} else if($address[1]=='甘肃' || $address[1]=='甘肃省') {$address[1]=29;} else if($address[1]=='青海' || $address[1]=='青海省') {$address[1]=30;} else if($address[1]=='宁夏' || $address[1]=='宁夏省') {$address[1]=32;} else if($address[1]=='台湾' || $address[1]=='台湾省') {$address[1]=33;} else if($address[1]=='香港' || $address[1]=='香港特别行政区') {$address[1]=34;} else if($address[1]=='澳门' || $address[1]=='澳门特别行政区') {$address[1]=35;} else {$address[1]=31;} // dump ($address); if(count($address)<5) { return $this->_redirectMessage( '文件格式错误', '导入失败。', url('teamup/import')); break; } $value=Teamup::meta()->props2fields; $vartext=$value['name'].','.$value['province_id'].','.$value['city_id'].','.$value['teamupclass_id'].','.$value['country_id']; $useraddress=explode(",", $vartext); $temp = new Teamup(array_combine($useraddress,$address)); $temp['order']=0; // $aaaa=array_combine($useraddress,$address); Teamup::meta()->deleteWhere('name=?',$address['0']);// Teamup::meta()->deleteWhere(array($aaaa)); $temp->save(); } } // dump(iconv("GB2312","UTF-8//IGNORE",$line)); endforeach; // dump(iconv("GB2312","UTF-8//IGNORE",$lines)); //foreach ($lines as $line_num => $line) { // echo "Line #<b>{$line_num}</b> : " . htmlspecialchars($line) . "<br />\n"; // } echo '成功!'; /* return $this->_redirectMessage( '', '导入成功。', url('teamup/import'));*/ } else return $this->_redirectMessage( '', '导入失败。', url('teamup/import')); } $this->_view['form'] = $form; }
导出:
$roodir=dirname(dirname(__FILE__)); set_include_path("{$roodir}/1.7.2/classes");//设置包含phpexcel包的路径 require_once 'PHPExcel.php'; require_once'PHPExcel/Writer/Excel2007.php'; // 创建一个处理对象实例 $objExcel = new PHPExcel(); // 创建文件格式写入对象实例, uncomment //$objWriter = new PHPExcel_Writer_Excel5($objExcel);// 用于其他版本格式 // or $objWriter = new PHPExcel_Writer_Excel2007($objExcel); // 用于 2007 格式 $objExcel->setActiveSheetIndex(0); $objActSheet = $objExcel->getActiveSheet(); //设置当前活动sheet的名称 $objActSheet->setTitle('测试Sheet'); //************************************* //设置单元格内容 // //由PHPExcel根据传入内容自动判断单元格内容类型 $z=Teamup::meta()->find()->getAll(); // $z=Province::meta()->find()->getAll(); $i=0; foreach ($z as $y) { //print_r($y->province_id); //print_r($y->name);break; $i++; $objActSheet->setCellValue('A'.$i,$y->name); $x=Province::meta()->find('province_id=?',$y->province_id)->getAll(); foreach ($x as $w): $objActSheet->setCellValue('B'.$i,$w->name); endforeach; $objActSheet->setCellValue('C'.$i,$y->city_id); } //输出内容 // $outputFileName = "output.xls"; //到文件 ////$objWriter->save($outputFileName); //or //到浏览器 header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="'.$outputFileName.'"'); header("Content-Transfer-Encoding: binary"); header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: no-cache"); $objWriter->save('php://output');
相关推荐
PHP开发中遇到的数据导入功能,发现此插件比较好用,实现代码见本人博客。PHPExcel Excel1.8导入导出。
PHPExcel数据库表导出PHPExcel数据库表导出
利用PHPExcel导入表格存入数据库中并显示
Thinkphp5和PHPexcel的结合,成就了导入导出的功能需求。
本文介绍利用phpexcel对数据库数据导入excel(excel筛选)、导出excel,大家参考使用吧
thinkphp3.2整合PHPExcel 数据导入导出完整示例(导入数据并插入数据库,充数据库导出数据到excel表格,包括数据库和源码)
phpExcel导入excel文件,对导入的文件限定导入列数,并将符合格式的写入数据库。
在PHP编程中,有时需要把excel表格中的数据,导入到数据库中,方便储存
phpexcel的导入导出,导入支持03 和 07 导入。导出有标准导出,html格式导出
1,建立mysql数据库,把excel_demo.sql导入到你的数据库,采取utf-8编码; 2,导入时候请按照xls/demo.xls的格式导入,在linux系统下 xls目录要有下的权限; 3, 默认只有一条数据,导出至少有一条数据; 4,如果数据...
分享一个利用phpexcel对数据库数据的导入excel(excel筛选)、导出excel的类。根据时间生成采购报表,实例化excel类,设置选定sheet表名,合并单元格 给单元格赋值(数值,字符串,公式),大边框样式 边框加粗,设置...
PHPExcel导入导出插件,含有示例,用户可以根据示例自己编写导出到处代码,简单的导出可直接用现成的方法
用php,将excel内容导入mysql。代码注释非常详细,需要的下。。。。不懂的可以给我回信
摘自实际项目中关于带有图片的excel表格的导入、导出功能。官方不要再提高下载积分了,不要再提高了!
1.导入数据库文件sql.sql文件 2.修改index.php中$connect = mysqli_connect("localhost", "root", "root", "test");给你自己的数据库账号密码 3.修改export.php中$connect = mysqli_connect("localhost", "root", ...
在ThinkPHP5.0.10框架下使用PHPExcel实现带图片excel表格的导入导出功能。application\index\model\ExcelDown.php文件中,第93行加上判断if($res[$i][$data['field'][$j]]!='')改为if($res[$i][$data['field'][$j]]!...
ThinkPHP实现数据导出为Excel文件的PHPExcel类库文件
利用PHPExcel实现 exl导入数据库 和 数据库 导出 exl 表格