Recently I came across a requirement where I had to create a custom excel report based on grouping concept of SQL. Usage of Group by clause and Group new clause are already there on internet but there combined usage and creation of custom columns in excel is something I felt is missing. So here is my blog solving such problems:-
Assuming 2 tables as shown below:-
1. Machine Table
2. Event Table
Combined Result in SQL
Required Output in excel format
In this example, we are grouping by UniqueId and also formatting the columns Action and GroupName.
We will create a entity class for Report as shown below, List all columns that are needed in excel Report. I have added UniqueId, Action and Group.
Below Method ‘GenerateCustomReport’ returns List of Report object, this return value can be exported into excel in calling function.
In this method, we are performing following steps:-
1. Read table data into variable (I am using ADO.Net Entity framework in this case, you can also use SQL methods to read data from Machine and Event tables)
2. Using Linq to join tables and group by Unique Id and store result in ‘Report’ Entity format.
3. Iterate through ‘Report’ Entity result. Customize Action and Group Column as per the requirement. Here I am appending values with counter number like 1., 2. … etc.
I have focused on column customization and group by logic. On how to create excel and exporting data into excel , there are multiple blogs available on internet that shows how to achieve this functionality.
You may need to call the above method and export the return data into excel. The final output will look like below:-
Write to me for suggestions or feedback.
Till then, Happy Coding!!!