Customizing Column Headers With DataSource

[CLOCA]

Hi,

I am wondering if it possible to change the text from whatever the viewmodel properties are in the column headers to something custom after the spreadsheet has read from the datasource.

For example, I have a viewmodel with properties "SundayOne", "MondayOne" etc but I would like to dynamically change the value in the spreadsheet cell from "SundayOne" to a specific date like "Nov 12th" depending on what data items get loaded after the read event to make it more user friendly.

However when I attempt to change the value of the cell after the read the changes don't take effect.

I have attached a picture of the cells I am trying to edit in case it is unclear.

Thanks.

[Veselin Tsvetanov]

Hello Cloca,

Yes, you can override the default headers for the columns loaded from a DataSource. This could be done by handling the requestEnd event of the DataSource itself. Here you will find a short KB demonstrating how the above could be acheived.

Regards,
Veselin Tsvetanov
Progress Telerik

[CLOCA]

Hi,
Thank you for your quick response, however I am having some difficulty implementing it. I am calling this function after performing a datasource.read(); on the spreadsheet.

$(function () {


              var shouldPopulateHeader = true;

              var payPeriodId = $("#PayPeriods").data("kendoDropDownList").value();
              var url = "@Url.Action("GetPayPeriodEntry", "PayPeriodsApi", new { payPeriodId = "{payPeriodId}" })";

              url = url.replace("%7BpayPeriodId%7D", payPeriodId);


              debugger;
              var dataSource = new kendo.data.DataSource({
                  requestEnd: function (e) {
                      setTimeout(function (e) {

                          debugger;
                          if (shouldPopulateHeader) {
                              shouldPopulateHeader = false;

                              var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
                              var sheet = spreadsheet.activeSheet();


                              sheet.batch(function () {
                                  sheet.range("D1").value("SERVICE ID");
                                  sheet.range("E1").value("CUSTOM ");
                              }, { recalc: true });
                          }
                      }, 0);
                  },
                  transport: {
                      read: {
                          url: url,
                          dataType: "jsonp"
                      }
                  },
                  schema: {
                      model: {
                          id: "EntryId",
                          fields: {
                              PayPeriodId: { type: "number" },
                              UserId: { type: "number" },
                              DepartmentCode: { type: "number" },
                              SundayOne: { type: "number" },
                              MondayOne: { type: "number" },
                              TuedayOne: { type: "number" },
                              WednesdayOne: { type: "number" },
                              ThursdayOne: { type: "number" },
                              FridayOne: { type: "number" },
                              SaturdayOne: { type: "number" },
                              SundayTwo: { type: "number" },
                              MondayTwo: { type: "number" },
                              TuedayTwo: { type: "number" },
                              WednesdayTwo: { type: "number" },
                              ThursdayTwo: { type: "number" },
                              FridayTwo: { type: "number" },
                              SaturdayTwo: { type: "number" }

                          }
                      }
                  }
              });
          });

When I the code reaches:

dataSource = new kendo.data.DataSource({
requestEnd: function (e) {

the code jumps past it and I can see that requestEnd is undefined when inspecting it in visual studio.
I am hoping you can point me in the right direction.
thanks again.

[Veselin Tsvetanov]

Hi Cloca,

I am not sure, that I fully understand the scenario described. How do you pass the configured DataSource to the Spreadsheet? What do you mean by "I am calling this function after performing a datasource.read(); on the spreadsheet."? Do you set the DataSource after the initialization of the widget? If this is the case, here you could find a simple Dojo demonstrating it:

var spread = $("#spreadsheet").getKendoSpreadsheet();
spread.activeSheet().setDataSource(dataSource);

Regards,
Veselin Tsvetanov
Progress Telerik

[CLOCA]

This is how I have my datasource configured for the spreadsheet using .NetCore. I have the datasource set on the widget load just the dataItems change after the page load based on some user input.

@(Html.Kendo().Spreadsheet()
           .Name("reportSpreadsheet")

           .Columns(5)
           .HtmlAttributes(new { style = "width:85%;" })
           .Events(events => events
               .Change("onChangeReport")
             )
           .Toolbar(true)
           .Sheetsbar(false)

           .Excel(excel => excel
               .ProxyURL(Url.Action("Index_Save", "PayPeriodsApi"))
           )
           .Pdf(pdf => pdf
               .ProxyURL(Url.Action("Index_Save", "PayPeriodsApi"))
           )
           .Sheets(sheets =>
           {
               sheets.Add()
                   .Name("Report")
                   .DataSource<IMSCore.ViewModels.ReportPayPeriodEntriesViewModel>(ds => ds
                     .Ajax()
                     .Batch(true)
                     .Read(read => read.Action("GetPayEntries", "PayPeriodsApi").Data("PayPeriodReport"))

                 .Model(m =>
                 {
                     m.Id(p => p.EntryId);
                 })

 

When some additional parameters are set by the user on the page I perform another read using "$("#spreadsheet").data("kendoSpreadsheet").activeSheet().dataSource.read();" in the following bit of code

@* change event for pay period drop down list *@

   $(document).on("change", "#PayPeriods", function (e) {

       $(".calendarWrapper").removeClass("hidden");

       //reload the spreadsheet with new data items
       $("#spreadsheet").data("kendoSpreadsheet").activeSheet().dataSource.read();


         $(function () {


               var shouldPopulateHeader = true;

               var payPeriodId = $("#PayPeriods").data("kendoDropDownList").value();
               var url = "@Url.Action("GetPayPeriodEntry", "PayPeriodsApi", new { payPeriodId = "{payPeriodId}" })";

               url = url.replace("%7BpayPeriodId%7D", payPeriodId);


               debugger;
               var dataSource = new kendo.data.DataSource({
                   requestEnd: function (e) {
                       setTimeout(function (e) {

                           debugger;
                           if (shouldPopulateHeader) {
                               shouldPopulateHeader = false;

                               var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
                               var sheet = spreadsheet.activeSheet();


                               sheet.batch(function () {
                                   sheet.range("D1").value("SERVICE ID");
                                   sheet.range("E1").value("CUSTOM ");
                               }, { recalc: true });
                           }
                       }, 0);
                   },
                   transport: {
                       read: {
                           url: url,
                           dataType: "jsonp"
                       }
                   },
                   schema: {
                       model: {
                           id: "EntryId",
                           fields: {
                               PayPeriodId: { type: "number" },
                               UserId: { type: "number" },
                               DepartmentCode: { type: "number" },
                               SundayOne: { type: "number" },
                               MondayOne: { type: "number" },
                               TuedayOne: { type: "number" },
                               WednesdayOne: { type: "number" },
                               ThursdayOne: { type: "number" },
                               FridayOne: { type: "number" },
                               SaturdayOne: { type: "number" },
                               SundayTwo: { type: "number" },
                               MondayTwo: { type: "number" },
                               TuedayTwo: { type: "number" },
                               WednesdayTwo: { type: "number" },
                               ThursdayTwo: { type: "number" },
                               FridayTwo: { type: "number" },
                               SaturdayTwo: { type: "number" }

                           }
                       }
                   }
               });

I was attempting to implement the "request end" function you provided after this, but with no luck. I hope this make things more clear as to what I am attempting to do.

Thanks for your help.

 

[Veselin Tsvetanov]

Hi Cloca,

Thank you for the additional information provided.

Please, excuse me for not fully explaining how the referred KB article could be applied in a .NET Core scenario.

In case you have the following declaration of the reportSpreadsheet Spreadsheet widget:

@(Html.Kendo().Spreadsheet()
    .Name("reportSpreadsheet")
    .Columns(5)
    .HtmlAttributes(new { style = "width:85%;" })
    .Events(events => events
        .Change("onChangeReport")
    )
    .Toolbar(true)
    .Sheetsbar(false)
    .Sheets(sheets =>
    {
        sheets.Add()
            .Name("Report")
            .DataSource<ServerFiltering.Models.TestViewModel>(ds => ds
                .Ajax()
                .Batch(true)
                .Read(read => read.Action("GetPayEntries", "Home").Data("PayPeriodReport"))
                .Model(m =>
                {
                    m.Id(p => p.EntryId);
                })
                .Events(e => e.RequestEnd("onRequestEnd"))
            );
    })
)

You could attach the requestEnd event handler directly in the HTML helper initialization (see the yellow line in the DataSource configuration). As you intend to perform multiple Read requests, you could alter the handler in the following way:

function onRequestEnd(e) {
    if (e.type === 'read') {
        setTimeout(function (e) {
            var spreadsheet = $("#reportSpreadsheet").data("kendoSpreadsheet");
            var sheet = spreadsheet.activeSheet();

            // Change the default headers for the first and the second column
            sheet.batch(function () {
                sheet.range("A1").value("SERVICE ID");
                sheet.range("B1").value("CUSTOM SERVICE DESCRIPTION HEDER");
            }, { recalc: true });
        }, 0);
    }
}

I would also suggest you to attach a change event handler for the DropDownList in question:

@(Html.Kendo().DropDownList()
    .Name("PayPeriods")
    .Events(e => e.Change("onChange"))

and:

function onChange(e) {
    $("#reportSpreadsheet").data("kendoSpreadsheet").activeSheet().dataSource.read();
}

While the Read.Data() function (in green on the first snippet) will pass the currently selected value in the DropDownList to the server:

function PayPeriodReport(e) {
    var payPeriodId;
    var dropDown = $("#PayPeriods").data("kendoDropDownList");

    if (dropDown) {
        payPeriodId = dropDown.value();
    }

    return {
        payPeriodId: payPeriodId
    }
}

Attached you will find a simple .NET Core solution implementing the above approach.

Regards,
Veselin Tsvetanov
Progress Telerik