欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-20 20:08:28
...
<?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();

?>