将excel数据存储在MySQL数据库中通过使用JSON的PHP

Question

我在excel电子表格中存储了两列数据，我希望将这些数据存储在MySQL数据库（本地托管的时刻）中。

我正在将电子表格转换为JSON字符串，并通过XMLHTTP发送给PHP代码。这里是我的VBA代码：

Sub sendjson() 
Dim json As String 
Dim filed1 As String 
Dim i As Integer 
Dim j As Integer 
Dim data As String 
Worksheets("param").Range("C1").Select 

data = "{" + Chr(34) + "data" + Chr(34) + ":[" 

j = 2 
Do While Not (IsEmpty(ActiveSheet.Cells(j, 3))) 
    j = j + 1 
Loop 

i = 2 
Do While Not (IsEmpty(ActiveSheet.Cells(i, 3))) 
    If i < j - 1 Then 
    data = data + ActiveSheet.Cells(i, 3) + "," 
    Else 
    data = data + ActiveSheet.Cells(i, 3) + "]}" 
    End If 
    i = i + 1 
Loop 
Worksheets("param").Range("D1").Value = data 

json = data 

Set objHTTP = CreateObject("Microsoft.XMLHTTP") 
objHTTP.Open "POST", "http://localhost/test/jsontomysql.php", False 
objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 
objHTTP.send ("field1=" & json) 
Set objHTTP = Nothing 
End Sub 

这里是jsontomysql.php代码：

<?php 
try 
{ 
    $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', ''); 
} 
catch(Exception $e) 
{ 
    die('Erreur : '.$e->getMessage()); 
} 
$data = json_decode($json); 
foreach ($data as $name => $value) { 
    foreach ($value as $entry){ 
     $req = $bdd->prepare('INSERT INTO param (tck, value) VALUES(:tck, :value)'); 
     $req->execute(array(
      ':tck'=>$entry->tck, 
      ':value'=>$entry->value 
     ));  
    } 
} 
?> 

我的MySQL数据库具有以下结构：

PARAM {TCK（VARCHAR255），价值（真的）}

当我运行代码时，什么都没有发生。我敢肯定的问题是围绕：$data = json_decode($json);

为了让事情变得简单，我直接拷贝JSON字符串在我的PHP代码，如下：

<?php 
try 
{ 
    $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', ''); 
} 
catch(Exception $e) 
{ 
    die('Erreur : '.$e->getMessage()); 
} 
//$phpArray = json_decode($_POST['field1']); 

$data = '{ 
    "u1":{"tck":"EUSA1 Curncy","value":0,005}, 
    "u2":{"tck":"EUSA2 Curncy","value":0,0049}, 
    "u3":{"tck":"EUSA3 Curncy","value":0,0048}, 
    "u4":{"tck":"EUSA4 Curncy","value":0,0047}, 
    "u5":{"tck":"EUSA5 Curncy","value":0,0046}, 
    "u6":{"tck":"EUSA6 Curncy","value":0,0045}, 
    "u7":{"tck":"EUSA7 Curncy","value":0,0044}, 
    "u8":{"tck":"EUSA8 Curncy","value":0,0043}, 
    "u9":{"tck":"EUSA9 Curncy","value":0,0042} 
    }'; 

$phpArray = json_decode($data, true); 

foreach ($phpArray as $key => $value) { 
    foreach ($value as $entry) { 
     $req = $bdd->prepare('INSERT INTO param (tck, value) VALUES(:tck, :value)'); 
     $req->execute(array(
      ':tck'=>$entry->tck, 
      ':value'=>$entry->value 
     )); 
    } 
} 
?> 

看来我在一个错误在我的第一个foreach循环线26 ...

Warning: Invalid argument supplied for foreach() in C:\wamp\www\finance\jsontomysql.php on line 26 

编辑：

的问题必须是VBA和PHP之间，因为，当我这样做时，SQL数据库是孔U pdated：

<?php 
try 
{ 
    $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', ''); 
} 
catch(Exception $e) 
{ 
    die('Erreur : '.$e->getMessage()); 
} 
//$phpArray = json_decode($_POST['field1']); 

$data = '{"u1":{"tck":"EUSA1 Curncy","value":0.005},"u2":{"tck":"EUSA2 Curncy","value":0.0049},"u3":{"tck":"EUSA3 Curncy","value":0.0048},"u4":{"tck":"EUSA4 Curncy","value":0.0047},"u5":{"tck":"EUSA5 Curncy","value":0.0046},"u6":{"tck":"EUSA6 Curncy","value":0.0045},"u7":{"tck":"EUSA7 Curncy","value":0.0044},"u8":{"tck":"EUSA8 Curncy","value":0.0043},"u9":{"tck":"EUSA9 Curncy","value":0.0042}}'; 
var_dump($data); 

$phpArray = json_decode($data, true); 
var_dump($phpArray); 

foreach ($phpArray as $u) { 
     $req = $bdd->prepare('INSERT INTO param (tck, value) VALUES(:tck, :value)'); 
     $req->execute(array(
      ':tck'=>$u['tck'], 
      ':value'=>$u['value'] 
     )); 
} 
?> 

这是什么问题？ 谢谢

Answer 1

我终于找到:-)。问题在于发送对象和用于我的数字的分隔符。

将“，”转换为“。”后。在我的Excel电子表格中，我必须找出发送的vba对象问题。

工作代码如下：

的VBA代码：（将数据转换成JSON字符串和发送字符串到我的PHP页面）

Sub sendjson() 
Dim i As Integer 
Dim j As Integer 
Dim data As String 
Worksheets("param").Range("D1").Select 

data = "{" 

j = 2 
Do While Not (IsEmpty(ActiveSheet.Cells(j, 4))) 
    j = j + 1 
Loop 

i = 2 
Do While Not (IsEmpty(ActiveSheet.Cells(i, 4))) 
    If i < j - 1 Then 
    data = data + ActiveSheet.Cells(i, 4) + "," 
    Else 
    data = data + ActiveSheet.Cells(i, 4) + "}" 
    End If 
    i = i + 1 
Loop 
Worksheets("param").Range("E1").Value = data 

'data --> php 
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 
    serverURL = "http://localhost/finance/jsontomysql.php" 
    objHTTP.Open "POST", serverURL, False 
    objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 
    objHTTP.send ("field1=" & data) 
Set objHTTP = Nothing 
End Sub 

我PHP代码& MySQL查询：

<?php 
try 
{ 
    $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', ''); 
} 
catch(Exception $e) 
{ 
    die('Erreur : '.$e->getMessage()); 
} 
// $file = fopen("test.txt","w"); 
// echo fwrite($file,$_POST['field1']); 
// fclose($file); 
$data = $_POST['field1']; 
$phpArray = json_decode($data, true); 
foreach ($phpArray as $u) { 
     $req = $bdd->prepare('INSERT INTO param (tck, value) VALUES(:tck, :value)'); 
     $req->execute(array(
      ':tck'=>$u['tck'], 
      ':value'=>$u['value'] 
     )); 
} 
?> 

为了确保字符串已被PHP代码接收，我将结果写入.txt文件（=我的代码中已注释的部分）。

现在，MySQL数据库很好地插入数据库中的值。

谢谢大家的帮助。

Answer 2

要调用您发布field1领域，因此该行很可能必须是这样的：

$data = json_decode($_POST['field1']); 

你也应该将你准备的语句出循环;你只需要准备一次。

Answer 3

你看过json_decode（$ json）实际上在输出什么吗？你是否将数据以一种您期望能够像尝试尝试的方式迭代的形式获取？

只要尝试做一个print_r（$ json）;作为快速验证来调试输出。

它会出现json_decode失败。在我的测试中，这是因为你的'价值'没有被引用。

实施例：

<?php 

$data = '{"u1":{"tck":"EUSA1 Curncy","value":"0,005"},"u2":{"tck":"EUSA2 Curncy","value":"0,0049"},"u3":{"tck":"EUSA3 Curncy","value":"0,0048"},"u4":{"tck":"EUSA4 Curncy","value":"0,0047"},"u5":{"tck":"EUSA5 Curncy","value":"0,0046"},"u6":{"tck":"EUSA6 Curncy","value":"0,0045"}}'; 

$phpArray = json_decode($data, true); 

foreach ($phpArray as $key => $value) { 
    print "$key\n"; 
    print "$value[tck]\n"; 
    print "$value[value]\n"; 
    print "\n\n"; 
} 

?> 

输出：

marks-mac-pro:~ mstanislav$ php data.php 
u1 
EUSA1 Curncy 
0,005 


u2 
EUSA2 Curncy 
0,0049 


u3 
EUSA3 Curncy 
0,0048 


u4 
EUSA4 Curncy 
0,0047 


u5 
EUSA5 Curncy 
0,0046 


u6 
EUSA6 Curncy 
0,0045