Import Excel and Export data to Excel files using PHP

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");Introduction of Php-excelreader
$TMP =$_files[' File ' [' Tmp_name '];
if (Empty ($TMP)) {
Echo' Please select the Excel file you want to import! ‘;
Exit
}
 
$Save_path ="xls/";
$file_name =$Save_path.date (' Ymdhis ').". xls";File save path and name after upload
if (Copy ($Tmp$file_name)) {
$XLS =New Spreadsheet_excel_reader ();
$Xls->setoutputencoding (' Utf-8 ');Set encoding
$Xls->read ($file_name);Parsing files
 for ($I=2;$i<=$xls->sheets[1]:' NumRows '];$i++) {
$Name =$xls->sheets[1]:' Cells ' [$i][0];
$Sex =$xls->sheets[1]:' Cells ' [$i][1];
$Age =$xls->sheets[1]:' Cells ' [$i][2];
$Data_values. ="(' $name ', ' $sex ', ' $age '),";
}
$Data_values = substr ($Data_values,0,-1)  //remove the last comma   
     $query = mysql_query ( //BULK insert data table   
     if ($query) {  
         < Span class= "Php__keyword" >echo  ' Import succeeded! ';   
     }ELSE{&NBSP; 
          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 sex \ 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 (  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      Header (      header ( "content-disposition: attachment; filename=". $filename);   
     header (      header (      header (    
      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

Disclaimer: This article is original article, helloweba.com and author have copyright, if need to reprint, please indicate from helloweba.com and keep original link: http://www.helloweba.com/view-blog-172.html

Import Excel and Export data to Excel files using PHP