thinkphp5.0資料匯出excel表格

第一步、創建Model類檔案（名稱自定）

第二步、在類中寫入以下代碼

<?php

namespace Admin\admin\model;

use think\Model;

class MarkModel extends Model{

    //匯出預約資訊
    public function outExcel1(){

        $del=@implode(",",$_POST["del"]);

        if($del!=""){

            $art = db('messga');

            $where['id'] = array('in',$del);

            $rs = $art->where($where)->order('id desc')->select();

            foreach ($rs as $v){
                $excdata[]=array(

                    'id'=>$v["id"],

                    'names'=>$v["names"],

                    'phone'=>$v["phone"],

                    'email'=>$v["email"],

                    'cid'=>$v["cid"],

                    'companynames'=>$v["companynames"],

                    'tel'=>$v["tel"],

                    'num'=>$v["num"],

                    'p_names'=>$v["p_names"],

                    'dates'=>date('Y-m-d H:i:s',$v["dates"])

                );
            }


        }else{

            $art = db('message');

            $rs = $art->order('id desc')->select();

            foreach($rs as $v){
                $excdata[]=array(

                    'id'=>$v["id"],

                    'names'=>$v["names"],


                    'phone'=>$v["phone"],

                    'email'=>$v["email"],

                    'cid'=>$v["cid"],

                    'companynames'=>$v["companynames"],

                    'tel'=>$v["tel"],


                    'num'=>$v["num"],

                    'p_names'=>$v["p_names"],

                    'dates'=>date('Y-m-d H:i:s',$v["dates"])

                );
            }

            //echo "<script>alert ('請選中要匯出的資料'); history.go(-1);</script>";

        }


　　　　//陣列資訊根據自己專案而定
        $expCellName=array(

            array('id','序號'),

            array('names','姓名'),

            array('phone','電話'),

            array('email','郵箱'),

            array('companynames','公司名稱'),

            array('num','人數'),

            array('cid','公開課'),

            array('p_names','職務'),

            array('tel','座機'),

            array('dates','提交日期')

        );

        $this->exportExcel('留言資訊',$expCellName,$excdata);

        exit;


    }




    /**

     * 匯出操作

     * 引數1：$expTitle :匯出excel的標題

     * 引數2：$expCellName:匯出到表格中需要顯示每一列的標題 如下：

     * $expCellName=array(

     *  引數三：$expTableData : 需要匯出的表中資料

     *

     *

     */

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

　　　　　//引入PHPExcell類
        import('PHPExcel.Classes.PHPExcel');

        import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');

        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);

        //檔案名稱

        $fileName = $expTitle.date('_YmdHis');

        //or $xlsTitle 檔案名稱可根據自己情況設定

        $cellNum = count($expCellName);

        $dataNum = count($expTableData);

        vendor("phpoffice.phpexcel.Classes.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');

        // 設定水平垂直居中

        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //設定excel表格 從 A1 到 AB1 這一行的字體加粗

        $objPHPExcel->getActiveSheet()->getStyle('A1:AB1')->getFont()->setBold(true);

        // 設定某一行的高度 1.2.3

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);



        //J 、 K列為文本 (輸入什么就是什么，不會隨著excel系統格式變化)

        // $objPHPExcel->getActiveSheet()->getStyle('J')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

        // $objPHPExcel->getActiveSheet()->getStyle('K')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

        //設定某一列的寬度

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

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

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

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

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

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

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

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

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

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

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

        // 設定某一行的高度 1.2.3

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

        // 隱藏某一列

        //$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setVisible(false);

        //$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setVisible(false);

        //$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);

        //$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);

        //合并單元格

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

        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));

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

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '1', $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 + 2), $expTableData[$i][$expCellName[$j][0]]);

            }

        }

        // 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;

    }



}

 3.寫個自定義方法呼叫（欄位換成自己的）

標籤：PHP

上一篇：[視頻教程] redis中的bit運用統計用戶在線天數

下一篇：[Linux] deepin系統添加PHP倉庫源出錯Error: could not find a distribution template for Deepin/stable