php 操作mysql 数据库生成Excel 并保存到服务器指定目录

<?php
/**
 * Created by PhpStorm.
 * User: David
 * Date: 2018/9/12
 * Time: 16:14
 */
header("Content-type:text/html;charset=utf8");
//数据同步脚本方便数据库表的增删改查
class DATA_TB{

    public $objPHPExcel;
    public function __construct(){
        $this->db = new mysqli('xxx.xxx.xxx','user','pwd','dbname');
        if(mysqli_connect_errno()){
            echo '数据库连接错误,错误信息:'.mysqli_connect_error();
            exit();
        }

        require_once ( $_SERVER['DOCUMENT_ROOT'].'\geox_api\utils\PHPExcel\Classes\PHPExcel.php');

        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
        $cacheSettings = array('memoryCacheSize'=>'32MB');
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
        $this->objPHPExcel = new PHPExcel();
    }


    public function to_array($sql){
        $result = $this->db->query($sql);
        while ($row = $result->fetch_assoc()){
            $res[] = $row;
        }
        return $res;
    }
    //下面方法可根据需求自行修改。
    public function tb_data(){
//        导出的数据对应的表数据
        $sql = 'select barcode_id,goods_id,cat_id,color_id,size_id,barcode  from goods_barcode gb limit 10';

        $res = $this->to_array($sql);
        $this->objPHPExcel->setActiveSheetIndex(0);
        $this->objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $this->objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);

        $this->objPHPExcel->getActiveSheet()
            ->setCellValue('A1', '第一列')
            ->setCellValue('B1', '第二列')
            ->setCellValue('C1', '第三列')
            ->setCellValue('D1', '第四列');

        foreach ($res as $k => $v) {

            $field1 = 'A'.($k+2);
            $this->objPHPExcel->getActiveSheet()->setCellValueExplicit($field1, $v['barcode_id'],
                PHPExcel_Cell_DataType::TYPE_STRING);

            $field2 = 'B'.($k+2);
            $this->objPHPExcel->getActiveSheet()->setCellValueExplicit($field2, $v['goods_id'],
                PHPExcel_Cell_DataType::TYPE_STRING);

            $field3 = 'C'.($k+2);
            $this->objPHPExcel->getActiveSheet()->setCellValueExplicit($field3, $v['cat_id'],
                PHPExcel_Cell_DataType::TYPE_STRING);

            $field4 = 'D'.($k+2);
            $this->objPHPExcel->getActiveSheet()->setCellValueExplicit($field4,$v['barcode'],
                PHPExcel_Cell_DataType::TYPE_STRING);
        }

        $user_path=$_SERVER['DOCUMENT_ROOT']."/uploads/up/";

        $filename = date('YmdHis',time()).'商品信息.xlsx';

        $filename = iconv("utf-8","gb2312",$filename);

        $objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');

//        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: applicationnd.ms-excel ;charset=utf-8");
//        header("Content-Type:application/octet-stream");
//        header("Content-Type:application/download");
//        header("Content-Disposition:attachment;filename=".$filename);
//        header("Content-Transfer-Encoding:binary");
//        $objWriter->save("php://output");
          $objWriter->save($user_path.$filename);
    }

}

$obj = new DATA_TB();
$obj->tb_data();

?>