How to export excel file from mysql in php_PHP tutorial

The article introduces two methods to export data in the mysql database into excel documents. You can refer to each for its own merits.

We use the simplest method to implement it directly using php+mysql. The method is as follows.

代码如下	复制代码
include('db/db.php'); //包含连库类 $db = new db(); $result = mysql_query('select * from market_sig into outfile "d:product3.xls";'); var_dump($result); ?>

The above is our native PHP combined with the mysql outfile file export method. One problem with this method is that it cannot implement the download function and is only generated on the server.

The following method is more comprehensive

Download PHPExcel: http://phpexcel.codeplex.com

Let’s take a look at the code first,

// $query = mb_convert_encoding("gb2312", "UTF -8", $query);if(!$query)

The code is as follows

代码如下

复制代码

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'); $objWriter->save('php://output'); } } ?>

Copy code

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");

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', 'Chinese 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');//Send title to force user Download fileheader('Content-Type: application/vnd.ms-excel');header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls "');header('Cache-Control: max-age=0');$objWriter->save('php://output');}}?> Let’s take a look at the configuration method1) Unzip the Classes folder in the compressed package The contents in the applicationlibraries directory, the directory structure is as follows: -- applicationlibrariesPHPExcel.php-- applicationlibrariesPHPExcel (folder)2) Modify the applicationlibrariesPHPExcelIOFactory.php file-- Change its class name from PHPExcel_IOFactory to IOFactory to follow the CI class naming rules. --Change its constructor to publicThere are many methods like this method that I like to use, because the phpexcel plug-in is very practical and easy to operate excel tables. http://www.bkjia.com/PHPjc/444693.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/444693.htmlTechArticleThe article introduces two methods to export the data in the mysql database into an excel document. Each has its own strengths. Please refer to it. We use the simplest method to implement it directly using php+mysql,...