Using Phpexcel to import and export Excel data (full step detailed analysis) _php tips

Many articles have mentioned about the use of Phpexcel to achieve Excel data import and export, most of the articles are similar, or is reproduced, will appear some problems, the following is my study phpexcel use routines summed up the use of methods, and then directly into the business.

First of all, I have this routine is used in the thinkphp development framework, if used in other frameworks is the same method, many people may not be able to correctly implement the import export of Excel, the problem is basically phpexcel core class reference path error, If you have a problem, be sure to test the Lu Jing reference correctly.

(i) Import Excel

First, upload files in the foreground HTML page:

Copy Code code as follows:

<form method= "post" action= "PHP file" enctype= "Multipart/form-data" >

<input type= "Submit" value= "Import"/>
</form>

Second, in the corresponding PHP file for the processing of files

Copy Code code 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];

/* discriminant is not an. xls file, to determine whether the Excel file * *
if (Strtolower ($file _type)!= "xls")
{
$this->error (' Not Excel file, re-upload ');
}

/* Set Upload path * *
$savePath = Site_path. '/public/upfile/excel/';

* * To name the uploaded file in time.
$str = Date (' Ymdhis ');
$file _name = $str. "." . $file _type;

/* Upload Success * *
if (! Copy ($tmp _file, $savePath. $file _name))
{
$this->error (' upload failed ');
}

/*

* The upload of Excel data processing to generate programming data, this function will be in the next step in the third Exceltoarray class

Note: This call executes the Read function in the third step class, converts Excel to an array and returns to $res, and then writes to the database

*/
$res = Service (' Exceltoarray ')->read ($savePath. $file _name);

/*

Important code solves problems that thinkphp M and D methods cannot call

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

*/
Spl_autoload_register (Array (' Do ', ' autoload '));

/* Write database to the generated array/*
foreach ($res as $k => $v)
{
if ($k!= 0)
{
$data [' uid '] = $v [0];
$data [' password '] = SHA1 (' 111111 ');
$data [' email '] = $v [1];

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

$data [' institute '] = $v [4];
$result = M (' user ')->add ($data);
if (! $result)
{
$this->error (' Import Database failed ');
}
}
}

}

Third: Exceltoarrary class, used to refer to Phpexcel and work with Excel data

Copy Code code as follows:

Class Exceltoarrary extends service{

Public Function __construct () {

/* Import Phpexcel Core class Note: Your path is not the same as mine can not be copied directly.
Include_once ('./excel/phpexcel.php ');
}

/**

* Read Excel $filename path file name $encode the encoding of the returned data defaults to UTF8

* Do not modify the following basic

*/

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;

}

}

Four, the above is the full content of the import, Phpexcel package attached to the last.

(ii) Excel export (much simpler than import)

First, identify the database to generate Excel data, such as:

$data = M (' User ')->findall (); Identify the data
$name = ' excelfile '; The generated Excel file file name
$res =service (' exceltoarrary ')->push ($data, $name);

Second, the Exceltoarrary class is used to refer to the Phpexcel and process the data

Copy Code code as follows:

Class Exceltoarrary extends service{

Public Function __construct () {

/* Import Phpexcel Core class Note: Your path is not the same as mine can not be copied directly.
Include_once ('./excel/phpexcel.php ');
}

/* Export 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, what the author title Ah, and so on * *
$objPHPExcel->getproperties ()->setcreator ("Turn of the Sun")
->setlastmodifiedby ("Turn of the Sun")
->settitle ("Data Excel Export")
->setsubject ("Data Excel Export")
->setdescription ("Backup Data")
->setkeywords ("Excel")
->setcategory ("result file");
/* The following is the processing of data in Excel, horizontal access to data, mainly this step, the other basic do not change * *
foreach ($data as $k => $v) {

$num = $k +1;
$objPHPExcel->setactivesheetindex (0)

Column A of Excel, UID is the key value of the array you identified, 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= '. $name. ') XLS "');
Header (' cache-control:max-age=0 ');
$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');
$objWriter->save (' php://output ');
Exit
}

Third, the above is the entire content of the export, Phpexcel package attached to the last.