首先需要确认项目内是否存在
ThinkPHP/Library/Org/Util/PHPExcel
ThinkPHP/Library/Org/Util/PHPExcel.class.php
ThinkPHP/Library/Org/Util/PHPExcel
ThinkPHP/Library/Org/Util/PHPExcel.class.php
- //模板代码
- <html>
- <head></head>
- <body>
- <P><a href="{:U('Index/allExp')}" >导出数据并生成excel</a></P><br/>
- <form action="{:U('Index/importExp')}" method="post" enctype="multipart/form-data" >
- <div>
- <label>Excel文件地址</label>
- <div>
- <input type="text" id="ExcelURL" name="ExcelURL" >
- </div>
- <div>
- <button type="button">选择文件</button>
- <input id="doc-form-file" type="file" name="excel" multiple>
- </div>
- </div>
- <div class="am-form-group">
- <div id="imgPreview" >
- <img id="img1" src=<pre name="code" class="html">"" alt="" width="200" height="200"/>
- </div>
- </div>
- <div>
- <div>
- <button type="submit">导入</button>
- </div>
- </div>
- </form>
- </body>
- </html>
- //导入Excel方法操作
- public function importExp()
- {
- header("Content-type: text/html;charset=utf-8");//设置页面内容是html编码格式是utf-8
- $m=D("Webinfo");//连接数据表
- $cell=array();
- //导入Excel前要上传Excel文件到项目文件夹,如果成功进行,如果失败提示错误信息
- //I('post.ExcelURL','','htmlspecialchars')为获取上传控件传来的文件名称
- if(I('post.ExcelURL','','htmlspecialchars')!="")
- {
- $uploads="Uploads";
- $upload = new \Think\Upload();// 实例化上传类
- $upload->maxSize = 5242880 ;// 设置附件上传大小
- $upload->exts = array('xlsx','xls');// 设置附件上传类型
- $upload->rootPath = './'.$uploads.'/'; // 设置附件上传根目录
- $upload->subName = array('date','Ym');
- // 上传单个文件
- $info = $upload->uploadOne($_FILES['excel']);
- if(!$info) {// 上传错误提示错误信息
- $this->error($upload->getError());
- }else{
- //上传Excel成功
- $exts = $info['ext'];
- $file_name=$uploads.'/'.$info['savepath'].$info['savename'];
- $res=$this->getdata($file_name,$exts);
- //循环读取每行数据,进行写入数据库
- foreach ( $res as $k => $v )
- {
- if ($k != 0)
- {
- //获取数据库中的最大ID自增加1
- $m->create();
- $id=$m->max('ID');
- if($id==0||$id==NULL||$id==""){
- $id=1;
- }
- else
- {
- $id=$id+1;
- }
- //读取数据后赋给数组data
- $data['ID']=$id;
- $data ['Name'] = $v [B];
- $data ['Site'] = $v [C];
- $result = $m->add($data);//添加操作
- }
- }
- if($result!=0){
- $this->success('网站数据导入成功');
- }else{
- $this->error('网站数据导入失败');
- }
- }
- }
- else
- {
- $this->error("请选择上传的文件");
- }
- }
- //获取excel文件、读取数据方法
- public function getdata($file_name,$exts='xls')
- {
- //导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
- import("Org.Util.PHPExcel");
- //创建PHPExcel对象,注意,不能少了\
- $PHPExcel=new \PHPExcel();
- if($exts=="xls")
- {
- import("Org.Util.PHPExcel.Reader.Excel5");
- $PHPReader=new \PHPExcel_Reader_Excel5();
- }
- else if($exts=="xlsx")
- {
- import("Org.Util.PHPExcel.Reader.Excel2007");
- $PHPReader=new \PHPExcel_Reader_Excel2007();
- }
- //载入文件
- $PHPExcel=$PHPReader->load($file_name);
- //获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
- $currentSheet=$PHPExcel->getSheet(0);
- //获取总列数
- $allColumn=$currentSheet->getHighestColumn();
- //获取总行数
- $allRow=$currentSheet->getHighestRow();
- $excelData = array();
- //循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始
- for($currentRow=2;$currentRow<=$allRow;$currentRow++){
- //从哪列开始,A表示第一列
- for($currentColumn='B';$currentColumn<=$allColumn;$currentColumn++){
- //数据坐标
- $address=$currentColumn.$currentRow;
- //读取到的数据,保存到数组$arr中
- $excelData[$currentRow][$currentColumn] = $currentSheet-> getCell($address)-> getValue();
- }
- }
- return $excelData;
- }
- //全部导出数据方法
- public function allExp()
- {
- //链接所导出的数据表
- $xlsModel = D('Webinfo');
- //“WID,WName,WebSite,Remark”为所查询的字段,“Status=2”查询条件
- $goods_list = $xlsModel->relation(TRUE)->field('WID,WName,WebSite,Remark')->where('Status=2')->select();
- $count=1;//导出Excel序号排列
- $data = array();
- //循环查询后的数据,进行每一列
- foreach ($goods_list as $k=>$goods_info){
- $data[$k][ID] = $count++;//序号列
- $data[$k][Name] = $goods_info['Name'];//名称列
- $data[$k][ebSite] = $goods_info['Site'];//地址列
- $data[$k][Remark] = $goods_info['Remark'];//备注列
- }
- //每列表的名称
- foreach ($data as $field=>$v){
- if($field == 'ID'){
- $headArr[]='序号';
- }
- if($field == 'Name'){
- $headArr[]='名称';
- }
- if($field == 'Site'){
- $headArr[]='网址';
- }
- if($field == 'Remark'){
- $headArr[]='备注';
- }
- }
- $filename="网站信息数据表";//所导出的保存文件名称
- $sss=$this->getExcel($filename,$headArr,$data);//调用导出引用方法
- }
- //导出引用方法
- public function getExcel($fileName,$headArr,$data)
- {
- //导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
- import("Org.Util.PHPExcel");
- import("Org.Util.PHPExcel.Writer.Excel5");
- import("Org.Util.PHPExcel.IOFactory.php");
- $date = date("Y_m_d",time());
- $fileName .= "_{$date}.xls";
- //创建PHPExcel对象,注意,不能少了\
- $objPHPExcel = new \PHPExcel();
- $objProps = $objPHPExcel->getProperties();
- //设置表头
- $key = ord("A");
- //print_r($headArr);exit;
- foreach($headArr as $v){
- $colum = chr($key);
- $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
- $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
- $key += 1;
- }
- $column = 2;
- $objActSheet = $objPHPExcel->getActiveSheet();
- //print_r($data);exit;
- foreach($data as $key => $rows){ //行写入
- $span = ord("A");
- foreach($rows as $keyName=>$value){// 列写入
- $j = chr($span);
- $objActSheet->setCellValue($j.$column, $value);
- $span++;
- }
- $column++;
- }
- $fileName = iconv("utf-8", "gb2312", $fileName);
- //重命名表
- //$objPHPExcel->getActiveSheet()->setTitle('test');
- //设置活动单指数到第一个表,所以Excel打开这是第一个表
- $objPHPExcel->setActiveSheetIndex(0);
- ob_end_clean();//清除缓冲区,避免乱码
- header('Content-Type: application/vnd.ms-excel');
- header("Content-Disposition: attachment;filename=\"$fileName\"");
- header('Cache-Control: max-age=0');
- $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
- $objWriter->save('php://output'); //文件通过浏览器下载
- exit;
- }