PHP using Phpexcel class export to import Excel usage
Source: Time: 2013-09-05 19:26:56 read:
Share to:
[Guide] Phpexcel class is a PHP Excel table processing plug-ins, below I would like to introduce the use of Phpexcel class to import and export Excel table application method, there is a need to know the friend not reference reference (Phpexcel himself Baidu download not introduced here). Export Excel usage Settings environment variables
Phpexcel class is a PHP Excel table processing plug-ins, below I would like to introduce the use of Phpexcel class to import and export Excel table application method, there is a need to know the friend not reference reference (Phpexcel himself Baidu download not introduced here).
Export Excel Usage
Setting environment variables (new Phpexcel)
| The code is as follows |
Copy Code |
Set_include_path ('. '). Path_separator. Yii::app ()->basepath. ' /lib/phpexcel '. Path_separator. Get_include_path ());
Note: In Yii, can also direct yii::import ("application.lib.phpexcel.*");
Introducing Phpexcel related files
Require_once "phpexcel.php";
Require_once ' phpexcel/iofactory.php ';
Require_once ' phpexcel/writer/excel5.php '; |
Put the content you want to export into a table
| copy code |
New $resultPHPExcel = new Phpexcel ();
Setting parameters Set Value $resultPHPExcel->getactivesheet ()->setcellvalue (' A1 ', ' quarter ');
$resultPHPExcel->getactivesheet ()->setcellvalue (' B1 ', ' name ');
$resultPHPExcel->getactivesheet ()->setcellvalue (' C1 ', ' quantity ');
$i = 2;
foreach ($data as $item) {
$resultPHPExcel->getactivesheet ()->setcellvalue (' A '. $i, $item [' Quarter ']);
$resultPHPExcel->getactivesheet ()->setcellvalue (' B '. $i, $item [' name ']);
$resultPHPExcel->getactivesheet ()->setcellvalue (' C ' $i, $item [' number ']);
$i + +;
} |
Set Export parameters
| The code is as follows |
Copy Code |
Set Export file name $outputFileName = ' Total.xls '; $xlsWriter = new Phpexcel_writer_excel5 ($resultPHPExcel); Ob_start (); Ob_flush (); Header ("Content-type:application/force-download"); Header ("Content-type:application/octet-stream"); Header ("Content-type:application/download"); Header (' Content-disposition:inline;filename= '. $outputFileName. ' '); Header ("Content-transfer-encoding:binary"); Header ("Expires:mon, Jul 1997 05:00:00 GMT"); Header ("last-modified:".) Gmdate ("D, D M Y h:i:s"). "GMT"); Header ("Cache-control:must-revalidate, Post-check=0, pre-check=0"); Header ("Pragma:no-cache"); $xlsWriter->save ("Php://output"); |
Output error.
Default $xlswriter->save ("Php://output"), perhaps because the cache is not large enough, and the display is not complete, so do a relay, the way is as follows:
| The code is as follows |
Copy Code |
$finalFileName = (Yii::app ()->basepath. ' /runtime/'. Time (). XLS '; $xlsWriter->save ($finalFileName); Echo file_get_contents ($finalFileName); |
The file_get_contents () function reads the entire file into a string. As with file (), the difference is that the file_get_contents ()
A string is read into the piece.
Import Excel Usage
| The code is as follows |
Copy Code |
<?
if ($_post[' leadexcel ') = = "true")
{
$filename = $_files[' inputexcel ' [' name '];
$tmp _name = $_files[' Inputexcel ' [' tmp_name '];
$msg = UploadFile ($filename, $tmp _name);
Echo $msg;
} Import an Excel file
function UploadFile ($file, $filetempname)
{
Upload file storage path set by yourself
$filePath = ' upfile/';
$str = "";
The following path is modified according to the path you phpexcel
Require_once '. /phpexcel/phpexcel.php ';
Require_once '. /phpexcel/phpexcel/iofactory.php ';
Require_once '. /phpexcel/phpexcel/reader/excel5.php '; Note Setting the time zone
$time =date ("y-m-d-h-i-s");//go to the current time of uploading
Get the extension of the uploaded file
$extend =strrchr ($file, '. ');
The file name after uploading
$name = $time. $extend;
$uploadfile = $filePath. $name;//file name address after upload
The Move_uploaded_file () function moves the uploaded file to a new location. Returns true if successful, otherwise false is returned.
$result =move_uploaded_file ($filetempname, $uploadfile);//if uploaded to the current directory
echo $result;
if ($result)//If the upload file is successful, perform the import Excel operation
{
Include "conn.php";
$objReader = Phpexcel_iofactory::createreader (' Excel5 ');//use excel2007 for
$objPHPExcel = $objReader->load ($uploadfile);
$sheet = $objPHPExcel->getsheet (0);
$highestRow = $sheet->gethighestrow (); Total number of rows obtained
$highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained
/* First method Loop through the Excel file, read a line, insert a
for ($j =1; $j <= $highestRow; $j + +)//Read data starting from the first line
{
for ($k = ' a '; $k <= $highestColumn; $k + +)//Read data from column A
{
//
This method is simple, but inappropriate, to "merge an array, and then split into a field value into the database
Measured in Excel, if the value of a cell contains the imported data is empty
//
$str. = $objPHPExcel->getactivesheet ()->getcell ("$k $j")->getvalue (). /Read Cells
}
Echo $str; Die ();
Explode: function splits a string into arrays.
$strs = Explode ("", $str);
$sql = "INSERT into te (' 1 ', ' 2 ', ' 3 ', ' 4 ', ' 5 ') VALUES (
' {$strs [0]} ',
' {$strs [1]} ',
' {$strs [2]} ',
' {$strs [3]} ',
' {$strs [4]} ');
Die ($sql);
if (!mysql_query ($sql))
{
return false;
echo ' SQL statement error ';
}
$str = "";
}
Unlink ($uploadfile); Delete an uploaded Excel file
$msg = "Import succeeded! ";
*/ /* The second method */
$objWorksheet = $objPHPExcel->getactivesheet ();
$highestRow = $objWorksheet->gethighestrow ();
Echo ' highestrow= '. $highestRow;
echo "<br>";
$highestColumn = $objWorksheet->gethighestcolumn ();
$highestColumnIndex = phpexcel_cell::columnindexfromstring ($highestColumn);//Total number of columns
Echo ' highestcolumnindex= '. $highestColumnIndex;
echo "<br>";
$headtitle =array ();
for ($row = 1; $row <= $highestRow; $row + +)
{
$strs =array ();
Note the number of columns in the Highestcolumnindex index starts at 0
for ($col = 0; $col < $highestColumnIndex; $col + +)
{
$strs [$col] = $objWorksheet->getcellbycolumnandrow ($col, $row)->getvalue ();
}
$sql = "INSERT into te (' 1 ', ' 2 ', ' 3 ', ' 4 ', ' 5 ') VALUES (
' {$strs [0]} ',
' {$strs [1]} ',
' {$strs [2]} ',
' {$strs [3]} ',
' {$strs [4]} ');
Die ($sql);
if (!mysql_query ($sql))
{
return false;
echo ' SQL statement error ';
}
}
}
Else
{
$msg = "Import failed! ";
}
return $msg;
}
?> |
HTML page code
| The code is as follows |
Copy Code |
<form action= "upload.php" method= "post" enctype= "Multipart/form-data" >
<input type= "hidden" name= "Leadexcel" value= "true" >
<table align= "center" width= "90%" border= "0" >
<tr>
<td>
<input type= "File" Name= "Inputexcel" ><input type= "Submit" value= "Import Data" >
</td>
</tr>
</table>
</form> |
Phpexcel Import and Export
