MYSQL导出为excle文件

    方法一

    // 脚本不超时

    set_time_limit(0);

    /*

    * 导出EXECL

    */

    function to_execl($data, $title, $firstline, $datacharset, $specialarr,$topTitle=''){

date_default_timezone_set("PRC");

Header("Content-Type: application/vnd.ms-excel; charset=UTF-8");

Header("Accept-Ranges:bytes");

Header("Content-Disposition:attachment;filename=" . date('YmdHis') . ".xls");

Header("Pragma: no-cache");

Header("Expires: 0");

if($topTitle){

   echo iconv("UTF-8", "GBK", $topTitle."\r\n\r\n\r\n");

}

if($firstline){

   $lineData = explode(",", $firstline);

   $lineStr = '';

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

$lineStr .= $v."\t";

   }

   $lineStr .= "\r\n\r\n";

   echo iconv("UTF-8", "GBK", $lineStr);

}

$tx = explode(",", $title);

$str = '';

foreach ($tx as $vv) {

   $str .= $vv . "\t";

}

$str .= "\r\n";

$info = iconv("UTF-8", "GBK", $str);

echo $info;

$str2 = '';

foreach ($data as $k => $arr) {

   $i = 0;

   foreach ($arr as $kkk => $vvv) {

switch ($datacharset[$i]) {

   case 'i' : $str2 .= $vvv . "\t";

break;

   case 'li' : $str2 .= "`" . $vvv . "\t";

break;

   case 's' : $str2 .= trim(@iconv("UTF-8", "GBK", $vvv)) . "\t";

break;

   case 'd' : $str2 .= date("Y-m-d H:i:s", $vvv) . "\t";

break;

   default : $str2 .= trim(@iconv("UTF-8", "GBK", $vvv)) . "\t";

break;

}

$i++;

   }

   $str2 .= "\r\n";

}

echo $str2;

    }

    //连接数据库

    $conn = new mysqli('localhost', 'root', '','demo');

    if(!$conn){

die("Could not connect to database");

    }

    

    //设置字符编码

    mysqli_set_charset($conn, "utf8");

    $page = isset($_GET['page']) ? $_GET['page'] : 1;

    if((int)$page < 1){

$page = 1;

    }

    $pagesize = 1000;

    $limit = ($page-1)*$pagesize.",".$pagesize;

    $query  = "select * from t_order order by id desc limit ".$limit;

    $data = array();

    $result = $conn->query($query);

    if ($result) {  

        if($result->num_rows>0){                                               //判断结果集中行的数目是否大于0  

   while($row = $result->fetch_assoc() ){                      //循环输出结果集中的记录  

                unset($row['id']);

$data[] = $row;

            }  

        }  

    }else {  

        echo "查询失败";  

    }  

    $result->free();

    $title = "订单号,下单人id,配送方式,销售商品(名称),销售额(元),收货姓名,收货电话,收货地址,付款时间";

    $datacharset = array('li','s','s','s','s','s','li','s','d');

    to_execl($data,$title,'',$datacharset,'',''); 

    方法二

    // 脚本不超时

    set_time_limit(0);

    //连接数据库

    $conn = new mysqli('localhost', 'root', '','demo');

    if(!$conn){

die("Could not connect to database");

    }

    //设置字符编码

    mysqli_set_charset($conn, "utf8");

    $page = isset($_GET['page']) ? $_GET['page'] : 1;

    if((int)$page < 1){

$page = 1;

    }

    $pagesize = 1000;

    $limit = ($page-1)*$pagesize.",".$pagesize;

    $query  = "select * from t_order order by id desc limit ".$limit;

    $data = array();

    $result = $conn->query($query);

    if ($result) {  

        if($result->num_rows>0){                                               //判断结果集中行的数目是否大于0  

   while($row = $result->fetch_assoc() ){                      //循环输出结果集中的记录  

                unset($row['id']);

$data[] = $row;

            }  

        }  

    }else {  

        echo "查询失败";  

    }  

    $result->free();

     set_time_limit(0); // 脚本不超时

    //以下加载phpExcel的类---mysql导入到excle文件

    include 'PHPExcel.php';

    include 'PHPExcel/Writer/Excel2007.php';

    //或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的

    //创建一个excel

    $objPHPExcel = new PHPExcel();

    //保存excel—2007格式

    $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

    //或者非2007格式

//    $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

    //设置excel的属性--保护cell

    $objPHPExcel->getActiveSheet()->getProtection()->setSheet(false);

    //设置行高度

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

    //设置宽度

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

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

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

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

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

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

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

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

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

    //设置水平居中

    $objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    //设置背景色

    $objPHPExcel->getActiveSheet()->getStyle( 'A1:I1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

    $objPHPExcel->getActiveSheet()->getStyle( 'A1:I1')->getFill()->getStartColor()->setARGB('FFCAE8EA');

    //设置字体颜色,大小,名称

    $styleArray = array(

'font' => array(

   'bold' => false,

   'color' => array('rgb' => 'FFF'),

   'size' => 12,

   'name' => 'Verdana'

)

    );

    $objPHPExcel->getActiveSheet()->getStyle('A1:I1')->applyFromArray($styleArray);

    //设置文件名称

    $title = time().'.xls';

    $objPHPExcel->getActiveSheet()->setCellValue('A' . 1, '用户名');

    $objPHPExcel->getActiveSheet()->setCellValue('B' . 1, '邮箱');

    $objPHPExcel->getActiveSheet()->setCellValue('C' . 1, '注册IP');

    $objPHPExcel->getActiveSheet()->setCellValue('D' . 1, '注册时间');

    $objPHPExcel->getActiveSheet()->setCellValue('E' . 1, '注册类型');

    $objPHPExcel->getActiveSheet()->setCellValue('F' . 1, '帐号状态');

    $objPHPExcel->getActiveSheet()->setCellValue('G' . 1, '可用积分');

    $objPHPExcel->getActiveSheet()->setCellValue('H' . 1, '经验值');

    $objPHPExcel->getActiveSheet()->setCellValue('I' . 1, '经验值');

    foreach($data as $key=>$val){

$objPHPExcel->getActiveSheet(0)->setCellValue('A'.($key+2), "`".$val['order_sn']);

$objPHPExcel->getActiveSheet(0)->setCellValue('B'.($key+2), $val['user_id']);

$objPHPExcel->getActiveSheet(0)->setCellValue('C'.($key+2), $val['shipping']);

$objPHPExcel->getActiveSheet(0)->setCellValue('D'.($key+2), $val['shipping_name']);

$objPHPExcel->getActiveSheet(0)->setCellValue('E'.($key+2), $val['goods_amount']);

$objPHPExcel->getActiveSheet(0)->setCellValue('F'.($key+2), $val['consignee']);

$objPHPExcel->getActiveSheet(0)->setCellValue('G'.($key+2), $val['mobile']);

$objPHPExcel->getActiveSheet(0)->setCellValue('H'.($key+2), $val['address']);

$objPHPExcel->getActiveSheet(0)->setCellValue('I'.($key+2), date("Y-m-d H:i:s",$val['add_time']));

    }

//保存成文件--保存在服务器中,用户看不到,不使用了

//$objWriter->save($title);

//直接输出到浏览器--下载的文件就在用户的电脑上

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

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=".$title);

header("Content-Transfer-Encoding:binary");

$objWriter->save('php://output');    

exit();