Issue with reading dynamic data from DataTable in QuestPDF

huangapple go评论65阅读模式
英文:

Issue with reading dynamic data from DataTable in QuestPDF

问题

以下是您提供的内容的翻译:

我正在尝试弄清楚如何使用QuestPDF从DataTable中获取的数据填充表格。

我目前正在使用JSON作为数据源。JSON的格式如下:

{
    "Brother MFC (60)": {
        "AssetId": 60,
        "AssetName": "Brother MFC",
        "AssetTag": "PRN-001",
        "AssetTypeId": 19,
        "AssetTypeName": "Office Equipment",
        "Parameters": {
            "Color (12)": "12"
        }
    },
    "Office Table (63)": {
        "AssetId": 63,
        "AssetName": "Office Table",
        "AssetTag": "TAB-002",
        "AssetTypeId": 19,
        "AssetTypeName": "Office Equipment",
        "Parameters": {
            "Color (12)": "2",
            "Height (13)": "300"
        }
    },
    "Office Desk (64)": {
        "AssetId": 64,
        "AssetName": "Office Desk",
        "AssetTag": "DESK-03",
        "AssetTypeId": 9,
        "AssetTypeName": "New Office Equipment",
        "Parameters": {
            "Color (12)": "4",
            "Height (13)": "400"
        }
    }
}

我有一个名为“Parameters”的变量,它可以根据资产的不同而变化,我希望将它们显示在单独的列中。如果有匹配的列名,我需要显示与该参数对应的值。参数以记录的形式存储在数据库中,而不是列名。因此,它们是完全动态的(例如,我们可以有多个参数,如“颜色”,“宽度”,“塑料类型”等)。

为了实现这一点,我正在将JSON文件转换为DataTable。尽管调试器正确显示了列数,但我在使用QuestPDF时遇到了显示“Parameters”的困难。尽管我已经包括了必要的列,但该库似乎跳过了“Parameters”。与“资产”相关的列正常显示。

我认为在应用程序运行时无法访问QuestPDF所需的一些数据,但我不确定如何解决这个问题。我需要使用字典来返回参数,因此我需要使用动态模型和字典。

预期的示例输出:

资产名称 ... 颜色 (12) 高度 (13)
Brother MFC ... 12
Office Table ... 2 300
Office Desk ... 4 400

以下是将JSON转换为DataTable的代码:

// 这里是您的代码

如果您需要更多详细信息,请告诉我。

英文:

I'm trying to figure out how to populate table with data coming from DataTable using QuestPDF.

I'm currently using JSON as a data source. The JSON is formatted like this:

{
    "Brother MFC (60)": {
        "AssetId": 60,
        "AssetName": "Brother MFC",
        "AssetTag": "PRN-001",
        "AssetTypeId": 19,
        "AssetTypeName": "Office Equipment",
        "Parameters": {
            "Color (12)": "12"
        }
    },
    "Office Table (63)": {
        "AssetId": 63,
        "AssetName": "Office Table",
        "AssetTag": "TAB-002",
        "AssetTypeId": 19,
        "AssetTypeName": "Office Equipment",
        "Parameters": {
            "Color (12)": "2",
            "Height (13)": "300"
        }
    },
    "Office Desk (64)": {
        "AssetId": 64,
        "AssetName": "Office Desk",
        "AssetTag": "DESK-03",
        "AssetTypeId": 9,
        "AssetTypeName": "New Office Equipment",
        "Parameters": {
            "Color (12)": "4",
            "Height (13)": "400"
        }
    }}

I have variable "Parameters" that can vary depending on the asset, and I want to display them in separate columns. If there is a matching column name, I need to show the value corresponding to that parameter. Parameters are stored in database as records and not column names. Therefore, they are completely dynamic (for example we can have multiple parameters like "color," "width," "type of plastic," etc.)

To accomplish this, I'm converting a JSON file into a DataTable. Although the debugger correctly shows the number of columns, I'm having difficulty displaying "Parameters" using QuestPDF. Even though I have included the necessary columns, the library seems to be skipping over "Parameters." Columns related to "Assets" show up without any issues.

I believe that some of the data necessary for QuestPDF is not accessible while the application is running, but I am unsure of how to address this problem. I need to use Dictionaries to return Parameters, so I need to use dynamic model and dictionary for that.

Expected sample output:

AssetName ... Color (12) Height (13)
Brother MFC ... 12
Office Table ... 2 300
Office Desk ... 4 400

Below is my code for converting JSON into DataTable:

    public class ReportsController : Controller
{
private readonly ApplicationDbContext _context;
public ReportsController(ApplicationDbContext context)
{
_context = context;
}
public static class DynamicModel
{
public static dynamic CreateModel()
{
return new ExpandoObject();
}
public static void SetProperty(dynamic model, string propertyName, object propertyValue)
{
var expando = (IDictionary<string, object>)model;
expando[propertyName] = propertyValue;
}
public static object GetProperty(dynamic model, string propertyName)
{
var expando = (IDictionary<string, object>)model;
return expando.ContainsKey(propertyName) ? expando[propertyName] : null;
}
}
public class AssetParam
{
public int AssetId { get; set; }
public string AssetName { get; set; }
public string AssetTag { get; set; }
public int AssetTypeId { get; set; }
public string AssetTypeName { get; set; }
public Dictionary<string, string> Parameters { get; set; }
}
public static DataTable JsonToDataTable(string jsonString)
{
var assets = JsonConvert.DeserializeObject<Dictionary<string, AssetParam>>(jsonString);
var dataTable = new DataTable();
// Add columns for non-parameter properties
dataTable.Columns.Add("AssetId", typeof(int));
dataTable.Columns.Add("AssetName", typeof(string));
dataTable.Columns.Add("AssetTag", typeof(string));
dataTable.Columns.Add("AssetTypeId", typeof(int));
dataTable.Columns.Add("AssetTypeName", typeof(string));
// Loop over each asset and add its parameters to the DataTable as columns
foreach (var asset in assets.Values)
{
foreach (var parameter in asset.Parameters)
{
if (!dataTable.Columns.Contains(parameter.Key))
{
dataTable.Columns.Add(parameter.Key, typeof(string));
}
}
}
// Loop over each asset and add a new row to the DataTable
foreach (var asset in assets.Values)
{
var row = dataTable.NewRow();
// Add non-parameter properties to the row
row["AssetId"] = asset.AssetId;
row["AssetName"] = asset.AssetName;
row["AssetTag"] = asset.AssetTag;
row["AssetTypeId"] = asset.AssetTypeId;
row["AssetTypeName"] = asset.AssetTypeName;
// Add parameter values to the row
foreach (var parameter in asset.Parameters)
{
row[parameter.Key] = parameter.Value;
}
dataTable.Rows.Add(row);
}
return dataTable;
}
[HttpGet]
public IActionResult GetParameterAssetsReport(int[] selectedParameterIDs, int[] selectedAssetIDs)
{
try
{
var getSelectedAssets = _context.Assets.Include(s => s.AssetTypes)
.Where(i => selectedAssetIDs.Contains(i.Id))
.ToList();
var getSelectedParameterValues = _context.AssetParamInt.Include(s => s.Parameter)
.Where(i => selectedParameterIDs.Contains(i.ParameterID))
.ToList();
dynamic myModel = DynamicModel.CreateModel();
var assetsDistinct = _context.AssetParamInt
.Include(i => i.Assets)
.Where(i.Parameter.ParameterType.isAsset)
.Select(s => new
{
AssetId = s.AssetID,
AssetName = s.Assets.AssetName,
AssetTypeName = s.Assets.AssetTypes.AssetTypeName,
AssetTypeNameId = s.Assets.AssetTypes.Id,
}).Distinct().ToList();
// get asset types
var assetTypesDistinct1 = assetsDistinct.Select(s => new
{
AssetTypeNameId = s.AssetTypeNameId,
AssetTypeName = s.AssetTypeName
}
).Distinct();
foreach (var asset in getSelectedAssets)
{
dynamic record = new ExpandoObject();
record.AssetId = asset.Id;
record.AssetName = asset.AssetName;
record.AssetTag = asset.AssetTag;
record.AssetTypeId = asset.AssetTypes.Id;
record.AssetTypeName = asset.AssetTypes.AssetTypeName;
var headers = new Dictionary<string, string>();
var selectedAssetParameters = getSelectedParameterValues.Where(s => s.AssetID == asset.Id).ToList();
foreach (var parameters in selectedAssetParameters)
{
if (!headers.ContainsKey(parameters.Parameter.ParameterName + " (" + parameters.ParameterID + ")"))
{
headers.Add(parameters.Parameter.ParameterName + " (" + parameters.ParameterID + ")", parameters.ParameterValue);
}
}
record.Parameters = headers;
DynamicModel.SetProperty(myModel, asset.AssetName + " (" + asset.Id + ")", record);
}
string json = JsonConvert.SerializeObject(myModel, new ExpandoObjectConverter());
// store in DataTable
DataTable dtAssetsParams = JsonToDataTable(json);
DateTime now = DateTime.Now;
DateTime utc = DateTime.UtcNow;
TimeZoneInfo localZone = TimeZoneInfo.Local;
var ms = new MemoryStream();
Document.Create(container =>
{
foreach (var assetType in assetTypesDistinct1)
{
container.Page(page =>
{
page.Size(PageSizes.Letter.Landscape());
page.Margin(1, Unit.Centimetre);
page.DefaultTextStyle(x => x.FontSize(8).FontFamily("Arial"));
page.Header()
.Text("Asset Parameter Report")
.SemiBold().FontSize(14).FontColor(Colors.Blue.Medium);
page.Content()
.Column(x =>
{
x.Spacing(10);
x.Item().Text("Asset Type: " + assetType.AssetTypeName).SemiBold().FontSize(12);
x.Item().Table(table =>
{
table.ColumnsDefinition(columns =>
{
// loop through all columns
foreach (DataColumn column in dtAssetsParams.Columns)
{
columns.RelativeColumn();
}
});
table.Header(header =>
{
foreach (DataColumn column in dtAssetsParams.Columns)
{
var columnName = column.ColumnName;
header.Cell().Element(CellStyle).AlignCenter().Text(columnName);
}
static IContainer CellStyle(IContainer container)
{
return container.DefaultTextStyle(x => x.SemiBold()).BorderBottom(1).BorderColor(Colors.Black);
}
});
/* Cells go here (removed for now) -----
table.Cell().Element(CellStyle).Text("cell data");
---------------------------------------- */
static IContainer CellStyle(IContainer container)
{
return container.Border(1).BorderColor(Colors.Grey.Lighten3).Padding(3);
}
});
});
page.Footer()
.AlignCenter()
.Text(x =>
{
x.Span("Page ");
x.CurrentPageNumber();
x.Line("");
x.Span("Generated on " + $"{now:f} " + localZone.DisplayName).FontSize(8).FontColor(Colors.Grey.Medium);
});
});
}
}).GeneratePdf(ms);
ms.Position = 0;
return File(ms, "application/pdf");
}
catch (Exception e)
{
return BadRequest();
}
}
}

Please, let me know if you need more details.

Tested:

  1. I've tried using separate List for storing column names.
  2. I also created a sample string and appended all column names - it was still the same issue, QuestPDF was unable to "read" these parameters.
  3. I tried manually adding extra columns so "Parameters" were between Asset data and those hardcoded extra columns.

答案1

得分: 0

代码运行正常,但存在一个关于 AJAX 调用的问题。我调用了该函数两次,但由于第二次调用紧随在第一次之后,很难进行调试。调试器向我显示了正确的数值,但第二次调用返回了一个空文件,没有传递任何数据。

英文:

The code was working fine, but there was a problem with an AJAX call. I was calling the function twice, but because the second call was right after the first, it was difficult to debug. The debugger was showing me the correct values, but the second call was returning an empty file without passing any data.

huangapple
  • 本文由 发表于 2023年3月31日 23:45:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/75900430.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定