HTML Table into List then Display List Contents in Excel

[Ian]

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 *******

 

[Boyan Boev]

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

 

[Ian]

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: [e-mail]'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

[Boyan Boev]

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

 

[Ian]

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

[Ian]

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++;
    }

[Ian]

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))

[Boyan Boev]

Hello Ian,

I am very happy to hear that!

If you need further assistance please let us know.

Regards,
Boyan Boev
Telerik