PHP使用PHPExcel实现导出数据

如果前端请求一定不要使用Ajax否则会乱码，使用window.location="地址"









在后台管理中会经常需要将数据生成excel表格的；

首先下载PHPExcel类库
示例项目：https://github.com/baijunyao/thinkphp-bjyadmin

到https://github.com/PHPOffice/PHPExcel下载PHPExcel
如果不懂得使用git，可以到这https://codeload.github.com/PHPOffice/PHPExcel/zip/1.8下载压缩包，懂得的自行用git下载。

一：导入phpexcel
放到：ThinkPho根目录/vendor/PHPExcel


方法一：
 static public function dataExcel($fileName, $expCellName = "test", $expTableData = "test")
{
//        $data = input("post.");
//        //连接数据库
//        try{
//            $prizes = array();
//            $counts = self::select();
//            $gb = new GbCenter();
//            if (array_key_exists("PrizeTime", $data)) {
//                $where['PrizeTime'] = array('like',$data["PrizeTime"].'%');
//            }
//            if (array_key_exists("PrizeType", $data)) {
//                $typeWhere['PrizeType'] = array('like',$data["PrizeType"].'%');
//            }
//            if (isset($where) || isset($typeWhere)) {
//                if (isset($where)) {
//                    $sql = $gb->order("id")
//                        ->where($where)
//                        ->select();
//                    $sql = array_filter($sql);
//                } else {
//                    $sql = self::select();
                    $sqlData = self::select();
//                }
//                foreach ($sql as $key => $prize) {
//                    if (array_key_exists("PrizeType", $data)) {
//                        //通过奖品ID查询
//                        if ($data["PrizeType"] == 0) {
//                            $wheres[] = GbPrize::where("Id", $prize["PrizeNumber"])
//                                ->field("PrizeMoney, PrizeType")
//                                ->select();
//                        } else {
//                            $wheres[] = GbPrize::where("Id", $prize["PrizeNumber"])
//                                ->where($typeWhere)
//                                ->field("PrizeMoney, PrizeType")
//                                ->select();
//                        }
//                    } else {
//                        $wheres[] = GbPrize::where("Id", $prize["PrizeNumber"])
//                            ->field("PrizeMoney, PrizeType")
//                            ->select();
//                    }
//                    if (!empty($wheres)) {
//                        $record = GbRecord::where("MemberNumber", $prize["MemberNumber"])
//                            ->distinct(true)
//                            ->field("OrderNumber")
//                            ->select();
//                        $record = array_filter($record);
//                        foreach ($record as $rekey => $reval) {
//                            if (!empty($record)) {
//                                $orderData[] = GbOrder::where("OrderNumber", $reval["OrderNumber"])->select();
//                                $order[] = GbOrder::where("OrderNumber", $reval["OrderNumber"])
//                                    ->field("TranMoney, OrderType")
//                                    ->select();
//                            }
//                        }
//                    }
//                }
//                if (!empty($wheres)) {
//                    foreach ($wheres as $k => $v) {
//                        foreach ($v as $ke => $val) {
//                            foreach ($order as $orkey => $orval) {
//                                foreach ($orval as $rekey => $reval) {
//                                    $prizes[$k]["0"] = $reval["TranMoney"];
//                                    $prizes[$k]["1"] = $reval["OrderType"];
//                                }
//                                $prizes[$k]["2"] = $val["PrizeMoney"];
//                                $prizes[$k]["3"] = $sql[$k]["PrizeTime"];
//                                $prizes[$k]["4"] = $val["PrizeType"];
//                            }
//                        }
//                    }
//                }
//            }
//
//        } catch (Exception $e) {
//            return $e->getMessage();
//        }
        $prizes = [
           array('a', 'b', 'c','d','o'),
           array('d', 'e', 'f','d','g')
        ];
        $key = [
            array('下单金额', '下单类型', '中奖金额', "中奖时间", "中奖类型"),
        ];
        if (empty($prizes)) {
            return json_encode(["msg" => "当前没有数据需要导出······", "code" => 400], JSON_UNESCAPED_UNICODE);
        }
        $mulit_arr = array_merge($key, $prizes);
        /* @实例化 */
        vendor("Classes.PHPExcel");
        vendor("Classes.IOFactory.php");
        $obpe = new \PHPExcel();

        /* @func 设置文档基本属性 */
        $obpe_pro = $obpe->getProperties();
        $obpe_pro->setCreator('cheng')//设置创建者
        ->setLastModifiedBy(date("Y-m-d H:i:s"))//设置时间
        ->setTitle('test')//设置标题
        ->setSubject('test')//设置备注
        ->setDescription('destesribe')//设置描述
        ->setKeywords('keyword')//设置关键字 | 标记
        ->setCategory('catagory');//设置类别


        /* 设置宽度 */
        //$obpe->getActiveSheet()->getColumnDimension()->setAutoSize(true);
        //$obpe->getActiveSheet()->getColumnDimension('B')->setWidth(10);

        //设置当前sheet索引,用于后续的内容操作
        //一般用在对个Sheet的时候才需要显示调用
        //缺省情况下,PHPExcel会自动创建第一个SHEET被设置SheetIndex=0
        //设置SHEET
        $obpe->setactivesheetindex(0);
        //写入多行数据
        foreach($mulit_arr as $k=>$v){
            $k = $k+1;
            /* @func 设置列 */
            $obpe->getactivesheet()->setcellvalue('A'.$k, $v[0]);
            $obpe->getactivesheet()->setcellvalue('B'.$k, $v[1]);
            $obpe->getactivesheet()->setcellvalue('C'.$k, $v[2]);
            $obpe->getactivesheet()->setcellvalue('D'.$k, $v[3]);
            $obpe->getactivesheet()->setcellvalue('E'.$k, $v[4]);
        }

        //创建一个新的工作空间(sheet)
        $obpe->createSheet();
        $obpe->setactivesheetindex(1);
        //写入多行数据
        foreach($mulit_arr as $k=>$v){
            $k = $k+1;
            /* @func 设置列 */
            $obpe->getactivesheet()->setcellvalue('A'.$k, $v[0]);
            $obpe->getactivesheet()->setcellvalue('B'.$k, $v[1]);
            $obpe->getactivesheet()->setcellvalue('C'.$k, $v[2]);
            $obpe->getactivesheet()->setcellvalue('D'.$k, $v[3]);
            $obpe->getactivesheet()->setcellvalue('E'.$k, $v[4]);
        }
        //写入类容
        $obwrite = \PHPExcel_IOFactory::createWriter($obpe, 'Excel5');
        $filenames = "GanGuBang" . date("Y-m-d") . "." . "xls";
        $obwrite->save($filenames);
        //ob_end_clean();
        //输出到浏览器下载
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename='.$filenames.'');
        header("Content-Transfer-Encoding:binary");
        $obwrite->save('php://output');
        //保存文件名
        if ($obwrite == true) {
            return json_encode(["msg" => "导出成功", "code" => 200], JSON_UNESCAPED_UNICODE);
        } else {
            return json_encode(["msg" => "导出失败", "code" => 400], JSON_UNESCAPED_UNICODE);
        }
    }



方法二：
static public function dataExcel($fileName, $expCellName = "gangubang", $expTableData = "gangu")
    {
        $data = "数组";
     

        //对数据进行检验
        if (empty($data) || !is_array($data)) {
            die("data must be a array");
        }
        //检查文件名
        if (empty($fileName)) {
            exit;
        }

//        $date = date("Y_m_d", time());
//        $fileName .= ".xls";

        //创建PHPExcel对象，注意，不能少了\
        vendor("Classes\PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(17);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(19);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);

        //设置表头
//        $key = ord("A");
//        foreach ($key as $v) {
//            $colum = chr($key);
//            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
//            $key += 1;
//        }

        $column = 1;
        $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);
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        // 从浏览器直接输出$filename
        header('Content-Type:application/csv;charset=UTF-8');
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-excel;");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="'.$fileName.'.xls"');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }