Import Data from Excel in PHP [closed]

问题:

I want to import data from excel file using PHP and then if possible, save it to a MySQL database.

回答1:

Importing from Excel files (XLS) is way harder than improting from CSV files. Usually I save my XLS to CSV with Excel then work on this CSV with PHP...

Look at PHP function fgetcsv at: http://ca.php.net/manual/en/function.fgetcsv.php

<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}
?> 

If you still want to load XLS directly from PHP it's possible (but how reliable)... A quick seach resulted in http://sourceforge.net/projects/phpexcelreader/ which might be helpful.

回答2:

Quite possible. You can save your Excel file as a CSV file, and use fgetcsv() to read that file in to PHP. fgetcsv() will parse your data into an array, which you can then create SQL queries out of to put into your database.

If all you're doing is putting it into a database, you might be able to bypass the need for a PHP script entirely and just use MySQL's LOAD DATA INFILE syntax on your CSV file instead:

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

回答3:

Best bet is to export from Excel to a CSV (Comma separated values) file. These files are easy to parse and load. If you are reading directly from an XLS file, I'm not sure how to do that. You might want to look and see if there is a libarary for PHP that can read Excel data files.

回答4:

Here's a tutorial on reading/writing an Excel spreadsheet directly (without having to export to CSV). The necessary packages are available from SourceForge and PEAR (cf. article).

回答5:

<?
 i$db = mysql_connect(“localhost”, “root”, “”) or die(“Could not connect.”);

if(!$db)

die(“no db”);

if(!mysql_select_db(“test”,$db))

die(“No database selected.”);

if(isset($_POST['submit']))

{

$filename=$_POST['filename'];

$handle = fopen(“$filename”, “r”);

while (($data = fgetcsv($handle, 1000, “,”)) !== FALSE)

{

$import=”INSERT into sample(name,email) values(‘$data[0]‘,’$data[1]‘)”;

mysql_query($import) or die(mysql_error());

}

fclose($handle);

print “Import done”;

}

else

{

print “<form action=’import.php’ method=’post’>”;

print “Type file name to import:<br>”;

print “<input type=’text’ name=’filename’ size=’20′><br>”;

print “<input type=’submit’ name=’submit’ value=’submit’></form>”;

}
 ?>

Source