ThinkPHP Method for importing Excel files based on PHPExcel _ php instance

This article mainly introduces ThinkPHP's method of importing Excel files based on PHPExcel. It gives a detailed description of the uploading, reading, and writing of Excel files to the database, it is of practical value in the process of project development. If you need it, you can refer to the example in this article to describe how ThinkPHP imports Excel files based on PHPExcel. Share it with you for your reference. The specific method is as follows:

The main knowledge point is to use PHPExcel to import Excel Data. After these days of testing, you can still use xls or xlsx to obtain Excel Data.
: Http://phpexcel.codeplex.com/

Development ideas:

1. Upload the Excel file to the server first.

2. Obtain the Excel file content on the server

3. Write data to the database

1. upload an Excel file, Using the built-in Upload method "\ Think \ Upload ();" in PHP, you can easily implement it. For this reason, I sorted out the simplest way to use this method.

The Code is as follows:

/**
* TODO File Upload Method
* @ Param $ fileid form file name value
* @ Param $ dir upload to the $ dir folder in the uploads directory
* @ Param int $ maxsize maximum upload limit. The default value is 1024000 bytes.
* @ Param array $ exts: the default object type that can be uploaded is array ('gif', 'jpg ', 'jpeg', 'bmp ', and 'png ')
* @ Return array returns array, failure status = 0 success status = 1, filepath = newspost/2014-9-9/a.jpg
*/
Function uploadfile ($ fileid, $ dir, $ maxsize = 5242880, $ exts = array ('gif', 'jpg ', 'jpeg', 'bmp ', 'png '), $ maxwidth = 430 ){
$ Upload = new \ Think \ Upload (); // instantiate the upload class
$ Upload-> maxSize = $ maxsize; // sets the attachment upload size, in bytes (the image size is 1 MB.
$ Upload-> exts = $ exts; // you can specify the attachment upload type.
$ Upload-> rootPath = './uploads/'; // you can specify the root directory for uploading attachments.
$ Upload-> savePath = $ dir. '/'; // sets the attachment upload (sub) directory.
// Upload a file
$ Info = $ upload-> upload ();

If (! $ Info) {// upload error message
Return array (status => 0, msg => $ upload-> getError ());
} Else {// upload successful
Return array (status => 1, msg => 'upload succeeded ', filepath => $ info [$ fileid] ['savepath']. $ info [$ fileid] ['savename']);
}
}

The index file is uploaded to the ThinkPHP portal by default. in the uploads folder where php is located, this method returns a data. status = 1 is successful. We also recommend that you write the function module or encapsulate it, the entire system should have an agreement at the beginning of the architecture. If necessary, the return value should be in the form of an array, and the return value will be returned successfully.

The Code is as follows:

Return array (status => 1, data =>..., info => .....)

Can be returned if the failure occurs.

The Code is as follows:

Array (status-> 0, info => 'can indicate the cause of the error ',....)

In this way, the unified method is conducive to standardized development. When you look at the code during team collaboration, you can improve efficiency and reduce the running of thinking. To put it far away, the upload method is called as follows:

The Code is as follows:

// Excel File
If (! Empty ($ _ FILES ['xls '] ['name']) {
$ Upload = uploadfile ('xls ', 'tempxls', 5242880, array ('xls ', 'xlsx '));
If ($ upload ['status']) {
$ Path = $ upload ['filepath'];
} Else {
$ This-> error ($ upload ['msg ']);
}
}

2. Obtain Excel Data

1. first introduce the PHPExcel class library

The Code is as follows:

Require_once 'module/PHPExcel/Classes/PHPExcel/IOFactory. php ';

2. Obtain 0th Excel tables (Sheet1)

The Code is as follows:

// Obtain an excel file
$ ObjPHPExcel = \ PHPExcel_IOFactory: load ("uploads/$ path ");
$ ObjPHPExcel-> setActiveSheetIndex (0 );
$ Sheet0 = $ objPHPExcel-> getSheet (0 );

3. Get the number of rows and read the data $ data array

The Code is as follows:

$ RowCount = $ sheet0-> getHighestRow (); // Number of excel rows
$ Data = array ();
For ($ I = 2; $ I <= $ rowCount; $ I ++ ){
$ Item ['name'] = $ this-> getExcelValue ($ sheet0, 'A'. $ I );
$ Item ['sex'] = $ this-> getExcelValue ($ sheet0, 'B'. $ I );
$ Item ['Contact '] = $ this-> getExcelValue ($ sheet0, 'c'. $ I );
$ Item ['remark'] = $ this-> getExcelValue ($ sheet0, 'D'. $ I );
$ Item ['addtime'] = $ this-> getExcelValue ($ sheet0, 'E'. $ I );

$ Data [] = $ item;
}

3. Save it to the database

The Code is as follows:

$ Success = 0;
$ Error = 0;
$ Sum = count ($ data );
Foreach ($ data as $ k => $ v ){
If (M ('temp _ area3 ')-> data ($ v)-> add ()){
$ Success ++;
} Else {
$ Error ++;
}
}

Echo "Total {$ sum} records, successful {$ success} records, failed {$ error} records. ";

This is a success! I hope this article will help you with ThinkPHP framework programming.