PHP upload xls file import into MySQL datasheet

Source: Internet
Author: User

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

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.