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

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:
Copy codeThe Code is as follows:
<Form method = "post" action = "php file" enctype = "multipart/form-data">
<H3> import an Excel table:

<Input type = "submit" value = "import"/>
</Form>

Second, process the corresponding PHP File
Copy codeThe 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
Copy codeThe 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.
Copy codeThe 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 ');
}

/* Export the excel function */
Public function push ($ data, $ name = 'excel '){

Error_reporting (E_ALL );
Date_default_timezone_set ('Europe/London ');
$ ObjPHPExcel = new PHPExcel ();

/* The following are some settings, such as the author title */
$ ObjPHPExcel-> getProperties ()-> setCreator ("turning sunshine ")
-> SetLastModifiedBy ("sunshine in turns ")
-> SetTitle ("Data EXCEL export ")
-> SetSubject ("Data EXCEL export ")
-> SetDescription ("Backup Data ")
-> SetKeywords ("excel ")
-> SetCategory ("result file ");
/* The following figure shows how to process the data in an Excel file and obtain the data horizontally. This step is the main step. Do not change the other steps */
Foreach ($ data as $ k => $ v ){

$ Num = $ k + 1;
$ ObjPHPExcel-> setActiveSheetIndex (0)

// Column A of Excel, uid is the key value of the array you found, and so on
-> SetCellValue ('A'. $ num, $ v ['uid'])
-> SetCellValue ('B'. $ num, $ v ['email '])
-> SetCellValue ('C'. $ num, $ v ['Password'])
}

$ ObjPHPExcel-> getActiveSheet ()-> setTitle ('user ');
$ ObjPHPExcel-> setActiveSheetIndex (0 );
Header ('content-Type: application/vnd. ms-excel ');
Header ('content-Disposition: attachment; filename = "'.w.name.'.xls "');
Header ('cache-Control: max-age = 0 ');
$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 ');
$ ObjWriter-> save ('php: // output ');
Exit;
}

Third, the above is all the exported content, and the phpExcel package is appended to the end.