Import Excel and Export data to Excel files using PHP

Sometimes it is necessary to import the data of Excel table into MySQL database, we can easily implement the import of Excel by using an open source project of PHP Php-excelreader.

1. Import xls

Php-excelreader This is an open source project, mainly to parse Excel files, you can go to Http://sourceforge.net/projects/phpexcelreader to get the latest version of the source code. After downloading, the main use of the Excel folder inside the two files reader.php and oleread.inc.

Import XLS processing flow: Select xls file, upload xls file to server, and resolve excel-> batch storage by Php-excelreader.

Include_once ("excel/reader.php"); Introduced php-excelreader$tmp = $_files[' file ' [' Tmp_name '];if (Empty ($tmp)) {echo ' Select the Excel file to import! '; exit;} $save _path = "xls/"; $file _name = $save _path.date (' Ymdhis '). ". xls"; The uploaded file saves the path and name if (copy ($tmp, $file _name)) {$xls = new Spreadsheet_excel_reader (); $xls->setoutputencoding (' Utf-8 ' );  Set the encoding $xls->read ($file _name);  Parse file for ($i =2; $i <= $xls->sheets[0][' numrows '); $i + +) {$name = $xls->sheets[0][' cells '] [$i][0]; $sex = $xls- >sheets[0][' cells ' [$i][1]; $age = $xls->sheets[0][' cells '] [$i][2]; $data _values. = "(' $name ', ' $sex ', ' $age ')," ;} $data _values = substr ($data _values,0,-1); Remove the last comma $query = mysql_query ("INSERT into student (name,sex,age) values $data _values");//BULK INSERT in Datasheet if ($query) {echo ' Import Successful! ';} Else{echo ' Import failed! ';}}

After reading the uploaded Excel file, Php-excelreader returns an array containing all the information from the table, which you can loop through to get the information you need.

2. Export XLS

Export XLS process: Read the Student information table, looping record building tab-delimited field information, setting header information, export file (download) to local

$result = mysql_query ("SELECT * from Student"), $str = "name \ t gender \ t age \t\n"; $str = Iconv (' utf-8 ', ' gb2312 ', $str); while ($row = Mysql_fetch_array ($result)) {    $name = iconv (' utf-8 ', ' gb2312 ', $row [' name ']);    $sex = Iconv (' utf-8 ', ' gb2312 ', $row [' sex ']);    $str. = $name. " \ t ". $sex." \ t ". $row [' age ']." \t\n ";} $filename = Date (' Ymd '). XLS '; Exportexcel ($filename, $STR);

The Exportexcel function is used to set header information.

function Exportexcel ($filename, $content) {header ("cache-control:must-revalidate, Post-check=0, pre-check=0"); Header ("Content-type:application/vnd.ms-execl"); Header ("Content-type:application/force-download"); Header (" Content-type:application/download ");    Header ("content-disposition:attachment; Filename= ". $filename);    Header ("Content-transfer-encoding:binary");    Header ("Pragma:no-cache");    Header ("expires:0");    echo $content;}

In addition, about importing and exporting Excel, you can also use Phpexcel, this is very powerful, we can go to research, the official website: Http://www.codeplex.com/PHPExcel Import and export, you can export office2007 format, also compatible with 2003

