读取excel文件并将其存储到mysql数据库 [英] Read excel file and store it to mysql database

问题描述

我有以下代码执行错误。

I have the following code which is giving an error on execution.

<?php
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('exceltestsheet.xlsx');

$conn = mysql_connect("localhost","root","");
mysql_select_db("exceltest",$conn);
 for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) {
   // $sno = $data->sheets[0]["cells"][$x][1];
    $name = $data->sheets[0]["cells"][$x][1];
    $extension = $data->sheets[0]["cells"][$x][2];
    $email = $data->sheets[0]["cells"][$x][3];
    $sql = "INSERT INTO mytable (name,extension,email) 
        VALUES ('$name',$extension,'$email')";
    echo $sql."\n";
    mysql_query($sql);
 }
?>

错误是：已弃用：通过引用分配新的返回值在第262行中的C：\wamp\www\xltodb\Excel\reader.php中已弃用，并且还显示文件名exceltestsheet.xlsx不可读。

The error is: Deprecated: Assigning the return value of new by reference is deprecated in C:\wamp\www\xltodb\Excel\reader.php on line 262 and it also says The filename exceltestsheet.xlsx is not readable.

而且reader.php的代码是：

And the code for reader.php is:

 function Spreadsheet_Excel_Reader()
 {
     $this->_ole =& new OLERead();
     $this->setUTFEncoder('iconv');
 }

这是显示错误的行。

推荐答案

查看这个

    <?php
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('siteb_ceccwo.xls');

$con=mysqli_connect("localhost","root","","exceltest");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Create table
$sql="CREATE TABLE siteb_ceccwo(

    quote_number VARCHAR(100),
    line_no VARCHAR(100),
    item_no VARCHAR(100),
    name VARCHAR(100),
    unit VARCHAR(100),
    rm VARCHAR(100),
    cwo_rm VARCHAR(100))";

// Execute query
if (mysqli_query($con,$sql))
  {
  echo "Table siteb_ceccwo created successfully";
  }
else
  {
  echo "Error creating table: " . mysqli_error($con);
  }

for($x = 2; $x <= count($data->sheets[0]["cells"]); $x++)
 {
    //$sno = $data->sheets[0]["cells"][$x][1];
    $quote_number = $data->sheets[0]["cells"][$x][1];
    $line_no = $data->sheets[0]["cells"][$x][2];
    $item_no = $data->sheets[0]["cells"][$x][3];
    $name = $data->sheets[0]["cells"][$x][4];
    $unit = $data->sheets[0]["cells"][$x][5];
    $rm = $data->sheets[0]["cells"][$x][6];
    $cwo_rm = $data->sheets[0]["cells"][$x][7];

    $res = mysqli_query($con,"INSERT INTO siteb_ceccwo 
    (quote_number, line_no, item_no,name,unit,rm,cwo_rm) VALUES ('$quote_number','$line_no','$item_no','$name','$unit','$rm','$cwo_rm')");
mysqli_close($res);
}
?>

这篇关于读取excel文件并将其存储到mysql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！