KJDS/malai/php/excel.php

<?php
error_reporting(0);
/**
 * 导出excel(csv)
 * @data 导出数据
 * @headlist 第一行,列名
 * @fileName 输出Excel文件名
 */
// $data = print_r(base64_encode(unserialize($_POST['post_data'])));
//$data = array(array( "产品编号"=>"51","产品Id"=>"555"), array("产品Id"=>"665", "产品编号"=>"50"));
// print_r($data);

//   }
/*
语法mysqli_connect(host,username,passwd,dbname,port);
mysqli_connect()方法具有返回值，返回值就是PHP与MySQL数据库建立连接的连接对象
*host - MySQL数据库所在的计算机的ip地址
* username - 登陆MySQL数据库的用户名称
* passwd - 登陆MySQL数据库的用户密码（如果密码为空则写  “” 站位）
* dbname - 要操作的数据库的名称
* port - MySQL数据库所使用的端口号
*/
    //1.建立连接
    // $starttime=$_GET['val1'];
    // $endtime=$_GET['val2'];
    // $val3=$_GET['val3'];
    // $val4=$_GET['val4'];
    // $val5=$_GET['val5'];
    // $val6=$_GET['val6'];

    // $valArr = json_decode($_GET['vals']);
    // echo $valArr;
    $valArr = $_GET['vals'];
    $m=mysqli_connect('127.0.0.1','root','usestudio-1','MaLai','3306');    
    //判断如果数据库连接不成功
    if ($m->connect_error) {
        //返回错误，结束运行，退出脚本。
        //echo json_encode(Array("Error" => $m->connect_errno . " - " . $m->connect_error));
        return;
    }
    //2.定义sql语句,设置字符集
    #$guid = trim(com_create_guid(),"{}");//随机生成的唯一标识码
    #$sql = "insert into jyc_test(UserId,UserName,UserPassWord,time) values('".$guid."','jyc','123456',now())";    
    #$sql = "select * from jyc_test";
    //$sql = "CALL test_fc( '201810419327450') ";
    // $sql = "CALL SelectOrderInfo( $starttime, $endtime,$val3,$val4,$val5,$val6) ";
    // $sql = "CALL SelectOrderInfo( '2018-10-11','2018-10-12','','','1','10000') ";

    $sql = "CALL PrintExcel('".$valArr."');";
    //设置字符集
    $m->set_charset('utf8');   
    $m->query("set character set 'utf8';");
    //执行存储过程语句
   // $res = $m->query($sql);

    //判断返回值的类型是否是布尔型,如果是布尔型，返回字符串"true";
    $arr = array();
    $arr1 = array();
    $all_rows = array();  //定义一个数组，用于存放结果集
    $r = array();
    
    $m -> multi_query(urldecode($sql));

    $s = array();
    if ($res=$m -> store_result()){
        while($row = $res->fetch_array(MYSQLI_ASSOC)){
            //   array_push($s, $v);
                $arr["OrderId"]="A10107A";
                $arr["LogisticsChannel"]=" ";
                $arr["OrderNum"]="\t".$row['OrderNum'];
                $arr['UserReallyName']=$row['UserReallyName'];
                $arr['Address']=$row['Address'].$row['City'].$row['Province'];
                $arr['1']=" ";
                $arr['2']=" ";
                $arr['Area']=$row['Area'];
                $arr['City']=$row['City'];
                $arr['Province']=$row['Province'];
                $arr['CountryCode']="TH";
                $arr['PostalCode']=$row['PostalCode'];
                $arr['UserPhoneNum']=$row['UserPhoneNum'];
                $arr['IsCOD']="Y";
                $arr['Payment']=$row['Payment'];
                $arr['Service1']=" ";
                $arr['CurrencyCode']="THB";
                $arr['DeclaredValue']=$row['Amount'];
                $arr['Weight']="0.5";
                $arr['Volume']="26*17*16*1";
                $arr['IsInsured']="N";
                $arr['Insurance']=" ";
                $arr['Sensitive']="N";
                $arr['ServiceCode']="PDO";
                $arr['Remarks']=$row['Remarks'];
                $arr['ProductRemarks']=$row['ProductRemarks'];
                $arr['EnglishName']=' ';//$row['EnglishName'];
                $arr['SKU']=" ";
                $arr['attribute1']=" ";
                $arr['ProductIntroduce']=$row['ProductIntroduce'];
                $arr['ProductNumber']=$row['ProductNumber'];
                $arr['ProductPrice']=$row['ProductPrice'];
                //把结果集放入到数据中
                // array_push($all_rows, $row); 
                array_push($arr1,$arr);

            //  print_r($arr);
        }
    // //  array_push($r, $s);
     }

		// $j = json_encode(count($r) > 1 ? $r : (count($r) == 0  ? $m -> affected_rows : $r[0]));
    //    print_r($r);

    // echo($arr[Address]);


    //关闭数据库连接
    mysqli_close($m);     
    $data = $arr1;
    $headlist= array('商家编码',   '物流渠道',  '销售订单号',  '收件人姓名',  '收件人地址','','', '收件人区/县', '收件人城市',  '收件人省份','收件人国家二字简码','收件人邮编','收件人电话','是否COD','应收款额','增值服务','币种','货值','包裹重量','客户自测体积','是否保险','保额','是否有敏感货：Y/N','PDO ','备注','报关商品名称','报关商品英文名称','商家商品编码','产品属性(如颜色,尺寸)','商品销售链接','包裹内商品数量','单价');
    // print_r($headlist);
    $fileName='商家订单';
    // function csv_export($data = array(), $headlist = array(), $fileName) {


         header('Content-Type: text/csv; charset=utf-8');
         header('Content-Disposition: attachment;filename="'.$fileName.'.csv"'); 
         header('Cache-Control: max-age=0');

        //打开PHP文件句柄,php://output 表示直接输出到浏览器
        $fp = fopen('php://output', 'a');
    //    fwrite($fp,xEFxBBxBF);
  fwrite($fp,chr(0xEF).chr(0xBB).chr(0xBF));
        //输出Excel列名信息
        foreach ($headlist as $key => $value) {
            //CSV的Excel支持GBK编码，一定要转换，否则乱码
              $headlist[$key] = iconv('utf-8',  'utf-8', $value);
        }
      

        //将数据通过fputcsv写到文件句柄
       fputcsv($fp, $headlist);

        $count = count($data);


       for ($i = 0; $i <  $count; $i++) {
            $row = $data[$i];

	 foreach ($row as $key => $value) {
            //CSV的Excel支持GBK编码，一定要转换，否则乱码
              $row[$key] = iconv('utf-8',  'utf-8', $value);
        	 }

            fputcsv($fp, $row);
       }
fclose($fp);

?>