Recently in a project, the platform there are more than 1000 data, there are exel tables, can also be in the system inside a record, but this is too slow, then there is what good method, you can put this data, a few buttons, on a one-time completion. This article is original, if need reprint, please famous source: http://blog.csdn.net/qq_22327455
Here's a more detailed story:
My function is to click the Upload File button, select the desired Exel file, and then select the data table to import, click Import, the data is imported once.
1. First need to download a PHP Exel class package, in Baidu input: Phpexcel download any download, download will get a Phpexcel folder.
2. Write HTML code: Create a new View file
<! DOCTYPE html>
<meta http-equiv= "Content-type" content= "text/html; Charset=utf-8 "/>
<link type= "Text/css" rel= "Stylesheet" href= "/public/css/cuspermission/globalset.css"/>
<script src= '/public/js/cuspermission/globalset.js ' ></script>
<script src= "/public/js/orgtree.js" ></script>
<body>
<div class= "Content" >
<center>
<div style= "margin-top:50px;" id= "Scbox" >
<form action= "/exel/save_exeldata" method= "post" enctype= "Multipart/form-data" class= "form" >
<div style= "margin-top:20px;" >
<span style= "font-size:14px;margin-top:55px;" > Please upload exel file:</span>
<input id= "File" type= "file" Name= "Exel" value= ""/>
</div>
<div style= "margin-top:30px;" >
<span style= "FONT-SIZE:14PX;" > select database table:</span>
<input type= "Radio" name= "table" value= "User"/> users table
<input type= "Radio" name= "table" value= "Projects"/> Project table
</div>
<div style= "margin-top:30px;" >
<input type= "hidden" name= "max_file_size" value= "52428800"/>
<input id= "Submit_st" type= "button" value= "Start import"/>
</div>
</form>
</div>
<center>
</div>
</body>
<script>
$ (function () {
$ ("#submit_st"). Click (function () {
var file = $ ("#file"). Val ();
if (file = = ") {
Alert (' You have not selected file ');
return false;
}else{
if (Confirm (' Are you sure you want to import ')}) {
$ (this). attr (' disabled ', true); Prohibit submit button after commit, prevent duplicate data author:http://blog.csdn.net/qq_22327455
$ ("form"). Submit ();
}else{
return false;
}
}
});
});
</script>
3. To import exel table data into a database, we first need to convert the data from the Exel table into an array, the resulting array is good, and the next step is to insert the data in the array into the database. The following method is a method for data conversion.
New change.php File
<?php
/*
* @author:
*user: Convert Excel data to an array
* */
Include DirName (DirName (__file__)). ' /phpexcel/phpexcel.php ';//the file you downloaded in the first step has a class file called: phpexcel.php, introducing him
function Format_excel2array ($filePath = ", $sheet =0) {
if (empty ($filePath) or!file_exists ($filePath)) {die (' File not exists ');}
$PHPReader = new phpexcel_reader_excel2007 (); Create a Reader object
if (! $PHPReader->canread ($filePath)) {
$PHPReader = new Phpexcel_reader_excel5 ();
if (! $PHPReader->canread ($filePath)) {
Echo ' no Excel ';
return;
}
}
$PHPExcel = $PHPReader->load ($filePath); Create an Excel object
$currentSheet = $PHPExcel->getsheet ($sheet); * * Read the specified worksheet in the Excel file */
$allColumn = $currentSheet->gethighestcolumn (); * * Get the largest column number */
$allRow = $currentSheet->gethighestrow (); * * Get a total number of lines */
$data = Array ();
for ($rowIndex =1; $rowIndex <= $allRow; $rowIndex + +) {//iterates through the contents of each cell. Note that the row starts at 1 and the column starts from a
for ($colIndex = ' A '; $colIndex <= $allColumn; $colIndex + +) {
$addr = $colIndex. $rowIndex;
$cell = $currentSheet->getcell ($addr)->getvalue ();
if ($cell instanceof Phpexcel_richtext) {//Rich text conversion string
$cell = $cell->__tostring ();
}
$data [$rowIndex] [$colIndex] = $cell;
}
}
return $data;
}
4. Create a method to process the data under the controller exel.php Save_exeldata (), in this method, call the phpexcel.php file, convert the data, and keep the data.
<?php
/*
@author: Naruto-qq_22327455 's Column http://blog.csdn.net/qq_22327455
@user storing Exel table data in the database
* */
Class exel{
Public Function Save_exeldata () {
First keep the file submitted by the form at the specified location
$file = $_files[' Exel ');
Echo ' <pre> ';p rint_r ($file);d ie ();
1 Error judgment
if ($file [' Error ']!=0) {
echo "<script>alert (' File upload error '); location.href= '/xxx ';</script>";d ie ();
}
2 Limit Size
if ($file [' Size '] > $_post[' max_file_size ') {
echo "<script>alert (' max upload file limit 50 mb '); location.href= '/xxx ';</script>";d ie ();
}
Here you can also make other judgments as needed, such as whether the file type is Exel file type, etc.
3. Storing files
$path = DirName (dirname (dirname (__file__)). ' /uploadfile/exel_tmp '; The path to be stored
if (!file_exists ($path)) {///does not exist the directory is created
mkdir ($path, 0777);
}
$dest = $path. ' /'. $file [' name ']; Where the target will be stored
Move_uploaded_file ($file [' Tmp_name '], $dest); There will be temporary files on the server, and keep them where you made them.
Above the above steps, the Exel table has been placed in the position you specified.
The next step is to find the location you specified, locate the file, and then turn the data into an array,
4. Data conversion and deposit into the database
$path _s = dirname (dirname (__file__));
Include $path _s. '/libs/phpexcel/changge.php ';
$exelist = Format_excel2array ($dest);
Echo ' <pre> ';p rint_r ($exelist);d ie ();//print to see if it has been converted into an array
unset ($exelist [' 1 ']);
if ($_post[' table '] = = ' personal ') {
$res = $this->exel_personal ($exelist); The//exel_personal method inserts the contents of the data into the database, I do not write here, each frame has a different wording, and the data and the database is not the same, There's no way to write.
Unlink ($dest); Destroying files
$this->exel_tip ($res); Exel_tip method for after inserting regardless of success, give a hint, and jump
}elseif ($_post[' table '] = = ' projects ') {
$res = $this->exel_projects ($exelist);//Ibid.
Unlink ($dest); Ditto
$this->exel_tip ($res); Ditto
}else{
echo "<script>alert (' Please select data table before starting import '); location.href= '/xxx ';</script>";d ie ();
}
}
}
?>
Here, the functionality has been implemented. Interested friends, you can test to play, later in the work, you may encounter to do such a function. Of course, there are some tools, you can directly import the Exel data into the database without the program, but there is the case: the Exel table gives the string, and the data table that field is to save an ID, through this ID, query another table, get this string, then there is no way the tool. So using the program to reality is easier to control, including some related data, can be obtained through the query, and then inserted. Well, just write it down here, there's something funny going on back there, and I'll blog again.
How to deposit data from a Exel table into a database
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
