从网格视图导出数据时启用Excel自动筛选

本文关键字：Excel 启用 筛选 数据 网格 视图 | 更新日期: 2023-09-27 18:30:06

如果有人能帮助我在从gridview导出数据时如何在Excel上启用筛选功能，我将不胜感激。（Excel自动筛选）

 public ActionResult ExportToExcel(List<EventViewModel> list)
    {
        try
        {
           // Main
             GridView gv = new GridView();
            gv.DataSource = list.ToList();
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.Charset = "";
            Response.AddHeader("content-disposition", "attachment; filename=filename.xls");//Response.AddHeader("content-disposition", "inline; filename=Excel.xls");
            Response.ContentType = "application/excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
            return RedirectToAction("Index");
        }
        catch (System.Exception e)
        { 
            return View("Error");
        }
    }

从网格视图导出数据时启用Excel自动筛选

您的代码导出一个扩展名为XLS的HTML文件。它不是一个二进制XLS文件，您不能有Excel自动筛选器，而不是使用您的代码。

如果不能使用Microsoft.Office.Interop.Excel库，则可以搜索其他导出真实Excel文件的Excel库。

我使用EasyXLS Excel库。这段代码导出了gridview的数据表，并添加了一个自动过滤器，它使用这个库：

// Create an instance of the class that exports Excel files, having one sheet
ExcelDocument xls = new ExcelDocument(1);
// Get the sheet
ExcelWorksheet xlsWorksheet = (ExcelWorksheet)xls.easy_getSheetAt(0);
// Create a dataset that keeps the gridview datatable
DataSet dataSet = new DataSet();
dataSet.Tables.Add((DataTable)gridView.DataSource);
// Insert the dataset into sheet
xlsWorksheet.easy_insertDataSet(dataSet, true);
//Add AutoFilter
ExcelFilter xlsFilter = xlsWorksheet.easy_getFilter();
xlsFilter.setAutoFilter("A1:E1");
// Choose a name for the xls file 
string fileName = "filename.xls";
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
Response.ContentType = "application/vnd.ms-excel";
// Export Excel file and prompt the "Open or Save Dialog Box" 
xls.easy_WriteXLSFile(Response.OutputStream);
// Dispose memory
xls.Dispose();
Response.End();

有关格式化的更多详细信息，请阅读有关将网格视图导出到Excel的链接。

相关文章：