PHP从数据库导出EXCEL文件

参考博客链接：http://www.cnblogs.com/huangcong/p/3687665.html

我的程序代码

原生导出Excel文件

<?php
header('Content-type: text/html; charset=utf-8');
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:filename=test.xls");

$conn = mysqli_connect("localhost","zhouqi","445864742") or die("无法连接数据库");
mysqli_select_db($conn,"test");
mysqli_set_charset($conn,'utf8');
$sql = "SELECT * FROM student";
$result = mysqli_query($conn,$sql);
echo "ID号 姓名 分数 ";
while ($row = mysqli_fetch_array($result)){
    echo $row[0]." ".$row[1]." ".$row[2]." ";
}
?>

为换格    为换行

PHPEXCEL用法

<?php
require_once(phpexcel_dir()); //引入PHPExcel 类

$objPHPExcel=new PHPExcel();
//获得数据  ---一般是从数据库中获得数据

$conn = mysqli_connect(Conf::$db_host,Conf::$db_username,Conf::$db_password) or die("无法连接数据库");//连接数据库主机，用户名，密码 配置文件里设置
mysqli_select_db($conn,Conf::$db_dbname);//选择数据库
mysqli_set_charset($conn,'utf8'); //设置字符集

 

//左连接连接三张表
$sql = "SELECT
              a.id,a.order_sn,a.status,a.rev_name,a.rev_addr,a.rev_mail,a.rev_post,a.rev_mobile,b.account_name,c.brands_name,a.project
            FROM ec_orders AS a
            LEFT JOIN ec_account AS b ON a.account_id = b.id

            LEFT JOIN ec_goods_brands AS c ON a.brands_id = c.id
            WHERE a.is_del = 0 AND b.is_del = 0 AND c.is_del =0";
$result = mysqli_query($conn,$sql);
$data = array();
$i = 0;
while ($row = mysqli_fetch_array($result)){
    $data[$i]['id'] = $row['id'];
    $data[$i]['order_sn'] = $row['order_sn'];
    //订单的状态   0:待确认 1:已确认/待付款 2:已付款/待发货 3:发货中 4:已发货 5:买家收货确认 6:订单完成 7:买家取消订单 8:卖家取消订单
    switch ($row['status']){
        case 0:
            $row['status'] = '待确认';
            break;
        case 1:
            $row['status'] = '已确认/待付款';
            break;
        case 2:
            $row['status'] = '已付款/待发货';
            break;
        case 3:
            $row['status'] = '发货中';
            break;
        case 4:
            $row['status'] = '已发货';
            break;
        case 5:
            $row['status'] = '买家确认收货';
            break;
        case 6:
            $row['status'] = '订单完成';
            break;
        case 7:
            $row['status'] = '买家取消订单';
            break;
        case 8:
            $row['status'] = '卖家取消订单';
            break;
        default:
            $row['status'] = '其他未知错误';
            break;
    }
    $data[$i]['status'] = $row['status'];
    $data[$i]['rev_name'] = $row['rev_name'];

    $data[$i]['rev_addr'] = $row['rev_addr'];
    $data[$i]['rev_mail'] = $row['rev_mail'];
    $data[$i]['rev_post'] = $row['rev_post'];
    $data[$i]['rev_mobile'] = $row['rev_mobile'];
    $data[$i]['account_name'] = $row['account_name'];
    $data[$i]['brands_name'] = $row['brands_name'];
    $data[$i]['project'] = $row['project'];
    $i++;
}
/*echo "<pre>";
print_r($data);
echo "</pre>";*/

//设置excel列名
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','订单ID');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1','订单编号');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1','状态');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1','收货人');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1','收货地址');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1','收货人邮箱');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1','邮编');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H1','收货人电话');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I1','会员帐号');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J1','品牌id');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K1','项目名');
//背景填充颜色
$objPHPExcel->getActiveSheet()->getStyle( 'A1:K1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle( 'A1:K1')->getFill()->getStartColor()->setARGB('FF808080');
//把数据循环写入excel中
foreach($data as $key => $value){
    $key+= 2;   //从第二行开始填充
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$key,$value['id']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$key,$value['order_sn']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$key,$value['status']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$key,$value['rev_name']);

    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$key,$value['rev_addr']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$key,$value['rev_mail']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$key,$value['rev_post']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$key,$value['rev_mobile']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$key,$value['account_name']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$key,$value['brands_name']);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('k'.$key,$value['project']);
}
//设置默认字体
$objPHPExcel->getDefaultStyle()->getFont()->setName( 'Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(12);

//设置列宽
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
//设置居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//excel保存在根目录下  如要导出文件，以下改为注释代码
//$objPHPExcel->getActiveSheet() -> setTitle('SetExcelName');
//$objPHPExcel-> setActiveSheetIndex(0);
//$objWriter = $iofactory -> createWriter($objPHPExcel, 'Excel2007');
//$objWriter -> save('SetExcelName.xlsx');
//导出代码
$objPHPExcel->getActiveSheet() -> setTitle('订单列表');
$objPHPExcel-> setActiveSheetIndex(0);

$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
$filename = '订单列表.xlsx';
ob_end_clean();//清除缓存以免乱码出现
header('Content-Type: application/vnd.ms-excel');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$objWriter -> save('php://output');
?>