Export To Excel grid data

[Avinash]

I have a grid with the toolbar to export grid data in excel. Exporting grid data to excel works however i need to modify value of one of the column when it gets exported. The grid has inline editing, when in edit mode the 'Gender' column changes to dropdownlist. The value of 'GenderCombinedValues' is the combination of 4 properties with comma delimiter. When exporting grid data to excel, the Gender column exports data value (4 properties with comma delimeter) and i want to only export text value of the Gender column.

 Model:

 

public class SupplierSelfServiceResultViewModel
{
    public List<ContactPersonDetailsViewModel> contactPersonDetails   { get; set; }
}

public class ContactPersonDetailsViewModel
{
    public string LineID                         { get; set; }
    public string FirstName                      { get; set; }
    public string LastName                       { get; set; }

    [Display(Name = "Gender")]
    public string Gender                         { get; set; }
    public string GenderProductCode              { get; set; }
    public string GenderUserDefinedCodes         { get; set; }
    public string GenderUserDefinedCode          { get; set; }
    [Display(Name = "Gender")]
    public string GenderCombinedValues           { get; set; }
  //combination of Gender, GenderProductCode, GenderUserDefinedCodes, GenderUserDefinedCode
}

public class CommonDetailsViewModel
{
    public List<GenderItem> Genders              { get; set; }
}

public class GenderItem : CommonDetailsCommonProperties
{
    public string Gender               { get; set; }
    public string ProductCode          { get; set; }
    public string UserDefinedCodes     { get; set; }
    public string UserDefinedCode      { get; set; }
    public string CombinedValuesToSave { get; set; }
   //combination of Gender, ProductCode, UserDefinedCodes, UserDefinedCode
}

EditorTemplate:

 

@using System.Collections
@using Kendo.Mvc.UI;

@(Html.Kendo().DropDownList()
 .BindTo((IEnumerable)ViewBag.GenderList)
    .OptionLabel("- Select Gender - ")
    .DataValueField("CombinedValuesToSave")
    .DataTextField("Gender")
    .Name("GenderCombinedValues")
)

View:

@(Html.Kendo().Grid(Model.contactPersonDetails)
    .Name("GridContactPersonDetails")
    .Columns(columns =>
    {
        columns.Command(command => { command.Edit(); }).Width("180px").Title("Action");
        columns.Bound(e => e.LineID).Width("200px").Visible(Model.ContactPersonColumns.LineID);
        columns.Bound(e => e.FirstName).Width("120px");
        columns.Bound(e => e.LastName).Width("120px");
        columns.Bound(e => e.GenderCombinedValues).Width("120px").EditorTemplateName("_GenderDropDownList").ClientTemplate("#:Gender#");
    })
    .Excel(excel => excel
        .FileName("ContactPersonDetails.xlsx")
        .ProxyURL(Url.Action("Excel_Export_Save", "Supplier"))
        .AllPages(true))
    .ToolBar(tools =>
    {
        tools.Template(@<text>                                                                                                                 <div class="col-lg-1 col-md-2 col-sm-2 col-xs-4 pull-right" style="padding-left: 0; margin-top: 2px; text-align: right;">
      <a class="k-button k-button-icontext k-grid-excel POexport" href="#" style="padding-left: 10px;" title="Export to Excel"><span class="k-icon k-i-excel"></span></a>
                           </div>
                        </text>);
    })
    .Editable(editable => editable.Mode(GridEditMode.InLine))
    .DataSource(dataSource => dataSource
                .Ajax()
                .PageSize(10)
                .ServerOperation(false)
                .Events(events => events.Error("webMethodErrHandler"))
                .Model(model =>
                {
                    model.Id(p => p.LineID);
                    model.Field(p => p.LineID).Editable(false);
                })
                .Create(update => update.Action("CreateContactPersonDetail", "SupplierSelfService"))
                .Update(update => update.Action("UpdateContactPersonDetail", "SupplierSelfService")))
)

 

 Controller

 

public ActionResult Index()
{
    SupplierSelfServiceResultViewModel model = service.InvokeGetContactInfo();

    CommonDetailsViewModel commonDetailsViewModel = commonService.InvokeGetContacts();
    ViewBag.GenderList = commonDetailsViewModel.Genders;

    return View(model);
}

[HttpPost]
public ActionResult Excel_Export_Save(string contentType, string base64, string fileName)
{
    var fileContents = Convert.FromBase64String(base64);
    return File(fileContents, contentType, fileName);
}

 

 Any suggestion how can i achieve that? See attached excel.png to get the idea of the exported data.

Thanks

Avinash

[Dimiter Madjarov]

Hello Avinash,

In this case you could manually modify the value of the Gender cell in the excelExport event of the Grid. Here is an example that demonstrates the approach.

Regards,
Dimiter Madjarov
Telerik

 

 

[Avinash]

Dimiter,

Do we have any example showing  this for ASP.NET MVC?

Thanks

[Avinash]

Dimiter, you can close this issue.

Thanks.

Here is how i fixed it :)

@(Html.Kendo().Grid(Model.contactPersonDetails)
    .Name("GridContactPersonDetails")
    .Columns(columns =>
    {
        columns.Command(command => { command.Edit(); }).Width("180px").Title("Action");
        columns.Bound(e => e.LineID).Width("200px").Visible(Model.ContactPersonColumns.LineID);
        columns.Bound(e => e.FirstName).Width("120px");
        columns.Bound(e => e.LastName).Width("120px");
        columns.Bound(e => e.GenderCombinedValues).Width("120px").EditorTemplateName("_GenderDropDownList").ClientTemplate("#:Gender#");
    })
    .Excel(excel => excel
        .FileName("ContactPersonDetails.xlsx")
        .ProxyURL(Url.Action("Excel_Export_Save", "Supplier"))
        .AllPages(true))
    .ToolBar(tools =>
    {
        tools.Template(@<text>
          <div class="col-lg-1 col-md-2 col-sm-2 col-xs-4 pull-right" style="padding-left: 0; margin-top: 2px; text-align: right;">
             <a class="k-button k-button-icontext k-grid-excel POexport" href="#" style="padding-left: 10px;" title="Export to Excel">
             <span class="k-icon k-i-excel"></span></a>
          </div>
                        </text>);
    })
    .Editable(editable => editable.Mode(GridEditMode.InLine))
    .DataSource(dataSource => dataSource
                .Ajax()
                .PageSize(10)
                .ServerOperation(false)
                .Events(events => events.Error("webMethodErrHandler"))
                .Model(model =>
                {
                    model.Id(p => p.LineID);
                    model.Field(p => p.LineID).Editable(false);
                })
                .Create(update => update.Action("CreateContactPersonDetail", "SupplierSelfService"))
                .Update(update => update.Action("UpdateContactPersonDetail", "SupplierSelfService")))
    .Events(events => events.ExcelExport("contactPersonExcelExport"))
)

function contactPersonExcelExport(e) {
    var data = e.data;
    var rows = e.workbook.sheets[0].rows;

    for (var i = 1; i < rows.length; i++) {
        rows[i].cells[3].value = data[i - 1].Gender;
    }
}