Using template with different values on Excel Export

[Jack]

So I have this model with these 2 property

 

public DateTime DateTimeChangeDate //  a date time object, with a UTC value, so kendo grid will provides me a date picker for filtering

and 

public string ChangeDate //is an UTC date in iso 8601 format that I pass to moment.js in my view to format the date

 

So in my view it works fine. I have the column defined as like this

  columns.Bound(m => m.DateTimeChangeDate)<br>                    .Title("Date and Time")<br>                    .ClientTemplate("#= formatUtcDateToLocal(ChangeDate)#")<br>                    .Width("12%");

and I am listening to the excel export as well

  .ExcelExport("modifyFormatForExcel")

where modify excel is defined like this

I also have looked at how to use template as mentioned here, but becuase all my template does is call a js function it did not make sense for me to write code to grab the template to only run the js function.

modifyFormatForExcel = function (e) {
        var sheet = e.workbook.sheets[0];
        for (var i = 0; i < sheet.columns.length; i++) {
            if (i === 0) {
                sheet.columns[i].width = 200;
            } else {
                sheet.columns[i].autoWidth = true;
            }
        }

        try {
            // By grouping we shift the column to the right so we need to keep track of how many time it has been shifted
            // in order to find out when the cells with actual data will start
            var dataColumnStart = sheet.filter.from;
            for (var j = 0; j < sheet.rows.length; j++) {
                var row = sheet.rows[j];
                //only apply formatting to excel rows that contains data
                if (row.type === "data") {
                    console.log(dataColumnStart);
                    console.log(e.data);
                    row.cells[dataColumnStart].value = formatUtcDateToLocal(HOW DO I GET CHANGE DATE HERE);
                }
            }
        } catch (exception) {
            logJavascriptError(exception.message, exception.fileName, exception.lineNumber, exception.columnNumber, exception.stack);
           notification.error("An error occurred while exporting to excel", true);
        }
    };

I also looked at using e.data but the array becomes nested as I group and sort the grid before I export to excel. 

Is there a simple way for me to gracmodifyFormatForExcel 

 

[Konstantin Dikov]

Hi Jack,

The "ChangeDate" from the dataItem will be available in the cell within the excelExport event, so you could use it as shown below:

row.cells[dataColumnStart].value = formatUtcDateToLocal(row.cells[dataColumnStart].value);

Hope this helps.


Regards,
Konstantin Dikov
Progress Telerik

[ExactBid]

@Konstantin Dikov - I checked and

1. row.cells only contains the columns that are are visible. I have a column

 columns.Bound(m => m.DateOnlyChangeDate)//hidden column used for grouping
                    .Hidden(true)

that is hidden and is not a property of rows.cells

2. The ChangeDate property of the model is not used as a column either. It is merely there for me to be able to use to format.

[ExactBid]

Found this post. Trying to see if it works for me.

https://www.telerik.com/forums/export-to-grid-hide-columns

[ExactBid]

It did not work. Once I grouped the rows, I lost reference to the hidden columns.

[Konstantin Dikov]

Hello,

Could you please share your Grid configuration and the code that you have for the export? Note that if you are using the e.data collection for the custom code to get reference to the dataItems, you need to take into account the group header rows when traversing the rows in the workbook.


Regards,
Konstantin Dikov
Progress Telerik

[ExactBid]

I found a solution that worked for me, and yes, I saw that I needed to account for group header rows for both the workbook and the data source of e.sender (the solution I decided to go with). So for workbook I checked if the row is a data row, and for the data source I flatten it out so it is easy to traverse.

Here is my grid configuration and excel export for the solution I decided to go with

@(Html.Kendo().Grid<ChangeLogModel>()
            .Name("ChangeLog")
            .Columns(columns =>
            {
                columns.Bound(m => m.DateOnlyChangeDate)//hidden column used for grouping
                    .Hidden(true)
                columns.Bound(m => m.DateTimeChangeDate)
                    .Title("Date and Time")
                    .ClientTemplate("#= formatUtcDateToLocal(new Date(DateTimeChangeDate).toISOString())#")
                    .Width("12%");
                columns.Bound(m => m.Component)
                    .Title("Component")
                    .Width("8%")
                    .Filterable(filterable => filterable
                        .UI("componentFilter")
                        .Extra(false)
                        .Operators(operators => operators
                            .ForString(str => str.Clear()
                            .Contains("Contains")
                         ))
                );
                columns.Bound(m => m.Action)
                    .Title("Action")
                    .Width("15%")
                    .Filterable(filterable => filterable
                        .UI("actionFilter")
                        .Extra(false)
                        .Operators(operators => operators
                            .ForString(str => str.Clear()
                            .Contains("Contains")
                        ))
                     );
                columns.Bound(m => m.Changer)
                    .Title("Changer")
                    .Width("10%");
                columns.Bound(m => m.Identifier)
                    .Title("On")
                    .Width("15%");
                columns.Bound(m => m.OldValue)
                    .Title("Old Value")
                    .Width("20%");
                columns.Bound(m => m.NewValue)
                    .Title("New Value")
                    .Width("20%");
            })
            .Filterable()
            .Selectable()
            .Sortable()
            .Groupable()
            .Pageable()
            .Scrollable()
            .Resizable(resize => resize.Columns(true))
            .ToolBar(toolbar =>
            {
            toolbar.Template(
                @<text>
                    @*
                        The year is 2017, kendo does not have a realiable html server control for the excel button when we are using a template.
                        So we have to explicitly use the excel export command button mark up. Kendo will recoginze the k-grid-excel CSS class and configure it accoridngly.
                    *@
                    <a class="k-button k-button-icontext k-grid-excel" href="#"><span class="k-icon k-i-excel"></span>Export to Excel</a>
                    <span style='padding-left:35%;font-weight:bold;padding-top:7px'>@ViewBag.ProjectNumber</span>
                </text>
                );
            })
            .Excel(excel => excel.AllPages(true).FileName(@ViewBag.ChangeLogType + " Change Log " + @ViewBag.ProjectNumber + ".xlsx").Filterable(true))
            .DataSource(dataSource => dataSource
                 .Ajax()
                 .Model(model =>
                 {
                     model.Id(p => p.Id);
                 })
                 .PageSize(500)
                 .Read(read => read.Action("FetchChangeLogData", "ChangeLog")
                 .Data("buildParam"))
           )
           .Events(e => e.FilterMenuInit("resizeFilter")
                         .ExcelExport(".modifyFormatForExcel")
                         .DataBound("preselectCustomFilter"))
           .ClientDetailTemplateId("change-log-details-template")
    )

 

and  my excel export function

 

modifyFormatForExcel = function(e) {
        var sheet = e.workbook.sheets[0];
        for (var i = 0; i < sheet.columns.length; i++) {
            if (i === 0) {
                sheet.columns[i].width = 200;
            } else {
                sheet.columns[i].autoWidth = true;
            }
        }
        var gridDataSource = e.sender.dataSource.data().toJSON();
        var flatDataSource = flattenDataSource(gridDataSource);

        try {
            // By grouping we shift the column to the right so we need to keep track of how many time it has been shifted
            // in order to find out when the cells with actual data will start
            var dataColumnStart = sheet.filter.from;
            for (var j = 0; j < sheet.rows.length; j++) {
                var row = sheet.rows[j];
                //only apply formatting to excel rows that contains data
                if (row.type === "data") {
                    //Find ISO string of DateTimeChangeDate
                    for (var k = 0; k < flatDataSource.length; k++) {
                        var currentDataItem = flatDataSource[k];
                        if (currentDataItem.DateTimeChangeDate.getTime() === row.cells[dataColumnStart].value.getTime()) {
                            row.cells[dataColumnStart].value = formatUtcDateToLocal(currentDataItem.ChangeDate);
                            break;
                        }
                    }
                }
            }
        } catch (exception) {
           logging.logJavascriptError(exception.message,
                exception.fileName,
                exception.lineNumber,
                exception.columnNumber,
                exception.stack);
           notification.error("An error occurred while exporting to excel", true);
        }
    };

 

[ExactBid]

Opps copied the wrong code for the configuration

This needs to be changed, the cahnge date field is already in ISO 8601 format

.ClientTemplate("#= formatUtcDateToLocal(new Date(DateTimeChangeDate).toISOString())#")

 

to

.ClientTemplate("#= eformatUtcDateToLocal(ChangeDate)#")

[Konstantin Dikov]

Hello,

I am glad to see that everything is working as per your requirement now. I also want to thank you for sharing the solution with the community.

You should find your Telerik Points updated.


Best Regards,
Konstantin Dikov
Progress Telerik