phpexcel--Importing Excel tables

Source: Internet
Author: User
Tags import database

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.