如何将jqgrid过滤后的内容导出到excel？

Question

这正是我想要的。我想在 jqgrid 的分页器中显示“导出到 excel”按钮，它将导出当前数据集（基于当前过滤器）。

但是在 Grails 中。请建议如何实现它。

我试图做这个方式。

Answer 1

JQGrid 类提供 ExportToExcel 函数，您可以使用该函数将网格内容导出到 Excel。

您可以使用 JQGridState 类在导出时维护网格的当前状态（分页、过滤、排序等之后）。您还可以指定是否只想导出当前页面或整个数据源。

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using JQGridMVCExamples.Models;
    using Trirand.Web.Mvc;

    namespace JQGridMVCExamples.Controllers.Grid
{
public partial class GridController : Controller
{
    // This is the default action for the View. Use it to setup your grid Model.
    public ActionResult FunctionalityExportExcel()
    {
        // Get the model (setup) of the grid defined in the /Models folder.
        var gridModel = new OrdersJqGridModel();
        var ordersGrid = gridModel.OrdersGrid;

        // Setting the DataUrl to an action (method) in the controller is required.
        // This action will return the data needed by the grid
        ordersGrid.DataUrl = Url.Action("ExcelGridDataRequested");

        // customize the default Orders grid model with custom settings
        // NOTE: you need to call this method in the action that fetches the data as well,
        // so that the models match
        SetExportGrid(ordersGrid);

        // Pass the custmomized grid model to the View
        return View(gridModel);
    }

    // This method is called when the grid requests data        
    public JsonResult ExcelGridDataRequested()
    {
        // Get both the grid Model and the data Model
        // The data model in our case is an autogenerated linq2sql database based on Northwind.
        var gridModel = new OrdersJqGridModel();
        var northWindModel = new NorthwindDataContext();

        // customize the default Orders grid model with our custom settings
        SetExportGrid(gridModel.OrdersGrid);

        // Save the current grid state in Session
        // We will later need it for Excel Export
        JQGridState gridState = gridModel.OrdersGrid.GetState();
        Session["gridState"] = gridState;

        // return the result of the DataBind method, passing the datasource as a parameter
        // jqGrid for ASP.NET MVC automatically takes care of paging, sorting, filtering/searching, etc

        return gridModel.OrdersGrid.DataBind(northWindModel.Orders);
    }

    public JsonResult ExcelExport_AutoCompleteShipName(string term)
    {
        var northWindModel = new NorthwindDataContext();
        JQAutoComplete autoComplete = new JQAutoComplete();

        autoComplete.DataField = "ShipName";
        autoComplete.AutoCompleteMode = AutoCompleteMode.BeginsWith;
        autoComplete.DataSource = from o in northWindModel.Orders
                                  select o;
        return autoComplete.DataBind();
    }

    private void SetExportGrid(JQGrid ordersGrid)
    {
        // show the search toolbar
        ordersGrid.ToolBarSettings.ShowSearchToolBar = true;
        ordersGrid.ToolBarSettings.ShowSearchButton = true;

        var orderDateColumn = ordersGrid.Columns.Find(c => c.DataField == "OrderDate");
        orderDateColumn.DataFormatString = "{0:yyyy/MM/dd}";
        orderDateColumn.SearchType = SearchType.DatePicker;
        orderDateColumn.DataType = typeof(DateTime);
        orderDateColumn.SearchControlID = "DatePicker";
        orderDateColumn.SearchToolBarOperation = SearchOperation.IsEqualTo;

        var shipNameColumn = ordersGrid.Columns.Find(c => c.DataField == "ShipName");
        shipNameColumn.SearchType = SearchType.AutoComplete;
        shipNameColumn.DataType = typeof(string);
        shipNameColumn.SearchControlID = "AutoComplete";
        shipNameColumn.SearchToolBarOperation = SearchOperation.Contains;

        var orderIDColumns = ordersGrid.Columns.Find(c => c.DataField == "OrderID");
        orderIDColumns.Searchable = true;
        orderIDColumns.DataType = typeof(int);
        orderIDColumns.SearchToolBarOperation = SearchOperation.IsEqualTo;

        SetCustomerIDSearchDropDown(ordersGrid);
        SetFreightSearchDropDown(ordersGrid);
    }

    private void SetCustomerIDSearchDropDown(JQGrid ordersGrid)
    {
        // setup the grid search criteria for the columns
        JQGridColumn customersColumn = ordersGrid.Columns.Find(c => c.DataField == "CustomerID");
        customersColumn.Searchable = true;

        // DataType must be set in order to use searching
        customersColumn.DataType = typeof(string);
        customersColumn.SearchToolBarOperation = SearchOperation.IsEqualTo;
        customersColumn.SearchType = SearchType.DropDown;

        // Populate the search dropdown only on initial request, in order to optimize performance
        if (ordersGrid.AjaxCallBackMode == AjaxCallBackMode.RequestData)
        {
            var northWindModel = new NorthwindDataContext();
            var searchList = from customers in northWindModel.Customers
                             select new SelectListItem
                             {
                                 Text = customers.CustomerID,
                                 Value = customers.CustomerID
                             };

            customersColumn.SearchList = searchList.ToList();
            customersColumn.SearchList.Insert(0, new SelectListItem { Text = "All", Value = "" });
        }
    }

    private void SetFreightSearchDropDown(JQGrid ordersGrid)
    {
        // setup the grid search criteria for the columns
        JQGridColumn freightColumn = ordersGrid.Columns.Find(c => c.DataField == "Freight");
        freightColumn.Searchable = true;

        // DataType must be set in order to use searching
        freightColumn.DataType = typeof(decimal);
        freightColumn.SearchToolBarOperation = SearchOperation.IsGreaterOrEqualTo;
        freightColumn.SearchType = SearchType.DropDown;

        // Populate the search dropdown only on initial request, in order to optimize performance
        if (ordersGrid.AjaxCallBackMode == AjaxCallBackMode.RequestData)
        {
            List searchList = new List();
            searchList.Add(new SelectListItem { Text = "> 10", Value = "10" });
            searchList.Add(new SelectListItem { Text = "> 30", Value = "30" });
            searchList.Add(new SelectListItem { Text = "> 50", Value = "50" });
            searchList.Add(new SelectListItem { Text = "> 100", Value = "100" });

            freightColumn.SearchList = searchList.ToList();
            freightColumn.SearchList.Insert(0, new SelectListItem { Text = "All", Value = "" });
        }
    }

    public ActionResult ExportToExcel(string exportType)
    {

        var gridModel = new OrdersJqGridModel();
        var northWindModel = new NorthwindDataContext();
        var grid = gridModel.OrdersGrid;

        // Get the last grid state the we saved before in Session in the DataRequested action
        JQGridState gridState = Session["GridState"] as JQGridState;

        // Need to set grid options again
        SetExportGrid(grid);

        if (String.IsNullOrEmpty(exportType))
            exportType = "1";

        switch (exportType)
        {
            case "1":
                grid.ExportToExcel(northWindModel.Orders);
                break;
            case "2":
                gridState.CurrentPageOnly = false;
                grid.ExportToExcel(northWindModel.Orders, gridState);
                break;
            case "3":
                gridState.CurrentPageOnly = true;
                grid.ExportToExcel(northWindModel.Orders, gridState);
                break;
        }


        return View();
    }
}
}

Answer 2

最简单的方法实际上是将数据呈现为直接的 HTML 表，然后使用内容类型告诉浏览器在 Excel 中打开它。您始终可以使用更复杂的东西（例如 Apache POI）来生成真正的电子表格，但除非您需要公式，否则实际上没有任何意义。

因此，最简单的方法就是使用视图，而不需要复杂的布局。有一个 VBScript 示例：http://support.microsoft.com/kb/271572，它具有足够的可读性，您应该不会在将其适应 Grails/GSP 时遇到太多麻烦。请注意，对于数据，一个带有表格的简单 HTML 响应似乎就足够了，而我们在实践中从来不需要嵌入的特定于 Excel 的命名空间。

您需要的 MIME 类型在这里找到答案：Setting mime type for excel document，接受答案中的标题显示如何将数据传递到 Excel 中。附件的内容配置可能是您下载数据所需的。

尽管这会下载到实际包含 HTML 的 .xls 文件，但当您打开该文件时，Excel 似乎仍然会执行正确的操作。