dongpenggan6812
2014-11-11 05:34
浏览 68
已采纳

如何将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")');
}
?>

图片转代码服务由CSDN问答提供 功能建议

我使用以下代码读取 exceltodb.xlsx 文件的数据并导入 它进入了数据库世界的表城。 读取文件的库是PHPExcel，它是非常常见的库。我找到的代码如下，但代码正在执行，但行未添加到数据库中。 \ n 

 ＆lt;？php 
include'PHPExcel-develop / Classes / PHPExcel / IOFactory.php'; 
 
 $ inputFileName ='exceltodb.xlsx'; 
 
 //读取您的Excel工作簿 
try {
 $ inputFileType = PHPExcel_IOFactory :: identify（$ inputFileName）; 
 $ objReader = PHPExcel_IOFactory :: createReader（$ inputFileType）; 
 $ objPHPExcel = $ objReader-＆gt; load（'exceltodb.xlsx'）;  
} catch（Exception $ e）{
 die（'Error loading file“'。pathinfo（$ inputFileName，PATHINFO_BASENAME）。'”：'。$ e-＆gt; getMessage（））; 
} 
 \  n //获取工作表尺寸
 $ sheet = $ objPHPExcel-＆gt; getSheet（0）;  
 $ highestRow = $ sheet-＆gt; getHighestRow（）;  
 $ highestColumn = $ sheet-＆gt; getHighestColumn（）; 
 
 //依次循环遍历工作表的每一行
（$ row = 1; $ row＆lt; = $ highestRow; $ row ++）{\  n //将一行数据读入数组
 $ rowData = $ sheet-＆gt; rangeToArray（'A'。$ row。'：'。$ highestColumn。$ row，
 NULL，
 TRUE，
  FALSE）; 
 //在这里将行数据数组插入您选择的数据库
 mysql_connect（'localhost'，'root'，''）; 
 mysql_select_db（'world'）; 
 mysql_query（'insert into city  （“city”，“id”，“stateid”，“countryid”）值（“$ rowData”）'）; 
} 
？＆gt;
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • 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."');");
    已采纳该答案
    评论
    解决 无用
    打赏 举报

报告相同问题？

提交

相关推荐 更多相似问题