如何将excel数据导入MySQL数据库？

Question

我使用以下代码读取exceltodb.xlsx文件的数据，并将其导入数据库世界的表城。读取文件的库是PHPExcel，这是我发现的非常常见的library.The代码，但代码正在执行，但行没有添加到数据库中。

<?php
include 'PHPExcel-develop/Classes/PHPExcel/IOFactory.php';

$inputFileName = 'exceltodb.xlsx';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load('exceltodb.xlsx');
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++){ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                    NULL,
                                    TRUE,
                                    FALSE);
    //  Insert row data array into your database of choice here
    mysql_connect('localhost','root','');
    mysql_select_db('world');
    mysql_query('insert into city("city","id","stateid","countryid") values("$rowData")');
}
?>

Answer 1

此代码段将将xml数据表上载到服务器中的特定位置：

<?php
 $uploadedStatus = 0;
    if ( isset($_POST["submit"]) ) {
    if ( isset($_FILES["file"])) {
    //if there was an error uploading the file
    if ($_FILES["file"]["error"] > 0) {
    echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
    }
    else {
    if (file_exists($_FILES["file"]["name"])) {
    unlink($_FILES["file"]["name"]);
    }
    $storagename = "discussdesk.xlsx";
    move_uploaded_file($_FILES["file"]["tmp_name"],  $storagename);
    $uploadedStatus = 1;
    }
    } else {
    echo "No file selected <br />";
    }
    }

?>

这将将从xml获取的数据上载到数据库：

     <?php
    /************************ YOUR DATABASE CONNECTION START HERE   ****************************/

    define ("DB_HOST", "localhost"); // set database host
    define ("DB_USER", ""); // set database user
    define ("DB_PASS",""); // set database password
    define ("DB_NAME",""); // set database name

    $link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
    $db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

    $databasetable = "YOUR_TABLE";

    /************************ YOUR DATABASE CONNECTION END HERE  ****************************/


    set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
    include 'PHPExcel/IOFactory.php';

    // This is the file path to be uploaded.
    $inputFileName = 'discussdesk.xlsx'; 

    try {
        $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
    } catch(Exception $e) {
        die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
    }

    $allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
    $arrayCount = count($allDataInSheet);  // Here get total count of row in that Excel sheet

    for($i=2;$i<=$arrayCount;$i++){
    $userName = trim($allDataInSheet[$i]["A"]);
    $userMobile = trim($allDataInSheet[$i]["B"]);

    $query = "SELECT name FROM YOUR_TABLE WHERE name = '".$userName."' and email = '".$userMobile."'";
    $sql = mysql_query($query);
    $recResult = mysql_fetch_array($sql);
    $existName = $recResult["name"];
    if($existName=="") {
    $insertTable= mysql_query("insert into YOUR_TABLE (name, email) values('".$userName."', '".$userMobile."');");