如何在C#中从ObservableCollection创建具有自动筛选功能的Excel
本文关键字：筛选 功能 Excel 中从 创建 ObservableCollection | 更新日期: 2023-09-27 18:22:11
如何使用C#中ObservableCollection的Autofilter功能创建Excel？
模型类中MobileModel的所有列标题的自动筛选。
模型和视图模型粘贴在下面：-C#编码
public class Mobile
{
private ObservableCollection<MobileModel> _mobileList;
public ObservableCollection<MobileModel> MobileList
{
get { return _mobileList; }
set { _mobileList = value; OnPropertyChanged(); }
}
public void GetMobile()
{
List<MobileModel> mList = new List<MobileModel>();
List<MobileModelInfo> modList = new List<MobileModelInfo>();
MobileModel mob = new MobileModel();
modList.Clear();
mob.Brand = "Apple";
modList.Add(new MobileModelInfo { Name = "iPhone 4", Catagory = "Smart Phone", Year = "2011" });
modList.Add(new MobileModelInfo { Name = "iPhone 5", Catagory = "Smart Phone", Year = "2013" });
modList.Add(new MobileModelInfo { Name = "iPhone 6", Catagory = "Premium Smart Phone", Year = "2015" });
mob.Model = new ObservableCollection<MobileModelInfo>(modList);
mob.OS = "IOS";
mList.Add(mob);
mob = new MobileModel();
modList.Clear();
mob.Brand = "Samsung";
modList.Add(new MobileModelInfo { Name = "S4", Catagory = "Smart Phone", Year = "2011" });
modList.Add(new MobileModelInfo { Name = "S5", Catagory = "Smart Phone", Year = "2013" });
modList.Add(new MobileModelInfo { Name = "S6", Catagory = "Ultra Smart Phone", Year = "2015" });
mob.Model = new ObservableCollection<MobileModelInfo>(modList);
mob.OS = "Android";
mList.Add(mob);
mob = new MobileModel();
modList.Clear();
mob.Brand = "MicroSoft";
modList.Add(new MobileModelInfo { Name = "Lumina 9900", Catagory = "Phone", Year = "2011" });
modList.Add(new MobileModelInfo { Name = "Opera X220", Catagory = "Smart Phone", Year = "2013" });
mob.Model = new ObservableCollection<MobileModelInfo>(modList);
mob.OS = "Windows";
mList.Add(mob);
mob = new MobileModel();
modList.Clear();
mob.Brand = "Sony Ericssion";
modList.Add(new MobileModelInfo { Name = "S4", Catagory = "Smart Phone", Year = "2011" });
modList.Add(new MobileModelInfo { Name = "S5", Catagory = "Smart Phone", Year = "2013" });
modList.Add(new MobileModelInfo { Name = "S6", Catagory = "Ultra Smart Phone", Year = "2015" });
mob.Model = new ObservableCollection<MobileModelInfo>(modList);
mob.OS = "Android";
mList.Add(mob);
MobileList = new ObservableCollection<MobileModel>(mList);
ExportToExcel(MobileList);
}
public void ExportToExcel(ObservableCollection<MobileModel> Source)
{
// How to Create Excel Sheet with AutoFilter from ObservableCollection<MobileModel>
}
}
public class MobileModel : Notify
{
private string _brand = string.Empty;
private ObservableCollection<MobileModelInfo> _model = new ObservableCollection<MobileModelInfo>();
private string _os = string.Empty;
public string Brand
{
get { return _brand; }
set { _brand = value; OnPropertyChanged(); }
}
public ObservableCollection<MobileModelInfo> Model
{
get { return _model; }
set { _model = value; OnPropertyChanged(); }
}
public string OS
{
get { return _os; }
set { _os = value; OnPropertyChanged(); }
}
}
public class MobileModelInfo
{
public string Name { get; set; }
public string Catagory { get; set; }
public string Year { get; set; }
}
步骤1：添加引用"Microsoft Office Interop Excel"添加参考的快照
步骤2：在类文件中包含"Microsoft Office Interop Excel"
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Win32;
步骤3:ExportToExcel（）方法
public void ExportToExcel(ObservableCollection<MobileModel> MobileList)
{
if (MobileList.Count > 0)
{
// Displays a SaveFileDialog so the user can save the Image
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel File|*.xls";
saveFileDialog1.Title = "Save an Excel File";
saveFileDialog1.FileName = "Mobile List";
// If the User Clicks the Save Button then the Module gets executed otherwise it skips the scope
if ((bool)saveFileDialog1.ShowDialog())
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp != null)
{
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "";
Excel.Range formatRange;
int rowCount = 1;
xlWorkSheet.Cells[rowCount, 1] = "Brand";
xlWorkSheet.Cells[rowCount, 2] = "OS";
rowCount++;
formatRange = xlWorkSheet.get_Range("a1"); formatRange.EntireRow.Font.Bold = true;
formatRange = xlWorkSheet.Range["a1"]; formatRange.Cells.HorizontalAlignment = HorizontalAlignment.Center;
formatRange = xlWorkSheet.get_Range("b1"); formatRange.EntireRow.Font.Bold = true;
foreach (var item in MobileList)
{
xlWorkSheet.Cells[rowCount, 1] = item.Brand;
xlWorkSheet.Cells[rowCount, 2] = item.OS;
rowCount++;
}
formatRange = xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, rowCount - 1]]; formatRange.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
xlWorkSheet.Columns.AutoFit();
// If the file name is not an empty string open it for saving.
if (!String.IsNullOrEmpty(saveFileDialog1.FileName.ToString()) && !string.IsNullOrWhiteSpace(saveFileDialog1.FileName.ToString()))
{
xlWorkBook.SaveAs(saveFileDialog1.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel File Exported Successfully", "Export Engine");
}
}
}
}
else
{
MessageBox.Show("Nothing to Export", "Export Engine");
}
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
System.GC.Collect();
}
}