Import and export of Excel data using Phpexcel (full step)

Import and export of Excel data using Phpexcel (full step) Many articles have mentioned about the use of phpexcel to achieve the import and export of Excel data, most of the articles are similar, or is reproduced, there will be some problems, the following is my study of the use of Phpexcel to summarize the use of the method, the next straight into the topic.

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 and export of Excel, the problem is basically phpexcel core class reference path error, If there is a problem, be sure to test whether the brutishness reference is correct.

(i) Import Excel

First, upload the file in the foreground HTML page: for example:

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

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

Second, in the corresponding PHP file for file processing

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, discriminant is not an Excel file */
if (Strtolower ($file _type)! = "xls")
{
$this->error (' Not Excel file, re-upload ');
}

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

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

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

/*

* Processing of uploaded Excel data generates programming data, this function will be in the Exceltoarray class of the third step below

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

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

/*

Important code resolves an issue where thinkphp M, D methods cannot be called

If the M, D method fails in thinkphp, add the following line of code

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

/* Write database writes 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 (' failed to import database ');
}
}
}

}

Third: The Exceltoarrary class, which is used to reference phpexcel and process Excel data

Class Exceltoarrary extends service{

Public Function __construct () {

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

/**

* Read Excel $filename path file name $encode return data encoding 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;

}

}

The above is the whole content of the import, the Phpexcel package is attached to the last.

(b) Excel export (much simpler than import)

First, find out the data in the database to generate Excel, such as:

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

Second, the Exceltoarrary class, used to refer to phpexcel and process the data

Class Exceltoarrary extends service{

Public Function __construct () {

/* Import Phpexcel Core class Note: Your path is not the same as mine. cannot 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 ();

/* Below are some settings, what the author title ah or something * *
$objPHPExcel->getproperties ()->setcreator ("Sunshine of the Turn")
->setlastmodifiedby ("Sunshine of the Turn")
->settitle ("Data Excel Export")
->setsubject ("Data Excel Export")
->setdescription ("Backing Up data")
->setkeywords ("Excel")
->setcategory ("result file");
/* The following is the processing of data in Excel, the data is taken sideways, mainly this step, the other basic do not change */
foreach ($data as $k = = $v) {

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

                         //excel column A, UID is the key value you have found in the array, as follows So
                         ->setcellvalue (' A '. $nu m,  $v [' uid '])   &NBSP
                        &N Bsp ->setcellvalue (' B '. $num,  $v [' email '])
                    nbsp    ->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 contents of the export, Phpexcel package attached to the last.

Original: http://blog.csdn.net/teresa502/article/details/8091083

Import and export of Excel data using Phpexcel (full step)