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號\t姓名\t分數\t\n";
while ($row = mysqli_fetch_array($result)){
    echo $row[0]."\t".$row[1]."\t".$row[2]."\t\n";
}
?>

 

\t為換格   \n為換行

 

 

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‘);
?>

 

PHP從資料庫匯出EXCEL檔案