Corona example

In this example we will see how multiple SPCoder connectors can be used together. We are going to scrape the data from the page that tracks data about Coronavirus Cases in different countries.

Based on that we will create the SharePoint online list and copy the coronavirus cases data to the List

At the end, we will create new modern page with pie-chart webpart, that shows the Total cases per country:

Implementation

Here we will go through the implementation details. You can copy the code below to SPCoder to try it yourself. All the code is also available in the Scripts\CSharp\Examples directory in SPCoder repo.

The best way to try this out is to copy code blocks to SPCoder's code editor, select the code you want to execute and execute it by pressing F5 or using the Execute button.

//Get the data using "Web page" connector
main.Connect("https://www.worldometers.info/coronavirus/#countries", "Web page"); 

//Get the table of latest infromation about the corona virus in the different countries
//------------

//here we use the HtmlAgilityPack project (https://html-agility-pack.net/) to scrape the page and get the html table 
//element that contains the data

var htmlnode = htmldocument.DocumentNode;
var htmltable = htmlnode.SelectSingleNode("//table[@id='main_table_countries_today']");
var thnodes = htmltable.SelectSingleNode("thead").SelectNodes(".//th");

var tbodyNode = htmltable.SelectSingleNode("(tbody)[1]");
var nodes     = tbodyNode.SelectNodes("tr[not(contains(@class,'row_continent'))]");

//create the DataTable object
var table = new DataTable("Corona");

var headers = thnodes.Select(th => th.InnerText.Trim().Replace("\n","").Replace(","," ").Replace(" "," ").Replace("/"," per ")).ToList();

//create the columns in DataTable
foreach (var header in headers)
{
    table.Columns.Add(header);
}

//get the rows from html table and clean some of the values
var rows = nodes.Skip(1).Select(tr => tr
    .Elements("td")
    .Select(td => td.InnerText.Trim().Replace(",","").Replace("N/A","").Replace("+",""))
    //.Select(td => td.InnerText.Trim())
    .ToArray());

//add the rows to the DataTable    
foreach (var row in rows)
{
    table.Rows.Add(row);
}
//------------

At this point, you can use GridViewer to check the table variable:

Now, since we have the data we want in the DataTable object, we are going to create the SharePoint list, add the appropriate fields to it and insert the data from DataTable to it.

//------------
//first we need to connect to the SharePoint online site
//Here I use the main.Connect method, but you could also use the SPCoder's Explorer window for this.
//The third and the fourth parameters of the main.Connect method are username and password. It is possible to write those values in clear text, but here I use the SPCoder's encryption mechanism.
//ENCRYPTEDUSERNAME and ENCRYPTEDPASSWORD have been created using "Crypto helper" window

string myUsername = main.Decrypt("ENCRYPTEDUSERNAME");
string myPassword = main.Decrypt("ENCRYPTEDPASSWORD");

main.Connect("https://MYtenant.sharepoint.com/sites/SPCodertest/", "SharePoint Client O365", myUsername, myPassword);
//after this we are connected to the SP Online site and have the context (ClientContext) variable available
//you will also notice that the site has appeared in the Explorer window and you can expand its subsites and see all the lists and libraries

//prepare the code for creating lists (you can also open the Utils.csx file in SPCoder and execute it instead of the following line)
execFile("Scripts\\CSharp\\SharePoint\\Utils.csx");
   
//Here we prepare the fields of the list
List<SPCoderField> myFields = new List<SPCoderField> (); 

for(int i = 0; i < headers.Count; i++)
{
    var header       = headers[i];
    string fieldType = "Number";
    if (header == "Country Other" || header == "Continent")  fieldType = "String";
    if (header =="#") continue;
    myFields.Add(new SPCoderField {Name = header.Replace(" ",""), DisplayName = header.Replace(" ",""), Type = fieldType, Group = "Corona", Values = null});
}    
//here we create the SharePoint list called Corona.
var list = CreateListWithFields(myFields, web, "Corona", context);

//------------

Now you can open your SharePoint site in browser and check if the list is created. If everything went fine, you should see the empty list called Corona, with the columns that you were also able to see in GridViewer.

The next step is to insert the data to the list.

//------------
//add the data to the list
//System.Data.DataRow row = table.Rows[0];
int cnt                 = 0;
foreach(System.Data.DataRow row in table.Rows)
{
    ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
    ListItem oListItem                         = list.AddItem(itemCreateInfo); 

    oListItem["Title"] = row["Country Other"].ToString();
    
    for(int i = 0; i < headers.Count; i++)
    {
        var header       = headers[i];
        if (!String.IsNullOrEmpty(row[header].ToString()))
        {
            if (header =="#") continue;
            string internalName = list.Fields.Where(m => m.Title == header.Replace(" ","")).FirstOrDefault().InternalName;
            if (header != "Country Other" && header != "Continent")
            {
                double num = Double.Parse(row[header].ToString());
                oListItem[internalName] = num; 
            }
            else
            {
                oListItem[internalName] = row[header].ToString(); 
            }
        }
    }
    oListItem.Update();
//we are sending the data to the server after every 50 items for performance reasons
    if (++cnt % 50 == 0) context.ExecuteQuery();
}    

context.ExecuteQuery();
//------------

After this code is executed, we should be able to see the data in the SharePoint list.

Now, the next step is to create the modern page, add the pie-chart to it and show the Total cases per country on the chart. This part of the code uses OfficeDevPnP CSOM library for easier handling of the modern pages. The library is included in SPCoder, so you don't have to download it yourself.

using OfficeDevPnP.Core.Pages;

// get a list of possible client side web parts that can be added
ClientSidePage p = new ClientSidePage(context);
var components = p.AvailableClientSideComponents();

var myWebPart = components.Where(s => s.ComponentType == 1 && s.Manifest.Contains("QuickChartWebPart")).FirstOrDefault();

CanvasSection cs = new CanvasSection(p, CanvasSectionTemplate.OneColumn, 5);
p.AddSection(cs);

ClientSideWebPart helloWp = new ClientSideWebPart(myWebPart) { Order = 10 };
helloWp.PropertiesJson = @"{'data':[{'id':'7CFFD4B0-436E-430D-94C5-A4F9D22DB3FE','label':'','value':'','valueNumber':0}],'type':1,'isInitialState':false,'dataSourceType':1,'listItemOrderBy':0,'selectedListId':'" + list.Id.ToString() + "','selectedLabelFieldName':'Title','selectedValueFieldName':'TotalCases','xAxisLabel':'','yAxisLabel':''}";
p.AddControl(helloWp, cs.Columns[0]);

//This will save the page to SitePages library
p.PageTitle = "Corona Stats page 2";
p.LayoutType = ClientSidePageLayoutType.Article;
p.Save("CoronaStats2.aspx");

After this code runs, you should check the SitePages library on your SharePoint online site and look for the CoronaStats2.aspx page. If everything worked fine the page should contain the pie chart with the total corona cases per country.