Thinkphp import and export an excel table

As a php enthusiast, we need to regularly learn about some open-source projects, which is not a good improvement for us, so that we can easily program

As a php enthusiast, we need to regularly learn about some open-source projects, which is not a good improvement for us, so that we can easily program

Thinkphp import and export

(1) import Excel

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

Second, process the corresponding php file

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];
If (strtolower ($ file_type )! = "Xls ")
{
$ This-> error ('not an Excel file, re-upload ');
}
$ SavePath = SITE_PATH. '/public/upfile/Excel /';

/* Name the uploaded file after the time Display */
$ Str = date ('ymdhis ');
$ File_name = $ str. ".". $ file_type;
If (! Copy ($ tmp_file, $ savePath. $ file_name ))
{
$ This-> error ('die ');
}

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

 

// 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

Note: The ExcelToArrary class is created in addons/services/ExcelToArrary. class. php under the root directory.

Class ExcelToArrary extends Service {

Public function _ construct (){
Include_once ('./Excel/PHPExcel. php ');
}

 

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.

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 ();
$ ObjPHPExcel-> getProperties ()-> setCreator ("php fans ")
-> SetLastModifiedBy ("php fans ")
-> SetTitle ("data EXCEL export ")
-> SetSubject ("data EXCEL export ")
-> SetDescription ("backup data ")
-> SetKeywords ("excel ")
-> SetCategory ("result file ");

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;
}