Corona can even spread to SharePoint - with C#

I've used SPCoder, (the tool for writing and executing C# code, that I had developed) to import Coronavirus data to SharePoint.

The idea is to get the data from the page that tracks the daily Coronavirus updates in the world:

Worldometer's COVID-19 data

Then, create a SharePoint list in SharePoint Online site and insert the coronavirus data to it:

SharePoint list with data from Worldometer site

And finally, create a SharePoint modern page, insert a chart webpart to it and show a pie-chart with coronavirus data:

SharePoint modern page - pie chart with coronavirus data

The code and explanation are also available on GitHub wiki page.

//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("+",""))
    .ToArray());

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

After executing the previous part of the code, the coronavirus details are scraped from the web page and stored in DataTable variable called "table". You can open GridViewer in SPCoder, and inspect the variable:

SPCoder GridView window showing the scraped data

Now we can create the SharePoint list, add the appropriate fields to it, and insert the data from DataTable to the list.

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

After this step 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. Names of some of the columns have been changed due to the rules that SharePoint field names must follow, related to special characters.

The next step is to insert the data to the list. Here we use standard CSOM code for adding the items to the SharePoint list. We use some batching in order to speed up the process (pushing the data to server after every 50 newly added items):

//------------
//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 the code above 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.

SharePoint online - use classic rich text editor in spfx webpart

In one of my recent projects for SharePoint online, the client wanted to use the classic rich text editor in the SharePoint Framework (spfx) webpart. The one that is available by default when you edit a Multiple lines of text field in classic SharePoint experience.

The reason for this was the fact they knew how to use it from previous experience in SharePoint on-premise and the fact that all available rich text editors for modern pages had (and still do have!) many limitations.

I did some research and found out that Microsoft also uses the classic editor when editing the content of the rich text fields in modern list experience (details pane).

So I've implemented similar functionality in my webpart, which can be used on any modern or classic page for editing rich text/html.

The implemented DEMO webpart looks like this:

After clicking on the "Edit html" button, the dialog box with the editor, containing the html code opens:

You can also edit the html directly if you click the "Edit Source" button:

Implementation

In short, my approach was to:

• use the out of the box NewForm.aspx page in dialog box, with only one rich text field; 
• fill the field with some initial html using client side code;
• allow the user to edit the html and click the Save button;
• get the produced html into a javascript variable, prevent default saving and close the dialog box.

After that you can do anything with the produced html (save it in a list or library field, send it to some third-party API, save it as a property of a webpart...). My DEMO webpart saves the edited html in it's property variable.

Setup

In order for the webpart to work, you need to provide it with the title of a list and a name of the Multiple lines of text column.You can, of course, use any existing list for this, but I created a separate list (called TmpTextEditHelper), added one Multiple lines of text column (called HtmlField) and selected Enhanced rich text (Rich text with pictures, tables, and hyperlinks) as type of text. The important thing to mention is that, webpart will NEVER save anything to this list. 

The NewForm page can be opened with the following link:
https://YOURTENANT.sharepoint.com/sites/YOURSITE/Lists/TmpTextEditHelper/NewForm.aspx?OnlyIncludeOneField=HtmlField&ClientFormOverwriteSave=1&isdlg=1

• TmpTextEditHelper - The name of the list.
• OnlyIncludeOneField=HtmlField - Tells the server side code to render only one field (HtmlField) and exclude other fields like Title.
• ClientFormOverwriteSave=1 - Microsoft uses this parameter also when opening the form in modern list edit pane. It probably tells the server side code not to save the form in case it is submitted, but I wasn't able to find any documentation on it.
• isdlg=1 - Tells the server side code that the form will be viewed inside the dialog, so it does not render any unnecessary things, like navigation

Installation

After the webpart is added to AppCatalog, set as trusted and added to the SharePoint site, it can be added to any page. After adding to the page, you need to edit it's properties and set the name of the list and the field to be used, as described above.

You can find the source code for the webpart here.