First time when using the template approach.
We have the following case scenario:
Two tables as .csv files, one accident and the other vehicle. The relationship is one accident to many vehicles, so accident can be the base.
We’re loading the CSV’s into workbooks and after export into Datatable, so will be dtAccident and dtVehicle, thinking is easier to manipulate data in this way.
We can easily refer to each field from the separate datatables with smart markers, eg: &=dtAccs.Date,
&=dtAccs.Police_ref, &=dtVeh.Type, &=dtVeh.Type.
But the scope is to link them (by Police_ref field) so that we can place smart markers as:
Row1Col1: &=dtAccs.Date(skip:2,noadd), Row1Col2: &=dtAccs.Police_ref(skip:2,noadd)
Row2Col1: &=dtAccs.Road_Cond(skip:2,noadd)
Row3Col1: &=dtAccs.dtVeh.Type(skip:2), Row3Col1: &=dtAccs.dtVeh.DrvAge(skip:2)
We’ve tried to create a DataRelation between the 2 tables but don’t think works to refer datarelation with smart markers.
What approach should we use to refer with smart markers for a basic inheritance such this.
I’ll attach the simpler version of the 2 csv tables as sample (repaccs1.csv, repvehs1.csv). And the template we’re trying to fill (Interpreted.xlsx). Below just the code we’ve used so far:
DataSet dataSet = new DataSet();
//accident
bookAccs = new Workbook(csvAccs);
cellsAccs = bookAccs.Worksheets[0].Cells;
dataTabAccs = bookAccs.Worksheets[0].Cells.ExportDataTable
(0, 0, cellsAccs.MaxDataRow + 1, cellsAccs.MaxDataColumn + 1, true);
dataTabAccs.TableName = “acc”;
dataSet.Tables.Add(dataTabAccs);
//vehicle → just repeat the above and replace with vehicle
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(“Interpreted.xlsx”);
designer.SetDataSource(dataSet);
designer.Process();
designer.Workbook.Save($“InterprList.xlsx”);
@Remus87
We think it is too complicated to express and extract data according to special relation in smart markers. How about linking data of two tables according to the relation you expect with your own logic to create another table and then using the resultant table for smart markers?
We’re thinking on that also, to join the tables and create another from it, however might be another level of hierarchy, casualties . Hence might complicating adding all tables 3 into another one. Any suggestions are welcome.
Thanks
@Remus87
If several tables have complex association relationships, you can choose to use a database to handle their relationships first, and then import the final results into Excel. You can refer to the following documents for importing data from database tables into Excel.
We managed to link the tables according to the relation into another table . Now don’t know how would place the data into smart markers. We added a 3rd table Casualty and linked all three tables as suggested into one attached in this thread (repint.csv) repint.zip (4.7 KB)
The final table has a column RecType and would differentiate the individual table (Accs, Vehs and Casualties) by a letter A, V or C
We got stuck as noticed that Group feature on Smart Markers only supports group for 1 field, or perhaps we don’t know how to use it’s syntax correct.
Can you please show an example with smart markers on a template how would we display an accident details, than vehicle details per accident, then casualties per vehicle.
Either with the single table attached here , or with the 2 separate tables (if using those just display accident details, than vehicle details per accident; as we can follow than from your example and implement the Casualties table also)
We also have Aspose.Words and is not necessarily to be in spreadsheet format (as we’ll convert to PDF) and if you think the task is simpler to achieve with ReportingEngine we can use that if you can give a basic template example to achieve it.
Thanks in advance
@Remus87
Could you share an excepted file?
The simplest method now seems to be to merge three tables according to your expected results by yourself, and then simply import one table instead of smartmarker
the feed files needed (in their simplest form sufficient to showcase the required functionality as they have more than 50 columns each but we make abstractions of rest) → repaccs1.csv, repvehs1.csv, repcas1.csv
the Expected output → ExpectedResult.xlsx
Documentation needed to understand the task (with just few self explanatory diagrams) Input-Expected-Documentation.zip (726.5 KB)
We need to evaluate your requirements/issue in details. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Do you mean processing the data into a hierarchical structure?
If so, we did that. And we have 2 hierarchical structures to choose from: Dataset and/or JSON.
1 - We’ve loaded each csv file into a datatable, add the datatables into a dataset , initialize the required nested links via datarelations, set the relations as nested (datarelation.Nested = true)
2 - We serialized the dataset object into a JSON string
Still didn’t figure it out how to reference the markers to fit the data in.
So it is not supported on this level of nested (level 0 - level 1 - level 2) to work with the designer template and place the markers via spreadsheet editor ?
We know how to achieve that programmatically (in code) and fill in the data to generate the final report.
The scope on this use case is to be able to modify the template post development via editor and not modify the code for simpler maintainability.
@Remus87
Thank you for your feedback and detailed information. Smart Markers currently cannot support such complex requirements. We are redesigning smart markers to meet complex hierarchical nesting requirements. Once there are updates, we will notify you promptly.
We are pleased to inform you that your issue (logged earlier as “CELLSNET-54674”) has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.12) that we plan to release in the next week. You will be notified when the new version is published.