php 中mysql导出excel文件方法
我们用最简单的方法直接用php+mysql来实现了,代码如下:
<?php include ( 'db/db.php' ); //包含连库类 $db = new db(); $result = mysql_query( 'select * from market_sig into outfile "d:product3.xls";' ); var_dump( $result ); ?>上面是我们的原生php结合了mysql outfile文件导出方法,这种方法有个问题就是不能实现下载功能,只在生成在服务器上.
下面方法更全面:下载PHPExcel：http://phpexcel.codeplex.com
先来看看代码,代码如下:
<?php class Table_export extends CI_Controller { function __construct() { parent::__construct(); // Here you should add some sort of user validation // to prevent strangers from pulling your table data } function index( $table_name ) { $this ->load->database(); $query = $this ->db->query( "select * from `$table_name` WHERE del= 1" ); // $query = mb_convert_encoding("gb2312", "UTF-8", $query); if (! $query ) return false; // Starting the PHPExcel library $this ->load->library( 'PHPExcel' ); $this ->load->library( 'PHPExcel/IOFactory' ); $objPHPExcel = new PHPExcel(); $objPHPExcel ->getProperties()->setTitle( "export" )->setDescription( "none" ); $objPHPExcel ->setActiveSheetIndex(0) ->setCellValue( 'A1' , iconv( 'gbk' , 'utf-8' , '中文Hello' )) ->setCellValue( 'B2' , 'world!' ) ->setCellValue( 'C1' , 'Hello' ); // Field names in the first row $fields = $query ->list_fields(); $col = 0; foreach ( $fields as $field ) { $objPHPExcel ->getActiveSheet()->setCellValueByColumnAndRow( $col , 1, $field ); $col ++; } // Fetching the table data $row = 2; foreach ( $query ->result() as $data ) { $col = 0; foreach ( $fields as $field ) { $objPHPExcel ->getActiveSheet()->setCellValueByColumnAndRow( $col , $row , $data -> $field ); $col ++; } $row ++; } $objPHPExcel ->setActiveSheetIndex(0); $objWriter = IOFactory::createWriter( $objPHPExcel , 'Excel5' ); //发送标题强制用户下载文件 header( 'Content-Type: application/vnd.ms-excel' ); header( 'Content-Disposition: attachment;filename="Products_' . date ( 'dMy' ). '.xls"' ); header( 'Cache-Control: max-age=0' ); //开源代码phpfensi.com $objWriter ->save( 'php://output' ); } } ?>看看配置方法吧.
1) 解压压缩包里的Classes文件夹中的内容到applicationlibraries目录下,目录结构如下:
-- applicationlibrariesPHPExcel.php
-- applicationlibrariesPHPExcel(文件夹)
2) 修改applicationlibrariesPHPExcelIOFactory.php 文件
-- 将其类名从PHPExcel_IOFactory改为IOFactory,遵从CI类命名规则.
-- 将其构造函数改为public.
还有很多方法像这种方法多喜欢用,因为phpexcel这个插件很实用,对excel表格操作方便.
查看更多关于php 中mysql导出excel文件方法 - php高级应用的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did30275