Import Excel and Export data to Excel files using PHP

Source: Internet
Author: User
Tags bulk insert
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
  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.