PHP method to import Excel into database and export database data to Excel, excel data export

PHP method to import Excel into database and export database data to Excel, excel data export_PHP tutorial

WBOY

Release： 2016-07-13 09:48:51

Original

829 people have browsed it

How to import Excel into the database and export database data to Excel with PHP, and export excel data

This article describes the method of importing Excel into the database and exporting database data to Excel with PHP. Share it with everyone for your reference. The specific implementation method is as follows:

1. Import

Importing requires the use of components that can read Excel. There are also better components on the Internet. Here I share the ones I use: Download Extraction code: vxyn. (Note that the two files are referenced)

<&#63;php
//传入要导入的Excel的文件名
function import_to_DB($filename) {
  require_once'reader.php';
  $data = new Spreadsheet_Excel_Reader();
  //创建读取Excel的对象
  $data->setOutputEncoding('utf-8');
  //设置读取Excel内容后输出的字符编码
  $data->read("data/Excel/{$filename}.xls");
  $db = mysql_connect('localhost', '用户名', '密码') or die("Could not connect to database.");
  //连接数据库  
  mysql_query("set names 'uft8'");
  //输出中文  
  mysql_select_db('数据库名');
  //选择数据库  
  error_reporting(E_ALL ^ E_NOTICE);
  for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
    echo $data->sheets[0]['cells'][$i][列数];
    //这里可以把每一行相应列的值插到数据库中，如：
    /*
    $sql="insert "表名" values(对应项...)";
    mysql_query($sql);
    可加上错误判断
    */ 
  } 
&#63;>

Copy after login

In short, if you can read the value of the corresponding column $data->sheets[0][row][column] in each row of the table, the insertion operation will be easy.

2. Export

Export can easily export table files using the MIME protocol without relying on any components. Set the header in the following format to export to Excel, and the browser will download it at the same time

header('Content-type: text/html; charset=utf-8');
header("Content-type:application/vnd.ms-excel;charset=UTF-8"); //application/vnd.ms-excel指定输出Excel格式
header("Content-Disposition:filename=表格文件名.xls");
//输出的表格名称

Copy after login

The complete code is as follows:

<&#63;php 
header('Content-type: text/html; charset=utf-8');
header("Content-type:application/vnd.ms-excel;charset=UTF-8"); 
header("Content-Disposition:filename=表格文件名.xls");
$conn = mysql_connect("localhost","root","数据库密码") or die("不能连接数据库");
mysql_select_db("数据库名", $conn);
mysql_query("set names 'UTF-8'");
$sql="select * from 表名 where 条件";
$result=mysql_query($sql);
echo "表头1\t表头2\t表头3\n";
while($row=mysql_fetch_array($result)){
  echo $row[0]."\t".$row[1]."\t".$row[2]."\n";
}
&#63;>

Copy after login

Here, t is actually a format change, and n is a line break. Set the link to this php file in a web page, and when clicked, the browser will automatically save the passed stream as an Excel file.

I hope this article will be helpful to everyone’s PHP programming design.