Export To Excel grid data

4 posts, 1 answers
  1. Avinash
    Avinash  avatar
    52 posts
    Member since:
    Jan 2016

    Posted 27 Jan Link to this post

    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

  2. Answer
    Dimiter Madjarov
    Admin
    Dimiter Madjarov avatar
    2153 posts

    Posted 28 Jan Link to this post

    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
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  3. Kendo UI is VS 2017 Ready
  4. Avinash
    Avinash  avatar
    52 posts
    Member since:
    Jan 2016

    Posted 28 Jan in reply to Dimiter Madjarov Link to this post

    Dimiter,

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

    Thanks

  5. Avinash
    Avinash  avatar
    52 posts
    Member since:
    Jan 2016

    Posted 28 Jan in reply to Dimiter Madjarov Link to this post

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

Back to Top
Kendo UI is VS 2017 Ready