Use PHP to import Excel files and export data as Excel files

Source: Internet
Author: User
When using PHP to import Excel files and export data into Excel files, sometimes you need to import the data in the Excel tables to the mysql database. we can use PHP's open-source project PHP-ExcelReader to easily import Excel files.

1. import XLS

PHP-ExcelReader is an open-source project mainly used to parse excel files. you can obtain the latest source code from http://sourceforge.net/projects/phpexcelreader. After downloading and decompressing the files, we mainly use the two files reader. php and oleread. inc in the excel folder.

Import the Xls file: Select the xls file> upload the xls file to the server> use PHP-ExcelReader to parse the excel file> import the file in batches.

Include_once ("excel/reader. php "); // introduce 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 "; // save path and name of the uploaded file if (copy ($ tmp, $ file_name) {$ xls = new Spreadsheet_Excel_Reader (); $ xls-> setOutputEncoding ('utf-8'); // Set the encoding $ xls-> read ($ file_name); // Parse the 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]; $ dat A_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"); // insert data tables in batches if ($ query) {echo 'import successful! ';} Else {echo' import failed! ';}}

PHP-ExcelReader reads the uploaded excel file and returns an array containing all information about the table. you can obtain the required information cyclically.

2. export XLS

Export the XLS process: Read the student information table-> Create a tab-separated field information loop record-> set the header information-> export a file (download) to a 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, on the import and export Excel, you can also use PHPExcel, this is very powerful, you can go to study, official website: http://www.codeplex.com/PHPExcel import and export have become, you can export the office2007 format, at the same time 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.