Grid Excel Export with custom Parameter

2 Answers 469 Views
Grid
Mitchell
Top achievements
Rank 1
Iron
Mitchell asked on 03 May 2023, 04:48 PM

I have a drop down list suppling a parameter to my grid to load data specifically based on the selected drop down list item.  It is working on filtering data and sorting, and when the drop down list change event.  Some reason the Excel Export is not working.  Any information would be appreciated.

<select asp-page-handler="LoadGrid" asp-items="Model.Months" asp-for="@Model.mySetDate" value="@Model.mySetDate" onselect="this.EditLog.submit();" onchange="DoPostBack();" class="form-control" ></select>
        <br />

 

<kendo-grid name="gvRequests" navigatable="true" auto-bind="true" on-excel-export="excelExport">
            <pageable enabled="true" page-size="100"></pageable>
            <sortable enabled="true" />
            <filterable enabled="true" />
            <columns>
                <column field="LogID" width="100" hidden="true" />
                <column field="SetDate" title="Set Date" width="100" template="#= kendo.toString(kendo.parseDate(SetDate, 'yyyy-MM-dd'), 'MM/dd/yyyy') #"  />
                <column field="Project" title="Project" width="200" />
                <column field="Task" title="Task" width="200" />
                <column field="Action" title="Action" width="200" />
                <column field="LogHours" title="Hours" width="200" />
                <column template="<input name='Edit' type='submit' value='Edit LogID:#=LogID#' />"></column>
            </columns>
            <datasource type="DataSourceTagHelperType.Ajax" server-operation="true">
                <transport >
                    <read  url="@Url.Content("~/ManageLog?handler=Read")" datatype="json" type="POST" data="forgeryToken"  />
                    
                </transport>
            </datasource>
            <toolbar>
                <toolbar-button name="excel" action=""></toolbar-button>
            </toolbar>
            <excel all-pages="true" file-name="EntryLog.xlsx" proxy-url="@Url.Content("~/ManageLog?handler=Read")" data="kendo.antiForgeryTokens()"  />
            
        </kendo-grid>
        


        <script>
            function forgeryToken() {
                var select = document.getElementById("mySetDate");
                var option = select.options[select.selectedIndex];
                const requestVerificationToken = '@Xsrf.GetAndStoreTokens(HttpContext).RequestToken';
                const beforeSend = req => req.setRequestHeader('RequestVerificationToken', requestVerificationToken);
                const grid = $("#gvRequests").getKendoGrid();
                grid.dataSource.transport.options.read.beforeSend = beforeSend;

                //return kendo.antiForgeryTokens();
                var values = {};
                
                values["mydate"] = option.value;
                return values;

                //return {
                //    antiForgeryTokens: kendo.antiForgeryTokens(),
                //    mydate: option.value//$("#ServiceCompany").val()
                //};

            }
        </script>
        <script type="text/javascript">


    function DoPostBack() {
        var select = document.getElementById("mySetDate");
        var option = select.options[select.selectedIndex];
        if (option.value != "Please select") {
            
            //$("#gvRequests").data("kendoGrid").setDataSource(dataSource);
            $('#gvRequests').data('kendoGrid').dataSource.read({ mydate: option.value })
            
        }
    }
        </script>

2 Answers, 1 is accepted

Sort by
0
Accepted
Mitchell
Top achievements
Rank 1
Iron
answered on 03 May 2023, 10:40 PM

This is the solution I came up with seems kind of hacky so if others have a solution please let me know.  Thanks.

public JsonResult OnPostRead([DataSourceRequest] DataSourceRequest request, string mydate)
        {
            AppLog authcheck = new AppLog(SQLWrapper);
            //mySetDate = mydate;
            if (mydate == null && HttpContext.Session.GetString("mySetDate2") == null)
            {
                mydate = DateTime.Now.ToShortDateString();
                HttpContext.Session.SetString("mySetDate2", DateTime.Now.ToShortDateString());
            }
            else if(mydate == null)
            {
                mydate = HttpContext.Session.GetString("mySetDate2");
            }
            else
            {
                HttpContext.Session.SetString("mySetDate2", mydate);
            }
            DataSet ds = authcheck.SelectRecordsForGrid(HttpContext.Session.GetInt32(Globals.SessionName.Userid.ToString()), mydate, "SET_DATE");

            List<AppLog> list = new List<AppLog>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                list.Add(new AppLog(SQLWrapper)
                {
                    LogID = Convert.ToInt32(dr["LOG_ID"].ToString()),
                    Project = dr["PROJECT"].ToString(),
                    Task = dr["TASK"].ToString(),
                    Action = dr["ACTION"].ToString(),
                    LogHours = Double.Parse(dr["LOG_HOURS"].ToString()),
                    SetDate = DateTime.Parse(dr["SET_DATE"].ToString())

                });
            }


            var dsResult = list.ToDataSourceResult(request);
            return new JsonResult(dsResult);

        }

                                
Aleksandar
Telerik team
commented on 08 May 2023, 07:30 AM

When the all-pages attribute is set to true, the Grid will make a request to the read endpoint to fetch all the data. If you need to pass additional data to the endpoint I can suggest doing so via the data handler for the read action. I understand the application is a RazorPages application, so the antiforgery token should be passed as well. I have created an example to demonstrate a possible approach:

@addTagHelper *, Kendo.Mvc
@inject Microsoft.AspNetCore.Antiforgery.IAntiforgery Xsrf
@Html.AntiForgeryToken()

<script src="//cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>

<select name="options" id="selectedOption" onchange="DoPostBack();">
  <option value="1">Option 1</option>
  <option value="2">Option 2</option>
  <option value="3">Option 3</option>
  <option value="4">Option 4</option>
</select>

<kendo-grid name="grid" resizable="true">
    <datasource type="DataSourceTagHelperType.Ajax" page-size="10">
        <schema>
            <model id="ProductID">
                <fields>
                    <field name="ProductID" type="number" editable="false"></field>
                    <field name="ProductName" type="string"></field>
                    <field name="UnitPrice" type="number"></field>
                    <field name="UnitsOnOrder" type="number"></field>
                    <field name="UnitsInStock" type="number"></field>
                </fields>
            </model>
        </schema>
        <groups>
            <group field="UnitsInStock">
                <aggregates>
                    <aggregate field="UnitsInStock" aggregate="count" />
                    <aggregate field="UnitsOnOrder" aggregate="average" />
                    <aggregate field="ProductName" aggregate="count" />
                </aggregates>
            </group>
        </groups>
        <aggregates>
            <aggregate field="UnitsInStock" aggregate="min" />
            <aggregate field="UnitsInStock" aggregate="max" />
            <aggregate field="UnitsInStock" aggregate="count" />
            <aggregate field="UnitsOnOrder" aggregate="average" />
            <aggregate field="ProductName" aggregate="count" />
            <aggregate field="UnitPrice" aggregate="sum" />
        </aggregates>
        <transport>
	        <read url="@Url.Action("Excel_Export_Read","Grid")" data="forgeryToken"/>
        </transport>
    </datasource>
    <columns>
        <column field="ProductName" width="300" footer-template="Total Count: #=count#" group-footer-template="Count: #=count#"/>
        <column field="UnitPrice" width="300"/>
        <column field="UnitsOnOrder" width="300" footer-template="Average: #=average#" group-footer-template="Average: #=average#">
            <exportable enabled="false"/>
        </column>
        <column field="UnitsInStock" width="300" footer-template="Min: #=min# Max: #=max#" group-header-template="Units In Stock: #=value# (Count: #=count#)"/>
    </columns>
    <toolbar>
        <toolbar-button name="excel"></toolbar-button> 
    </toolbar>
    <pageable enabled="true"/>
    <sortable enabled="true"/>
    <scrollable enabled="true"/>
    <groupable enabled="true"/>
    <excel all-pages="true" proxy-url="@Url.Action("Excel_Export_Save","Grid")" filterable="true" file-name="Kendo UI Grid Export.xlsx"/>
    <reorderable enabled="true"/>
    <column-menu enabled="true"> </column-menu>
</kendo-grid>

<script>
	function forgeryToken(){
		return {
			__RequestVerificationToken: kendo.antiForgeryTokens().__RequestVerificationToken,
			selectedOption: $("#selectedOption").val()
		}
	}

	function DoPostBack(){
		$("#grid").getKendoGrid().dataSource.read()
	}
</script>

In the above example, when the grid makes a read request the antiforgery token along with the currently selected option will be passed. Chancing a selection will fire a request with the new option and clicking on the Export to Excel button will make a read request to fetch all data, passing the currently selected option.

Check this REPL for a runnable version of the above. You can inspect the network tab for details on the requests made.

I hope this helps.

0
Mitchell
Top achievements
Rank 1
Iron
answered on 03 May 2023, 10:13 PM

By Adding 

DoPostBack()

To

on-excel-export="DoPostBack"

It does do the export however it calls the read postback twice once with the parameter set to default instead of the selected drop down item.  The second postback does call with the correct selected parameter.  However the exported excel uses the first postback with the default parameter.  Perhaps using ViewData will solve this will report back.

Tags
Grid
Asked by
Mitchell
Top achievements
Rank 1
Iron
Answers by
Mitchell
Top achievements
Rank 1
Iron
Share this question
or