How to export an excel file from mysql in php _ PHP Tutorial

How to export an excel file from mysql in php. This article introduces two methods to export data from a mysql database to an excel document. for details, see. We use php + mysql directly in the simplest way. This article introduces two methods to export data from the mysql database into an excel document. for details, see.

We implemented it using php + mysql in the simplest way. The method is as follows.

The code is as follows:
Include ('Db/db. php'); // contains the database connection class $ 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. The problem with this method is that the download function cannot be implemented, 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,

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', '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 the title to force the user to download the object 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 '); } } ?>

Check the configuration method.

1) decompress the contents in the Classes folder of the compressed package to the applicationlibraries Directory. the directory structure is as follows:

-- ApplicationlibrariesPHPExcel. php

-- ApplicationlibrariesPHPExcel (folder)

2) modify the applicationlibrariesPHPExcelIOFactory. php file

-- Change the class name from PHPExcel_IOFactory to IOFactory and follow the CI class naming rules.

-- Change the constructor to public.

Many other methods like this are preferred, because the phpexcel plug-in is very practical and easy to operate on excel tables.

Bytes. We use php + mysql directly in the simplest way ,...