Json数据转化为DataTable的两种⽅法（vb）
⽂章⽬录
前⾔
初接触Json类型数据，习惯⽤DataTable处理数据，因此想要将Json数据转化为datatable格式的数据。
⼀、利⽤Spit字符分隔的⽅式解释Json
1.步骤
1. 引⽤库：【sion】;
2. 定义分隔符格式： Dim JsonChar As Char =【json数据格式分隔符】；
3. 定义字符串数组： Dim JsonStr As String()
4. 建新的Table数据表： Dim JsonTable as DataTable=New DataTable();
5. 加⼊Table的Column表头，利⽤【spit】分隔字符获取，并写⼊DataTable；
6. 循环将解释的Json数据写⼊datatable；
2.例⼦
已获取字符串：
{“service”:“QREELQTY”,“msgcode”:0,“msg”:null,“strdata”:[{“mc”:“SM2104AB-
1”,“mo”:1,“stage”:1,“slot”:12,“pn”:“201-018404-
0222”,“did”:“LD000000000022994093”,“qty”:12,“fid”:“KT8FL 455125”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:4,“stage”:1,“slot”:10,“pn”:“301-030622-
2492”,“did”:“LD000000000022939266”,“qty”:22,“fid”:“KT8FL 331810”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:3,“stage”:1,“slot”:10,“pn”:“301-030622-
5102”,“did”:“LD000000000022949755”,“qty”:85,“fid”:“KT8FL 331801”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:7,“stage”:1,“slot”:11,“pn”:“440-900009-
04R0”,“did”:“LD000000000022683192”,“qty”:85,“fid”:“KT8FL 332733”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:11,“pn”:“301-900058-
10R0”,“did”:“LD000000000022948432”,“qty”:170,“fid”:“KT8FL 452899”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:9,“pn”:“301-030622-
8202”,“did”:“LD000000000022947701”,“qty”:170,“fid”:“KT8FL 455160”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:12,“pn”:“131-005213-
0000”,“did”:“LD000000000022861683”,“qty”:170,“fid”:“KT8FL 332724”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:10,“pn”:“301-030596-
05R1”,“did”:“LDVFHDH2020082200059”,“qty”:170,“fid”:“KT8FL 331847”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:1,“stage”:1,“slot”:10,“pn”:“101-005181-
06R2”,“did”:“LDVYJBA0092007132067”,“qty”:225,“fid”:“KT8FL 331915”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:12,“pn”:“131-005213-
0000”,“did”:“LD000000000022861688”,“qty”:307,“fid”:“KT8FL 452850”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:8,“stage”:1,“slot”:12,“pn”:“466-900013-
0000”,“did”:“LD000000000022970377”,“qty”:324,“fid”:“KT16F 040227”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:9,“pn”:“301-030622-
8202”,“did”:“LD000000000022965778”,“qty”:798,“fid”:“KT8FL 455027”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:9,“stage”:1,“slot”:23,“pn”:“201-900093-
0680”,“did”:“LDVCAPHW202007210002”,“qty”:798,“fid”:“KT12F 028230”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:10,“pn”:“301-030596-
05R1”,“did”:“LDVFHDH2020082900053”,“qty”:1108,“fid”:“KT8FL 332795”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:12,“pn”:“301-900057-
{“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:12,“pn”:“301-900057-
R025”,“did”:“LDVTA202007160000685”,“qty”:1208,“fid”:“KT8FL 452952”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:8,“stage”:1,“slot”:11,“pn”:“121-900030-
0000”,“did”:“LD000000000022994199”,“qty”:1357,“fid”:“KT12F 028245”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:3,“stage”:1,“slot”:10,“pn”:“301-030622-
5102”,“did”:“LD000000000022966007”,“qty”:1496,“fid”:“KT8FL 737007”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:4,“stage”:1,“slot”:10,“pn”:“301-030622-
2492”,“did”:“LD000000000022965999”,“qty”:1998,“fid”:“KT8FL 331858”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:7,“stage”:1,“slot”:10,“pn”:“201-020052-
0105”,“did”:“LD000000000022995502”,“qty”:2237,“fid”:“KT8FL 331912”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:1,“stage”:1,“slot”:11,“pn”:“201-020170-
0104”,“did”:“LD000000000022994277”,“qty”:2605,“fid”:“KT8FL 455165”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:7,“stage”:1,“slot”:11,“pn”:“440-900009-
04R0”,“did”:“LDVBT201011000017430”,“qty”:2608,“fid”:“KT8FL 455123”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:4,“stage”:1,“slot”:12,“pn”:“201-900022-
0471”,“did”:“LD000000000022995506”,“qty”:2778,“fid”:“KT8FL 996740”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:11,“pn”:“201-018964-
0105”,“did”:“LD000000000022995530”,“qty”:2908,“fid”:“KT8FL 360042”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:10,“pn”:“301-900059-
8R25”,“did”:“LDVTA202008280000014”,“qty”:2950,“fid”:“KT8FL 331874”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:12,“pn”:“131-005213-
0000”,“did”:“LD000000000022861692”,“qty”:2998,“fid”:“KT8FL 455194”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:1,“stage”:1,“slot”:12,“pn”:“201-018404-
0222”,“did”:“LD000000000022995512”,“qty”:3178,“fid”:“KT8FL 455004”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:12,“pn”:“301-900057-
R025”,“did”:“LDVTA202007160000684”,“qty”:3298,“fid”:“KT8FL 729575”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:11,“pn”:“301-900058-
10R0”,“did”:“LDVYAG20101300000699”,“qty”:3450,“fid”:“KT8FL 455024”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:10,“pn”:“301-030596-
05R1”,“did”:“LDVFHDH2020092700087”,“qty”:3498,“fid”:“KT8FL 331924”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:3,“stage”:1,“slot”:11,“pn”:“161-000705-
0000”,“did”:“LDVXHD20101400000010”,“qty”:3850,“fid”:“KT8FL 455159”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:1,“stage”:1,“slot”:11,“pn”:“201-020170-
0104”,“did”:“LD000000000022994353”,“qty”:3900,“fid”:“KT8FL 360065”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:10,“pn”:“301-030622-
2202”,“did”:“LD000000000022948125”,“qty”:4160,“fid”:“KT8FL 332601”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:11,“pn”:“301-900053-
6201”,“did”:“LDVYAG20101300000644”,“qty”:5900,“fid”:“KT8FL 331962”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:11,“pn”:“201-018964-
0105”,“did”:“LD000000000022994732”,“qty”:6587,“fid”:“KT8FL 332690”,“status”:“2”}, {“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:10,“pn”:“301-030622-
2202”,“did”:“LDVXHD20101500000015”,“qty”:7998,“fid”:“KT8FL 996979”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:4,“stage”:1,“slot”:11,“pn”:“201-018959-
0104”,“did”:“LD000000000022995508”,“qty”:9461,“fid”:“KT8FL 452811”,“status”:“1”}, {“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:11,“pn”:“301-900053-
6201”,“did”:“LDVYAG20092300000976”,“qty”:12224,“fid”:“KT8FL 994066”,“status”:“2”}]}
Imports Newtonsoft.Json.Linq
Imports Newtonsoft.Json
Imports System.Web.Script.Serialization
Public Function JasonToDataTable(Jason As String)As DataTable
Try
'定义分隔符格式
Dim rowf As Char= "{"
Dim Str As Char=","
Dim StrC As Char=""""
'定义字符串数组
Dim rowStr As String()
Dim ColStr As String()
JasonToDataTable =New DataTable
rowStr = Jason.Split(rowf)'分⾏
ColStr = rowStr(2).Split(Str)
'加⼊DataTable列名
For i =0To ColStr.Length-2
json值的类型有哪些Dim ColName As String()
ColName = ColStr(i).Split(StrC)
JasonToDataTable.Columns.Add(ColName(1))
Next
Dim EachColunm As String()
Dim EachStr As String()
Dim EachValue(ColStr.Length)As String
'加⼊Json数据
For j =0To rowStr.Length-3
JasonToDataTable.Rows.Add()
EachColunm = rowStr(j +2).Split(",")
For k =0To EachColunm.Length-1
If EachColunm(k).Length>1Then
EachStr = EachColunm(k).Split(":")
EachValue(k)= EachStr(1).Replace("}", "").Replace("]", "").Replace("""", "") JasonToDataTable.Rows(j).Item(k)= EachValue(k)
End If
Next
Next
Return JasonToDataTable
Catch ex As Exception
WarmTXT.Visible =True
WarmTXT.Text= "Json数据转换出错！"
End Try
End Function
⼆、利⽤反序列化公式转化【JavaScriptSerializer】1.步骤
1. 安装【Nugen】解决⽅案插件；
2. 读出Json数据；
3. ⽤JavaScriptSerializer排列Json数据；
4. 使⽤循环语句将Json数据列名、数据导⼊新建的DT；
5.
2.例⼦
Public Function JsonToDataSet(Json As String)As DataSet
Try
Dim ds =New DataSet()
Dim JSS =New JavaScriptSerializer()
Dim obj As Object= JSS.DeserializeObject(Json)
Dim datajson As Dictionary(Of String,Object)
datajson = obj
For Each item In datajson
Dim dt =New DataTable("JsonDT")
Dim rows As Object= item.Value
If item.Key<>"strdata"Then Continue For
For Each row In rows
Dim val As Dictionary(Of String,Object)
val= row
Dim dr As DataRow = dt.NewRow()
Dim sss As KeyValuePair(Of String,Object)
For Each sss In val
If dt.Columns.Contains(sss.Key)=True Then
dr(sss.Key)= sss.Value
Else
dt.Columns.Add(sss.Key)
dr(sss.Key)= sss.Value
End If
Next
dt.Rows.Add(dr)
Next
ds.Tables.Add(dt)
Next
Return ds
Catch ex As Exception
End Try
Return Nothing
End Function
总结
以上两种⽅法都可将Json数据转化为DataTable数据，总体上说没有差别。
⽅法⼀：⽐较适合Json数据包简单，⽽且永不会变化的数据类型格式；
⽅法⼆：普遍通⽤使⽤于所有Json数据转化为DataTable类型数据格式。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论