PHPExcel导出excel

最新推荐文章于 2025-09-09 18:53:59 发布

weixin_33908217

最新推荐文章于 2025-09-09 18:53:59 发布

阅读量93

点赞数

CC 4.0 BY-SA版权

文章标签： php 数据库

原文链接：https://yq.aliyun.com/articles/344051

本文介绍使用PHPExcel库进行Excel文件导出的方法，并提供了一个实际应用案例，包括如何设置单元格样式、宽度及从数据库获取数据填充Excel。

PHPExcel可以很好的实现excel的导出功能。

案例

<?php 

/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Asia/Shanghai');

/** Include PHPExcel */
require_once 'PHPExcel/Classes/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Add some data
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '姓名')
            ->setCellValue('B1', '年龄')
            ->setCellValue('C1', '性别')
            ->setCellValue('D1', '国籍');
//$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);

// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A2', '张三')
            ->setCellValue('B2', '250000000000000000')
            ->setCellValue('C2', '男')
            ->setCellValue('D2', '中国');
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A3', '李四')
            ->setCellValue('B3', '25')
            ->setCellValue('C3', '男')
            ->setCellValue('D3', '中国');


// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('人员信息'); // Excel 名

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="人员信息2015-11-9.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;

应用到项目当中，获取数据库的数据

date_default_timezone_set('Asia/Shanghai');

        $objPHPExcel = new PHPExcel();

        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A1', '下单日期')
                    ->setCellValue('B1', '工号')
                    ->setCellValue('C1', '组别')
                    ->setCellValue('D1', '客户名称')
                    ->setCellValue('E1', '联系电话')
                    ->setCellValue('F1', '联系地址')
                    ->setCellValue('G1', '数量')
                    ->setCellValue('H1', '颜色')
                    ->setCellValue('I1', '快递')
                    ->setCellValue('J1', '订单状态');

        $arr = array('A','B','C','D','E','F','G','H','I','J');
        foreach ($arr as $k => $v) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(15); // 设置长度
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true); // 设置粗
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
 
        /* 筛选条件 */
        if ($client_name != "") { // 客户名称
            $sql1 = " and a.client_name like '%" . $client_name . "%'";
        }
        if ($phone_number != "") { // 手机号码，模糊查询
            $sql2 = " and a.phone_number like '%" . $phone_number . "%'";
        }
        if ($user != "") { // 坐席号
            $sql3 = " and a.user='" . $user . "'";
        }
        if ($user_group != "") { // 组别
            $sql4 = " and a.user_group='" . $user_group . "'";
        }
        if ($verifysta != "") { // 订单状态
            $sql5 = " and a.verifysta='" . $verifysta . "'";
        }
        
        // 下单日期，时间
        if($time != ""){
            $b_time = strtotime($time." 00:00");
            $e_time = strtotime($time." 23.59");
            $sql6 = " and a.time >'".$b_time ."' and a.time <'".$e_time."'";
        }

        if ($signsta != "") { // 签收状态
            $sql7 = " and a.signsta='" . $signsta . "'";
        }

        if ($return_reason != "") { // 退回原因
            $sql8 = " and a.return_reason like '%" . $return_reason . "%'";
        }
        $wheres = $sql1 . $sql2 . $sql3. $sql4 . $sql5 . $sql6 .$sql7 .$sql8;


        $select_sql = "select FROM_UNIXTIME(time, '%Y/%m/%d %H:%I:%S' ) as time ,user,b.group_name as user_group,client_name,phone_number,prov,city,dist,address,pro_count,pro_color,express,verifysta from vicidial_order a left join vicidial_user_groups b on a.user_group = b.user_group where 1=1 ".$wheres." order by time asc"; // order by 要放在where条件之后

        $rows = mysqli_query($db_conn, $select_sql);
        $row_counts_list = mysqli_num_rows($rows);

        if ($row_counts_list != 0) {
            $i = 2;
            while ($rs = mysqli_fetch_array($rows)) {
                if($rs['express'] == '1'){
                    $rs['express'] = "顺丰";
                }else if($rs['express'] == '2'){
                    $rs['express'] = "EMS";
                }

                if($rs['verifysta'] == 'Y'){
                    $rs['verifysta'] = "通过";
                }else if($rs['verifysta'] == 'N'){
                    $rs['verifysta'] = "未通过";
                }else{
                    $rs['verifysta'] = "未审核";
                }

                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A'.$i, $rs['time'])
                        ->setCellValue('B'.$i, $rs['user'])
                        ->setCellValue('C'.$i, $rs['user_group'])
                        ->setCellValue('D'.$i, $rs['client_name'])
                        ->setCellValue('E'.$i, $rs['phone_number'])
                        ->setCellValue('F'.$i, $rs['prov'].$rs['city'].$rs['dist']." ".$rs['address'])
                        ->setCellValue('G'.$i, $rs['pro_count'])
                        ->setCellValue('H'.$i, $rs['pro_color'])
                        ->setCellValue('I'.$i, $rs['express'])
                        ->setCellValue('J'.$i, $rs['verifysta']);

                $i++;
            }
        }
        

        $objPHPExcel->getActiveSheet()->setTitle('订单信息'); // Excel 名

        $objPHPExcel->setActiveSheetIndex(0);

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="订单信息'.date("Y-m-d").'.xlsx"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');