Export excel from grid not working with dropdownlist multi checkbox

[Theo B]

Hello Syncfusion,

I'm trying to use the excel export from the grid in asp.net mvc but i'm having trouble.

The export doesn't work with data in dropdown list multi checkbox.

Here is the cshtml page named : ListExport.cshtml 

@(Html.EJ().Grid<object>("TableGrid")

    .Datasource(ds => ds.Json((IEnumerable<object>)ViewBag.dataSource).UpdateURL("../Exceptionnel/InlineUpdate").Adaptor(AdaptorType.RemoteSaveAdaptor))

    .EditSettings(edit => { edit.AllowEditing(); })

    .ToolbarSettings(toolbar =>

    {

        toolbar.ShowToolbar().ToolbarItems(items =>

        {

            items.AddTool(ToolBarItems.Edit);

            items.AddTool(ToolBarItems.Update);

            items.AddTool(ToolBarItems.Cancel);

            items.AddTool(ToolBarItems.Search);

            items.AddTool(ToolBarItems.ExcelExport);

        });

    })

    .Mappers(map => map.ExportToExcelAction("ExcelAction"))

    .FilterSettings(filter => { filter.FilterType(FilterType.Excel); })

    .Columns(col =>

    {

        col.Field("Id").IsPrimaryKey(true).HeaderText().Width(0).Visible(false).ShowInColumnChooser(false).Add();

        col.Field("CodeIsagi").HeaderText("CodeIsagi").Visible(true).AllowEditing(false).Add();

        col.Field("Clients").HeaderText("Clients").Visible(true).AllowEditing(false).Add();

        col.Field("Dirigeant").HeaderText("Dirigeant").Visible(true).AllowEditing(false).Add();

        col.Field("NumeroDossier").HeaderText("NumeroDossier").Visible(true).AllowEditing(false).Add();

        col.Field("IdUtilisateur").HeaderText("Collaborateur fulturis").Visible(true).ForeignKeyField("Id").ForeignKeyValue("Nom").DataSource((IEnumerable<object>)ViewBag.Utilisateur).Add();

        col.Field("IdAvocat").HeaderText("Avocat").Visible(true).ForeignKeyField("Id").ForeignKeyValue("Libelle").DataSource((IEnumerable<object>)ViewBag.Avocat).Add();

        col.Field("IdCollaborateur").HeaderText("Collaborateur").Visible(true).ForeignKeyField("Id").ForeignKeyValue("Libelle").DataSource((IEnumerable<object>)ViewBag.Collaborateur).Add();

        col.Field("IdExpertComptable").HeaderText("Expert comptable").Visible(true).ForeignKeyField("Id").ForeignKeyValue("Nom").DataSource((IEnumerable<object>)ViewBag.Expert).Add();

        col.Field("Mission").HeaderText("Mission").Width(150).Visible(true).EditTemplate(a => { a.Create("create").Read("read").Write("write"); }).Add();

        col.Field("Formalite").HeaderText("Formalite").Width(150).Visible(true).EditTemplate(b => {b.Create("createFormalite").Read("readFormalite").Write("writeFormalite"); }).Add();

        col.Field("IdEtat").HeaderText("Etat").Width(150).Visible(true).ForeignKeyField("Id").ForeignKeyValue("Libelle").DataSource((IEnumerable<object>)ViewBag.EtatDossier).Add();

    }).ClientSideEvents(evt => evt.ToolbarClick("OnToolbarClick"))

)

<script type="text/javascript">

    function create(args) {

        return "<input>";

    }

    function read(args) {

        return args.ejDropDownList("getValue").split(",");

    }

    function write(args) {

        var dropData = @Html.Raw(Json.Encode(ViewData["data"]));

        var selIndex = [];

        if (args.rowdata != undefined)

        {

            for (i = 0; i < args.rowdata["Mission"].length; i++)

            for (j = 0; j < dropData.length; j++){

                if (args.rowdata["Mission"][i] == dropData[j].value) {

                    selIndex.push(j);

                    break;

                }

            }

        }

        args.element.ejDropDownList({ width: "100%", dataSource: dropData, fields: { id: "text", text: "text", value: "value" }, showCheckbox: true, allowMultiSelection: true, selectedItems: args.rowdata !== undefined ? selIndex : "" });

    }

</script>

<script type="text/javascript">

    function createFormalite(args) {

        return "<input>";

    }

    function readFormalite(args) {

        return args.ejDropDownList("getValue").split(",");

    }

    function writeFormalite(args) {

        var dropDataFormalite = @Html.Raw(Json.Encode(ViewData["formalite"]));

        var selIndex = [];

        if (args.rowdata != undefined)

        {

            for (i = 0; i < args.rowdata["Formalite"].length; i++)

            for (j = 0; j < dropDataFormalite.length; j++){

                if (args.rowdata["Formalite"][i] == dropDataFormalite[j].value) {

                    selIndex.push(j);

                    break;

                }

            }

        }

        args.element.ejDropDownList({ width: "100%", dataSource: dropDataFormalite, fields: { id: "text", text: "text", value: "value" }, showCheckbox: true, allowMultiSelection: true, selectedItems: args.rowdata !== undefined ? selIndex : "" });

    }

</script>

-----------------------------------------

And my controller page :

public void ExcelAction(string GridModel)

{

ExcelExport exp = new ExcelExport();

var DataSource = ListECVM();

GridProperties obj = ConvertGridObject(GridModel);

obj.Columns[5].DataSource = CollaborateurFulturisVM();

obj.Columns[6].DataSource = AvocatVM();

obj.Columns[7].DataSource = CollaborateurVM();

obj.Columns[8].DataSource = ExpertVM();

obj.Columns[11].DataSource = EtatDossierVM();

exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, true, false, "flat-saffron");

}

private GridProperties ConvertGridObject(string gridProperty)

{

JavaScriptSerializer serializer = new JavaScriptSerializer();

IEnumerable div = (IEnumerable)serializer.Deserialize(gridProperty, typeof(IEnumerable));

GridProperties gridProp = new GridProperties();

foreach (KeyValuePair<string, object> ds in div)

{

var property = gridProp.GetType().GetProperty(ds.Key, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);

if (property != null)

{

Type type = property.PropertyType;

string serialize = serializer.Serialize(ds.Value);

object value = serializer.Deserialize(serialize, type);

property.SetValue(gridProp, value, null);

}

}

return gridProp;

}

public ActionResult ListExport()

{

ViewBag.Datasource = ListECVM();

ViewBag.EtatDossier = EtatDossierVM();

ViewBag.Avocat = AvocatVM();

ViewBag.Collaborateur = CollaborateurVM();

ViewBag.Expert = ExpertVM();

ViewBag.Utilisateur = CollaborateurFulturisVM();

List<string> listString = new List<string>();

foreach (Mission m in dal.GetAllMission())

{

listString.Add(m.Libelle);

}

var drop = listString.ToList();

var missionData = new List<object>();

foreach (var li in drop)

{

missionData.Add(new { value = li, text = li });

}

ViewData["data"] = missionData;

List<string> listStringFormalite = new List<string>();

foreach (Formalite f in dal.GetAllFormalite())

{

listStringFormalite.Add(f.Libelle);

}

var dropFormalite = listStringFormalite.ToList();

var formaliteData = new List<object>();

foreach (var li in dropFormalite)

{

formaliteData.Add(new { value = li, text = li });

}

ViewData["formalite"] = formaliteData;

return View();

}

You can see the attach file which is the result.

The dropdownlist are exported too System.Collections.Generic.List`1[System.String]

It is possible to export them with the same value that is in the grid?

Thank.

Attachment: image_50a011d8.zip

[Thavasianand Sankaranarayanan]

Hi Theo, 

Thanks for contacting Syncfusion support. 

We have analyzed your query and we are able to reproduce the reported issue from our end. The reported issue is because of the cell has a list of string in it and we do not have the support to render the list of string in the exported file. So, we suggest you to use the ServerExcelQueryCellInfo event of Grid control and it will triggered for every cell value has been rendered in excel sheet.  

 

Refer the below code example. 

 

[GridCOntroller.cs] 

List<Orders> currentData = null; 

public int count = 0; 

---- 

public void ExportToExcel(string GridModel) 

        { 

            ExcelExport exp = new ExcelExport(); 

            BindDataSource(); 

            var DataSource = order; 

            currentData = order;  

            GridProperties obj = ConvertGridObject(GridModel); 

            obj.ServerExcelQueryCellInfo = QueryCellInfo; // 

            exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron"); 

 

        } 

        public void QueryCellInfo(object currentCell) 

        { 

            IRange range = (IRange)currentCell; 

            if (range.Column == 7) 

            { 

                range.Value = string.Join(",", currentData[count].Cities);  //apply the string to the excel style sheet cell 

                count++; 

 

            }         

         } 

We have prepared a sample and it can be downloadable from the below location. 

Sample: http://www.syncfusion.com/downloads/support/forum/134809/ze/SyncfusionMvcApplication14784212154.zip 

Refer the help documentation. 

Link: https://help.syncfusion.com/aspnetmvc/grid/exporting#exporting-server-events 

  

Regards, 

Thavasianand S. 

[Theo B]

Hello Thavasianand,

Thank for your help!

I tried your code and it works for one column but when i try with two columns, i got an error. 

Here is my controller :

public void QueryCellInfo1(object currentCell)

        {

            IRange range = (IRange)currentCell;

            if (range.Column == 10)

            {

                range.Value = string.Join(",", currentData[count].Mission);  //apply the string to the excel style sheet cell 

                count++;

            }

            if (range.Column == 11)

            {

                range.Value = string.Join(",", currentData[count].Formalite);  //apply the string to the excel style sheet cell 

                count++;

            }

        }

Regards,

Theo

[Thavasianand Sankaranarayanan]

Hi Theo, 

We have analyzed your query and we are able to reproduce the reported issue from our end. So we suggest you to use the below code example. 

Refer the below code example. 

[GridController.cs] 

public void QueryCellInfo1(object currentCell) 

        { 

            IRange range = (IRange)currentCell; 

            if (range.Column == 10) 

            { 

                range.Value = string.Join(",", currentData[count].Mission);  //apply the string to the excel style sheet cell  

 

                count++; // remove this line from your code example. 

 

            } 

            if (range.Column == 11) 

            { 

                range.Value = string.Join(",", currentData[count].Formalite);  //apply the string to the excel style sheet cell  

                count++; 

            } 

        } 

   

Regards, 

Thavasianand S. 

[Theo B]

Hello Thavasianand,

I tried your fix and it works!

Thank a lot for the help.

Keep up your great work.

Regards,

Theo

[Thavasianand Sankaranarayanan]

Hi Theo, 

 

We are happy that the problem has been solved at your end. 

 

Please get back to us if you need any further assistance.  

 

Regards, 

Thavasianand S.