Epplus筛选
using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Xml; using OfficeOpenXml; namespace _2022_05_30_Epplus筛选 { class Program { static void Main(string[] args) { ExcelPackage.LicenseContext = LicenseContext.NonCommercial; AutoFilter_Test(); } public static void AutoFilter_Test() { //http://stackoverflow.com/questions/32723483/adding-a-specific-autofilter-on-a-column //Throw in some data var datatable = new DataTable("tblData"); datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object)) }); for (var i = 0; i < 10; i++) { var row = datatable.NewRow(); row[0] = i; row[1] = i * 10; row[2] = Path.GetRandomFileName(); datatable.Rows.Add(row); } //Create a test file var fi = new FileInfo(@"autofilter.xlsx"); if (fi.Exists) fi.Delete(); using (var pck = new ExcelPackage(fi)) { var worksheet = pck.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(datatable, true); var range = worksheet.Cells["A1:C10"]; range.AutoFilter = true; pck.Save(); } //Needed prior save in order for the XML to be generated using (var pck = new ExcelPackage(fi)) { var worksheet = pck.Workbook.Worksheets.First(); //Get reference to the worksheet xml for proper namespace var xdoc = worksheet.WorksheetXml; var nsm = new XmlNamespaceManager(xdoc.NameTable); nsm.AddNamespace("default", xdoc.DocumentElement.NamespaceURI); //Create the filters themselves var filter1 = xdoc.CreateNode(XmlNodeType.Element, "filter", xdoc.DocumentElement.NamespaceURI); var att = xdoc.CreateAttribute("val"); att.Value = "40"; filter1.Attributes.Append(att); //var filter2 = xdoc.CreateNode(XmlNodeType.Element, "filter", xdoc.DocumentElement.NamespaceURI); //att = xdoc.CreateAttribute("val"); //att.Value = "50"; //filter2.Attributes.Append(att); //Add filters to the collection var filters = xdoc.CreateNode(XmlNodeType.Element, "filters", xdoc.DocumentElement.NamespaceURI); filters.AppendChild(filter1); // filters.AppendChild(filter2); //Create the parent filter container var filterColumn = xdoc.CreateNode(XmlNodeType.Element, "filterColumn", xdoc.DocumentElement.NamespaceURI); att = xdoc.CreateAttribute("colId"); att.Value = "1"; filterColumn.Attributes.Append(att); filterColumn.AppendChild(filters); //Now add it to the autoFilters node var autoFilter = xdoc.SelectSingleNode("/default:worksheet/default:autoFilter", nsm); autoFilter.AppendChild(filterColumn); //Have to manually hide rows based on criteria worksheet.Cells .Where(cell => cell.Address.StartsWith("B") && cell.Value is double && (double)cell.Value != 40d && (double)cell.Value != 50d) .Select(cell => cell.Start.Row) .ToList() .ForEach(r => worksheet.Row(r).Hidden = true); pck.Save(); } } } }
效果图: