PHP exports Excel Import Database and database data to Excel

one. Import

　　Import needs to use the components that can read Excel, there are better components on the net, here to share I use: Download extract code: Vxyn. (Note that two files have a citation relationship)

<?PHP//Pass in the file name of the Excel you want to importfunctionimport_to_db ($filename) {    require_once' Reader.php '; $data=NewSpreadsheet_excel_reader ();//create an object that reads Excel    $data->setoutputencoding (' Utf-8 ');//set character encoding for output after reading Excel content    $data->read ("data/excel/{$filename}.xls "); $db=mysql_connect(' localhost ', ' username ', ' password ') or die("Could not connect to database.");//connecting to a database    mysql_query("Set names ' Uft8 '");//Output Chinese    mysql_select_db(' database name ');//Select Database    error_reporting(E_all^E_notice);  for($i= 1;$i<=$data->sheets[0][' NumRows '];$i++) {        Echo $data->sheets[0][' cells ' [$i[number of columns]; //Here you can insert the values of the corresponding columns of each row into the database, such as:        /*$sql = "Insert" Table name "values (corresponding items ...)";        mysql_query ($sql); Can add a false judgment .*/     }  ?>

In short, you can read the values of the corresponding column $data->sheets[0][[column] in each row in the table, and the insert operation will be done.

two. Export

　　Export can easily export tabular files without relying on any components using the HTTP protocol. You can export Excel by setting the header in the following format, while the browser downloads

Header (' content-type:text/html; Charset=utf-8 '); Header // application/vnd.ms-excel specifying output Excel format Header // table name of the output

The complete code is as follows:

<?PHPHeader(' content-type:text/html; Charset=utf-8 ');Header("Content-type:application/vnd.ms-excel;charset=utf-8"); Header("content-disposition:filename= table file name. xls");$conn=mysql_connect("localhost", "root", "Database Password") or die("Cannot connect to database");mysql_select_db("Database name",$conn);mysql_query("Set names ' UTF-8 '");$sql= "SELECT * from table name where condition";$result=mysql_query($sql);Echo"Table header 1\t Header 2\t header 3\n"; while($row=Mysql_fetch_array($result)){    Echo $row[0]. " \ t ".$row[1]. " \ t ".$row[2]. " \ n ";}?>

In fact, \ t is a change of grid, \ nthe line. Set up a link to this PHP file in a Web page, and the browser will automatically save the streamed stream as an Excel file when clicked.

PHP exports Excel Import Database and database data to Excel