HTML Table into List then Display List Contents in Excel

8 posts, 0 answers
  1. Ian
    Ian avatar
    3 posts
    Member since:
    Jan 2016

    Posted 15 Feb Link to this post

    Hi, i hope someone is able to guide me in the right direction.

     I am currently trying to export a SQL list and a HTML Table list into excel, then run a comparison on the results.

     I can export the SQL list into excel and the data shows correctly, but when i try to do the same on the HTML list, it is only showing 1 item.

     Here is the code that i am using below with the HTML code at the top, and the working SQL code below (have masked some parts with ????)

     Many thanks in advance!

     

    01.IList<HtmlTableCell> myList = ActiveBrowser.Find.AllByTagName<HtmlTableCell>("td");   
    02.     
    03.     foreach (HtmlTableCell item in myList)  
    04.     {
    05.         string csv2 = item.InnerText.ToString().Trim();
    06.         string[] parts2 = csv2.Split(',');
    07.         List<string> cellList = new List<string>(parts2);
    08.         cellList.Add(csv2);
    09. 
    10.             foreach (var tableitems in cellList)
    11.             {
    12.                 // WRITE EXCEL CODE HERE
    13.                    Log.WriteLine("SHOW CSV2: " + csv2);
    14.                    int row = 2;
    15.                    int col = 2;
    16.                    excelApp.Cells[row, col] = tableitems;
    17.                    row++;
    18.                    if (cellList.IndexOf(tableitems) == cellList.Count - 1)
    19.                             
    20.                    col = 2;
    21.             }
    22.     }
    23.     
    24. 
    25.// ********** SQL DATA CODE START **********
    26.List<string>LicenseeList=new List<string>();
    27.using(SqlConnection Conn = new SqlConnection(@"" + myVar2))
    28.{
    29.    string qry = @"SELECT Name FROM ?????? WHERE ?????? = '????'";
    30.    var cmd = new SqlCommand(qry,Conn);
    31.    cmd.CommandType = dt.CommandType.Text;
    32.    Conn.Open();
    33.        using (SqlDataReader reader = cmd.ExecuteReader())
    34.        {
    35.            if (reader.HasRows)
    36.            {
    37.                while (reader.Read())
    38.                {
    39.                    string item = reader.GetString(reader.GetOrdinal("?????"));
    40.                    LicenseeList.Add(item);
    41.                     
    42.                }
    43.                 
    44.                // DO THE EXCEL STUFF HERE
    45.                Log.WriteLine("THIS MANY IN LIST(SQL DATA): " + LicenseeList.Count.ToString());
    46.                int row = 2;
    47.                int col = 1;
    48.                foreach (var LicenseeItem in LicenseeList)
    49.                {
    50. 
    51.                    excelApp.Cells[row, col] = LicenseeItem;
    52.                    row++;
    53.                    if (LicenseeList.IndexOf(LicenseeItem) == LicenseeList.Count - 1)
    54.                             
    55.                    col = 1;
    56.                }
    57.            }
    58.        // Close the Reader   
    59.        reader.Close();
    60.        }
    61.    // Close the connection   
    62.    Conn.Close();
    63.}
    64.// ******** SQL DATA CODE END *******

     

  2. Boyan Boev
    Admin
    Boyan Boev avatar
    1045 posts

    Posted 15 Feb Link to this post

    Hello Ian,

    The code seems to be correct.

    Could you please print all values of the HTML IList in the log so you can check whether it contains more than one item?

    Let me know the results.

    Regards,
    Boyan Boev
    Telerik
     
    The New Release of Telerik Test Studio Is Here! Download, install,
    and send us your feedback!
  3. Ian
    Ian avatar
    3 posts
    Member since:
    Jan 2016

    Posted 15 Feb in reply to Boyan Boev Link to this post

    Hi Boyan,

    Here is the log result from the items in the IList

    '15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: AllArgentinaAustraliaChileFranceItalyNew ZealandSouth AfricaSpainUnited KingdomUruguayUSA'15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: AllFixedJuicingMainSlicing'15/02/2016 15:47:12' - LOG: SHOW CSV2: Another Fixed'15/02/2016 15:47:12' - LOG: SHOW CSV2: United Kingdom'15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: Fixed'15/02/2016 15:47:12' - LOG: SHOW CSV2: Iantest'15/02/2016 15:47:12' - LOG: SHOW CSV2: United Kingdom'15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: Main'15/02/2016 15:47:12' - LOG: SHOW CSV2: Impreza (Pink Lady Main)'15/02/2016 15:47:12' - LOG: SHOW CSV2: United Kingdom'15/02/2016 15:47:12' - LOG: SHOW CSV2: Neal Tester'15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:12' - LOG: SHOW CSV2: neal@tester.com'15/02/2016 15:47:12' - LOG: SHOW CSV2: Main'15/02/2016 15:47:12' - LOG: SHOW CSV2: Testing'15/02/2016 15:47:12' - LOG: SHOW CSV2: USA'15/02/2016 15:47:12' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: Main'15/02/2016 15:47:13' - LOG: SHOW CSV2: Testing & Ampersand'15/02/2016 15:47:13' - LOG: SHOW CSV2: United Kingdom'15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: Main'15/02/2016 15:47:13' - LOG: SHOW CSV2: Testing 1'15/02/2016 15:47:13' - LOG: SHOW CSV2: Australia'15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: Fixed'15/02/2016 15:47:13' - LOG: SHOW CSV2: Testing 2'15/02/2016 15:47:13' - LOG: SHOW CSV2: Chile'15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: Fixed'15/02/2016 15:47:13' - LOG: SHOW CSV2: Testing 3'15/02/2016 15:47:13' - LOG: SHOW CSV2: United Kingdom'15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: Main'15/02/2016 15:47:13' - LOG: SHOW CSV2: Testing 4'15/02/2016 15:47:13' - LOG: SHOW CSV2: France'15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: Main'15/02/2016 15:47:13' - LOG: SHOW CSV2: Testing, Comma'15/02/2016 15:47:13' - LOG: SHOW CSV2: United Kingdom'15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: Main'15/02/2016 15:47:13' - LOG: SHOW CSV2: testzdata'15/02/2016 15:47:13' - LOG: SHOW CSV2: United Kingdom'15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: '15/02/2016 15:47:13' - LOG: SHOW CSV2: Main

     

    The item that gets displayed in the excel file is the value "Main"  which although gets displayed a few times, interestingly it is the last value in the IList

    It is almost as if it is getting entered into the spreadsheet, but is overwriting itself rather than going to the next row.

    Many Thanks

     Ian

  4. Boyan Boev
    Admin
    Boyan Boev avatar
    1045 posts

    Posted 16 Feb Link to this post

    Hi Ian,

    I don't have access to your file and I cannot debug the code. The issue seems to be in the two foreach loops.

    You should debug this code and find why after the first it write only one item.

    Looking forward to hearing from you.

    Regards,
    Boyan Boev
    Telerik
     
    The New Release of Telerik Test Studio Is Here! Download, install,
    and send us your feedback!
  5. Ian
    Ian avatar
    3 posts
    Member since:
    Jan 2016

    Posted 16 Feb in reply to Boyan Boev Link to this post

    Hi Boyan,

    I have tried so many different variations to try to get this data to display, but have been unable to get it to work properly.

    If i was to ask for your help further, what do you need to help?

    Many Thanks

    Ian

  6. Ian
    Ian avatar
    5 posts
    Member since:
    Feb 2016

    Posted 17 Feb Link to this post

    Hi Boyan, i have managed to solve it by using the following code,  it turns out that the 2 int variables needed to be outside the loop, as each iterate it would reset back to the initial value!

    I now have another issue though, in that i need to loop through the list in iterations of 6, myList.Skip(6) will jump over the 1st 6, but i am not sure how to continue to skip each 6 items.

    HtmlTable tabletest = ActiveBrowser.Find.ById<HtmlTable>("MainContent_ucLicensees_gviGeneric");
    IList<HtmlTableCell> myList = tabletest.Find.AllByTagName<HtmlTableCell>("td");;
     
            int rowh = 1;
            int colh = 2;    
     
        foreach (HtmlTableCell item in myList.Skip(6))
        {
            string celltext = item.InnerText.ToString();            
            excelApp.Cells[rowh, colh] = celltext;
            rowh++;   
        }

  7. Ian
    Ian avatar
    5 posts
    Member since:
    Feb 2016

    Posted 17 Feb in reply to Ian Link to this post

    Hi Boyan,

    I have managed to figure out how to jump every 6 items issue that i was having

    Changing the foreach loop below solved the issue.

    Thank you for all your help, just hearing that the code wasnt fundamentally wrong helped a lot!

    foreach (HtmlTableCell item in myList.Skip(6).Where((x,i) => i % 6 == 0))

  8. Boyan Boev
    Admin
    Boyan Boev avatar
    1045 posts

    Posted 19 Feb Link to this post

    Hello Ian,

    I am very happy to hear that!

    If you need further assistance please let us know.

    Regards,
    Boyan Boev
    Telerik
     
    The New Release of Telerik Test Studio Is Here! Download, install,
    and send us your feedback!
Back to Top