public static class JsonToDataTableConverter { /// <summary> /// Json转换为DataTable /// </summary> /// <param name="json"></param> public static DataTable JsonToDataTable(this string json) { // 尝试解析JSON字符串 JToken parsedJson = JToken.Parse(json); // 创建 DataTable DataTable dataTable = new DataTable(); AddRowsFromJsonWithDynamicColumns(parsedJson, dataTable); return dataTable; } /// <summary> /// JSON数据添加为行 /// </summary> /// <param name="json"></param> /// <param name="dataTable"></param> /// <param name="row"></param> /// <param name="prefix"></param> static void AddRowsFromJson(JToken json, DataTable dataTable, DataRow row = null, string prefix = "") { switch (json.Type) { case JTokenType.Object: bool mark=false; foreach (JProperty property in json.Children<JProperty>()) { string columnName = $"{prefix}{property.Name}"; if (row == null) { row = dataTable.NewRow(); mark = true; } AddRowsFromJsonWithDynamicColumns(property.Value, dataTable, row, columnName + "-"); } if (mark) { dataTable.Rows.Add(row); } break; case JTokenType.Array: foreach (JToken item in json) { string columnName = $"{prefix}"; DataRow newRow = dataTable.NewRow(); AddRowsFromJson(item, dataTable, newRow, string.IsNullOrWhiteSpace(columnName) ? "" : columnName + "-"); dataTable.Rows.Add(newRow); } break; default: Type columnType; if (json.Type == JTokenType.String) columnType = typeof(string); else if (json.Type == JTokenType.Integer) columnType = typeof(int); else return; string columnNameWithValue = prefix.TrimEnd('-'); if (!dataTable.Columns.Contains(columnNameWithValue)) { dataTable.Columns.Add(columnNameWithValue, columnType); } if (row != null) row[columnNameWithValue] = json.ToObject(columnType); break; } } /// <summary> /// 使用动态列从Json添加行 /// </summary> /// <param name="json"></param> /// <param name="dataTable"></param> /// <param name="row"></param> /// <param name="prefix"></param> static void AddRowsFromJsonWithDynamicColumns(JToken json, DataTable dataTable, DataRow row = null, string prefix = "") { switch (json.Type) { case JTokenType.Object: bool mark=false; foreach (JProperty property in json.Children<JProperty>()) { string columnName = $"{prefix}{property.Name}"; if (row == null) { row = dataTable.NewRow(); mark = true; } AddRowsFromJsonWithDynamicColumns(property.Value, dataTable, row, columnName + "-"); } if (mark) { dataTable.Rows.Add(row); } break; case JTokenType.Array: foreach (JToken item in json) { string columnName = $"{prefix}"; DataRow newRow = dataTable.NewRow(); AddRowsFromJsonWithDynamicColumns(item, dataTable, newRow, string.IsNullOrWhiteSpace(columnName) ? "" : columnName + "-"); // Fill missing columns with null values foreach (DataColumn column in dataTable.Columns) { if (newRow.Table.Columns.Contains(column.ColumnName)) continue; newRow[column.ColumnName] = DBNull.Value; } dataTable.Rows.Add(newRow); } break; default: Type columnType; switch (json.Type) { case JTokenType.String: columnType = typeof(string); break; case JTokenType.Integer: columnType = typeof(int); break; case JTokenType.Float: columnType = typeof(float); break; case JTokenType.Date: columnType = typeof(DateTime); break; case JTokenType.Boolean: columnType = typeof(bool); break; default: return; } string columnNameWithValue = prefix.TrimEnd('-'); if (!dataTable.Columns.Contains(columnNameWithValue) && json.ToObject(columnType) != null) { dataTable.Columns.Add(columnNameWithValue, columnType); } if (row != null) row[columnNameWithValue] = json.ToObject(columnType); break; } } }
使用:
try { string json = @"[{ ""RowNumber"": 1, ""CustomerCode"": """", ""ProjectName"": ""TEST"", ""CustomerName"": ""A客户"", ""YearMonthData"": { ""2023"": { ""2"": { ""Weight"": ""0"", ""Count"": 0 }, ""3"": { ""Weight"": ""0"", ""Count"": 0 }, ""4"": { ""Weight"": ""0"", ""Count"": 0 } } } },{ ""RowNumber"": 2, ""CustomerCode"": """", ""ProjectName"": ""879789"", ""CustomerName"": ""123123213"", ""YearMonthData"": { ""2023"": { ""2"": { ""Weight"": ""2"", ""Count"":2 }, ""3"": { ""Weight"": ""5"", ""Count"": 2 } } } }]"; DataTable dataTable = json.JsonToDataTable(); // 使用 DataTable foreach (DataRow dataRow in dataTable.Rows) { foreach (DataColumn dataColumn in dataTable.Columns) { Console.WriteLine($"{dataColumn.ColumnName}: {dataRow[dataColumn]}"); } } } catch (Exception ex) { }
效果
发表评论