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. This article is mainly to share with you the Excel file import data to MySQL database method instance, hope can help everyone.
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 defaultcharacter 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 (a) not null,pprice Floatnot Null,pcount Floatnot 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
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 */publicfunction Inde X () {$this->display (); }/** * Upload Excel file */publicfunction 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 successful//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 */PubliCfunction 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