Click here to Skip to main content
14,580,625 members
Rate this:
Please Sign up or sign in to vote.
See more:
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;
        }
    }
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100