How to create or export Excel data table with PHP-PHP Tutorial-php.cn

How to create or export Excel data table with PHP

小云云

Release： 2023-03-22 13:36:01

Original

2651 people have browsed it

This article mainly shares with you how to create or export Excel data tables with PHP. It mainly shares with you a piece of code. I hope it can help you.

$filename = "order_".date(&#39;Y-m-d&#39;).".xls";  
        $header = array(&#39;订单编号&#39;,&#39;订单类型&#39;,&#39;会员ID&#39;,&#39;总加工费&#39;,&#39;商品总价&#39;,&#39;邮费&#39;,&#39;应付金额&#39;,&#39;订单状态&#39;,&#39;下单时间&#39;);  
        $index = array(&#39;order_sn&#39;,&#39;kind&#39;,&#39;mid&#39;,&#39;other_price&#39;,&#39;goods_price&#39;,&#39;shipping_price&#39;,&#39;order_amount&#39;,&#39;order_status&#39;,&#39;regtime&#39;);  
$$orderlist = M(&#39;table&#39;)->where($where)->order(&#39;id&#39;)->select();

create_xls($orderlist,$filename,$header,$index);


/**
* 数组转xls格式的excel文件
* @param  array   $data 要导出的数组格式的数据 
* @param  string  $filename 导出的Excel表格数据表的文件名 
* @param  array   $header Excel表格的表头 
* @param  array   $index $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值) 
* 比如: $header = array(&#39;编号&#39;,&#39;姓名&#39;,&#39;性别&#39;,&#39;年龄&#39;); 
*       $index = array(&#39;id&#39;,&#39;username&#39;,&#39;sex&#39;,&#39;age&#39;); 
*       $data = array(array(&#39;id&#39;=>1,&#39;username&#39;=>&#39;YQJ&#39;,&#39;sex&#39;=>&#39;男&#39;,&#39;age&#39;=>24)); 
*      示例数据：        $strexport = array(
            array(NULL, 2010, 2011, 2012),
            array(&#39;Q1&#39;,   12,   15,   21),
            array(&#39;Q2&#39;,   56,   73,   86),
            array(&#39;Q3&#39;,   52,   61,   69),
            array(&#39;Q4&#39;,   30,   32,    0),
           );
 */
function create_xls($data,$filename=&#39;simple.xls&#39;,$header,$indexKey){
    ini_set(&#39;max_execution_time&#39;, &#39;0&#39;);
    Vendor(&#39;PHPExcel.PHPExcel&#39;);    $filename=str_replace(&#39;.xls&#39;, &#39;&#39;, $filename).&#39;.xls&#39;;    $phpexcel = new PHPExcel();    $phpexcel->getProperties()
        ->setCreator("Maarten Balliauw")
        ->setLastModifiedBy("Maarten Balliauw")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
    //组合单元格的内容
    foreach ($data as $k=>$row) {  
        foreach ($indexKey as $key=>$value){  
            //这里是设置单元格的内容  
            $strexport[$k][$key]=$row[$value];  
        }  
    }  
    array_unshift($strexport,$header);    $phpexcel->getActiveSheet()->fromArray($strexport);    $phpexcel->getActiveSheet()->setTitle(&#39;Sheet1&#39;);    $phpexcel->setActiveSheetIndex(0);
    header(&#39;Content-Type: application/vnd.ms-excel&#39;);
    header("Content-Disposition: attachment;filename=$filename");
    header(&#39;Cache-Control: max-age=0&#39;);
    header(&#39;Cache-Control: max-age=1&#39;);
    header (&#39;Expires: Mon, 26 Jul 1997 05:00:00 GMT&#39;); // Date in the past
    header (&#39;Last-Modified: &#39;.gmdate(&#39;D, d M Y H:i:s&#39;).&#39; GMT&#39;); // always modified
    header (&#39;Cache-Control: cache, must-revalidate&#39;); // HTTP/1.1
    header (&#39;Pragma: public&#39;); // HTTP/1.0
    $objwriter = PHPExcel_IOFactory::createWriter($phpexcel, &#39;Excel5&#39;);    $objwriter->save(&#39;php://output&#39;);    exit;
}

Copy after login

Related recommendations:

Create Excel file with PHP

The above is the detailed content of How to create or export Excel data table with PHP. For more information, please follow other related articles on the PHP Chinese website!