Import chuỗi JSON vào Excel

Trước tiên ta cần hiểu sơ qua chuỗi JSON là gì nhé!
Thông thường lập trình web người ta sẽ liên kết dữ liệu vào một hệ quản trị cơ sở dữ liệu. Hệ quản trị này phải được cài đặt trước (MySQL chẳng hạn)
Với những CSDL dạng nhỏ, để tránh phiền phức về việc cài đặt chương trình, người ta muốn "ăn ngay" bằng cách chuyển đổi CSDL thành dạng chuỗi theo cấu trúc nào đó. Khi download về máy tính, ta lại giải mã cấu trúc chuỗi này để nhận được dữ liệu hoàn chỉnh
Chuỗi JSON chính là cái chuỗi có cấu trúc đặt biệt như tôi nói ở trên. Thêm nữa là hiện nay JSON hỗ trợ hầu hết các ngôn ngữ lập trình (vì thực chất cấu trúc này chỉ là dạng chuỗi)
----------------------------------
Tôi giả định rằng ông lập trình viên web giao cho tôi đường link như sau:
http://warehouse.bigapptech.com.vn/api/material/get
Ông ấy nói rằng đường link này sẽ trả về một chuỗi JSON. Tôi gõ link trên vào trình duyệt và nhận được kết quả

Capture1.JPG

hoặc:

Capture2.JPG


tùy theo cách hiển thị của trình duyệt (Firefox cho phép hiển thị theo 2 kiểu)
-------------------
Giờ tôi sẽ tiến hành viết code để 1> Download chuỗi JSON, 2> Biến đổi chuỗi JSON thành dữ liệu trên Excel

Mã:
Public Const URL = "http://warehouse.bigapptech.com.vn/api/material/get"
Dim data, total
Function DownloadJSON(ByVal sURL As String) As Object
  Dim objHTTP   As Object
  Dim objScript As Object
  Set objScript = CreateObject("MSScriptControl.ScriptControl")
  objScript.Language = "JScript"
  Set objHTTP = CreateObject("MSXML2.XMLHTTP")
  On Error Resume Next
  With objHTTP
    .Open "GET", sURL, False
    .send
    Set DownloadJSON = objScript.Eval("(" & .responseText & ")")
    .abort
  End With
  Set objHTTP = Nothing: Set objScript = Nothing
End Function
Function GetBigAppTech(ByVal JSON As Object)
  Dim jsData    As Object
  Dim jsItem    As Object
  Dim lCount    As Long
  Dim idx       As Long
  On Error Resume Next
  If JSON Is Nothing Then Exit Function
  Set jsData = JSON.data
  lCount = JSON.total
  ReDim aRes(1 To lCount, 1 To 3)
  For Each jsItem In jsData
    idx = idx + 1
    aRes(idx, 1) = jsItem.material_id
    aRes(idx, 2) = jsItem.material_name
    aRes(idx, 3) = jsItem.material_inventory
  Next
  If idx Then GetBigAppTech = aRes
  Set jsData = Nothing: Set jsItem = Nothing
End Function
Sub Test()
  Dim aRes, JSON As Object
  Set JSON = DownloadJSON(URL)
  If JSON Is Nothing Then
    MsgBox "Please check the status of Network!"
    Exit Sub
  End If
  aRes = GetBigAppTech(JSON)
  If IsArray(aRes) Then
    Range("A1:C1").Resize(UBound(aRes)).Value = aRes
    MsgBox "Done!"
  End If
End Sub
Code chạy tốt nhưng có 3 vấn đề xuất hiện:
1> Các bạn để ý câu lệnh Set jsData = JSON.data, ngay khi gõ xong thì chắc chắn chữ data sẽ bị biến thành Data (viết HOA ký tự "D"). Ác cái code này có phân biệt HOA thường nên sẽ bị lỗi (dòng thứ 2 trong kết quả trên trình duyệt là data chứ không phải Data). Tôi đang chơi "ăn gian" bằng cách khai báo biến data trên đầu code (mà chẳng để làm gì)
2> Cũng câu lệnh trên Set jsData = JSON.data, ý tôi là muốn lấy dữ liệu từ nhánh data. Trong trường hợp tôi muốn viết code theo cách tổng quát hơn:
Mã:
Function GetBigAppTech(ByVal JSON As Object, byVal sProperty as String)
....................
End Function
thì cái đối số sProperty trong hàm sẽ được truyền như thế nào cho câu lệnh trên (ở đây tôi muốn truyền sProperty = "data")
3> Tôi có câu lệnh:
Mã:
 lCount = JSON.total
  ReDim aRes(1 To lCount, 1 To 3)
là vì may mắn chuỗi JSON trả về có đoạn total: 5 nên từ đây tôi biết được dữ liệu có 5 dòng. Đặt trường hợp chuỗi JSON này không có dòng total: 5 như trên thì bằng cách nào tôi biết được phải khai báo chiều thứ nhất cho mảng aRes bao nhiêu là đủ?
--------------------------
Đang tập tành nên còn nhiều thứ chưa biết nên nhận được sự góp ý từ các bạn. Xin cảm ơn
(thật ra trên mạng có cả 1 thư việc viết sẵn để xử lý nhưng dài quá, trong khi tôi muốn tự mình xây dựng lấy ứng dụng)
 

Bình luận

#62
Cũng cùng chủ đề nhưng là câu hỏi ngược lại: Có cách nào chuyển 1 table thành chuỗi JSON không?
Đương nhiên, chuyện xử lý text thông thường mình làm được (mình đã làm bằng cách xem cấu trúc JSON rồi bắt chước theo). Vấn đề ở đây là mình muốn biết JavaScript có làm điều ngược lại được không
Mình đang hy vọng: nếu dùng công cụ chuyên nghiệp thì tốc độ xử lý phải nhanh hơn
???
Anh sống sao mà hỏi mấy bữa không ai vào chơi vậy cà ?
Mình có cái thắc mắc là việc chuyển từ mảng thành 1 chuỗi VBA đâu có yếu ? tại sao cần có công cụ nào khác hổ trợ vậy anh ?
Và có thể cho em xem cái đoạn anh làm bằng VBA với, giấu đâu vui.
 
#63
Trước tiên ta cần hiểu sơ qua chuỗi JSON là gì nhé!
Thông thường lập trình web người ta sẽ liên kết dữ liệu vào một hệ quản trị cơ sở dữ liệu. Hệ quản trị này phải được cài đặt trước (MySQL chẳng hạn)
Với những CSDL dạng nhỏ, để tránh phiền phức về việc cài đặt chương trình, người ta muốn "ăn ngay" bằng cách chuyển đổi CSDL thành dạng chuỗi theo cấu trúc nào đó. Khi download về máy tính, ta lại giải mã cấu trúc chuỗi này để nhận được dữ liệu hoàn chỉnh
Chuỗi JSON chính là cái chuỗi có cấu trúc đặt biệt như tôi nói ở trên. Thêm nữa là hiện nay JSON hỗ trợ hầu hết các ngôn ngữ lập trình (vì thực chất cấu trúc này chỉ là dạng chuỗi)
----------------------------------
Tôi giả định rằng ông lập trình viên web giao cho tôi đường link như sau:
http://warehouse.bigapptech.com.vn/api/material/get
Ông ấy nói rằng đường link này sẽ trả về một chuỗi JSON. Tôi gõ link trên vào trình duyệt và nhận được kết quả

View attachment 196700

hoặc:

View attachment 196701


tùy theo cách hiển thị của trình duyệt (Firefox cho phép hiển thị theo 2 kiểu)
-------------------
Giờ tôi sẽ tiến hành viết code để 1> Download chuỗi JSON, 2> Biến đổi chuỗi JSON thành dữ liệu trên Excel

Mã:
Public Const URL = "http://warehouse.bigapptech.com.vn/api/material/get"
Dim data, total
Function DownloadJSON(ByVal sURL As String) As Object
  Dim objHTTP   As Object
  Dim objScript As Object
  Set objScript = CreateObject("MSScriptControl.ScriptControl")
  objScript.Language = "JScript"
  Set objHTTP = CreateObject("MSXML2.XMLHTTP")
  On Error Resume Next
  With objHTTP
    .Open "GET", sURL, False
    .send
    Set DownloadJSON = objScript.Eval("(" & .responseText & ")")
    .abort
  End With
  Set objHTTP = Nothing: Set objScript = Nothing
End Function
Function GetBigAppTech(ByVal JSON As Object)
  Dim jsData    As Object
  Dim jsItem    As Object
  Dim lCount    As Long
  Dim idx       As Long
  On Error Resume Next
  If JSON Is Nothing Then Exit Function
  Set jsData = JSON.data
  lCount = JSON.total
  ReDim aRes(1 To lCount, 1 To 3)
  For Each jsItem In jsData
    idx = idx + 1
    aRes(idx, 1) = jsItem.material_id
    aRes(idx, 2) = jsItem.material_name
    aRes(idx, 3) = jsItem.material_inventory
  Next
  If idx Then GetBigAppTech = aRes
  Set jsData = Nothing: Set jsItem = Nothing
End Function
Sub Test()
  Dim aRes, JSON As Object
  Set JSON = DownloadJSON(URL)
  If JSON Is Nothing Then
    MsgBox "Please check the status of Network!"
    Exit Sub
  End If
  aRes = GetBigAppTech(JSON)
  If IsArray(aRes) Then
    Range("A1:C1").Resize(UBound(aRes)).Value = aRes
    MsgBox "Done!"
  End If
End Sub
Code chạy tốt nhưng có 3 vấn đề xuất hiện:
1> Các bạn để ý câu lệnh Set jsData = JSON.data, ngay khi gõ xong thì chắc chắn chữ data sẽ bị biến thành Data (viết HOA ký tự "D"). Ác cái code này có phân biệt HOA thường nên sẽ bị lỗi (dòng thứ 2 trong kết quả trên trình duyệt là data chứ không phải Data). Tôi đang chơi "ăn gian" bằng cách khai báo biến data trên đầu code (mà chẳng để làm gì)
2> Cũng câu lệnh trên Set jsData = JSON.data, ý tôi là muốn lấy dữ liệu từ nhánh data. Trong trường hợp tôi muốn viết code theo cách tổng quát hơn:
Mã:
Function GetBigAppTech(ByVal JSON As Object, byVal sProperty as String)
....................
End Function
thì cái đối số sProperty trong hàm sẽ được truyền như thế nào cho câu lệnh trên (ở đây tôi muốn truyền sProperty = "data")
3> Tôi có câu lệnh:
Mã:
lCount = JSON.total
  ReDim aRes(1 To lCount, 1 To 3)
là vì may mắn chuỗi JSON trả về có đoạn total: 5 nên từ đây tôi biết được dữ liệu có 5 dòng. Đặt trường hợp chuỗi JSON này không có dòng total: 5 như trên thì bằng cách nào tôi biết được phải khai báo chiều thứ nhất cho mảng aRes bao nhiêu là đủ?
--------------------------
Đang tập tành nên còn nhiều thứ chưa biết nên nhận được sự góp ý từ các bạn. Xin cảm ơn
(thật ra trên mạng có cả 1 thư việc viết sẵn để xử lý nhưng dài quá, trong khi tôi muốn tự mình xây dựng lấy ứng dụng)
Tốc độ thì mình không dám bàn, mình chưa đủ khả năng tối ưu code của anh.
Ở đây em chỉ ngẫu hứng làm chơi 1 cách dùng gọi hàm Jscript 1 lần duy nhất làm hết việc từ A-Z. Cái kết quả mà nó trả về ta chỉ Convert lại thành mảng trong VBA. Cái khó khăn ở đây là khái niệm và cách dùng mảng trong Javascript khác quá xa so với VBA, và đương nhiên không có cách gì chuyển đổi trực tiếp qua lại giữa mảng Javascript và mảng VBA.
Ta sẽ tạo ra 1 hàm trong Jscript

Mã:
objScript.AddCode "function parseData(tex) {" & _
    "var dict = new ActiveXObject('Scripting.Dictionary');" & _
    "var jdata = eval('(' + tex + ')');" & _
    "var payload = jdata.payload;" & _
    "var lcount = payload.length;" & _
    "var indez = 0;" & _
    "for(indez = 0; indez < lcount; indez++){" & _
    "var row = payload[indez];" & _
    "dict.add(indez, " & _
    "{m_displayName: row.displayName, " & _
    "m_purchaseDescription: row.valassisOfferId, " & _
    "m_savingsValueStatement: row.purchaseDescription, " & _
    "m_displayValue: row.displayValue, " & _
    "m_valassisOfferId: row.valassisOfferId" & _
    "});" & _
    "}" & _
    "return dict.items();} "
ta sẽ gọi đến hàm này

Mã:
Public Function parseData(jText As String) As Variant
parseData = objScript.Run("parseData", jText)
End Function
Mã:
Dim jRow As Object, arr
arr = parseData(jsString)
  ReDim ares(1 To UBound(arr) + 1, 1 To 6)
  For lRow = 0 To UBound(arr) Step 1
      Set jRow = arr(lRow)
      ares(lRow + 1, 2) = jRow.m_displayName
      ares(lRow + 1, 3) = jRow.m_purchaseDescription
      ares(lRow + 1, 4) = jRow.m_savingsValueStatement
      ares(lRow + 1, 5) = jRow.m_displayValue
      ares(lRow + 1, 6) = jRow.m_valassisOfferId
  Next
Nhanh chậm em không chắc, chỉ ngẫu hứng làm cho vui thôi.
Anh cho em hỏi với cái này có thể ứng dụng vào khi chuyển từ File xml và txt sang excel theo điều kiện dạng bảng được không Anh !
 
Lần chỉnh sửa cuối:
#64
Chủ đề quá hay ạ,
Giờ e có 1 cơ sở dữ liệu chuẩn rồi, muốn biến nó thành JSON lên 1 trang web thì phải làm sao anh?
(Mục đích để cho dữ liệu nó nhẹ đi, ko phải lưu trên file cho nặng nề)
 
#65
Chủ đề quá hay ạ,
Giờ e có 1 cơ sở dữ liệu chuẩn rồi, muốn biến nó thành JSON lên 1 trang web thì phải làm sao anh?
(Mục đích để cho dữ liệu nó nhẹ đi, ko phải lưu trên file cho nặng nề)
Đương nhiên được! Nhưng theo tôi được biết thì hành động download hay upload đều phải được sự cho phép của người quản trị mạng, họ sẽ cung cấp cho bạn 1 api giống như đường link trong bài này là của người bạn cung cấp cho. Vậy nếu bạn muốn, bạn hỏi lại bên quản trị trang web xem sao
Riêng phần biến dữ liệu thành JSON thì đơn giản rồi, chỉ là xử lý chuỗi thôi
 
#66
Đương nhiên được! Nhưng theo tôi được biết thì hành động download hay upload đều phải được sự cho phép của người quản trị mạng, họ sẽ cung cấp cho bạn 1 api giống như đường link trong bài này là của người bạn cung cấp cho. Vậy nếu bạn muốn, bạn hỏi lại bên quản trị trang web xem sao
Riêng phần biến dữ liệu thành JSON thì đơn giản rồi, chỉ là xử lý chuỗi thôi
Trước mắt cứ đưa lên trang web tự tạo của máy mình cũng được anh.
Rồi thì các máy khác trên mạng LAN có thể join vào đấy để lấy.
 
Top