json - 解析 JSON 时 Unicode 字符不正确

Question

这是 JSON 示例的链接 MYSAMPLEJSON

这是样本

[{"{\"Title\":\"Close-Up\",\"Year\":\"1990\",\"Rated\":\"NOT RATED\",\"Released\":\"30 Oct 1991\",\"Runtime\":\"98 min\",\"Genre\":\"Documentary, Biography, Crime\",\"Director\":\"Abbas Kiarostami\",\"Writer\":\"Abbas Kiarostami\",\"Actors\":\"Matthew Barney, Björk, Mohsen Makhmalbaf, Abolfazl Ahankhah, Mehrdad Ahankhah\",\"Plot\":\"The true story of Hossain Sabzian that impersonated the director Mohsen Makhmalbaf to convince a family they would star in his so-called new film.\",\"Language\":\"Persian, Azerbaijani\",\"Country\":\"Iran\",\"Awards\":\"2 wins.\",\"Poster\":\"https://m.media-amazon.com/images/M/MV5BMzE4Mjc0MjI1N15BMl5BanBnXkFtZTcwNjI3MzEzMw@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"8.3/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"88%\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"8.3\",\"imdbVotes\":\"11,546\",\"imdbID\":\"tt0100234\",\"Type\":\"movie\",\"DVD\":\"19 Feb 2002\",\"BoxOffice\":\"N/A\",\"Production\":\"Zeitgeist Films\",\"Website\":\"http://www.zeitgeistfilm.com/current/closeup/closeup.html\",\"Response\":\"True\"}":true},{"{\"Title\":\"Life on Earth\",\"Year\":\"1998\",\"Rated\":\"N/A\",\"Released\":\"09 Jun 1999\",\"Runtime\":\"61 min\",\"Genre\":\"Comedy, Drama\",\"Director\":\"Abderrahmane Sissako\",\"Writer\":\"Abderrahmane Sissako\",\"Actors\":\"Abderrahmane Sissako, Nana Baby, Mohamed Sissako, Bourama Coulibaly\",\"Plot\":\"In the last days of 1999, after a few shots of a French supermarket, abundant in food and color, we hear Dramane compose a letter home to his father in Mali whom he then visits in the ...\",\"Language\":\"French, Bambara\",\"Country\":\"Mali, Mauritania, France\",\"Awards\":\"10 wins.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BNDAxMzg2MDE1M15BMl5BanBnXkFtZTcwMzU3MDMyMQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"297\",\"imdbID\":\"tt0157160\",\"Type\":\"movie\",\"DVD\":\"N/A\",\"BoxOffice\":\"N/A\",\"Production\":\"N/A\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Waiting for Happiness\",\"Year\":\"2002\",\"Rated\":\"N/A\",\"Released\":\"15 Jan 2003\",\"Runtime\":\"90 min\",\"Genre\":\"Drama, Music\",\"Director\":\"Abderrahmane Sissako\",\"Writer\":\"Abderrahmane Sissako\",\"Actors\":\"Khatra Ould Abder Kader, Maata Ould Mohamed Abeid, Mohamed Mahmoud Ould Mohamed, Fatimetou Mint Ahmeda\",\"Plot\":\"The story of two people who cross paths in Nouhadhibou.\",\"Language\":\"French, Hassanya, Mandarin\",\"Country\":\"France, Mauritania\",\"Awards\":\"8 wins & 3 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTI1MzA2NDc3Nl5BMl5BanBnXkFtZTcwNjcxNzY5MQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"76%\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"775\",\"imdbID\":\"tt0308363\",\"Type\":\"movie\",\"DVD\":\"11 Jun 2007\",\"BoxOffice\":\"N/A\",\"Production\":\"New Yorker Films\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Delbaran\",\"Year\":\"2001\",\"Rated\":\"N/A\",\"Released\":\"27 Mar 2002\",\"Runtime\":\"96 min\",\"Genre\":\"Drama\",\"Director\":\"Abolfazl Jalili\",\"Writer\":\"Abolfazl Jalili, Reza Saberi (story)\",\"Actors\":\"Kaim Alizadeh, Rahmatollah Ebrahimi, Hossein Hashemian, Ahmad Mahdavi\",\"Plot\":\"Acclaimed director Abolfazl Jalili offers a compassionate story of the young Afghan refugee who lives illegally in Iran. 14-year-old Kaim drifts to the Delbaran crossing on the Afghan-Iran ...\",\"Language\":\"Persian\",\"Country\":\"Iran, Japan\",\"Awards\":\"5 wins & 3 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMjA4OTQyNTQzM15BMl5BanBnXkFtZTcwMTM0MDYxMg@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"60%\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"228\",\"imdbID\":\"tt0293082\",\"Type\":\"movie\",\"DVD\":\"23 Dec 2008\",\"BoxOffice\":\"N/A\",\"Production\":\"Facets\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"A Horrible Way to Die\",\"Year\":\"2010\",\"Rated\":\"R\",\"Released\":\"19 Aug 2011\",\"Runtime\":\"87 min\",\"Genre\":\"Drama, Horror, Thriller\",\"Director\":\"Adam Wingard\",\"Writer\":\"Simon Barrett\",\"Actors\":\"AJ Bowen, Amy Seimetz, Joe Swanberg, Brandon Carroll\",\"Plot\":\"An escaped murderer is in pursuit of his ex-girlfriend, who has fled to start a new life in a small town.\",\"Language\":\"English\",\"Country\":\"USA\",\"Awards\":\"3 wins.\",\"Poster\":\"https://m.media-amazon.com/images/M/MV5BMTAzMjAyMzg2MzheQTJeQWpwZ15BbWU3MDg2NTc1MzY@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"5.3/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"67%\"},{\"Source\":\"Metacritic\",\"Value\":\"52/100\"}],\"Metascore\":\"52\",\"imdbRating\":\"5.3\",\"imdbVotes\":\"3,036\",\"imdbID\":\"tt1617620\",\"Type\":\"movie\",\"DVD\":\"06 Sep 2011\",\"BoxOffice\":\"N/A\",\"Production\":\"Anchor Bay Entertainment\",\"Website\":\"http://ahwtd.blogspot.com/\",\"Response\":\"True\"}":true},{"{\"Title\":\"Towelhead\",\"Year\":\"2007\",\"Rated\":\"R\",\"Released\":\"26 Sep 2008\",\"Runtime\":\"124 min\",\"Genre\":\"Drama\",\"Director\":\"Alan Ball\",\"Writer\":\"Alan Ball (written for the screen by), Alicia Erian (novel)\",\"Actors\":\"Summer Bishil, Chris Messina, Maria Bello, Peter Macdissi\",\"Plot\":\"A young Arab-American girl struggles with her sexual obsession, a bigoted Army reservist and her strict father during the Gulf War.\",\"Language\":\"English, French, Arabic, Spanish\",\"Country\":\"USA\",\"Awards\":\"1 win & 3 nominations.\",\"Poster\":\"https://ia.media-imdb.com/images/M/MV5BMTI3MTY3OTM5MF5BMl5BanBnXkFtZTcwOTQ4MTA3MQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.0/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"49%\"},{\"Source\":\"Metacritic\",\"Value\":\"57/100\"}],\"Metascore\":\"57\",\"imdbRating\":\"7.0\",\"imdbVotes\":\"10,855\",\"imdbID\":\"tt0787523\",\"Type\":\"movie\",\"DVD\":\"30 Dec 2008\",\"BoxOffice\":\"$277,354\",\"Production\":\"Warner Independent\",\"Website\":\"http://www.towelhead-themovie.com/\",\"Response\":\"True\"}":true},{"{\"Title\":\"Moloch\",\"Year\":\"1999\",\"Rated\":\"N/A\",\"Released\":\"13 Oct 1999\",\"Runtime\":\"108 min\",\"Genre\":\"Drama\",\"Director\":\"Aleksandr Sokurov\",\"Writer\":\"Yuriy Arabov, Marina Koreneva\",\"Actors\":\"Elena Rufanova, Leonid Mozgovoy, Irina Sokolova, Yelena Spiridonova\",\"Plot\":\"In 1942, in Bavaria, Eva Braun is alone, when Adolf Hitler arrives with Dr. Josef Goebbels and his wife Magda Goebbels and Martin Bormann to spend a couple of days without talking politics.\",\"Language\":\"German\",\"Country\":\"Russia, Germany, Japan, Italy, France\",\"Awards\":\"7 wins & 8 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTU5ODUzNTE1MV5BMl5BanBnXkFtZTcwNjQzNjgyMQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"1,898\",\"imdbID\":\"tt0199777\",\"Type\":\"movie\",\"DVD\":\"08 Mar 2005\",\"BoxOffice\":\"N/A\",\"Production\":\"N/A\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Mother and Son\",\"Year\":\"1997\",\"Rated\":\"N/A\",\"Released\":\"06 Feb 1998\",\"Runtime\":\"73 min\",\"Genre\":\"Drama\",\"Director\":\"Aleksandr Sokurov\",\"Writer\":\"Yuriy Arabov\",\"Actors\":\"Aleksei Ananishnov, Gudrun Geyer\",\"Plot\":\"A slow and poignant story of love and patience told via a dying mother nursed by her devoted son. The simple narrative is a thread woven among the deeply spiritual images of the countryside...\",\"Language\":\"Russian\",\"Country\":\"Russia, Germany\",\"Awards\":\"4 wins & 4 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTQwNTk4Nzk1MV5BMl5BanBnXkFtZTcwOTc0MDAyMQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.6/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"7.6\",\"imdbVotes\":\"3,143\",\"imdbID\":\"tt0119711\",\"Type\":\"movie\",\"DVD\":\"N/A\",\"BoxOffice\":\"N/A\",\"Production\":\"N/A\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Russian Ark\",\"Year\":\"2002\",\"Rated\":\"NOT RATED\",\"Released\":\"19 Apr 2003\",\"Runtime\":\"99 min\",\"Genre\":\"Drama, Fantasy, History\",\"Director\":\"Aleksandr Sokurov\",\"Writer\":\"Boris Khaimsky (dialogue), Anatoli Nikiforov, Svetlana Proskurina (dialogue), Aleksandr Sokurov (dialogue), Aleksandr Sokurov\",\"Actors\":\"Sergey Dreyden, Mariya Kuznetsova, Leonid Mozgovoy, Mikhail Piotrovsky\",\"Plot\":\"A 19th century French aristocrat, notorious for his scathing memoirs about life in Russia, travels through the Russian State Hermitage Museum and encounters historical figures from the last 200+ years.\",\"Language\":\"Russian, Persian\",\"Country\":\"Russia, Germany, Japan, Canada, Finland, Denmark\",\"Awards\":\"10 wins & 15 nominations.\",\"Poster\":\"https://m.media-amazon.com/images/M/MV5BMjEyNDEwOTU0NV5BMl5BanBnXkFtZTYwNjk0NTk5._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.4/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"89%\"},{\"Source\":\"Metacritic\",\"Value\":\"86/100\"}],\"Metascore\":\"86\",\"imdbRating\":\"7.4\",\"imdbVotes\":\"16,440\",\"imdbID\":\"tt0318034\",\"Type\":\"movie\",\"DVD\":\"09 Sep 2003\",\"BoxOffice\":\"$37,439\",\"Production\":\"Wellspring Media\",\"Website\":\"http://www.russianark.spb.ru/eng/index.html\",\"Response\":\"True\"}":true},{"{\"Title\":\"The Father\",\"Year\":\"1996\",\"Rated\":\"N/A\",\"Released\":\"10 Feb 1999\",\"Runtime\":\"91 min\",\"Genre\":\"Drama\",\"Director\":\"Majid Majidi\",\"Writer\":\"Majid Majidi, Mehdi Shojai\",\"Actors\":\"Hossein Abedini, Mohammad Kasebi, Parivash Nazarieh, Hassan Sadeghi\",\"Plot\":\"Mehrollah is a 14-year-old boy who is forced to find a job to support his family after his father dies. He travels to the southern parts of Iran, looking for work. Upon his return to his hometown, he notices certain changes in his family.\",\"Language\":\"Persian\",\"Country\":\"Iran\",\"Awards\":\"9 wins & 3 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTUxMTI1OTAyOF5BMl5BanBnXkFtZTcwOTc3OTQyMg@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.6/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"7.6\",\"imdbVotes\":\"1,122\",\"imdbID\":\"tt0117315\",\"Type\":\"movie\",\"DVD\":\"27 Jan 2009\",\"BoxOffice\":\"N/A\",\"Production\":\"N/A\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Inside\",\"Year\":\"2007\",\"Rated\":\"R\",\"Released\":\"13 Jun 2007\",\"Runtime\":\"82 min\",\"Genre\":\"Horror\",\"Director\":\"Alexandre Bustillo, Julien Maury\",\"Writer\":\"Julien Maury (by), Alexandre Bustillo (by), Alexandre Bustillo (scenario,  adaptation and dialogue)\",\"Actors\":\"Alysson Paradis, Jean-Baptiste Tabourin, Claude Lulé, Dominique Frot\",\"Plot\":\"Four months after the death of her husband, a woman on the brink of motherhood is tormented in her home by a strange woman who wants her unborn baby.\",\"Language\":\"French\",\"Country\":\"France\",\"Awards\":\"9 wins & 5 nominations.\",\"Poster\":\"https://m.media-amazon.com/images/M/MV5BNzA2OTM3NGMtYTVkZC00ZGM2LWEzNzYtMjk1OGEwYjFhZjA0XkEyXkFqcGdeQXVyMTQxNzMzNDI@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"83%\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"33,241\",\"imdbID\":\"tt0856288\",\"Type\":\"movie\",\"DVD\":\"15 Apr 2008\",\"BoxOffice\":\"N/A\",\"Production\":\"The Weinstein Company\",\"Website\":\"http://www.alinterieur.fr/\",\"Response\":\"True\"}":true},{"{\"Title\":\"Matthew Barney: No Restraint\",\"Year\":\"2006\",\"Rated\":\"N/A\",\"Released\":\"04 Oct 2008\",\"Runtime\":\"72 min\",\"Genre\":\"Documentary\",\"Director\":\"Alison Chernick\",\"Writer\":\"Alison Chernick\",\"Actors\":\"Matthew Barney, Björk\",\"Plot\":\"How does artist Matthew Barney use 45,000 pounds of petroleum jelly, a factory whaling vessel and traditional Japanese rituals to create his latest art project? Barney plowed the waters off...\",\"Language\":\"Japanese, English\",\"Country\":\"USA\",\"Awards\":\"1 nomination.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTk1OTY5OTc3Nl5BMl5BanBnXkFtZTcwMDc2Mjc0MQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.0/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"52%\"},{\"Source\":\"Metacritic\",\"Value\":\"59/100\"}],\"Metascore\":\"59\",\"imdbRating\":\"6.0\",\"imdbVotes\":\"108\",\"imdbID\":\"tt0481574\",\"Type\":\"movie\",\"DVD\":\"01 May 2007\",\"BoxOffice\":\"N/A\",\"Production\":\"IFC First Take\",\"Website\":\"http://www.matthewbarneynorestraint.com/\",\"Response\":\"True\"}":true},{"{\"Title\":\"Vegas: Based on a True Story\",\"Year\":\"2008\",\"Rated\":\"N/A\",\"Released\":\"24 Apr 2009\",\"Runtime\":\"102 min\",\"Genre\":\"Drama\",\"Director\":\"Amir Naderi\",\"Writer\":\"Susan Brennan (screenplay), Bliss Esposito (screenplay), Charlie Lake Keaton (screenplay), Amir Naderi (screenplay)\",\"Actors\":\"Mark Greenfield, Alexis Hart, Nancy La Scala, Zach Thomas\",\"Plot\":\"The film takes place away from the glittering strip of mega casinos, but the greed of Sin City is just as pervasive on the desert outskirts. This is where a happy family learns of a ...\",\"Language\":\"English\",\"Country\":\"USA\",\"Awards\":\"2 wins & 2 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMjE1MzYzMDIyMV5BMl5BanBnXkFtZTgwNjc2NjU5OTE@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.0/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"7.0\",\"imdbVotes\":\"141\",\"imdbID\":\"tt1283971\",\"Type\":\"movie\",\"DVD\":\"06 Jul 2010\",\"BoxOffice\":\"N/A\",\"Production\":\"Alphaville Films\",\"Website\":\"N/A\",\"Response\":\"True\"}":true}]

我已经使用这段代码来解析 json

Sub Test()
Dim ky1         As Collection
Dim ky2         As Collection
Dim ky3         As Dictionary
Dim fso         As Object
Dim jsonFile    As Object
Dim Json        As Object
Dim arr()       As String
Dim jsonText    As String
Dim i           As Long
Dim j           As Long

Set fso = CreateObject("Scripting.FileSystemObject")
Set jsonFile = fso.OpenTextFile(ThisWorkbook.Path & "\Sample.json")
jsonText = jsonFile.ReadAll
Set Json = JsonConverter.ParseJson(jsonText)

Set ky1 = Json("results")
Set ky2 = ky1(1)("allTests")
ReDim arr(1 To ky2.Count)

For i = 1 To ky2.Count
    Set ky3 = ky2(i)
    If InStr(ky3.keys(0), "Movie not found") = 0 Then
        j = j + 1
        arr(j) = ky3.keys(0)
    End If
Next i

Application.ScreenUpdating = False
    Sheets("Sheet1").Range("A1").Resize(UBound(arr)) = Application.Transpose(arr)
Application.ScreenUpdating = True
End Sub

它运行良好且符合预期。
唯一的问题是我在一些包含 unicode 字符的结果中发现不正确
例如调试宏并找到 arr(12) >> 它应该有值“Matthew Barney，Björk\”，但它在工作表上显示不正确

至于简单的示例，请尝试 arr(1)

感谢先进的帮助

Answer 1

使用 ADODB.Stream 提供 UTF-8 编码，也使用强类型对象让生活更轻松。

在 VBA 编辑器中添加对您的 excel 项目的引用。
工具/引用:[x]Microsoft Scripting Runtime , [x]Microsoft ActiveX Data Object x.x Library其中 ADO 库选择最近的一个。

Public Sub parseJSONFile()
    Dim ky1         As Collection
    Dim ky2         As Collection
    Dim ky3         As Dictionary
    'Dim fso         As Scripting.FileSystemObject
    'Dim jsonFile    As Scripting.TextStream
    Dim adofs       As ADODB.Stream
    Dim Json        As Object
    Dim arr()       As String
    Dim jsonText    As String
    Dim i           As Long
    Dim j           As Long

    'Set fso = New Scripting.FileSystemObject
    'Set jsonFile = fso.OpenTextFile(ThisWorkbook.Path & "\Sample.json")
    'jsonText = jsonFile.ReadAll

    Set adofs = New ADODB.Stream
    adofs.Charset = "UTF-8"
    adofs.Open
    Call adofs.LoadFromFile(ThisWorkbook.Path & "\Sample.json")
    jsonText = adofs.ReadText
    adofs.Close
    Set adofs = Nothing

    Set Json = JsonConverter.ParseJson(jsonText)
    ...and so on do the magic...
End Sub