using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Windows;
using System.Windows.Data;
using System.Windows.Input;
using System.Xml.Linq;
using System.Reflection;
using Action = System.Action;
using System.Data.SqlClient;
using System.Drawing;
using System.Data.OleDb;
using WPFWindow = System.Windows;
using System.Data;
using DataTable = System.Data.DataTable;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.Text.RegularExpressions;
using Microsoft.Win32;
using System.Collections.ObjectModel;
using System.Reflection.Emit;

namespace WpfApp1
{
    /// <summary>
    /// Interaction logic for Window033.xaml
    /// </summary>
    public partial class Window033 : WPFWindow.Window
	{
        public Window033()
        {
            InitializeComponent();
        }
		
 


		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
			{
				GC.Collect();
			}
		}

    }




	class ProductPriceViewModel : INotifyPropertyChanged
	{

		

		public ProductPriceViewModel()
		{

			//DataTableCollection = ExcelUtlity.ReadExcelFile(excelpath);
			command = new RelayCommand(Execute);

		}
		public DataTable DataTableCollection { get; set; }

		

		

		public List<string> MainProductCategory
		{
			get => new List<string>() {"PrintProduct","RetroPrint","PrintWithFrame","Calendar","Collage",
				"CollagePoster","SingleSidePhoto","PassportPhoto","IDPhotoCard","GadgetProduct","GreetingCard"};
		}

		public List<string> ProductName
		{
			get => new List<string>() {
				"10x10","10x15","10x8","10x20","13x18","13x13","15x20","15x23","20x25","20x30","Sticker","Metallic","Perfo" };
		}

		public event PropertyChangedEventHandler PropertyChanged;
		public event EventHandler? CanExecuteChanged;

		public void OnPropertyChanged([CallerMemberName] String info = "") =>
			PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(info));

		
		private bool Filter(object item)
		{
			Product p = item as Product;
			if (p == null) return true;
			var ret = true;
			if (!String.IsNullOrEmpty(MainProductSearch))
				ret = ret && p.Mainproduct.IndexOf(MainProductSearch, StringComparison.OrdinalIgnoreCase) >= 0 ||
				p.Name.IndexOf(MainProductSearch, StringComparison.OrdinalIgnoreCase) >= 0;
			if (!String.IsNullOrEmpty(SizeSearch))
				ret = ret && p.Name.IndexOf(SizeSearch, StringComparison.OrdinalIgnoreCase) >= 0;
			if (!String.IsNullOrEmpty(MediaType))
				ret = ret && p.Name.IndexOf(MediaType, StringComparison.OrdinalIgnoreCase) >= 0;
			if (Visible.HasValue)
				ret = ret && p.Visible.IndexOf(Visible.Value.ToString(), StringComparison.OrdinalIgnoreCase) >= 0;
			return ret;
		}

		private ICollectionView cvs;

		public ICollectionView View
		{
			get { return cvs; }
			set
			{
				cvs = value;
				OnPropertyChanged("View");
			}
		}
		private readonly ICommand command;
		public ICommand Command
		{
			get
			{
				return command;
			}
		}
		//private CollectionViewSource cvs = new CollectionViewSource();

		//      public ICollectionView View { get; set; }
		//public ICollectionView View
		//{
		//    get
		//    {
		//        if (cvs.Source == null)
		//        {
		//            cvs.Source = GetProductsPriceListXML();// GetProductsPriceListXML();GetProductsListPriceExcel();
		//            cvs.View.Filter = Filter;
		//        }
		//        return this.cvs.View;
		//    }
		//}




		//public Product SelectedProduct { get; set; }
		private Product selectedProduct;

		public Product SelectedProduct
		{
			get { return selectedProduct; }
			set
			{
				selectedProduct = value;
				OnPropertyChanged("SelectedProduct");
			}
		}

		private string _MainProductSearch;
		public string MainProductSearch
		{
			get { return _MainProductSearch; }
			set
			{
				_MainProductSearch = value;
				OnPropertyChanged();
				View.Refresh();
			}
		}

		private string _SizeSearch;
		public string SizeSearch
		{
			get { return _SizeSearch; }
			set
			{
				_SizeSearch = value;
				OnPropertyChanged();
				View.Refresh();
			}
		}

		private string _MediaType;
		public string MediaType
		{
			get { return _MediaType; }
			set
			{
				_MediaType = value;
				OnPropertyChanged();
				View.Refresh();
			}
		}

		private bool? _Visible;
		public bool? Visible
		{
			get { return this._Visible; }
			set
			{
				this._Visible = value;
				OnPropertyChanged();
				View.Refresh();
			}
		}
		public static DataTable ConvertToDataTable<T>(IList<T> data)
		{
			PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
			System.Data.DataTable table = new System.Data.DataTable();
			foreach (PropertyDescriptor prop in properties)
				table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
			foreach (T item in data)
			{
				DataRow row = table.NewRow();
				foreach (PropertyDescriptor prop in properties)
					row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
				table.Rows.Add(row);
			}
			return table;
		}

		//EEPlus parsing excel to list of objects
		//https://github.com/ducklmg/ExcelToObject

		public ICommand MyCommand { get => new RelayCommand(executemethod, canexecutemethod); }

		public ICommand ReadCommand { get => new RelayCommand(Execute, canexecutemethod); }

		//https://gist.github.com/kzelda/2facdff2d924349fe96c37eab0e9ee47 readwrite excel openxml

		string excelpath = @"C:\Users\krangaraj.KONICAFR\Desktop\Price_202301111303.xlsx";
		string pricinglocalfile = @"C:\xmltest\Prices_2022_12_15_All_CM_V4.txt";

		public  ObservableCollection<Product> Items { get; set; }
		private void Execute(object parm)
        {
			
			//Items = GetProductsPriceListXML();
			//View = CollectionViewSource.GetDefaultView(Items);
			//View.Filter = Filter;
			//View.Refresh();

		}


		private void executemethod(object parameter)
		{
			switch (parameter.ToString())
			{
				case "ExcelExport":
					ExcelUtlity obj = new ExcelUtlity();
					System.Data.DataTable dt = ConvertToDataTable(GetProductsPriceListXML());
					string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + "Price_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx";
					obj.WriteDataTableToExcel(dt, path);
					System.Windows.MessageBox.Show("Excel Pricing File created on the desktop");
					return;
				case "ReadExcel":
                    //DataTableCollection = GetDataTable();


                   // System.Data.DataTable dtexcel = ExcelUtlity.ReadExcelSheet(excelpath, false);
                    OpenFileDialog fileDialog = new OpenFileDialog();
                    fileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
                    fileDialog.FilterIndex = 1;
                    fileDialog.Multiselect = false;
                    dynamic result = fileDialog.ShowDialog();

                    if (result == true)
                    {
                        string excelfilepath = fileDialog.FileName;
                        DataTableCollection = ExcelUtlity.ReadExcelFile(excelfilepath);
						ObservableCollection<object> collection = GetCollection(DataTableCollection);
		

						//var test = new ObservableCollection<Product>();
						//foreach (var row in DataTableCollection.Rows)
						//{
						//	Product obj1 = new Product()
						//	{
						//		Mainproduct = (string)row["Mainproduct"],
						//		Name = (string)row["Name"],
						//		Price = (string)row["Price"],
						//		Visible = (string)row["Visible"],
						//		NameIcon = (string)row["NameIcon"]
						//	};
						//	test.Add(obj1);
						//}
						//View = CollectionViewSource.GetDefaultView(collection);
						View = CollectionViewSource.GetDefaultView(collection);
                        View.Filter = Filter;
                        View.Refresh();

                        //var test = new ObservableCollection<Product>();

                        //Items = GetProductsListPriceExcel();
                        //View = CollectionViewSource.GetDefaultView(Items);
                        //View.Filter = Filter;
                        //View.Refresh();
                        //foreach (DataRow dataRow in dtexcel.Rows)
                        //{
                        //    foreach (var item in dataRow.ItemArray)
                        //    {

                        //        Console.WriteLine(item);
                        //    }
                        //}
                        //MessageBox.Show(dtexcel.Rows[0].ItemArray[0].ToString());
                    }






                    //CommonMethod.ConvertToList<Product>(dtexcel);

                    return;
				case "ReadInternalPricing": //to read local pricing file
					Items = GetProductsPriceListXML();
					
					View = CollectionViewSource.GetDefaultView(Items);
					View.Filter = Filter;
					View.Refresh();
					return;

				case "cm2inc": //to read local pricing file

					Items = GetProductsPriceListXML();
					View = CollectionViewSource.GetDefaultView(Items);
					View.Filter = Filter;
					View.Refresh();
					return;

				case "SaveFile":
					data.Save("xxx.txt");
					return;
				case "Reset":
					MainProductSearch = string.Empty;
					SizeSearch = string.Empty;
					SizeSearch = string.Empty;
					Visible = null;
					MediaType = string.Empty;
					break;
				default:
					MediaType = parameter.ToString();
					break;
			}
			View.Refresh();
		}

		private static bool canexecutemethod(object obj) => true;

		//https://www.c-sharpcorner.com/blogs/converting-datatable-to-model-list2
		public List<Product> GetProductsListPriceExcel()
		{
			DataTable ResultDT = new DataTable();
		    //ResultDT = ExcelUtlity.ReadExcelSheet(excelpath, true); // Call BusinessLogic to fill DataTable, Here your ResultDT will get the result in which you will be having single or multiple rows with columns
			ResultDT = ExcelUtlity.ReadExcelFile(excelpath);
			var productlist = new List<Product>();
			productlist = CommonMethod.ConvertToList<Product>(ResultDT);
			//productlist = BindDataList<Product>(ResultDT);

			return productlist;
			
		}

		public static string RenameToInches(string cm)
		{
			var dic = GetCentimetersToInchesPairs();
			foreach (var pair in dic.OrderByDescending(x => x.Key.Length))
			{
				cm = cm.Replace(pair.Key, pair.Value);
			}
			return cm;
		}

		private static Dictionary<string, string> GetCentimetersToInchesPairs()
		{
			var dic = new Dictionary<string, string>();
			dic.Add("5x10", "2x4");
			dic.Add("5x10/20", "2x4/8");
			dic.Add("5x11", "2x4.5");
			dic.Add("5x15", "2x6");
			dic.Add("5x20", "2x8");
			dic.Add("7.5x10", "3x4");
			dic.Add("7.5x20", "3x8");
			dic.Add("7.5x10/20", "3x4/8");
			dic.Add("9x13", "3.5x5");
			dic.Add("10x10", "4x4");
			dic.Add("10x13", "4x5");
			dic.Add("10x13.5", "4x5.5");
			dic.Add("10x15", "4x6");
			dic.Add("10x18", "4x7");
			dic.Add("10x30", "4x12");
			dic.Add("11x15", "4.5x6");
			dic.Add("11x11", "4.5x4.5");
			dic.Add("11x18", "4.5x7");
			dic.Add("11x20", "4.5x8");
			dic.Add("13x13", "5x5");
			dic.Add("13x18", "5x7");
			dic.Add("13x20", "5x8");
			dic.Add("15x15", "6x6");
			dic.Add("15x20", "6x8");
			dic.Add("15x23", "6x9");
			dic.Add("17x20", "8x7");
			dic.Add("10x20", "4x8");
			dic.Add("20x20", "8x8");
			dic.Add("20x23", "8x9");
			dic.Add("20x25", "8x10");
			dic.Add("20x30", "8x12");
			dic.Add("15x36", "6x14");
			dic.Add("15x52", "6x20");
			dic.Add("20x46", "8x18");
			dic.Add("20x67", "8x26");
			dic.Add("20x56", "8x22");
			dic.Add("20x82", "8x32");
			dic.Add("13x21", "A4x5");
			dic.Add("15x21", "A4x6");
			dic.Add("20x21", "A4x8");
			dic.Add("21x25", "A4x10");
			dic.Add("14.8x21.0", "5.83x8.27");
			dic.Add("8x10 H", "3x4 H");
			dic.Add("8x10 V", "3x4 V");
			dic.Add("10x12", "4x5");
			dic.Add("(10x8)x2", "(4x3)x2");
			dic["28x35"] = "11x14";
			dic["30x30"] = "12x12";
			dic["30x45"] = "12x18";
			return dic;
		}

		private DataTable GetDataTable()
		{
			DataTable dataTable = new DataTable();
			dataTable.Columns.Add("Order ID", typeof(int));
			dataTable.Columns.Add("Customer Name", typeof(string));
			dataTable.Columns.Add("Customer ID", typeof(string));
			dataTable.Columns.Add("Country", typeof(string));
			dataTable.Rows.Add(1001, "Maria Anders", "ALFKI", "Germany");
			dataTable.Rows.Add(1002, "Ana Trujilo", "ANATR", "Mexico");
			dataTable.Rows.Add(1003, "Antonio Moreno", "ENDGY", "Mexico");
			dataTable.Rows.Add(1004, "Thomas Hardy", "ANTON", "UK");
			dataTable.Rows.Add(1005, "Christina Berglund", "BERGS", "Sweden");
			dataTable.Rows.Add(1006, "Hanna Moos", "BLAUS", "Germany");
			dataTable.Rows.Add(1007, "Frederique Citeaux", "BLONP", "France");
			dataTable.Rows.Add(1008, "Martin Sommer", "BOLID", "Spain");
			dataTable.Rows.Add(1009, "Laurence Lebihan", "BONAP", "France");
			dataTable.Rows.Add(1010, "Kathryn", "BOTTM", "Canada");
			dataTable.Rows.Add(1011, "Tamer", "XDKLF", "UK");
			dataTable.Rows.Add(1012, "Martin", "QEUDJ", "US");
			dataTable.Rows.Add(1013, "Nancy", "ALOPS", "France");
			dataTable.Rows.Add(1014, "Janet", "KSDIO", "Canada");
			dataTable.Rows.Add(1015, "Dodsworth", "AWSDE", "Canada");
			dataTable.Rows.Add(1016, "Buchanan", "CDFKL", "Germany");
			dataTable.Rows.Add(1017, "Therasa", "WSCJD", "Canada");
			dataTable.Rows.Add(1018, "Margaret", "PLSKD", "UK");
			dataTable.Rows.Add(1019, "Anto", "CCDSE", "Sweden");
			dataTable.Rows.Add(1020, "Edward", "EWUJG", "Germany");
			return dataTable;
		}


		public List<T> BindDataList<T>(DataTable dt)
		{
			List<string> columns = new List<string>();
			foreach (DataColumn dc in dt.Columns)
			{
				columns.Add(dc.ColumnName);
			}

			var fields = typeof(T).GetFields();
			var properties = typeof(T).GetProperties();

			List<T> lst = new List<T>();

			foreach (DataRow dr in dt.Rows)
			{
				var ob = Activator.CreateInstance<T>();

				foreach (var fieldInfo in fields)
				{
					if (columns.Contains(fieldInfo.Name))
					{
						fieldInfo.SetValue(ob, dr[fieldInfo.Name]);
						Console.WriteLine("fieldname {0}", dr[fieldInfo.Name].ToString());
					}
				}

				foreach (var propertyInfo in properties)
				{
					if (columns.Contains(propertyInfo.Name))
					{
						propertyInfo.SetValue(ob, dr[propertyInfo.Name]);
						Console.WriteLine("propertyname {0}", dr[propertyInfo.Name].ToString());
					}
				}

				lst.Add(ob);
			}

			return lst;
		}

		public static class CommonMethod
		{
			public static List<T> ConvertToList<T>(DataTable dt)
			{
				var columnNames = dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName.ToLower()).ToList();
				var properties = typeof(T).GetProperties();
				return dt.AsEnumerable().Select(row => {
					var objT = Activator.CreateInstance<T>();
					foreach (var pro in properties)
					{
						if (columnNames.Contains(pro.Name.ToLower()))
						{
							try
							{
								pro.SetValue(objT, row[pro.Name]);
								Console.WriteLine("str {0}, name {0}", objT.ToString(), row[pro.Name].ToString());
							}
							catch (Exception ex) 
							{
								Console.WriteLine("error message {0}", ex.ToString());
							}
						}
					}
					return objT;
				}).ToList();

				
			}

			
		}

		private static void GetProductList(DataTable table)
		{

            //var productList = new List<Product>(table.Rows.Count);
            //foreach (DataRow row in table.Rows)
            //{
            //    var values = row.ItemArray;
            //    var product = new Product()
            //    {
            //        Mainproduct = Convert.ToString(row[0]),
            //        Name = Convert.ToString(row[1]),
            //        Price = Convert.ToString(row[2]),
            //        Visible = Convert.ToString(row[3]),
            //        NameIcon = Convert.ToString(row[4]),

            //    };
            //    productList.Add(product);
            //}
        }

		public ObservableCollection<object> GetCollection(DataTable dataTable)
		{
			Type dynamicClassType = GetDynamicClassType(dataTable);

			ObservableCollection<object> collection = new ObservableCollection<object>();

			foreach (DataRow dataRow in dataTable.Rows)
			{
				object instance = Activator.CreateInstance(dynamicClassType);

				foreach (DataColumn dataColumn in dataTable.Columns)
				{
					object value = dataRow[dataColumn];

					if (value != DBNull.Value)
					{
						dynamicClassType.InvokeMember(dataColumn.ColumnName, BindingFlags.SetProperty, null, instance, new object[] { value });
					}
				}

				collection.Add(instance);
			}

			return collection;
		}
		public Type GetDynamicClassType(DataTable dataTable)
		{
			AssemblyName assemblyName = new AssemblyName("DynamicAssembly");

			AssemblyBuilder assemblyBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly
			(
				assemblyName,
				System.Reflection.Emit.AssemblyBuilderAccess.Run
			);

			ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule("DynamicModule");

			TypeBuilder typeBuilder = moduleBuilder.DefineType("DynamicClass", TypeAttributes.Public);

			foreach (DataColumn dataColumn in dataTable.Columns)
			{
				AddDynamicProperty(typeBuilder, "_" + dataColumn.ColumnName, dataColumn.ColumnName, dataColumn.DataType);
			}

			Type dynamicClassType = typeBuilder.CreateType();

			return dynamicClassType;
		}
		public void AddDynamicProperty(TypeBuilder typeBuilder, string fieldName, string propertyName, Type propertyType)
		{
			FieldBuilder fieldBuilder = typeBuilder.DefineField(fieldName, propertyType, FieldAttributes.Private);

			PropertyBuilder propertyBuilder = typeBuilder.DefineProperty
			(
				propertyName,
				System.Reflection.PropertyAttributes.HasDefault,
				propertyType,
				null
			);

			MethodAttributes methodAttributes = MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig;

			MethodBuilder getMethodBuilder = typeBuilder.DefineMethod
			(
				string.Format("get_{0}", propertyName),
				methodAttributes,
				propertyType,
				Type.EmptyTypes
			);

			ILGenerator getILGenerator = getMethodBuilder.GetILGenerator();

			getILGenerator.Emit(OpCodes.Ldarg_0);
			getILGenerator.Emit(OpCodes.Ldfld, fieldBuilder);
			getILGenerator.Emit(OpCodes.Ret);

			MethodBuilder setMethodBuilder = typeBuilder.DefineMethod
			(
				string.Format("set_{0}", propertyName),
				methodAttributes,
				null,
				new Type[] { propertyType }
			);

			ILGenerator setILGenerator = setMethodBuilder.GetILGenerator();

			setILGenerator.Emit(OpCodes.Ldarg_0);
			setILGenerator.Emit(OpCodes.Ldarg_1);
			setILGenerator.Emit(OpCodes.Stfld, fieldBuilder);
			setILGenerator.Emit(OpCodes.Ret);

			propertyBuilder.SetGetMethod(getMethodBuilder);
			propertyBuilder.SetSetMethod(setMethodBuilder);
		}


		XElement data;

		private ObservableCollection<Product> GetProductsPriceListXML()
		{
			var mylist = new ObservableCollection<Product>();

			data = XElement.Load(pricinglocalfile);
			foreach (XElement xe1 in data.Elements())
				if (xe1.Name == "Products")
					foreach (var xe2 in xe1.Elements()) mylist.Add(new Product(xe2));
			return mylist;
		}

		

	}

	public class MediaConverter : IValueConverter
	{
		public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
			=> value != null && parameter != null && value.ToString() == parameter.ToString();
		public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
			=> parameter;

	}
	
	public class Product
	{
		public Product(XElement xe3) => this.xe4 = xe3;
		private XElement xe4;
		public string Mainproduct 
		{ 
			//get => xe4.Name.LocalName;  
			get
            {
				
				return xe4.Name.LocalName;
			}
            set { }
		}
		public string Name 
		{
			get => xe4.Attribute("Name").Value; 
			//get
			//{
			//	return xe4.Attribute("Name").Value;
			//}
			//set { }
		}
		public string Price
		{
			get
			{
				XElement xe5 = xe4.Descendants("ProductPrice").FirstOrDefault();
				if (xe5 == null) return string.Empty;
				return xe5.Attribute("Price").Value;
			}
			set
			{
				XElement xe5 = xe4.Descendants("ProductPrice").FirstOrDefault();
				if (xe5 != null) xe5.Attribute("Price").Value = value;
			}
		}
		public string Visible
		{
			get
			{
				XElement xe5 = xe4.Descendants("ProductVisibility").FirstOrDefault();
				if (xe5 == null) return string.Empty;
				return xe5.Attribute("Visible").Value;
			}
			set
			{
				XElement xe5 = xe4.Descendants("ProductVisibility").FirstOrDefault();
				if (xe5 != null) xe5.Attribute("Visible").Value = value;
			}
		}
		public string NameIcon
		{
			get
			{
				XAttribute xe5 = xe4.Attribute("DefaultIconName");
				return (xe5 == null) ? string.Empty : xe5.Value;
			}
			set
			{
				XAttribute xe5 = xe4.Attribute("DefaultIconName");
				if (xe5 == null) xe4.Add(new XAttribute("DefaultIconName", value));
				else xe5.Value = value;
			}
		}

	
	}

#pragma warning disable CS8767
	public class RelayCommand : ICommand
	{
		private readonly Predicate<object> _canExecute;
		private readonly Action<object> _action;
		public RelayCommand(Action<object> action, Predicate<object> canExecute) { _action = action; _canExecute = canExecute; }
		public void Execute(object o) => _action(o);
		public bool CanExecute(object o) => _canExecute == null ? true : _canExecute(o);

		public event EventHandler CanExecuteChanged
		{
			add { CommandManager.RequerySuggested += value; }
			remove { CommandManager.RequerySuggested -= value; }
		}
		public RelayCommand(Action<object> execute)
		   : this(execute, null)
		{
		}
	}

	public class ExcelUtlity
	{
		public bool WriteDataTableToExcel(System.Data.DataTable table, string destination)
        {
			var ds = new DataSet();
			ds.Tables.Add(table);

			ExportDSToExcel(ds, destination);
			
			return true;
        }

		//method 1
		public static void ExportDSToExcel(DataSet ds, string destination)
		{
			// https://stackoverflow.com/questions/11811143/export-datatable-to-excel-with-open-xml-sdk-in-c-sharp
			using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
			{
				var workbookPart = workbook.AddWorkbookPart();
				workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
				workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

				uint sheetId = 1;

				foreach (DataTable table in ds.Tables)
				{
					var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
					var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
					sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

					DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
					string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

					if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
					{
						sheetId =
							sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
					}

					DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
					sheets.Append(sheet);

					DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

					List<String> columns = new List<string>();
					foreach (DataColumn column in table.Columns)
					{
						columns.Add(column.ColumnName);

						DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
						cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
						cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
						headerRow.AppendChild(cell);
					}

					sheetData.AppendChild(headerRow);

					foreach (DataRow dsrow in table.Rows)
					{
						DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
						foreach (String col in columns)
						{
							DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
							cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
							cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
							newRow.AppendChild(cell);
						}

						sheetData.AppendChild(newRow);
					}
				}
			}
		}

		public static DataTable ReadExcelSheet(string fname, bool firstRowIsHeader = true)
		{
			List<string> Headers = new List<string>();
			DataTable dt = new DataTable();
			using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fname, false))
			{
				//Read the first Sheets 
				Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
				Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
				IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
				int counter = 0;
				foreach (Row row in rows)
				{
					counter = counter + 1;
					//Read the first row as header
					if (counter == 1)
					{
						var j = 1;
						foreach (Cell cell in row.Descendants<Cell>())
						{
							var colunmName = firstRowIsHeader ? GetCellValue(doc, cell) : "Field" + j++;
							Console.WriteLine(colunmName);
							Headers.Add(colunmName);
							dt.Columns.Add(colunmName);
						}
					}
					else
					{
						dt.Rows.Add();
						int i = 0;
						foreach (Cell cell in row.Descendants<Cell>())
						{
							dt.Rows[dt.Rows.Count - 1][i] = GetCellValue(doc, cell);
							i++;
						}
					}
				}

			}
			return dt;
		}

		public static string GetCellValue(SpreadsheetDocument doc, Cell cell)
		{
			string value = cell.CellValue.InnerText;
			if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
			{
				return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
			}
			return value;
		}

		//method 2
		public static DataTable ReadExcelFile (string fname)
        {
			var table = new DataTable();
			using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fname,false))
			{
				WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
				IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
				string relationshipId = sheets.First().Id.Value;
				WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
				Worksheet workSheet = worksheetPart.Worksheet;
				SheetData sheetData = workSheet.GetFirstChild<SheetData>();
				IEnumerable<Row> rows = sheetData.Descendants<Row>();
				foreach (Cell cell in rows.ElementAt(0))
				{
					table.Columns.Add(GetCellValue(spreadSheetDocument, cell));
				}
				//this will also include your header row...
				foreach (Row row in rows)
				{
					DataRow tempRow = table.NewRow();
					for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
					{
						tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
					}
					table.Rows.Add(tempRow);
				}
			}
			table.Rows.RemoveAt(0);
			return table;
		}
	}

	public static class DatatTableHelper
	{
		/// <summary>
		/// Converts a DataTable to a list with generic objects
		/// </summary>
		/// <typeparam name="T">Generic object</typeparam>
		/// <param name="table">DataTable</param>
		/// <returns>List with generic objects</returns>
		private static readonly IDictionary<Type, ICollection<PropertyInfo>> _Properties =
	   new Dictionary<Type, ICollection<PropertyInfo>>();

		/// <summary>
		/// Converts a DataTable to a list with generic objects
		/// </summary>
		/// <typeparam name="T">Generic object</typeparam>
		/// <param name="table">DataTable</param>
		/// <returns>List with generic objects</returns>
		public static IEnumerable<T> DataTableToList<T>(this DataTable table) where T : class, new()
		{
			try
			{
				var objType = typeof(T);
				ICollection<PropertyInfo> properties;

				lock (_Properties)
				{
					if (!_Properties.TryGetValue(objType, out properties))
					{
						properties = objType.GetProperties().Where(property => property.CanWrite).ToList();
						_Properties.Add(objType, properties);
					}
				}

				var list = new List<T>(table.Rows.Count);

				foreach (var row in table.AsEnumerable().Skip(1))
				{
					var obj = new T();

					foreach (var prop in properties)
					{
						try
						{
							var propType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
							var safeValue = row[prop.Name] == null ? null : Convert.ChangeType(row[prop.Name], propType);

							prop.SetValue(obj, safeValue, null);
						}
						catch
						{
							// ignored
						}
					}

					list.Add(obj);
				}

				return list;
			}
			catch
			{
				return Enumerable.Empty<T>();
			}
		}
	}


	//public class ExcelUtlity
	//{
	//	public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
	//	{
	//		Application excel;
	//		Workbook excelworkBook;
	//		Worksheet excelSheet;
	//		Range excelCellrange;
	//		try
	//		{
	//			excel = new Application();

	//			excel.Visible = false;
	//			excel.DisplayAlerts = false;
	//			excelworkBook = excel.Workbooks.Add(Type.Missing);
	//			excelSheet = (Worksheet)excelworkBook.ActiveSheet;
	//			excelSheet.Name = worksheetName;


	//			//excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();
	//			int rowcount = 2;
	//			for (int Idx = 0; Idx < dataTable.Columns.Count; Idx++)
	//			{
	//				excelSheet.Range["A1"].Offset[0, Idx].Value = dataTable.Columns[Idx].ColumnName;
	//				//row header styles
	//				excelSheet.Range["A1"].Offset[0, Idx].Font.Size = 14;
	//				excelSheet.Range["A1"].Offset[0, Idx].Font.Name = "Arial";
	//				excelSheet.Range["A1"].Offset[0, Idx].Font.FontStyle = "Bold";
	//				excelSheet.Range["A1"].Offset[0, Idx].Font.Color = ColorTranslator.ToOle(System.Drawing.Color.White);
	//				excelSheet.Range["A1"].Offset[0, Idx].Interior.Color = ColorTranslator.ToOle(System.Drawing.Color.Gray);

	//			}
	//			for (int Idx = 0; Idx < dataTable.Rows.Count; Idx++)
	//			{
	//				excelSheet.Range["A2"].Offset[Idx].Resize[1, dataTable.Columns.Count].Value =
	//				dataTable.Rows[Idx].ItemArray;
	//			}
	//			//Filter 
	//			//Excel.Range activeCell = excelSheet.Cells[1, 1];// excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[1, 2]];
	//			//activeCell.Select();

	//			////Applying auto filter to Row 1
	//			//activeCell = excelSheet.Rows[1];

	//			//excelSheet.Columns["A"].Locked = true;
	//			//excelSheet.Columns["B"].Locked = true;
	//			//excelSheet.Columns["E"].Locked = true;
	//			//excelworkBook.Worksheets[0].Range.Style.Locked = true;

	//			//excelSheet.Range["A"].Locked = true;

	//			excelSheet.Activate();


	//			excelSheet.Application.ActiveWindow.FreezePanes = true;
	//               Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)excelSheet.Rows[1];

	//			firstRow.AutoFilter(1,
	//				Type.Missing,
	//				Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,
	//				Type.Missing,
	//				true);

	//			//Auto fit all columns
	//			excelSheet.Columns.AutoFit();


	//			excelworkBook.SaveAs(saveAsLocation); ;
	//			excelworkBook.Close();
	//			excel.Quit();
	//			return true;
	//		}
	//		catch (Exception ex)
	//		{
	//			System.Windows.MessageBox.Show(ex.Message);
	//			return false;
	//		}
	//		finally
	//		{
	//			excelSheet = null;
	//			excelCellrange = null;
	//			excelworkBook = null;
	//		}

	//	}
	//}
}