PHP upload Excel file import data to MySQL database

This article mainly introduces the PHP upload Excel file import data to the MySQL database example, you can write Excel data into the MySQL database, interested students can understand.

Recently in doing Excel file import data to the database. Website if you want to support bulk insert data, you can make an upload Excel file, import the contents of the data inside the MySQL database applet.

Tools to use:

Thinkphp: A lightweight homegrown PHP development framework. Can be downloaded from thinkphp website.

Phpexcel:office a PHP class library for Excel documents, based on Microsoft's OPENXML Standard and PHP language. Can be downloaded from CodePlex website. 、

1. Design MySQL Database product

Create the Product database

CREATE DATABASE product DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

Create a Pro_info table, table structure

CREATE TABLE pro_info (pId int (4) not NULL PRIMARY KEY auto_increment,pname varchar (no null,pprice float not Null,pcou NT float not NULL);

2. Build the Project

Create the project home by creating a new index.php file in the thinkphp sibling directory first.

<?php define (' app_name ', ' Home ');  Project name define (' App_path ', './home/'); Project path define (' App_debug ', true);   Open Debugrequire './thinkphp/thinkphp.php ';  Introduction of thinkphp core operating files?>

3. Uploading a file form

Create a new index folder under the Home/tpl folder, and create a new index.html file

<! DOCTYPE html> 
4. In/home/lib/action/indexaction.class.php, write the Show upload form page, upload Excel file, import Excel file Method (if there is no expansion package under Thinkphp/extend, Need to download the thinkphp website, then unzip the extension pack into it)

<?php/**** Import Excel file data to MySQL database */class indexaction extends Action {/** * display upload Form HTML page */Public Function Index  () {$this->display ();    }/** * Upload Excel File */Public function upload () {//Introduce thinkphp Upload file class import (' ORG.Net.UploadFile ');    Instantiate the Upload class $upload = new UploadFile ();    Set attachment upload file size 200Kib $upload->mixsize = 2000000;    Set the attachment upload type $upload->allowexts = array (' xls ', ' xlsx ', ' csv ');    Set the attachment upload directory under/home/temp $upload->savepath = './home/temp/';    Keep the upload file name unchanged $upload->saverule = ";    exists if the file with the same name is overwritten $upload->uploadreplace = true;    if (! $upload->upload ()) {//If the upload fails, prompt for error message $this->error ($upload->geterrormsg ());      } else {//upload succeeded//Get upload file information $info = $upload->getuploadfileinfo ();      Get upload Save file name $fileName = $info [0][' Savename ']; Redirect, pass the $filename file name to the Importexcel () method $this->redirect (' Index/importexcel ', array (' filename ' = = $fileName), 1, ' Upload success!    '); }}/** * * Import Excel file  */Public Function Importexcel () {Header ("content-type:text/html;charset=utf-8");    Introduction of Phpexcel Class vendor (' Phpexcel ');    Vendor (' phpexcel.iofactory ');     Vendor (' PHPExcel.Reader.Excel5 ');     REDIRECT file name $fileName = $_get[' filename '); File path $filePath = './home/temp/'. $fileName.    '. xlsx ';    Instantiate the Phpexcel class $PHPExcel = new Phpexcel ();    By default read Excel with excel2007, if the format is not correct, then use the previous version to read $PHPReader = new phpexcel_reader_excel2007 ();      if (! $PHPReader->canread ($filePath)) {$PHPReader = new Phpexcel_reader_excel5 ();        if (! $PHPReader->canread ($filePath)) {echo ' no Excel ';      Return    }}//read Excel file $PHPExcel = $PHPReader->load ($filePath);    Reads the first worksheet in an Excel file $sheet = $PHPExcel->getsheet (0);    Get the maximum column number $allColumn = $sheet->gethighestcolumn ();    Get the maximum line number $allRow = $sheet->gethighestrow (); Insert from the second line, the first row is the column name for ($currentRow = 2; $currentRow <= $allRow; $currentRow + +) {//Gets the value of column B $nAme = $PHPExcel->getactivesheet ()->getcell ("B". $currentRow)->getvalue ();      Gets the value of column c $price = $PHPExcel->getactivesheet ()->getcell ("C". $currentRow)->getvalue ();       Gets the value of the D column $count = $PHPExcel->getactivesheet ()->getcell ("D". $currentRow)->getvalue ();      $m = m (' Info ');    $num = $m->add (' pName ' + $name, ' pprice ' = $price, ' pcount ' + $count)); } if ($num > 0) {echo "added successfully!)    "; } else {echo ' Add failed!    "; }}}?>
5. Testing