php利用phpexcel插件实现数据的导入和导出(支持csvxlsxlsx格式和超过26个字段列)

一、导入（这其中show_msg和logFile是自定义函数）/***方法名:excelToTable*作用:【私有】将excel数据导入数据表中*@param1：file用户上传的文件信息*@param2：tableid用来区别是哪张表，1-statistics_rawdata_pct，2-statistics_rawdata_apply，3-statistics_rawdata_auth，4-statistics_rawdata_valid*@param3：month_number导入的数据属于哪一期的，比如201510*@param4：table_head用来判断excel表格是否有表头，默认有*@date:2015/11/26*@author:dingling*/privatefunctionexcelToTable($file,$tableid,$month_number,$table_head=1){if(!empty($file['name'])){$file_types=explode(.,$file['name']);$excel_type=array('xls','csv','xlsx');//判断是不是excel文件if(!in_array(strtolower(end($file_types)),$excel_type)){$this-show_msg(不是Excel文件，重新上传,/search/patentStatistics/uploadRawdata);}//设置上传路径$savePath=_以时间来命名上传的文件$str=date('Ymdhis');$file_name=$str...end($file_types);//是否上传成功$tmp_file=$file['tmp_name'];if(!copy($tmp_file,$savePath.$file_name)){$this-show_msg(上传失败,/search/patentStatistics/uploadRawdata);}if($tableid==1){$rawdata_obj=$this-rawdata_pctmodel;}elseif($tableid==2){$rawdata_obj=$this-rawdata_applymodel;}elseif($tableid==3){$rawdata_obj=$this-rawdata_authmodel;}elseif($tableid==4){$rawdata_obj=$this-rawdata_validmodel;}else{$this-show_msg(您要导入的数据表不存在！,/search/patentStatistics/uploadRawdata);}if($rawdata_obj)$fields=$rawdata_obj-returnFields();else$this-show_msg(未能指定明确的表！,/search/patentStatistics/uploadRawdata);//定义导入失败记录的文档$logfile=$savePath.$str.'.txt';//读取excel，存成数组，该数组的key是从1开始$res=$this-excelToArray($savePath.$file_name,end($file_types));//echo12321321;exit;//如果有表头，则过滤掉第一行if($table_head)unset($res[1]);//循环写入，不一次性写入，防止有错误的记录；错误记录会记录下第一个字段到txt文档中去foreach($resas$k=$v){foreach($fieldsas$key=$val){if($v[$key]===null){$v[$key]='null';}$data[$val]=$v[$key];}//该字段比较特殊，必须导入表中都有该字段$data['month_number']=$month_number;$result=$rawdata_obj-addSave($data);unset($data);if(!$result){$this-logFile($logfile,$v[0]);}}if(file_get_contents($logfile))return$logfile;elsereturntrue;}}/***方法名:excelToArray*作用:【私有】将excel数据转换成数组*@param1：filenameexcel文件名*@param2：filetypeexcel格式（xls、xlsx、csv）*@param3：encode编码格式，默认utf8*@return：返回2维数组，最小的key为1*@date:2015/11/26*@author:dingling*/privatefunctionexcelToArray($filename,$filetype,$encode='utf-8'){if(strtolower($filetype)=='xls'){$objReader=PHPExcel_IOFactory::createReader('Excel5');}elseif(strtolower($filetype)=='xlsx'){$objReader=PHPExcel_IOFactory::createReader('Excel2007');}elseif(strtolower($filetype)=='csv'){$objReader=PHPExcel_IOFactory::createReader('CSV');}$objReader-setReadDataOnly(true);$objPHPExcel=$objReader-load($filename);$objWorksheet=$objPHPExcel-getActiveSheet();$highestRow=$objWorksheet-getHighestRow();$highestColumn=$objWorksheet-getHighestColumn();$highestColumnIndex=PHPExcel_Cell::columnIndexFromString($highestColumn);$excelData=array();for($row=1;$row=$highestRow;$row++){for($col=0;$col$highestColumnIndex;$col++){$excelData[$row][]=(string)$objWorksheet-getCellByColumnAndRow($col,$row)-getValue();}}return$excelData;}二、导出（该功能支持页面上html中table表格直接导出）1、前端代码!doctypehtmlhtmlheadtitle专利数据检索平台2015年08月/titlemetacharset=utf-8/metaname=keywordscontent=北京市知识产权信息服务平台/metaname=descriptioncontent=专利数据检索平台北京市知识产权信息服务平台/metaname=viewportcontent=width=device-width,initial-scale=1/metaproperty=wb:webmastercontent=3c67ef6a26cfe34e/linkrel=apple-touch-icon-precomposedhref=/metaname=baidu-site-verificationcontent=5fNm7bQabR/metahttp-equiv=X-UA-Compatiblecontent=IE=8/scripttype=text/javascriptsrc=/js/placeholder.js/scriptscripttype=text/javascriptsrc=/js/jquery-1.8.3.min.js/scriptscripttype=text/javascriptsrc=/js/admin.js/scriptlinkhref=/js/dtree/dtree.cssrel=stylesheettype=text/cssscripttype=text/javascriptsrc=/js/dtree/dtree.js/scriptlinkhref=/css/index.cssrel=stylesheettype=text/csslinkhref=/css/navcss.cssrel=stylesheettype=text/csslinkhref=/css/govnet/self.cssrel=stylesheettype=text/css!--linkhref=/css/index.cssrel=stylesheettype=text/css--linkhref=/css/common.cssrel=stylesheettype=text/css/headbodyclass=body_index!DOCTYPEhtmlPUBLIC-//W3C//DTDXHTML1.0Transitional//EN==Content-Typecontent=text/html;charset=utf-8/title无标题文档/titlescripttype=text/javascriptsrc=/bootstrap/jquery.min.js/scriptlinkhref=/bootstrap/css/bootstrap.min.cssrel=stylesheetstyle.body_index{background:none!important;}#mian{width:95%;margin:20pxauto;}.table{margin-top:20px;}.tabletheadtr{text-align:center;}.tableth{font-weight:bold;vertical-align:middle;}.tabletd{vertical-align:middle;}.tableinput,.tabletextarea,.tableselect{margin-bottom:0px;}.titleh4{border-bottom:2pxsolid#01AFD4;padding-bottom:8px;}.titlea{margin-top:-50px;}.title{padding-bottom:10px;}.handle_label{float:left;margin-top:5px;margin-left:15px;width:100px;text-align:right;}.nav{width:auto!important;background:none!important;}.table2{margin-top:0px!important;margin-bottom:0px!important;}.hide{display:none;}/stylestyle.mainlevelDIV{width:1050px;}#mian{widt