Home > Developer > MySQL

PHP upload xls file import into MySQL datasheet

Source: Internet
Author: User
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

affirm that the following articles belong to reprint, now everyone new download version with the following column this is not the same! I read a lot of online, and finally I went to the English handbook, which said very clearly! Everyone English is not good can download Youdao! in fact, the focus is to include the following two lines:

Require_once ' excellib/phpexcel.php ';
Require_once ' excellib/phpexcel/iofactory.php ';

Each of you replaces the respective storage directory, which I use here is the Excellib directory. is to put phpexcel.php ', phpexcel/iofactory.php included in it, the others are similar to the following example.

This function is actually implemented by a foreign PHP to read the XLS file class, the online data is more than the Excel file saved as a CSV file, and then imported from the CSV file.

Php-excelreader,: Http://sourceforge.net/projects/phpexcelreader downloaded after the decompression,

Directory structure

First we need an upload page:

The code is as follows:

<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">

<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 "/>

<title> Import Tests </title>

<body>

<script>

function ImpOrt_check () {

var f_content = Form1.file.value;

var fileext=f_content.substring (F_content.lastindexof ("."), F_content.length)

Fileext=fileext.tolowercase ()

if (fileext!= '. xls ')

{

Alert ("Sorry, import data format must be XLS format file Oh, please re-upload the format after you, thank you!") ");

return false;

}

}

</script>

<table width= "98%" border= "0" align= "center" style= "margin-top:20px; border:1px solid #9abcde; " >

<form id= "Form1" Name= "Form1" enctype= "Multipart/form-data" method= "post" action= "insert.php" >

<tr >

<TD height= "colspan=" 2 "background=". /skins/top_bg.gif "><label> <strong><a href=" # "> Primary Math topic Data import </a></strong></ Label></td>

</tr>

<tr>

<TD width= "18%" height= ">" Select the datasheet you want to import </td>

<TD width= "82%" ><label>

<input name= "File" type= "file" id= "file" size= "/>"

</label>

<label>

<input name= "button" type= "Submit" class= "Nnt_submit" id= "button" value= "Import data" onclick= "ImpOrt_ Check (); " />

</label>

</td>

</tr>

<tr>

<TD colspan= "2" bgcolor= "#DDF0FF" > [<span class= "STYLE1" > Note </span>] Data import format Description:</td>

</tr>

<tr>

<TD colspan= "2" > 1, other. The Import data table file must be a <strong>execel</strong> file format {. <span class= "STYLE2" >xls< /span>} is a name extension. </td>

</tr>

<tr>

<TD colspan= "2" > 2, execel file import data order must be as follows: Serial number | Topics </td>

</tr>

<tr>

<TD colspan= "2" > </td>

</tr></form>

</table>

</body>

Database Connection code page:

<?php

$host = "localhost";

$user = "root";

$password = "123456";

$database = "Project";

[Email Protected]_connect ("$host", "$user", "$password");

if (! $connect)

{

echo "Database connect wrong";

Exit

}

$db =mysql_select_db ("$database", $connect);

$sql =mysql_query ("SET NAMES ' gb2312 '");

?>

Read the inserted page

The code is as follows:

<?php

Error_reporting (e_all ^ e_notice);

if ($_post) {

$IMPort_tmpfile = $_files[' file ' [' Tmp_name '];

require_once ' conn.php ';

mysql_select_db (' Test_xls '); Select Database

require_once ' excel/reader.php ';

$data = new Spreadsheet_excel_reader ();

$data->setoutputencoding (' UTF-8 ');

$data->read ($Import_tmpfile);

$array =array ();

for ($i = 1; $i <= $data->sheets[0][' numrows '); $i + +) {

for ($j = 1; $j <= $data->sheets[0][' numcols '); $j + +) {

$array [$i] [$j] =$da ta->sheets[0][' cells ' [$i] [$j];

}

}

Sava_data ($array);

}

function Sava_data ($array) {

$count = 0;

$total = 0;

foreach ($array as $tmp) {

$Isql = "SELECT id from xls where id= '". $tmp [1]. "'";

$sql = "Insert into xls (ID,TM) value (";

$sql. = "'". $tmp [1]. "', '". $tmp [2]. "')";

if (! mysql_num_rows (mysql_query ($Isql))) {

if (mysql_query ($sql)) {

$count + +;

}

}

$total + +;

}

echo "<script>alert (' shared '). $total." Data, import ". $count." Data successfully ');</script> ";

}

function Ttod ($text) {

$jd 1900 = Gregoriantojd (1, 1, 1900)-2;

$myJd = $text + $JD 1900;

$myDate = Jdtogregorian ($myJd);

$myDate = explode ('/', $myDate);

$MYDATESTR = Str_pad ($myDate [2],4, ' 0 ', str_pad_left). " -". Str_pad ($myDate [0],2, ' 0 ', str_pad_left)." -". Str_pad ($myDate [1],2, ' 0 ', str_pad_left);

return $myDateStr;

}

?>

Database Testz_xls Table

Test environment for Windows XP

Phpnow 1.4

Address: http://localhost/test/up.php

Test diagram:

This article is from "grow journal----Open Source World, share knowledge happiness!" "Blog, be sure to keep this provenance http://jason2016.blog.51cto.com/892969/289411 this article is from 51CTO. COM Technology Blog

PHP upload xls file import into MySQL datasheet

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

Related Keywords:
import xls file in php upload xls file in php mysql import file into table import sql file into mysql php read xls file into array how to upload xls file in php how to import sql file into mysql
Related Article
MySQL batch update and batch update different values for mult...
The solution of no package Mysql-server available error when ...
The efficiency of MySQL nested query and connection query
The differences between int, bigint, smallint, and tinyint in...
MySQL row-level lock, table-level lock, page-level lock Detai...
MySQL Case statement (with instance)

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.

What's Trending
Top 10 Tags
datastax versions naming convention zookeeper client class definition md5 microsoft sql server 2005 data structures exception handling error handling
Top 10 Keywords
microsoft download center down wordpress address url site address url wordpress address url windows installer 4 0 download 302 not found web address url definition site address url wordpress db2 integer mac os installation step by step pdf abbreviation for return

A Free Trial That Lets You Build Big!

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

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

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

    Open a Ticket

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

    Learn More