How to import Excel data into a database and export it to Excel using PHP

Source: Internet
Author: User

How to import Excel data into a database and export it to Excel using PHP

This example describes how to import Excel data into the database and export the database data to Excel in PHP. Share it with you for your reference. The specific implementation method is as follows:

1. Import

To import data, you must use a component that can read Excel. There are also good components on the Internet. Here, I will share my use: Download extraction code: vxyn. (Note that there is a reference relationship between the two files)

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

<? Php

// Input the file name of the Excel file to be imported

Function import_to_DB ($ filename ){

Require_once 'reader. php ';

$ Data = new Spreadsheet_Excel_Reader ();

// Create an object for reading Excel

$ Data-> setOutputEncoding ('utf-8 ');

// Set the character encoding output after reading the Excel content

$ Data-> read ("data/Excel/{$filename}.xls ");

$ Db = mysql_connect ('localhost', 'username', 'Password') or die ("cocould not connect to database .");

// Connect to the database

Mysql_query ("set names 'uft8 '");

// Output Chinese Characters

Mysql_select_db ('database name ');

// Select a 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];

// Insert the values of corresponding columns in each row to the database, for example:

/*

$ SQL = "insert" table name "values (corresponding item ...)";

Mysql_query ($ SQL );

Error judgment can be added

*/

}

?>

In short, you can read the value of the corresponding column $ data-> Sheet [0] [row] [column] in each row in the table, and the insert operation is easy.

Ii. Export

You can use the MIME protocol to easily export table files without relying on any components. Set the header in the following format to export the Excel file and download it in the browser.

?

1

2

3

4

Header ('content-type: text/html; charset = UTF-8 ');

Header ("Content-type: application/vnd. ms-excel; charset = UTF-8"); // application/vnd. ms-excel specifies the output Excel format

Header ("Content-Disposition: filename1_table file name.xls ");

// Output table name

The complete code is as follows:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

<? Php

Header ('content-type: text/html; charset = UTF-8 ');

Header ("Content-type: application/vnd. ms-excel; charsets = UTF-8 ");

Header ("Content-Disposition: filename1_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 "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 ";

}

?>

Here \ t is actually a change, \ n is a line feed. Set the PHP file link in a webpage. When you click it, the browser automatically saves the uploaded stream as an Excel file.

I hope this article will help you with php programming.

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.