Use phpExcel to import and export Excel Data (detailed analysis in full steps)

This routine is used in the development framework of Thinkphp. If it is used in other frameworks, many people may be able to correctly Import and Export Excel, the problem is basically the core class of phpExcel.

This routine is used in the development framework of Thinkphp. If it is used in other frameworks, many people may be able to correctly Import and Export Excel, the problem is basically the core class of phpExcel.

Many articles have mentioned that most of the articles that use phpExcel to import and export Excel Data are similar or reprinted, and some problems may occur, the following describes how to use phpExcel.

First of all, I use this routine on the Thinkphp development framework. If I use the same method in other frameworks, can many people correctly Import and Export Excel files, the problem is basically caused by an error in the Reference Path of the phpExcel core class. If there is a problem, you must check whether the reference of the path is correct.

(1) Import Excel

First, upload files on the front-end html page: for example:

The Code is as follows:

Second, process the corresponding PHP File

The Code is as follows:

If (! Empty ($ _ FILES ['file _ stu'] ['name'])

{
$ Tmp_file = $ _ FILES ['file _ stu'] ['tmp _ name'];
$ File_types = explode (".", $ _ FILES ['file _ stu'] ['name']);
$ File_type = $ file_types [count ($ file_types)-1];

/* Determine whether the file is an excel file */
If (strtolower ($ file_type )! = "Xls ")
{
$ This-> error ('not an Excel file, re-upload ');
}

/* Set the upload path */
$ SavePath = SITE_PATH. '/public/upfile/Excel /';

/* Name the uploaded file by time */
$ Str = date ('ymdhis ');
$ File_name = $ str. ".". $ file_type;

/* Whether the upload is successful */
If (! Copy ($ tmp_file, $ savePath. $ file_name ))
{
$ This-> error ('upload failed ');
}

/*

* Process uploaded Excel data to generate programming data. This function will be used in the ExcelToArray class in step 3 below.

Note: here, the read function in step 3 is called and executed to convert Excel into an array and return it to $ res. Then, the database is written.

*/
$ Res = Service ('exceltoarray')-> read ($ savePath. $ file_name );

/*

Important code solves the problem that Thinkphp M and D methods cannot be called

If M and D methods fail in thinkphp, add the following code:

*/
// Spl_autoload_register (array ('think', 'autoload '));

/* Write the database to the generated array */
Foreach ($ res as $ k => $ v)
{
If ($ k! = 0)
{
$ Data ['uid'] = $ v [0];
$ Data ['Password'] = sha1 ('000000 ');
$ Data ['email '] = $ v [1];

$ Data ['uname'] = $ v [3];

$ Data ['center'] = $ v [4];
$ Result = M ('user')-> add ($ data );
If (! $ Result)
{
$ This-> error ('database import failed ');
}
}
}

}

Third: ExcelToArrary class, used to reference phpExcel and process Excel Data

The Code is as follows:

Class ExcelToArrary extends Service {

Public function _ construct (){

/* Import the phpExcel core class. Note: If your path is different from mine, you cannot directly copy it */
Include_once ('./Excel/PHPExcel. php ');
}

/**

* Read excel $ filename Path file name $ encode the returned data encoding is utf8 by default

* Do not modify the following basic information:

*/

Public function read ($ filename, $ encode = 'utf-8 '){

$ ObjReader = PHPExcel_IOFactory: createReader ('excel5 ');

$ ObjReader-> setReadDataOnly (true );

$ ObjPHPExcel = $ objReader-> load ($ filename );

$ ObjWorksheet = $ objPHPExcel-> getActiveSheet ();

$ HighestRow = $ objWorksheet-> getHighestRow ();
$ HighestColumn = $ objWorksheet-> getHighestColumn ();
$ HighestColumnIndex = PHPExcel_Cell: columnIndexFromString ($ highestColumn );
$ ExcelData = array ();
For ($ row = 1; $ row <= $ highestRow; $ row ++ ){
For ($ col = 0; $ col <$ highestColumnIndex; $ col ++ ){
$ ExcelData [$ row] [] = (string) $ objWorksheet-> getCellByColumnAndRow ($ col, $ row)-> getValue ();
}
}
Return $ excelData;

}

}

Fourth, the above is all the imported content, and the phpExcel package is appended to the end.

(2) Exporting Excel (easier than importing)

First, find the data in the database that needs to generate Excel, such:

$ Data = M ('user')-> findAll (); // locate the data
$ Name = 'excel file'; // generated Excel file name
$ Res = service ('exceltoarrary ')-> push ($ data, $ name );

Second, the ExcelToArrary class is used to reference phpExcel and process data.

The Code is as follows: