I have one test data model and one excel book which has multiple sheets! all the sheet is having the same unique key "Test1" and I have a function which fetches all the data from all sheets having that key name "Test1" Now after join - I get one table having all the column name and values But now I want to access certain columns as Taable1.someobject(table).columnName Test Data Sheet: https://drive.google.com/file/d/1FdUq2iOVUXl8yIc2xDyO0bxCelhyRZ-t/view?usp=sharing What I have tried: Code: <pre>namespace AutoFramework.Model.Excel { public class AccessExcelData { public static string TestDataFileConnection() { string Filename = "D:\\GIT-TA\\src\\Automation\\Framework\\ExcelData\\TestData.xlsx"; string connectionString = string.Format("Dsn=Excel Files;READONLY=true;DBQ={0};", Filename); System.Data.Odbc.OdbcCommand odbcCmd = new System.Data.Odbc.OdbcCommand("", new System.Data.Odbc.OdbcConnection(connectionString)); return connectionString; } public static IList<TestDataModel> GetAllTestData(string keyName) { DataSet ds = new DataSet(); DataNamesMapper<TestDataModel> mapper = new DataNamesMapper<TestDataModel>(); DataTable dataTableALL = new DataTable(); List<TestDataModel> testData = new List<TestDataModel>(); using (var connection = new OdbcConnection(TestDataFileConnection())) { connection.Open(); OdbcCommand cmd = new OdbcCommand(); cmd.Connection = connection; System.Data.DataTable dtSheet = null; dtSheet = connection.GetSchema(OdbcMetaDataCollectionNames.Tables, null); foreach (DataRow row in dtSheet.Rows) { string sheetName = row["TABLE_NAME"].ToString(); if (!sheetName.EndsWith("$")) continue; // Query each excel sheet. var query = string.Format("select * from [{0}] where TestName = '{1}'", sheetName, keyName); cmd.CommandText = query; DataTable dt = new DataTable(); dt.TableName = sheetName; OdbcDataAdapter da = new OdbcDataAdapter(cmd); da.Fill(dt); ds.Tables.Add(dt); } cmd = null; connection.Close(); } DataTable data= JoinExcelDatatoOneRow(ds); testData = mapper.Map(data).ToList(); return testData.ToList(); } public static DataTable JoinExcelDatatoOneRow(DataSet ds) { DataTable flatTable = null; string ID = "TestName"; for (int i = 0; i < ds.Tables.Count; i++) { DataTable dt = ds.Tables[i]; if (i == 0) { flatTable = dt.AsEnumerable().CopyToDataTable(); } else { DataColumn[] columns = dt.Columns.Cast<DataColumn>().ToArray(); foreach (DataColumn col in columns) { if (col.ColumnName != ID) { flatTable.Columns.Add(col.ColumnName, col.DataType); } } var joins = from t1 in flatTable.AsEnumerable() join t2 in dt.AsEnumerable() on t1.Field<string>(ID) equals t2.Field<string>(ID) select new { t1 = t1, t2 = t2 }; foreach (var join in joins) { foreach (string column in columns.Cast<DataColumn>().Select(x => x.ColumnName)) { if (column != ID) { join.t1[column] = join.t2[column]; } } } } } string[] filteredColumns = { "payLater", "portal", "delivery" }; int[] filteredIndexes = filteredColumns.Select(x => flatTable.Columns.Cast<DataColumn>().Where(y => x == y.ColumnName).First().Ordinal).ToArray(); DataTable filteredTable = new DataTable(); foreach (int index in filteredIndexes) { filteredTable.Columns.Add(flatTable.Columns[index].ColumnName, flatTable.Columns[index].DataType); } foreach (DataRow row in flatTable.AsEnumerable()) { filteredTable.Rows.Add(filteredIndexes.Select(y => row[y]).ToArray()); }; filteredTable.AsEnumerable().Select((x, i) => flatTable.Rows[i][7]); DataRow row1 = filteredTable.AsEnumerable().Where(x => x.Field<string>("portal") == "abc").First(); return flatTable; } } }
TestData Model
namespace AutoFramework.Model.Excel { public partial class TestDataModel { public TestDataModel() { } [DataNames("TestName")] public string TestName { get; set; } [DataNames("productId")] public int productId { get; set; } [DataNames("orderId")] public int orderId { get; set; } [DataNames("designMethod")] public DesignMethod designMethod { get; set; } [DataNames("signedIn")] public bool signedIn { get; set; } [DataNames("increaseBasketQty")] public bool increaseBasketQty { get; set; } [DataNames("signedInCMS")] public bool signedInCMS { get; set; } [DataNames("editable")] public bool editable { get; set; } //[DataNames("paymentOptions")] //public PaymentOptions paymentOption { get; set; } [DataNames("checkInVoice")] public bool checkInVoice { get; set; } [DataNames("navigateToDesign")] public bool navigateToDesign { get; set; } [DataNames("checkOrderAuthorsie")] public bool checkOrderAuthorsie { get; set; } [DataNames("checkSplitOrder")] public bool checkSplitOrder { get; set; } [DataNames("SiteId")] public string SiteId { get; set; } [DataNames("SiteUrl")] public string SiteUrl { get; set; } [DataNames("CultureCode")] public string CultureCode { get; set; } [DataNames("SiteGroupId")] public string SiteGroupId { get; set; } [DataNames("NickName")] public string NickName { get; set; } [DataNames("byCard")] public string byCard { get; set; } [DataNames("payLater")] public string payLater { get; set; } [DataNames("sliceIt")] public string sliceIt { get; set; } [DataNames("portal")] public PaymentPortal portal { get; set; } [DataNames("delivery")] public static DeliveryMethod delivery{get;set;} } } Accessing Data var param1 = AccessExcelData.GetAllTestData("Test1"); var orderId = param[1].orderId; Now I want to access say column value of "payLater" as param1.PaymentOptions.payLater where PaymentOptions function is public class PaymentOptions { public PaymentPortal portal; public DeliveryMethod delivery = DeliveryMethod.Billing; public PaymentOptions() { } public PaymentOptions(Site site) { } } public class KlarnaOptions : PaymentOptions { //default - don't use card payment by deffault public bool byCard = false; public bool payLater = false; public bool sliceIt = false; public KlarnaOptions() { portal = PaymentPortal.Klarna; } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)