Grid Excel Export with custom Parameter

2 Answers 471 Views

Grid

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.

<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

Accepted

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

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.