JSON [Javascript Object Notation] là định dạng trao đổi dữ liệu được sử dụng nhiều nhất hiện nay. Microsoft Excel không có hỗ trợ tích hợp để nhập JSON sang excel hoặc xuất dữ liệu excel sang JSON
VBA-JSON là một thư viện tuyệt vời để phân tích cú pháp JSON trong VBA. Hãy xem cách xử lý JSON trong Excel VBA. Nếu bạn chưa quen với JSON thì hãy đọc hướng dẫn về JSON cho người mới bắt đầu
điều kiện tiên quyết
- Lưu tệp excel của bạn dưới dạng sổ làm việc đã bật Macro [Tham khảo ảnh chụp màn hình bên dưới]
- Bật macro nếu chúng chưa được bật. Bạn có thể bật nó bằng cách nhấp vào tệp > tùy chọn > Trung tâm tin cậy > Cài đặt trung tâm tin cậy > Bật tất cả macro
Bắt đầu
- Tải xuống VBA JSON phiên bản mới nhất từ đây
- Giải nén ra, mở trình soạn thảo mã VBA trong excel [Alt + F11] và nhập thư viện như trong gif bên dưới
- Thêm tham chiếu đến thời gian chạy tập lệnh của Microsoft. [Công cụ > tài liệu tham khảo > chọn]
- Thêm tham chiếu vào MSXML [Công cụ > tham chiếu]
Chọn phiên bản phù hợp dựa trên PC của bạn
1. Microsoft XML, phiên bản 3. 0
2. Microsoft XML, phiên bản 4. 0 [nếu bạn đã cài đặt MSXML 4. 0 riêng biệt]
3. Microsoft XML, phiên bản 5. 0 [nếu bạn đã cài đặt Office 2003 – 2007 cung cấp MSXML 5. 0 cho các ứng dụng Microsoft Office]
4. Microsoft XML, phiên bản 6. 0 cho các phiên bản mới nhất của MS Office
Nhập JSON vào Excel
Thư viện này cung cấp một phương thức đơn giản ParseJson
để phân tích chuỗi JSON thành một đối tượng từ điển có thể được sử dụng để trích xuất dữ liệu. Hãy xem một ví dụ
Tôi đang sử dụng dữ liệu giả mạo từ http. //jsonplaceholder. đánh máy. com/ là dịch vụ API có dữ liệu Json giả
Chúng tôi sẽ lấy dữ liệu người dùng từ http. //jsonplaceholder. đánh máy. com/users bằng cách thực hiện yêu cầu GET phản hồi với dữ liệu Json
Đọc thêm về yêu cầu GET trong VBA tại đây
Tiếp theo, chúng tôi sẽ phân tích cú pháp Json đó và nhập nó vào excel. Mã để nhập dữ liệu trông như thế này
Public Sub exceljson[] Dim http As Object, JSON As Object, i As Integer Set http = CreateObject["MSXML2.XMLHTTP"] http.Open "GET", "//jsonplaceholder.typicode.com/users", False http.Send Set JSON = ParseJson[http.responseText] i = 2 For Each Item In JSON Sheets[1].Cells[i, 1].Value = Item["id"] Sheets[1].Cells[i, 2].Value = Item["name"] Sheets[1].Cells[i, 3].Value = Item["username"] Sheets[1].Cells[i, 4].Value = Item["email"] Sheets[1].Cells[i, 5].Value = Item["address"]["city"] Sheets[1].Cells[i, 6].Value = Item["phone"] Sheets[1].Cells[i, 7].Value = Item["website"] Sheets[1].Cells[i, 8].Value = Item["company"]["name"] i = i + 1 Next MsgBox ["complete"] End Sub
giải thích mã
- Đầu tiên, xác định JSON là một đối tượng và thực hiện yêu cầu GET tới JSON API
- Dữ liệu JSON nhận được trong phản hồi được phân tích cú pháp bằng cách chuyển nó vào phương thức
ParseJson
- dữ liệu được phân tích cú pháp được chuyển đổi thành một bộ từ điển
- Lặp lại bộ sưu tập để lấy thông tin chi tiết của từng người dùng và đặt giá trị của nó cho trang tính đầu tiên
Chạy mã trên trông giống như gif bên dưới
Đọc JSON từ một tệp
Trong ví dụ tương tự ở trên, nếu bạn muốn đọc dữ liệu JSON từ tệp cục bộ thì bạn có thể sử dụng FileSystemObject
để đọc tất cả văn bản trong tệp rồi chuyển nó sang phương thức ParseJson
Dim FSO As New FileSystemObject Dim JsonTS As TextStream Set JsonTS = FSO.OpenTextFile["example.json", ForReading] JsonText = JsonTS.ReadAll JsonTS.Close Set JSON = ParseJson[JsonText]
Xuất Excel sang JSON
VBA-JSON cung cấp một phương thức khác
Dim FSO As New FileSystemObject Dim JsonTS As TextStream Set JsonTS = FSO.OpenTextFile["example.json", ForReading] JsonText = JsonTS.ReadAll JsonTS.Close Set JSON = ParseJson[JsonText]0 có thể được sử dụng để chuyển đổi dữ liệu excel thành JSON. Đây là một ví dụ
Dữ liệu mẫu với Tên, Điện thoại và Email có trong trang thứ hai. Hãy chuyển đổi nó thành JSON
Mã này trông giống như
Public Sub exceltojson[] Dim rng As Range, items As New Collection, myitem As New Dictionary, i As Integer, cell As Variant set rng = Range["A2:A3"] 'Set rng = Range[Sheets[2].Range["A2"], Sheets[2].Range["A2"].End[xlDown]] use this for dynamic range i = 0 For Each cell In rng Debug.Print [cell.Value] myitem["name"] = cell.Value myitem["email"] = cell.Offset[0, 1].Value myitem["phone"] = cell.Offset[0, 2].Value items.Add myitem Set myitem = Nothing i = i + 1 Next Sheets[2].Range["A4"].Value = ConvertToJson[items, Whitespace:=2] End Sub
Giải thích mã
- Đầu tiên, xác định
Dim FSO As New FileSystemObject Dim JsonTS As TextStream Set JsonTS = FSO.OpenTextFile["example.json", ForReading] JsonText = JsonTS.ReadAll JsonTS.Close Set JSON = ParseJson[JsonText]
1 là phạm vi và đặt nó thành phạm vi dữ liệu - Phương thức
Dim FSO As New FileSystemObject Dim JsonTS As TextStream Set JsonTS = FSO.OpenTextFile["example.json", ForReading] JsonText = JsonTS.ReadAll JsonTS.Close Set JSON = ParseJson[JsonText]
0 lấy một tập hợp từ điển hoặc mảng làm tham số. Vì vậy, chúng ta nên chuyển dữ liệu của mình dưới dạng bộ sưu tập - Từ điển là một đối tượng có các khóa và giá trị giống như JSON nhưng không hỗ trợ nhiều mục như mảng hoặc bộ sưu tập, vì vậy chúng tôi tạo từ điển cho từng mục và đẩy nó vào một mảng hoặc bộ sưu tập
- Xác định một từ điển và một bộ sưu tập, lặp qua phạm vi và đặt dữ liệu của từng hàng thành
Dim FSO As New FileSystemObject Dim JsonTS As TextStream Set JsonTS = FSO.OpenTextFile["example.json", ForReading] JsonText = JsonTS.ReadAll JsonTS.Close Set JSON = ParseJson[JsonText]
3 - Đẩy
Dim FSO As New FileSystemObject Dim JsonTS As TextStream Set JsonTS = FSO.OpenTextFile["example.json", ForReading] JsonText = JsonTS.ReadAll JsonTS.Close Set JSON = ParseJson[JsonText]
3 vào bộ sưu tập và đặt thành không có gì, bởi vì chúng tôi đang sử dụng cùng một từ điển để thêm dữ liệu của hàng tiếp theo và đẩy lại vào bộ sưu tập
Cuối cùng chuyển bộ sưu tập
Dim FSO As New FileSystemObject Dim JsonTS As TextStream Set JsonTS = FSO.OpenTextFile["example.json", ForReading] JsonText = JsonTS.ReadAll JsonTS.Close Set JSON = ParseJson[JsonText]5 sang phương thức
Dim FSO As New FileSystemObject Dim JsonTS As TextStream Set JsonTS = FSO.OpenTextFile["example.json", ForReading] JsonText = JsonTS.ReadAll JsonTS.Close Set JSON = ParseJson[JsonText]0 trả về chuỗi JSON
Chạy mã trên trông giống như gif bên dưới
Xuất tệp Excel sang tệp JSON
Trong ví dụ tương tự ở trên, nếu bạn muốn xuất dữ liệu excel sang tệp JSON thì có thể thực hiện bằng cách mở tệp để xuất bằng cách chỉ định đường dẫn của tệp và in dữ liệu trong đó. Mã mẫu bên dưới, Chạy mã này sẽ lưu tệp JSON trong thư mục của sổ làm việc hiện tại
Public Sub exceltojsonfile[] Dim rng As Range, items As New Collection, myitem As New Dictionary, i As Integer, cell As Variant, myfile As String Set rng = Range["A2:A3"] 'Set rng = Range[Sheets[2].Range["A2"], Sheets[2].Range["A2"].End[xlDown]] use this for dynamic range i = 0 For Each cell In rng Debug.Print [cell.Value] myitem["name"] = cell.Value myitem["email"] = cell.Offset[0, 1].Value myitem["phone"] = cell.Offset[0, 2].Value items.Add myitem Set myitem = Nothing i = i + 1 Next myfile = Application.ActiveWorkbook.Path & "\data.json" Open myfile For Output As #1 Print #1, ConvertToJson[items, Whitespace:=2] Close #1 End Sub
Xuất Excel sang JSON lồng nhau
Mã trên có thể được sửa đổi một chút để lấy JSON lồng nhau làm đầu ra. Chỉ cần thêm từ điển vào từ điển khác để nó tạo JSON lồng nhau. mã trông như thế này
Public Sub exceltonestedjson[] Dim rng As Range, items As New Collection, myitem As New Dictionary, subitem As New Dictionary, i As Integer, cell As Variant Set rng = Range["A2:A3"] 'Set rng = Range[Sheets[2].Range["A2"], Sheets[2].Range["A2"].End[xlDown]] use this for dynamic range i = 0 For Each cell In rng Debug.Print [cell.Value] myitem["name"] = cell.Value myitem["email"] = cell.Offset[0, 1].Value myitem["phone"] = cell.Offset[0, 2].Value subitem["country"] = cell.Offset[0, 3].Value myitem.Add "location", subitem items.Add myitem Set myitem = Nothing Set subitem = Nothing i = i + 1 Next Sheets[2].Range["A4"].Value = ConvertToJson[items, Whitespace:=2] End Sub
Chạy mã trên trông giống như hình ảnh dưới đây
Sử dụng mảng chuỗi và đối tượng trong JSON
Một trong những độc giả của chúng tôi đã hỏi tôi cách sử dụng một mảng các chuỗi và đối tượng bên trong JSON
Đây là cách thực hiện
Public Sub exceltojson[] Dim rng As Range, items As New Collection, myitem As New Dictionary, i As Integer, cell As Variant, objectContainer As New Dictionary, arrayContainer As New Collection Dim j As Integer 'Set rng = Range["A2:A3"] Set rng = Range[Sheets[1].Range["A2"], Sheets[1].Range["A2"].End[xlDown]] 'use this for dynamic range i = 0 For Each cell In rng myitem["id"] = cell.Value myitem["name"] = cell.Offset[0, 1].Value 'tags tagsString = cell.Offset[0, 2].Value tagsCollection = getCollectionFromString[tagsString] myitem.Add "tags", tagsCollection 'ingredients ingredientsString = cell.Offset[0, 3].Value weightsString = cell.Offset[0, 4].Value ingredientsUnit = cell.Offset[0, 5].Value ingredientsCollection = getCollectionFromString[ingredientsString] weightsCollection = getCollectionFromString[weightsString] j = 0 For Each ingredient In ingredientsCollection objectContainer["ingredientnaam"] = ingredient objectContainer["eenheid"] = ingredientsUnit objectContainer["hoeveelheid"] = weightsCollection[j] arrayContainer.Add objectContainer Set objectContainer = Nothing j = j + 1 Next myitem.Add "ingredienten", arrayContainer 'Reset values Set arrayContainer = Nothing j = 0 items.Add myitem Set myitem = Nothing i = i + 1 Next Sheets[1].Range["A6"].Value = ConvertToJson[items, Whitespace:=2] End Sub Function getCollectionFromString[val] getCollectionFromString = Split[val, ", "] End Function
Chạy mã trên trông giống như hình ảnh dưới đây
kết thúc
Đọc tài liệu chính thức về VBA-JSON tại đây và sử dụng VBA-Dictionary for Mac Support
Những bài viết liên quan
- Hoàn thành hướng dẫn JSON tại đây – JSON cho người mới bắt đầu
- Xử lý CSV trong VBA
Nếu bạn có bất kỳ câu hỏi hoặc phản hồi nào, hãy bình luận bên dưới và vui lòng sử dụng CodingisLove Bin để chia sẻ mã của bạn
Nhận thông báo khi có bài đăng mới bằng cách nhấp vào
Cần giúp đỡ?
Để trống trường này nếu bạn là người.rajith kumar Một CA- do giáo dục, lập trình viên tự học bằng niềm đam mê, thích khám phá các công nghệ mới và tin tưởng vào việc học bằng cách làmLàm cách nào để xuất dữ liệu từ JSON sang Excel trong C#?
Làm cách nào để chuyển đổi JSON sang XLSX?
Làm cách nào để trích xuất dữ liệu từ JSON?
Excel có thể đọc dữ liệu JSON không?