I needed to convert an excel file with its own defined columns to a new one with dynamic columns, and was a bit confused about how to get it right without some serious performance issues.
I came to a tutorial that you can find here using .NET ExpandoObject which practically makes it possible for you to create an object and add dynamic members.
ExpandoObject
Microsoft's definition is:
Represents an object whose members can be dynamically added and deleted at run time.
And you have some observations:
The ExpandoObject class enables you to add and delete members of your instances at runtime, and also determine and obtain values for these members. This class supports dynamic link, which enables you to use a standard syntax like sampleObject.sampleMember instead of a more complex syntax like sampleObject.GetAttribute (“sampleMember”).
Current behavior
We have one ExcelExportServiceque, when passing a List , for this case ExcelItem, we will use it Reflection to create a xlsx archive.
Until now, our code looks like this:
ExcelItem is the object with all the properties used to generate the Excel file.
This approach works great, the xlsx file is generated without problems and each column is each property, would use this GetExcelBytes method like this, as an example to save it to a file:
The problem
Since what we are doing well is using all the properties of the ExcelItem object, all i want is not to use them all, just use 2 O 3 from them.
It is also a requirement that you do not want to change the code, everything must be done by the administrator which will decide which columns should be displayed and may or may not know the properties of the code.
TLDR: everything must be dynamic, we have an object with properties and we need to make sure that the generated file has North properties of that object, but apparently it is not encoded.
Dynamic columns
The change would be to use a dynamic object, because we would like to determine what properties of the object will be used to generate the column list.
Let's say we have an object with many properties, What ton from them, and we really don't want to change the ExcelItem object every time we make a change, we created a ColumnExcelItem table, which will be used to generate that ExcelItem.
Structure of the databaseA database is an organized set of information that allows you to store, Manage and retrieve data efficiently. Used in various applications, from enterprise systems to online platforms, Databases can be relational or non-relational. Proper design is critical to optimizing performance and ensuring information integrity, thus facilitating informed decision-making in different contexts....
We save this definition in our database with something like this:
Note that we have fewer values than the existing properties of the ExcelItem object, since it has 6 properties and we only have 3 included in the database.
Also note that Property name it should match the property name of the object that I will use for dynamic mapping.
Building the object
Now that we have the database table, we need to build the repository to get that and be able to use them in the code.
I will not do a tutorial for this part, I'll jump to the start of the new. GetExcelBytes () function.
Now we will have to create an object with some of the properties of ExcelItem, but totally dynamic. Instead of using the 6 properties, we only use 3 of them, the one we only want to send.
Now imagine this ExcelItem the object has 200 properties, annoyed, but it could happen.
The only thing I have to do is insert those properties that I want to render in the excel file in that ColumnExcelItem table and that's it.
Cases
In this circumstance, we only use a single case, but suppose you want to have different reports for some users. Let's say that the management role should get all properties, then I would create something like this structure in the database.
Subsequently, when i get the template, could have different columns, all dynamically and without related code.
If we have a user with the role Administrators, the file will contain columns Go, potato, Last name, Age, Created in, Updated in.
In the event that we generate with the Users paper, what will it have Potato, Age. Last name.
Conclution
This is a great way to learn how to dynamic works in .NET and is a very good solution when you need to have different roles or templates for different users and you are not really interested in spending time coding.
