thinkPHP 利用PHPExcel导出网站数据为excel

一、下载PHPExcel包，并放在thinkPHP文件中

 

 

新建文件夹，命名为PHPExcel，将上面两个文件PHPExcel，PHPExcel.php放到我们新建的PHPExcel文件夹下面；然后将此文件夹放在thinkPHP核心包里面，路径如下：D:\***\你的项目\ThinkPHP\Library\Vendor

 

二、前端界面代码

前端界面：

三、导出表格样式代码：

//商家表格导出模板

   public function exportExcel($expTitle, $expCellName, $expTableData) {

           $xlsTitle = iconv('utf-8', 'gb2312', $expTitle); //文件名称

           $fileName = '网站商家信息表' . date('_YmdHis'); //or $xlsTitle 文件名称可根据自己情况设定

           $cellNum = count($expCellName);

           $dataNum = count($expTableData);

           vendor("PHPExcel.PHPExcel");

           $objPHPExcel = new\PHPExcel();

           $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');

           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(22);

           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);

           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);

           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);

           $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);

           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);

           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);

           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);

           $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);

           $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);

           $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);

           $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);

           $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);

           $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(22);

           $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12);

           $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);

           $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' .$cellName[$cellNum - 1] . '1');

            //合并单元格

            $objPHPExcel->getActiveSheet()->setCellValue('A1', '网站商家信息表')->getStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

       for ($i = 0; $i < $cellNum; $i++) {

           $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2',$expCellName[$i][1]);

        }

       // Miscellaneous glyphs, UTF-8

       for ($i = 0; $i < $dataNum; $i++) {

           for ($j = 0; $j < $cellNum; $j++) {

               $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i+ 3), " ".$expTableData[$i][$expCellName[$j][0]]);

           }

       }

       ob_end_clean(); //清除缓冲区,避免乱码

       header('pragma:public');

       header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');

       header("Content-Disposition:attachment;filename=$fileName.xls");

    //attachment新窗口打印inline本窗口打印

       $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

       $objWriter->save('php://output');

       exit;

    }

 

四、导出所需数据代码

 

 

function output($begin=0, $end=0) {//导出商家信息Excel

       $xlsName = "User";

       $xlsCell = array(

array('userid', '商家id'),

array('groupid', '商家等级'),

array('store_name', '店铺名称'),

array('contact_name', '联系人'),

array('phone', '手机'),

array('email', '邮箱'),

array('activity_count', '活动商品'),

array('frozen_deposit', '冻结中保证金'),

array('regdate', '注册时间'),

array('loginnum', '登录次数'),

array('lastdate', '最近登录'),

array('id_number', '身份证号码'),

array('name', '姓名'),

           array('qq', 'QQ'),

       );

       $beginToday = mktime(0, 0, 0, date('m'), date('d'), date('Y'));

       $endToday = mktime(0, 0, 0, date('m'), date('d') + 1, date('Y')) - 1;

        if ($begin > 0) {

           $beginToday = $begin;

       }

       if ($end > 0) {

           $endToday = $end;

       }

       $xlsModel = M('member');

       $xlsData=$xlsModel->query("SELECTm.userid,m.groupid,c.store_name,c.contact_name,m.phone,m.email,c.frozen_deposit,FROM_UNIXTIME(m.regdate)AS regdate,loginnum,FROM_UNIXTIME(m.lastdate) AS lastdate,m.qq FROM xw_memberAS m ,xw_member_merchant AS c WHERE m.modelid = 2 AND m.userid = c.userid and(m.lastdate between $beginToday and $endToday) ORDER BY m.userid DESC");

       $dataNum = count($xlsData);

       for ($i = 0; $i < $dataNum; $i++){

                if ($xlsData[$i][groupid] =='1') {

$xlsData[$i][groupid] = '普通商家';

                } else if($xlsData[$i][groupid] == '2') {

$xlsData[$i][groupid] = '金牌商家';

                } else if($xlsData[$i][groupid] == '3') {

$xlsData[$i][groupid] = '白金商家';

                } else if ($xlsData[$i][groupid] == '4') {

$xlsData[$i][groupid] = '钻石商家';

                }

$rs=M('member_attesta')->where("userid='".$xlsData[$i]['userid']."' AND type ='identity'")->getField('infos');

                        $identity =string2array($rs);

                       $xlsData[$i]['id_number'] = $identity['id_number'];

                        $xlsData[$i]['name'] =$identity['name'];

                      $xlsData[$i]['activity_count']= M('product')->where(array('company_id'=>$xlsData[$i]['userid']))->count();

           }

                 $this->exportExcel($xlsName, $xlsCell, $xlsData);

    }

}

五、导出数据表格如下