dongpenggan6812
dongpenggan6812
2014-11-11 05:34 阅读 63
已采纳

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

I have used the following code to read the data of exceltodb.xlsx file and import it into the table city of database world. The library to read the file is PHPExcel which is very common library.The code I found is as follows but the code is executing but the row is not added into the database.

<?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")');
}
?>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dongping6974 dongping6974 2014-11-11 05:44

    This code segment will upload your xml data sheet in to a particular location in the server:

    <?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 />";
    }
    }

?>

    This will upload the data taken from xml to the database:

         <?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."');");
    点赞 评论 复制链接分享
提交

相关推荐