Recently in a small project, is a small system of management information, requirements for import and export information for the function of Excel, study the import and export function must know that the export is much simpler than the import, import with the Phpexcel, at that time, Phpexcel is completely unaware of the So study for a long time to study out, midway also appeared a variety of problems, fortunately, the final result is out;
HTML page code:
<form method= "POST" action= "Submit processing page" enctype= "Multipart/form-data" >
<input type= "hidden" name= "Leadexcel" value= "true" >
<strong class= "Icon-reorder" > Import Excel Table <input type= "file" name= "File_stu"/>
<input type= "Submit" value= "import" style= "position:relative;margin-left:-40px;margin-top:-10px"/> </strong >
</form>
Submit Processing page Code:
<?php
Require ("function.php");
$con =con (' Manage ');//Connect to Database
When uploading too many files, when the time limit is exceeded, the upload file will be terminated, plus this sentence is to cancel the time limit;
Set_time_limit (0);
if ($_post[' leadexcel ') = = "true")
{
$filename = $_files[' File_stu ' [' name '];
$tmp _name = $_files[' File_stu ' [' tmp_name '];
$msg = UploadFile ($filename, $tmp _name);
Echo $msg;
echo "<script>settimeout (' History.back () ',") </script> ";
}
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/classes/phpexcel.php ';
Require_once './phpexcel/classes/phpexcel/iofactory.php ';
Require_once './phpexcel/classes/phpexcel/reader/excel5.php ';
Note Setting the time zone
$time =date ("Ymdhis");//go to the current time of uploading
Get the extension of the uploaded file
$extend =strrchr ($file, '. ');
Determine if the format is Excel
if ($extend! = ". xls") {
echo "<script>alert (' you uploaded the incorrect format '); History.back ();</script>";
Exit ();
}
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
if ($result)//If the upload file is successful, perform the import Excel operation
{
$excelpath = $uploadfile;
SetLocale (Lc_all, ' zh_cn '); Import database to prevent Chinese garbled characters
$fileType = Phpexcel_iofactory::identify ($excelpath);
$objReader = Phpexcel_iofactory::createreader ($fileType);
These two sentences are also to prevent the import of the time garbled
$objReader->setinputencoding (' GBK ');
$objReader->setdelimiter (', ');
$objPHPExcel = $objReader->load ($excelpath);
$Sheet = $objPHPExcel->getactivesheet ();
$highestRow = $Sheet->gethighestrow ();//Get Total rows
$highestColumn = $Sheet->gethighestcolumn ();//Get total number of columns
Why do you have this array, because I can only print one column when I print the Total row
$arr = Array (1=> ' A ',2=> ' B ',3=> ' C ',4=> ' D ',5=> ' E ',6=> ' F ',7=> ' G ',8=> ' H ',9=> ' I ',10=> ' J ',11=> ' K ',12=> ' L ',13=> ' M ', 14=> ' N ',15=> ' O ',16=> ' P ',17=> ' Q ',18=> ' R ',19=> ' S ',20=> ' T ',21=> ' U ',22=> ' V ',23=> ' W ',24=> ' X ',25=> ' Y ',26=> ' Z ');
$headtitle =array ();
for ($row = 2; $row <= $highestRow; $row + +)
{
$strs =array ();
for ($col = 0; $arr [$col]! = ' F '; $col + +)
{
$strs [$col] = $Sheet->getcellbycolumnandrow ($col, $row)->getvalue ();
}
$data =explode ("", $strs [0]);
$con =con (' Manage ');
$sql = "INSERT into ' data ' (' id ', ' domain ', ' regtime ', ' expiretime ', ' CID ') VALUES ('". $data [0]. "', '". $data [1]. "', '". $ DATA[2]. "', '". $data [3]. "', '". $data [4]. "')";
$re =mysqli_query ($con, $sql);
if (! $re)
{
return false;
echo ' SQL statement error ';
}else{
$msg = "Import succeeded";
}
}
}
Else
{
$msg = "Import failed! ";
}
return $msg;
}
?>
phpexcel--Importing Excel tables