How To Include Grid Column Template Data in Excel Export?

Question

Hello,

I am working on creating an export function for a Grid, but I am having some issues exporting the cells that are generated by a Column Template.

Background (Solution Attached):

Some of the columns are dynamic (user can add their own parameters for an entry)
These parameters are stored in a Dictionary
A List of all parameter names (Dictionary Keys) is calculated and then all columns are generated
A Column Template is used to extract the values from the dictionary

Issue I'm Facing:

When exporting, the columns populated by a Column Template are empty (see image)

Question:

Is it possible to configure the Grid or Export to get the String value of the cells, and include it in the Excel Export?

Code For Grid:

        ShowColumnChooser="true" AllowExcelExport="true"
        Toolbar=@ToolbarItems>






        @foreach (var parameter in DeviceParameters)
        {



        }

Attachment: SyncfusionExamples_d1cdcb2.zip

Renjith Singh Rajendran · Answer

Hi Jonathan, Thanks for contacting Syncfusion support. We don’t have support to export template column in Grid. So, to achieve this requirement, we suggest you to use the computed/additional property and assign the field names to Grid column to achieve exporting the Dictionary values in Grid, instead of using Template column in Grid. We have modified the attached sample based on your requirement. Please download the sample from the link below, Sample : https://www.syncfusion.com/downloads/support/directtrac/general/ze/SyncfusionExamples1852181865 In the above sample, we have defined a class FlattenDeviceModel extending from DeviceModel class. We have used this class as model for Grid, and added the column names(fetched from the Dictionary values) to Grid. Please refer the code below, ... } @code { public class FlattenDeviceModel : DeviceModel { public string Ledcolor { get { return DeviceParameters["led_color"]; } set { } } public string Powerstate { get { return DeviceParameters["power_state"]; } set { } } public string Powerofftime { get { if (DeviceParameters.ContainsKey("power_off_time")) { return DeviceParameters["power_off_time"]; } else { return null; } } set { } } } ... } Please get back to us if you need further assistance. Regards, Renjith Singh Rajendran.

Jonathan Swierczynski · Answer

Hi Renjith,Thank you for the quick response. Unfortunately, this solution won't work for us, because any Key can be added to the Dictionary. The keys I provided were just examples.In our actual project, we will have many different configurations of 20+ Keys each. So it won't be efficient for us to create/modify these flattened classes anytime a user needs to add a new Key to the dictionary. If exporting a Template Column is not supported, would there be any way to bind a column field to a Dictionary? Ex: Field="DeviceParameters["led_color"]"

Renjith Singh Rajendran · Answer

Hi Jonathan,

For this scenario(having column template in Grid) of performing excel exporting, we suggest you to use the “Custom DataSource Exporting” and “Custom Column Exporting” (which is currently implemented internally and available by Feb 26, 2020) features of Grid. With these support you can define your own DataSource and customized Columns(not the ones define for Grid) when performing Excel Exporting in Grid. So that the defined customized DataSource and Columns will be used during exporting, instead of the ones used for displaying the Grid.

Custom DataSource Export documentation : https://ej2.syncfusion.com/blazor/documentation/grid/excel-exporting/#custom-data-source

We have planned to include “Export by using custom column” support in our upcoming bi-weekly release which is scheduled to be rolled out on or before February 26,2020.

Once the release is rolled out, we suggest you to flatten your datasource as an ExpandoObject and generate the custom columns based on the generated ExpandoObject. And assign these to the DataSource and Columns property of ExcelExportProperties. Please refer the below code example showing the implementation. Once the February 26, 2020 release is rolled out you can try the below code implementation and suggestions to achieve exporting grid with the values displayed in template column.

<EjsGrid ID="DeviceGrid" @ref="DeviceGrid" DataSource="Devices" ...>

<GridEvents OnToolbarClick="ToolbarClick" TValue="DeviceModel"></GridEvents>

...

</EjsGrid>

@code

{

public List<DeviceModel> Devices = new List<DeviceModel>()

{

...

};

private EjsGrid<DeviceModel> DeviceGrid;

public List<System.Dynamic.ExpandoObject> gridData = new List<System.Dynamic.ExpandoObject>(); //Define the ExpandoObject which is the custom DataSource

public void ToolbarClick(Syncfusion.EJ2.Blazor.Navigations.ClickEventArgs args)

{

if (args.Item.Id == "DeviceGrid_excelexport")

{

gridData = new List<System.Dynamic.ExpandoObject>(); //Empty the ExpandoObject

List<string> cols = new List<string>(); //Define the custom columns field names List

foreach (var device in Devices)

{

var x = new ExpandoObject() as IDictionary<string, Object>;

foreach (var parameter in device.DeviceParameters)

{

x.Add(parameter.Key, parameter.Value); //Fetch the key and value from Dictionary(DeviceParameters) and add to the ExpandoObject

if (!cols.Contains(parameter.Key))

{

cols.Add(parameter.Key); //Add the field information to cols List

}

////Do the same for all other defined fields(like Id, Name etc.) as like the above Dictionary(DeviceParameters) value.

Concept is to flatten the DataSource bind to Grid, and generate a custom ExpandoObject based on the DataSource provided for Grid. And generate a custom column list based on the generated ExpandoObject.

...

gridData.Add(x as ExpandoObject); //Add the dynamically generated Objects to the List of ExpandoObject

}

ExcelExportProperties excelprop = new ExcelExportProperties(); //generate instance for ExcelExportProperties

excelprop.DataSource = gridData; //Assign the generated custom ExpandoObject to DataSource property

var columns = new List<GridColumn>();

foreach(var c in cols)

{

var additionalColumn = new GridColumn() { Field = c, Width = "100px" };

columns.Add(additionalColumn); //Add the columns from the “cols List” to List<GridColumn>

}

excelprop.Columns = columns; //Assign the custom columns generated based on the ExpandoObject to Columns property

DeviceGrid.ExcelExport(excelprop); //Export the Grid with the assigned custom Columns and DataSource

}

...

}

...

}

Please get back to us if you need further assistance.

Regards,

Renjith Singh Rajendran.

Unknown · Answer

Hi Renjith, Has the custom columns export been implemented in the grid or is it still on the roadmap? Or is custom export the only solution at the moment?Regards, Patrick

Renjith Singh Rajendran · Answer

Hi Patrick, 

Greetings from Syncfusion support. 

Yes, the Custom Column Exporting support is available in Grid. Now you can use the codes suggested in our previous update dated February 19, 2020 to perform custom column exporting in Grid.  





    public async Task ExcelExport() 
    { 
        ... 
        ExcelExportProperties ExportProperties = new ExcelExportProperties(); 
 
        //You can generate custom columns and assign to ExportProperties.Columns to perform custom column exporting 
        ExportProperties.Columns = Exportcols;      
        await this.DefaultGrid.ExcelExport(ExportProperties); 
    } 


Please get back to us if you need further assistance. 

Regards, 
Renjith Singh Rajendran

Unknown · Answer

Hi Renjith,thanks for the quick response, but I have still some issues. I tried it like this:private async Task ExcelExport(){    var excelProp = new ExcelExportProperties();    var columns = new List();     var additionalColumn = new GridColumn() { Field = BaseTranslations.worktime, Width = "100px" };     columns.Add(additionalColumn);                 excelProp.Columns = columns;        await Grid.ExcelExport(excelProp);}But I can not build the solutions because I am not allowed to set the "Field" and "With" outside of its component. Kind regards,Patrick

Renjith Singh Rajendran · Answer

Hi Patrick, 

We have prepared a sample based on this scenario. Please download the sample from the link below, 
Sample : https://www.syncfusion.com/downloads/support/directtrac/general/ze/ServerApp-1959590581 
 
Please use like the below codes to add an additional column while exporting in Grid. 





 
    public async Task ExcelExport() 
    {  
        var excelProp = new ExcelExportProperties(); 
        var columns = new List<GridColumn>(); 
        //You can also use GetColumns methods to get all existing grid columns 
#pragma warning disable BL0005 
 
        var additionalColumn = new GridColumn() { Field = "ShipCountry", Width = "100px" }; 
 
#pragma warning restore BL0005 
        columns.Add(additionalColumn); 
        excelProp.Columns = columns; 
        await Grid.ExcelExport(excelProp); 
    } 


Please get back to us if you need further assistance. 

Regards, 
Renjith Singh Rajendran

Petra Visic · Answer

Hello,do you have solution for including template column in Excel export for EJ1?The template column I'd like to export is much simpler than Patric's, it's calculated from some other columns. (as an illustration, having columns Amount and Quantity, template column would be TotalAmount)I can't just add property to datasource's model class because I also use batch edit so columns can be changed after initial load, that's why I need to have a template column.

Pon Selva Jeganathan · Answer

Hi Petra,   
 
Thanks for the update  . 
 
Query: do you have solution for including template column in Excel export for EJ1
 
We have already discussed the same(Excel export with column template) in following help document.   
  
Please refer the below help documentation :   
https://help.syncfusion.com/js/grid/exporting#columntemplate-exporting 
 
Please refer to the below demo: 
https://js.syncfusion.com/demos/jquery/#!/bootstrap/grid/exporting&printing/columntemplateexporting 
 
Kindly get back to us for further assistance. 
 
Regards,   
Pon selva

Petra Visic · Answer

I don't completely understand the documentation. Is templateInfo function obligatory or it's just used for some additional modifications?

I'd like the values from template column to be transferred to controller, not to calculate them again on server side. Is that possible?

Farveen Sulthana Thameeztheen Basha · Answer

Hi Petra, 

Query#:- I'd like the values from template column to be transferred to controller, not to calculate them again on server side. Is that possible? 

From your query we suspect that you need to Include the Template column on Export(without any customization from server end).  To export the Grid with columnTemplate we have to set the IsTemplateColumnIncluded as true in the parameter of the export method. 

Refer to the code below:- 




[HomeController.cs]        public void ExportToExcel(string GridModel) 
        { 
            ExcelExport exp = new ExcelExport(); 
            var DataSource = OrdersDetails.GetAllRecords(); 
            GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel); 
           exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, true, "flat-saffron", true); 
      } 


Please get back to us if you need any further assistance. 

Regards, 
Farveen sulthana T

Petra Visic · Answer

I use Export method with just three parameters, of which the third is GridExcelExport object in which I define properties like FileName, ExcelVersion, Theme and IsTemplateColumnIncluded. So my excel export action looks something like this:

public ActionResult ExcelExport(string GridModel)        
{            
ExcelExport exp = new ExcelExport();            
GridProperties gridProp = ConvertGridObject(GridModel);            
GridExcelExport excelExp = new GridExcelExport();            
excelExp.FileName = "Export" + DateTime.Now.ToString("_dd.MM.yyyy.") + ".xlsx";            
excelExp.Excelversion = ExcelVersion.Excel2016;            
excelExp.Theme = "flat-saffron";            
excelExp.IsTemplateColumnIncluded = true;            
gridProp.Columns.RemoveAt(...)            
gridProp.Columns[12].HeaderText = (...)            
using (SqlConnection connection = new SqlConnection(Global.SQLConnection))            
{                
gridProp.Columns[7].DataSource = (...)            
}            
gridProp.ServerExcelQueryCellInfo = QueryCellInfo;            
gridProp.Columns.Add(new Column() (...)            
var ds = JsonConvert.DeserializeObject(gridProp.DataSource.ToString());            
return exp.Export(gridProp, ds.dataSource.json, excelExp);        
}

When done this way, I get the template column in the excel file, but it's empty (there is just header name in that column).

I've tried do it your way, but I get multiple errors.

Pon Selva Jeganathan · Answer

Hi Petra,  
   
Thanks for the update. 
  
Query: When done this way, I get the template column in the excel file, but it's empty (there is just header name in that column). 
  
We checked your query by preparing sample based on your code snippet, but We are unable to reproduce the issue at our end.   
  
Please refer to the below sample, 
https://www.syncfusion.com/downloads/support/forum/168978/ze/WebApplication1_-_EJ1_MVC652356119 
After following the above reference, still faced issue please share us the following details.

Share the product detail version.
Share the Grid rendering code example.
Share the screenshot of stacktrace details(face if any)
If possible, reproduce the issue in the attached sample or share the issue reproducible sample.
Share the video demo of the issue.
  
The provided information will be helpful to provide you response as early as possible.   
  
Regards, Pon selva

Petra Visic · Answer

I've created incident 344543 and shared details there.

Pon Selva Jeganathan · Answer

Hi Petra,  
   
Thanks for the details. 
 
We will check and update our response via that incident. 

Kindly get back to us for further assistance.
 
Regards, 
Pon selva