使用 linq 和 Excel 设置自动筛选
本文关键字：筛选 设置 Excel linq 使用 | 更新日期: 2023-09-27 18:32:05
下一个代码运行良好，除了最后一个XElement，我必须为所有列设置自动过滤属性。我知道列数，但每次执行的行数可能不同。
没错，我的疑问在于下一个元素：
new XElement("AutoFilter", new XAttribute(x + "Range", "R1C1:R1C1"),
new XAttribute("Xmlns",
"urn:schemas-microsoft-com:office:excel")
的完整代码是（我的问题在最后一行）：
XNamespace aw = "urn:schemas-microsoft-com:office:spreadsheet";
XNamespace o = "urn:schemas-microsoft-com:office:office";
XNamespace x = "urn:schemas-microsoft-com:office:excel";
XNamespace ss = "urn:schemas-microsoft-com:office:spreadsheet";
XNamespace html = "http://www.w3.org/TR/REC-html40";
XDocument xdoc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"),
new XElement("Workbook",
new XAttribute("Xmlns", "urn:schemas-microsoft-com:office:spreadsheet"),
new XAttribute(XNamespace.Xmlns + "o", "urn:schemas-microsoft-com:office:office"),
new XAttribute(XNamespace.Xmlns + "x", "urn:schemas-microsoft-com:office:excel"),
new XAttribute(XNamespace.Xmlns + "ss", "urn:schemas-microsoft-com:office:spreadsheet"),
new XAttribute(XNamespace.Xmlns + "html", "http://www.w3.org/TR/REC-html40"),
new XElement("DocumentProperties", new XAttribute("Xmlns", "urn:schemas-microsoft-com:office:excel"),
new XElement("Author", ""),
new XElement("Created", DateTime.Today),
new XElement("Company", "")
),
new XElement("ExcelWorkBook",
new XAttribute("Xmlns", "urn=schemas-microsoft-com:office:excel"),
new XElement("WindowHeight", "16795"),
new XElement("WindowWidth", "8460"),
new XElement("WindowTopX", "120"),
new XElement("WindowTopY", "15"),
new XElement("ProtectStructure", "False"),
new XElement("ProtectWindows", "False")
),
new XElement("Styles", new XElement("Style", new XAttribute(ss + "ID", "Default"), new XAttribute(ss + "Name", "Normal"),
new XElement("Alignment", "", new XAttribute(ss + "Vertical", "Bottom")),
new XElement("Borders", ""),
new XElement("Font", ""),
new XElement("Interior", ""),
new XElement("NumberFormat", ""),
new XElement("Protection", "")
),
new XElement("Style", new XAttribute(ss + "ID", "s21"), new XElement("Font", "", new XAttribute(x + "Family", "Swiss"), new XAttribute(ss + "Bold", "1")), new XElement("NumberFormat", new XAttribute(ss + "Format", @"#,##0.00_ ;[Red]'-#,##0.00' "))),
new XElement("Style", new XAttribute(ss + "ID", "s83"), new XElement("NumberFormat", new XAttribute(ss + "Format", @"#,##0.00_ ;[Red]'-#,##0.00' "))),
new XElement("Style", new XAttribute(ss + "ID", "porcentaje"), new XElement("Font", "", new XAttribute(x + "Family", "Swiss")), new XElement("NumberFormat", new XAttribute(ss + "Format", "0%"))),
new XElement("Style", new XAttribute(ss + "ID", "porcentajeb"), new XElement("Font", "", new XAttribute(x + "Family", "Swiss"), new XAttribute(ss + "Bold", "1")), new XElement("NumberFormat", new XAttribute(ss + "Format", "0%"))),
new XElement("Style", new XAttribute(ss + "ID", "cabecera"),
new XElement("Font", "", new XAttribute(x + "Family", "Swiss"), new XAttribute(ss + "Bold", "1"), new XAttribute(ss + "FontName", "Calibri"), new XAttribute(ss + "Size", "11"), new XAttribute(ss + "Color", "#FFFFFF")),
new XElement("Alignment", new XAttribute(ss + "Horizontal","Center"), new XAttribute(ss + "Vertical", "Bottom")),
new XElement("Borders",
new XElement("Border", new XAttribute(ss+ "Position" , "Bottom"), new XAttribute(ss+ "LineStyle" , "Continuous"), new XAttribute(ss+ "Weight" , "1")),
new XElement("Border", new XAttribute(ss+ "Position" , "Left"), new XAttribute(ss+ "LineStyle" , "Continuous"), new XAttribute(ss+ "Weight" , "1")),
new XElement("Border", new XAttribute(ss+ "Position" , "Right"), new XAttribute(ss+ "LineStyle" , "Continuous"), new XAttribute(ss+ "Weight" , "1")),
new XElement("Border", new XAttribute(ss+ "Position" , "Top"), new XAttribute(ss+ "LineStyle" , "Continuous"), new XAttribute(ss+ "Weight" , "1"))
),
new XElement("Interior", new XAttribute(ss + "Color","#92D050"),new XAttribute(ss+ "Pattern","Solid"))
)
),
new XElement("Worksheet",
new XAttribute(ss + "Name", "SHEET1"),
new XElement("Table",
new XElement("Column", new XAttribute(ss + "Width", "140")),
new XElement("Row", new XElement("Cell",
new XElement("Cell", new XAttribute(ss + "StyleID", "cabecera"), new XElement("Data", new XAttribute(ss + "Type", "String"), "EMPLOYEE")),
),
from item in lista
select
new XElement("Row", new XElement("Cell", new XElement("Data", new XAttribute(ss + "Type", "String"), item.EMPLOYEE)),
),
new XElement("WorksheetOptions",
new XAttribute("Xmlns", "urn:schemas-microsoft-com:office:excel"),
new XElement("PageSetup", new XElement("Layout", new XAttribute(x + "Orientation", "Landscape")),
new XElement("Header", new XAttribute(x + "Margin", "0.51181102362204722")),
new XElement("Footer", new XAttribute(x + "Margin", "0.51181102362204722")),
new XElement("PageMargins", new XAttribute(x + "Bottom", "0.98425196850393704"),
new XAttribute(x + "Left", "0.74803149606299213"),
new XAttribute(x + "Right", "0.74803149606299213"),
new XAttribute(x + "Top", "0.98425196850393704"))
),
new XElement("FitToPage",""),
new XElement("Print", new XElement("FitHeight","100"),
new XElement("ValidPrinterInfo", ""),
new XElement("PaperSizeIndex", "9"),
new XElement("Scale", "77"),
new XElement("HorizontalResolution", "600"),
new XElement("VerticalResolution", "600")
),
new XElement("Selected", ""),
new XElement("DoNotDisplayZeros", ""),
new XElement("Panes", new XElement("Pane", new XElement("Number", "3"),
new XElement("ActiveRow", "5"),
new XElement("ActiveCol", "1"))),
new XElement("ProtectObjects", "False"), new XElement("ProtectScenarios", "False")
),
new XElement("AutoFilter", new XAttribute(x + "Range",
string.Format("R1C1:R{0}C1", lista.Count())),
new XAttribute("Xmlns", "urn:schemas-microsoft-com:office:excel"))
)
))
);
您似乎为lista中的每个项目创建了一行，因此您可以尝试这样的事情：
new XElement("AutoFilter",
new XAttribute(
x + "Range", String.Format("R1C1:R{0}C1", lista.Count())),
new XAttribute("Xmlns",
"urn:schemas-microsoft-com:office:excel")
我不知道它是否完全以这种方式工作，但我想你可以看到原理。（当然，您必须调整列数。