search

Home  >  Article  >  Backend Development  >  How to export excel using php

How to export excel using php

王林
王林Original
2019-10-14 11:49:206374browse

How to export excel using php

Method 1: Directly use the header information to output the excel format file, and the content is displayed in table form

//直接用头部信息输出excel格式文件，内容以表格形式展示。
 
$filename='orderexcel';
    header("Content-type: application/vnd.ms-excel; charset=gbk");
    header("Content-Disposition: attachment; filename=$filename.xls");
//$list为数据库查询结果，既二维数组。利用循环出表格，直接输出，既在线生成execl文件
 foreach($list as $key => $val)
    {
        $data .= "<table border=&#39;1&#39;>";
        $data .= "<tr><td colspan=&#39;2&#39;>订单号：".$val[&#39;order_sn&#39;].
        "</td><td>用户名：".$val[&#39;user_name&#39;]."</td><td colspan=&#39;2&#39;>收货人：".
        $val[&#39;consignee&#39;]."</td><td colspan=&#39;2&#39;>联系电话：".$val[&#39;tel&#39;]."</td></tr>";
        $data .= "<tr><td colspan=&#39;5&#39;>送货地址：".$val[&#39;address&#39;].
        "</td><td colspan=&#39;2&#39;>下单时间：".$val[&#39;add_time&#39;]."</td></tr>";
        $data .= "<tr bgcolor=&#39;#999999&#39;><th>序号</th><th>货号</th><th>商品名称</th><th>市场价</th>
        <th>本店价</th><th>购买数量</th><th>小计</th></tr>";
        $data .= "<tr><th>1</th><th>".$val[&#39;goods_sn&#39;]."</th><th>".$val[&#39;goods_name&#39;]."</th><th>".
        $val[&#39;market_price&#39;]."</th><th>".$val[&#39;goods_price&#39;]."</th><th>".$val[&#39;goods_number&#39;]."</th><th>".
        $val[&#39;money&#39;]."</th></tr>";
        $data .= "</table>";
        $data .= "<br>";
}
$data.=&#39;</table>&#39;;
    if (EC_CHARSET != &#39;gbk&#39;)
    {
        echo yzy_iconv(EC_CHARSET, &#39;gbk&#39;, $data) . "\t";
    }
    else
    {
        echo $data. "\t";
    }

Method 2: Use excel export plug-in PHPExcel

//利用excel导出插件PHPExcel
 // 引入phpexcel核心类文件
    require_once ROOT_PATH . &#39;/includes/phpexcel/Classes/PHPExcel.php&#39;;
    // 实例化excel类
    $objPHPExcel = new PHPExcel();
    // 操作第一个工作表
    $objPHPExcel->setActiveSheetIndex(0);
    // 设置sheet名
    $objPHPExcel->getActiveSheet()->setTitle(&#39;xx列表&#39;);
 
    // 设置表格宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;A&#39;)->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setWidth(50);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;F&#39;)->setWidth(5);
    $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;G&#39;)->setWidth(20);
 
 
    // 列名表头文字加粗
    $objPHPExcel->getActiveSheet()->getStyle(&#39;A1:J1&#39;)->getFont()->setBold(true);
    // 列表头文字居中
    $objPHPExcel->getActiveSheet()->getStyle(&#39;A1:J1&#39;)->getAlignment()
        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
    // 列名赋值
    $objPHPExcel->getActiveSheet()->setCellValue(&#39;A1&#39;, &#39;编号&#39;);
    $objPHPExcel->getActiveSheet()->setCellValue(&#39;B1&#39;, &#39;姓名&#39;);
    $objPHPExcel->getActiveSheet()->setCellValue(&#39;C1&#39;, &#39;电话&#39;);
    $objPHPExcel->getActiveSheet()->setCellValue(&#39;D1&#39;, &#39;擅长&#39;);
    $objPHPExcel->getActiveSheet()->setCellValue(&#39;E1&#39;, &#39;创建日期&#39;);
    $objPHPExcel->getActiveSheet()->setCellValue(&#39;F1&#39;, &#39;审核&#39;);
    $objPHPExcel->getActiveSheet()->setCellValue(&#39;G1&#39;, &#39;审核时间&#39;);
 
    // 数据起始行
    $row_num = 2;
    // 向每行单元格插入数据
    foreach($res as $value)
    {
        // 设置所有垂直居中
        $objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39; . $row_num . &#39;:&#39; . &#39;J&#39; . $row_num)->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 设置价格为数字格式
        $objPHPExcel->getActiveSheet()->getStyle(&#39;D&#39; . $row_num)->getNumberFormat()
            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
        // 居中
        $objPHPExcel->getActiveSheet()->getStyle(&#39;E&#39; . $row_num . &#39;:&#39; . &#39;H&#39; . $row_num)->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
        // 设置单元格数值
        $objPHPExcel->getActiveSheet()->setCellValue(&#39;A&#39; . $row_num, $value[&#39;id&#39;]);
        $objPHPExcel->getActiveSheet()->setCellValue(&#39;B&#39; . $row_num, $value[&#39;teacher_name&#39;]);
        $objPHPExcel->getActiveSheet()->setCellValue(&#39;C&#39; . $row_num, $value[&#39;teacher_mobile&#39;]);
        $objPHPExcel->getActiveSheet()->setCellValue(&#39;D&#39; . $row_num, $value[&#39;teacher_desc&#39;]);
        $objPHPExcel->getActiveSheet()->setCellValue(&#39;E&#39; . $row_num, date(&#39;Y-m-d h:i:s&#39;,$value[&#39;createtime&#39;]));
        $objPHPExcel->getActiveSheet()->setCellValue(&#39;F&#39; . $row_num, $value[&#39;state&#39;] ? &#39;√&#39; : &#39;×&#39;);
        $objPHPExcel->getActiveSheet()->setCellValue(&#39;G&#39; . $row_num, date(&#39;Y-m-d h:i:s&#39;,$value[&#39;statetime&#39;]));
        $row_num++;
    }
 
    $outputFileName = &#39;teacher_&#39; . time() . &#39;.xls&#39;;
    $xlsWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header(&#39;Content-Disposition:inline;filename="&#39; . $outputFileName . &#39;"&#39;);
    header("Content-Transfer-Encoding: binary");
    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Pragma: no-cache");
    $xlsWriter->save("php://output");
    echo file_get_contents($outputFileName);

Recommended tutorial: PHP video tutorial

The above is the detailed content of How to export excel using php. For more information, please follow other related articles on the PHP Chinese website!

php excel
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：How to deploy php to the serverNext article：How to deploy php to the server

Related articles

See more