(Advanced) Use PHP to import Excel and export data to Excel files-PHP Tutorial-php.cn

(Advanced) Use PHP to import Excel and export data to Excel files

黄舟

Release： 2023-03-05 14:02:01

Original

1823 people have browsed it

Sometimes it is necessary to import Excel table data into a mysql database. We use PHP-ExcelReader, an open source project of PHP, to easily achieve Excel import.

1. Import XLS

PHP-ExcelReader This is an open source project, mainly used to parse excel files. You can go to http://sourceforge.net/projects/phpexcelreader to get the latest version of the source code. After downloading, unzip it and mainly use the two files reader.php and oleread.inc in the excel folder.

Import Xls processing process: Select the xls file->Upload the xls file to the server->Parse excel through PHP-ExcelReader->Batch storage.

include_once("excel/reader.php"); //引入PHP-ExcelReader 
$tmp = $_FILES[&#39;file&#39;][&#39;tmp_name&#39;]; 
if (empty ($tmp)) { 
    echo &#39;请选择要导入的Excel文件！&#39;; 
    exit; 
} 
     
$save_path = "xls/"; 
$file_name = $save_path.date(&#39;Ymdhis&#39;) . ".xls"; //上传后的文件保存路径和名称 
if (copy($tmp, $file_name)) { 
    $xls = new Spreadsheet_Excel_Reader(); 
    $xls->setOutputEncoding(&#39;utf-8&#39;);  //设置编码 
    $xls->read($file_name);  //解析文件 
    for ($i=2; $i<=$xls->sheets[0][&#39;numRows&#39;]; $i++) { 
        $name = $xls->sheets[0][&#39;cells&#39;][$i][0]; 
        $sex = $xls->sheets[0][&#39;cells&#39;][$i][1]; 
        $age = $xls->sheets[0][&#39;cells&#39;][$i][2]; 
        $data_values .= "(&#39;$name&#39;,&#39;$sex&#39;,&#39;$age&#39;),"; 
    } 
    $data_values = substr($data_values,0,-1); //去掉最后一个逗号 
    $query = mysql_query("insert into student (name,sex,age) values $data_values");//批量插入数据表中 
    if($query){ 
        echo &#39;导入成功！&#39;; 
    }else{ 
        echo &#39;导入失败！&#39;; 
    } 
}

Copy after login

After PHP-ExcelReader reads the uploaded excel file, it returns an array containing all the information of the table. You can loop to obtain the required information.

2. Export XLS

Export XLS process: read student information table-> loop record to build tab-delimited field information-> set header information-> export file ( Download) to local

$result = mysql_query("select * from student"); 
$str = "姓名\t性别\t年龄\t\n"; 
$str = iconv(&#39;utf-8&#39;,&#39;gb2312&#39;,$str); 
while($row=mysql_fetch_array($result)){ 
    $name = iconv(&#39;utf-8&#39;,&#39;gb2312&#39;,$row[&#39;name&#39;]); 
    $sex = iconv(&#39;utf-8&#39;,&#39;gb2312&#39;,$row[&#39;sex&#39;]); 
    $str .= $name."\t".$sex."\t".$row[&#39;age&#39;]."\t\n"; 
} 
$filename = date(&#39;Ymd&#39;).&#39;.xls&#39;; 
exportExcel($filename,$str);

Copy after login

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; 
}

Copy after login

In addition, for importing and exporting Excel, you can also use PHPExcel. This is very powerful. You can study it when you have time. The official website: http://www.codeplex.com/PHPExcel can import and export. Can export office2007 format, and is compatible with 2003

The above is (advanced) the content of using PHP to import Excel and export data as Excel files. For more related content, please pay attention to the PHP Chinese website (www.php.cn) !