php、mysql导出excel数据 | rin部落-web前端开发-主流web技术分享博客

//搜索
    $start_time = strtotime($start_date);
    $end_time = strtotime($end_date);
    $sql = "select a.*,b.order_amount,b.money_paid from ".$ecs->table('invoice')." as a ".
                " left join ".$ecs->table('order_info')." as b on a.order_id=b.order_sn".
                " where a.add_time >=".$start_time." and a.add_time <=".$end_time." ";
    $temp_list = $db->getAll($sql);

    if($temp_list){//有数据
        $Html='<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><body>'.chr(13).chr(10);
        $Html.='<table width="700" border="1" align="center" cellpadding="2" cellspacing="1">
                            <tr align="center">
                                    <td align="center" nowrap="nowrap">时间：</td>
                                    <td align="center" nowrap="nowrap" colspan="9">'.$start_date.'~'.$end_date.'</td>
                            </tr>
                            <tr align="center">
                                     <td align="center" nowrap="nowrap">编号</td>
                                     <td align="center" nowrap="nowrap">发票类型</td>
                                     <td align="center" nowrap="nowrap">发票抬头</td>
                                     <td align="center" nowrap="nowrap">发票内容</td>
                                     <td align="center" nowrap="nowrap">订单号</td>
                                     <td align="center" nowrap="nowrap">金额</td>
                                     <td align="center" nowrap="nowrap">添加日期</td>
                                     <td align="center" nowrap="nowrap">收件人</td>
                                     <td align="center" nowrap="nowrap">联系方式</td>
                                     <td align="center" nowrap="nowrap">地址</td>
                           </tr>';
            //取得符合条件的数组
            for($i=0;$i<count($temp_list);$i++){
                 $temp_i = $i+1;
                 if($temp_list[$i][order_amount]==0){
                     $temp_money = $temp_list[$i][money_paid];
                 }else{
                     $temp_money = $temp_list[$i][order_amount];
                 }

                 $temp_time = date('Y-m-d', $temp_list[$i]['add_time']);
                 $Html.='<tr align="center">
                                     <td align="center" nowrap="nowrap">'.$temp_i.'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][type_name].'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][top].'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][content].'</td>
                                     <td align="center" nowrap="nowrap" style="vnd.ms-excel.numberformat:@">'.$temp_list[$i][order_id].'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_money.'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_time.'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][user_name].'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][mobile].' '.$temp_list[$i][tel].' </td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][address].'</td>
                           </tr>';
             }
             $Html.='</table>';
             $Html.='</body></html>';
             $mime_type = 'application/vnd.ms-excel';
             header('Content-Type: ' . $mime_type);
             header('Content-Disposition: attachment; filename="invoice.xls"');
             header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
             header('Pragma: public');
            echo $Html;

有时excel会自动把数字转换格式，于是有些手机号码，身份证之类的就乱了，因此可以在导出时，先定义好

<td align="center" nowrap="nowrap" style="vnd.ms-excel.numberformat:@">'.$temp_list[$i][order_id].'</td>

2013-06-09