Using template with different values on Excel Export

9 posts, 0 answers
  1. Jack
    Jack avatar
    1 posts
    Member since:
    Apr 2014

    Posted 13 Feb 2018 Link to this post

    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 

     

  2. Konstantin Dikov
    Admin
    Konstantin Dikov avatar
    2466 posts

    Posted 15 Feb 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. ExactBid
    ExactBid avatar
    8 posts
    Member since:
    Oct 2017

    Posted 20 Feb 2018 Link to this post

    @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.

  4. ExactBid
    ExactBid avatar
    8 posts
    Member since:
    Oct 2017

    Posted 20 Feb 2018 in reply to Konstantin Dikov Link to this post

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

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

  5. ExactBid
    ExactBid avatar
    8 posts
    Member since:
    Oct 2017

    Posted 22 Feb 2018 in reply to Konstantin Dikov Link to this post

    It did not work. Once I grouped the rows, I lost reference to the hidden columns.
  6. Konstantin Dikov
    Admin
    Konstantin Dikov avatar
    2466 posts

    Posted 26 Feb 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  7. ExactBid
    ExactBid avatar
    8 posts
    Member since:
    Oct 2017

    Posted 26 Feb 2018 in reply to Konstantin Dikov Link to this post

    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);
            }
        };

     

  8. ExactBid
    ExactBid avatar
    8 posts
    Member since:
    Oct 2017

    Posted 26 Feb 2018 in reply to Konstantin Dikov Link to this post

    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)#")
  9. Konstantin Dikov
    Admin
    Konstantin Dikov avatar
    2466 posts

    Posted 28 Feb 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top