php excel文件导出之phpExcel扩展库

客官°小女子只卖身不卖艺 2022-08-01 12:27 214阅读 0赞

php Excel  文件导出

phpExcel 官网 [ http://phpexcel.codeplex.com/][http_phpexcel.codeplex.com]

/**
         * 导出特定文件
    	 * 根据具体情况而定
         */
        public function download(){
    
            //1. 从数据库来获取对应的二维数组
            $alist = array(...);
            $list = $alist;
            $data = array();
    		//2. 设置xls的 表头名
            $headArr = array("排名","姓名","手机","获奖","参与时间");
            if(false === empty($list)){
                $i=0;
                foreach ($list as $key => $val){
                    //组装对应的单元格A，B，C，D。。。
    				$data[$i] = array(
                           ($i+1),            //A
                           $val['name'],      //B
                           $val['tel'],       //C
                           $val['award'],     //D
                           ...
                        );
                     $i++;
                }
            }else{
                $data[0] = array('暂无相关记录！');
            }
    
            $fileName = "test-excel_";
            $this->explodexls($data,$headArr,$fileName);
        }
    
        /**
    	 * 工具函数 处理 xls 文件导出
    	 * @param $data array() 对应的tbody 数据
         * @param $headArr array() 对应的thead 数据
    	 * @param $fileName string  指定的导出文件名 
    	 * @return xls格式 文件自动导出
         */
        public function explodexls($data,$headArr,$fileName){
    		//注意这里换成对应的根目录 找到绝对路径
            $dir = __dir__.'/../../../../public';
            require_once $dir.'/excel/PHPExcel.php';
            require_once $dir.'/excel/PHPExcel/Writer/Excel2007.php';
            require_once $dir.'/excel/PHPExcel/Writer/Excel5.php';
            include_once $dir.'/excel/PHPExcel/IOFactory.php';
    
            if(empty($data) || !is_array($data)){
                die("data must be a array");
            }
            if(empty($fileName)){
                exit;
            }
            $date = date("Y_m_d",time());
            $fileName .= "_{$date}.xls";
            //创建新的PHPExcel对象
            $objPHPExcel = new PHPExcel();
            $objProps = $objPHPExcel->getProperties();
            //设置表头
            $key = ord("A");
            foreach($headArr as $v){
                $colum = chr($key);
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum.'1', $v);
                $key += 1;
            }
            $column = 2;
            $objActSheet = $objPHPExcel->getActiveSheet();
            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('Simple');
            //设置活动单指数到第一个表,所以Excel打开这是第一个表
            $objPHPExcel->setActiveSheetIndex(0);
            //将输出重定向到一个客户端web浏览器(Excel2007)
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header("Content-Disposition: attachment; filename=\"$fileName\"");
            header('Cache-Control: max-age=0');
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $objWriter->save('php://output'); //文件通过浏览器下载
            exit;
        }

[http_phpexcel.codeplex.com]: http://phpexcel.codeplex.com/