PHP upload Excel file import data to MySQL database example _php instance

Recently in making Excel file import data to database. Web site if you want to support the bulk insert data, you can make a upload Excel file, import the contents of the data to the MySQL database applet.

Tools to use:

thinkphp: Lightweight domestic PHP development framework. can be downloaded at thinkphp official website.

Phpexcel:office is a PHP class library of Excel documents based on Microsoft's OPENXML Standard and PHP language. can be downloaded at CodePlex official website. 、

1. Design MySQL Database product

Creating the Product Database

CREATE DATABASE product DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

Create Pro_info table, table structure

CREATE TABLE pro_info (
pId Int (4) NOT null PRIMARY KEY auto_increment,
pname varchar is not NULL,
Pprice Floa T not null,
pcount float not null
);

2. Build Project

Create the project home by creating a new index.php file at the thinkphp sibling.

<?php
 
define (' app_name ', ' home ');  Project name
define (' App_path ', './home/');//project path
define (' App_debug ', true);   Turn on debug
require './thinkphp/thinkphp.php ';  Introduction of thinkphp core run files
?>

3. Uploading file Form

New index folder under Home/tpl folder, new index.html file inside

<! DOCTYPE html>
 
 
 

4. Write Display upload form page in/home/lib/action/indexaction.class.php, upload Excel file, import Excel file Method (if Thinkphp/extend does not have expansion pack, Need to download the thinkphp website, and then unzip the expansion pack into the)

<?php/** * * * Import Excel file data to MySQL Database/class Indexaction extends Action {/** * show upload Form HTML page/public FU
  Nction index () {$this->display ();
    /** * Upload Excel File * */Public Function upload () {///import thinkphp Upload file Class (' ORG.Net.UploadFile ');
    Instantiate the Upload class $upload = new UploadFile ();
    Set attachment upload file size 200Kib $upload->mixsize = 2000000;
    Set attachment upload type $upload->allowexts = array (' xls ', ' xlsx ', ' csv ');
    Set the attachment upload directory under/home/temp $upload->savepath = './home/temp/';
    Keep the uploaded filename unchanged $upload->saverule = ';
    Whether a file with the same name is overwritten $upload->uploadreplace = true;
    if (! $upload->upload ()) {//If upload fails, prompt for error message $this->error ($upload->geterrormsg ());
      else {//Upload success//Get upload file information $info = $upload->getuploadfileinfo ();
      Get upload save filename $fileName = $info [0][' Savename ']; Redirect, pass $filename filename to 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 the filename $fileName = $_get[' filename ']; File path $filePath = './home/temp/'. $fileName.
    '. xlsx ';
    Instantiate the Phpexcel class $PHPExcel = new Phpexcel ();
    The default is to read Excel with excel2007, if the format is not correct, then use the previous version of the Read $PHPReader = new phpexcel_reader_excel2007 ();
      if (! $PHPReader->canread ($filePath)) {$PHPReader = new Phpexcel_reader_excel5 ();
        if (! $PHPReader->canread ($filePath)) {echo ' no Excel ';
      Return
    Read the Excel file $PHPExcel =//$PHPReader->load ($filePath);
    Read the first worksheet in the Excel file $sheet = $PHPExcel->getsheet (0);
    Get the largest column number $allColumn = $sheet->gethighestcolumn ();
    Get the largest line number $allRow = $sheet->gethighestrow (); Inserts from the second line, the first row is the column name for($currentRow = 2; $currentRow <= $allRow; $currentRow + +)
      {//Get 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 column D $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 ' added failed!
    "; }}}?>

5. Test

Thank you for reading, I hope to help you, thank you for your support for this site!