Quick way to convert excel, csv file data to C# Datatable

I received English and Arabic data in Excel file and I have to migrate this data to SQL server.

I don’t want to use Office API for this as it’s a bit complex.

So for this, I used Nuget package “ExcelDataReader” and “ExcelDataReader.DataSet” as below

ExcelDataReader Nuget Package

Note :- Must include “ExcelDataReader.DataSet” Nuget package for getting AsDataSet() method in code.

Code for reading excel file and to get data table is very simple as 

using ExcelDataReader;

using System.IO;

namespace ExcelConsoleApp1

{

    class Program

    {

        static void Main(string[] args)

        {

            //1. Create File Stream for Excel file

            FileStream stream = File.Open("d:\\NewsListWithAllColumns.xlsx", FileMode.Open, FileAccess.Read);

            //2.. Reading from a Excel file *.xlsx

            IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream);

            //...

            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)

            //IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            //...

            //3. DataSet Configuration - Create column names from first row

            ExcelDataSetConfiguration excelDataSetConfiguration = new ExcelDataSetConfiguration

            {

                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()

                {

                    UseHeaderRow = true

                }

            };

            //4. DataSet - The result of each spreadsheet will be created in the result.Tables

            var dataSet = excelReader.AsDataSet(excelDataSetConfiguration);

            //5. Get Datatable from dataset

            var dataTable = dataSet.Tables[0];

            //6. Read one of the cell to test

            var c11 = dataTable.Rows[2][2];

            //7. Free resources (IExcelDataReader is IDisposable)

            excelReader.Close();

        }

    }

}

Below is the screenshot while to view datatable output in program debug mode as

Excel to Datatable

Let me know if you have any other idea to read excel file and get its values.

Manage Youtube videos from Sitecore

This is a series of 3 articles where I will show how you control Facebook, Twitter and Youtube feeds from Sitecore and display it on your web page.
 

In last 2 post, I showed how to manage Facebook feeds using Facebook graph API and Twitter Tweets by using its API.

In this post, we will check how to access and control Youtube video feeds from Sitecore. 

Youtube videos from Sitecore

For this you need API keys from Google for using Youtube API. 

Implementation

Create Sitecore template having fields like

Sitecore fields for Youtube video feeds

Code

Install Nuget package named “Google.Apis.YouTube.V3″ to your HELIX based feature project.

First we need to create Template mapping class for these Youtube fields as

namespace Sitecore.Feature.Social

{

    using Sitecore.Data;

    public struct Templates

    {

        public struct SocialFeedsManager

        {

            public static ID ID = new ID("{B1959961-A315-468D-8D03-CA50CB790C86}");

            public static ID ContentItemID = new ID("{EC8578D8-4C0F-413E-83F2-C9BA5B249E85}");

            public struct Fields

            {

            

                public static readonly ID YoutubeAPIKey = new ID("{D2B9B6B6-2A4C-4E7F-B63E-D9D119E599AA}");

                public static readonly ID YoutubeChannelId = new ID("{AEB5A02B-9CC8-4A30-92D6-63BA90F10C2B}");

                public static readonly ID YoutubeQueryPart = new ID("{BB545EE0-FFD7-4372-8D2B-AF36470A435C}");

                public static readonly ID YoutubePlatformName = new ID("{34465AAC-CB7A-4B39-96A5-2EB029C288D9}");

                public static readonly ID YoutubeFrom = new ID("{C1BE81E3-62E9-49A0-A805-F9A7A1B1B08D}");

                public static readonly ID YoutubeMaxFeedsInAll = new ID("{FAB9C465-C484-4928-8A89-C7191ADBCE61}");

                public static readonly ID YoutubeMaxFeeds = new ID("{E99D9E1D-D930-4244-B7A0-7C0E3923C44A}");

                public static readonly ID YoutubeIsActive = new ID("{D2508648-EB83-40A7-86F2-845EFA86A6F9}");            }

        }

    }

}

Create Model classes to holds values of Sitecore item fields values and JSON data return by Youtube for video feeds as

·         ChannelName – Enum for different social channel

·         YoutubeFeeds – Get video feeds from Youtube

·         SocialMediaFeed – Class to hold single video feed data from Youtube JSON data

using System.Collections.Generic;

using System.Linq;

using Google.Apis.YouTube.v3;

using Google.Apis.Services;

namespace Sitecore.Feature.Social.Models

{

    public class YoutubeFeeds

    {

        public List<SocialMediaFeed> GetYoutubeVideos(string apiKey, string queryPart, string channelId, string platformName, string from, int maxResult = 2)

        {

            YouTubeService yt = new YouTubeService(new BaseClientService.Initializer() { ApiKey = apiKey });

            var searchListRequest = yt.Search.List(queryPart);

            searchListRequest.MaxResults = maxResult;

            searchListRequest.Order = SearchResource.ListRequest.OrderEnum.Date;

            searchListRequest.ChannelId = channelId;

            var searchListResult = searchListRequest.Execute();

            var lstYoutubeFeeds = searchListResult.Items.Select(x => new SocialMediaFeed

            {

                FeedID = x.Id.VideoId.ToString(),

                PlatformName = platformName,

                Title = x.Snippet.Title,

                Description = x.Snippet.Description,

                ImageURL = (x.Snippet.Thumbnails.Medium ?? x.Snippet.Thumbnails.High ?? x.Snippet.Thumbnails.Standard ?? x.Snippet.Thumbnails.Default__).Url,

                FeedURL = "https://www.youtube.com/watch?v=" + x.Id.VideoId.ToString(),

                From = from,

                Timestamp = x.Snippet.PublishedAt.Value

            }).ToList();

            return lstYoutubeFeeds;

        }

    }

}

using Newtonsoft.Json;

using Newtonsoft.Json.Serialization;

using Sitecore.Data.Items;

using Sitecore.Foundation.SitecoreExtensions.Extensions;

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Web;

using static Sitecore.Feature.Social.Templates;

namespace Sitecore.Feature.Social.Models

{

    public enum ChannelName

    {

        All,

        Facebook,

        Twitter,

        Instagram,

        Youtube

    }

    /// <summary>

    /// Social Feeds Model for getting Sitecore fields data

    /// </summary>

    public class SocialFeedsManagerModel

    {

        //Youtube

        public string YoutubeAccessToken { get; set; }

        public string YoutubeAccessTokenSecret { get; set; }

        public string YoutubeConsumerKey { get; set; }

        public string YoutubeConsumerSecret { get; set; }

        public string YoutubePlatformName { get; set; }

        public int YoutubeMaxFeedsInAll { get; set; }

        public int YoutubeMaxFeeds { get; set; }

        public bool YoutubeIsActive { get; set; }

        public SocialFeedsManagerModel() { }

        public void InitializeData(Item dataSourceItem, ChannelName channelName)

        {

            if (channelName == ChannelName.Youtube || channelName == ChannelName.All)

            {

                //Youtube

                this.YoutubeIsActive = dataSourceItem.Fields[SocialFeedsManager.Fields.YoutubeIsActive].IsChecked();

                if (this.YoutubeIsActive)

                {

                    this.YoutubeAPIKey = dataSourceItem[SocialFeedsManager.Fields.YoutubeAPIKey];

                    this.YoutubeChannelId = dataSourceItem[SocialFeedsManager.Fields.YoutubeChannelId];

                    this.YoutubeFrom = dataSourceItem[SocialFeedsManager.Fields.YoutubeFrom];

                    this.YoutubeMaxFeeds = System.Convert.ToInt32(dataSourceItem[SocialFeedsManager.Fields.YoutubeMaxFeeds]);

                    this.YoutubeMaxFeedsInAll = System.Convert.ToInt32(dataSourceItem[SocialFeedsManager.Fields.YoutubeMaxFeedsInAll]);

                    this.YoutubePlatformName = dataSourceItem[SocialFeedsManager.Fields.YoutubePlatformName];

                    this.YoutubeQueryPart = dataSourceItem[SocialFeedsManager.Fields.YoutubeQueryPart];

                }

            }

        }

    }

    /// <summary>

    /// Model for Social Media Feeds returning as JSON

    /// </summary>

    public class SocialMediaFeed

    {

        public string FeedID { get; set; }

        public string PlatformName { get; set; }

        public string Title { get; set; }

        public string Description { get; set; }

        public string ImageURL { get; set; }

        public string FeedURL { get; set; }

        public DateTime Timestamp { get; set; }

        public string From { get; set; }

    }

}

Below is Controller code for fetching video feeds from Youtube model class and then as per settings in Sitecore item, return JSONResult of Youtube Feeds which finally received by AJAX request to show it on page.

namespace Sitecore.Feature.Social.Controllers

{

    using Newtonsoft.Json;

    using Sitecore.Data.Items;

    using Sitecore.Feature.Social.Models;

    using Sitecore.Foundation.SitecoreExtensions.Extensions;

    using Sitecore.Mvc.Presentation;

    using System;

    using System.Collections.Generic;

    using System.Globalization;

    using System.IO;

    using System.Linq;

    using System.Net;

    using System.Security.Cryptography;

    using System.Text;

    using System.Web.Mvc;

    using System.Web.Script.Serialization;

    using static Sitecore.Feature.Social.Templates;

    public class SocialController : Controller

    {

        private List<SocialMediaFeed> lstSocialFeeds = null;

        public ActionResult GetSocialFeeds(string channelName, string token)

        {

            string result = "Error - ";

            if (string.IsNullOrEmpty(channelName) || string.IsNullOrEmpty(token) || !token.Equals("accesstoken"))

            {

                result += "Unauthorized request!!!";

            }

            channelName = channelName ?? "all";

            result = CollectSocialFeeds(channelName, result);

            if (lstSocialFeeds != null && lstSocialFeeds.Count > 0)

            {

                return Json(new { success = true, responseResult = lstSocialFeeds }, JsonRequestBehavior.AllowGet);

            }

            else

            {

                return Json(new { success = false, responseResult = result }, JsonRequestBehavior.AllowGet);

            }

        }

        /// <summary>

        /// Collect all feeds in list

        /// </summary>

        /// <param name="channelName"></param>

        /// <param name="result"></param>

        /// <returns></returns>

        private string CollectSocialFeeds(string channelName, string result)

        {

            try

            {

                SocialFeedsManagerModel _SocialFeedsManagerModel = new SocialFeedsManagerModel();

                Item dataSourceItem = null;

                if (RenderingContext.CurrentOrNull != null)

                {

                    var dataSourceId = RenderingContext.CurrentOrNull.Rendering.DataSource;

                    dataSourceItem = Sitecore.Context.Database.GetItem(dataSourceId);

                }

                if (!dataSourceItem?.IsDerived(Templates.SocialFeedsManager.ID) ?? true)

                {

                    dataSourceItem = Context.Database.GetItem(SocialFeedsManager.ContentItemID);

                }

                lstSocialFeeds = new List<SocialMediaFeed>();

                switch (channelName.ToLower())

                {                    

                    case "youtube":

                        {

                            _SocialFeedsManagerModel.InitializeData(dataSourceItem, ChannelName.Youtube);

                            if (_SocialFeedsManagerModel.YoutubeIsActive)

                            {

                                YoutubeFeeds youtube = new YoutubeFeeds();

                                lstSocialFeeds = youtube.GetYoutubeVideos(_SocialFeedsManagerModel.YoutubeAPIKey, _SocialFeedsManagerModel.YoutubeQueryPart

                                    , _SocialFeedsManagerModel.YoutubeChannelId, _SocialFeedsManagerModel.YoutubePlatformName, _SocialFeedsManagerModel.YoutubeFrom,

                                    _SocialFeedsManagerModel.YoutubeMaxFeeds);

                            }

                            else

                            {

                                result += " Youtube feeds are disabled.";

                            }

                            break;

                        }       

                    default:

                        {

                            result += " Invalid Social Media.";

                            lstSocialFeeds = null;

                            break;

                        }

                }

            }

            catch (Exception ex)

            {

                result += " Occurred." + ex.Message;

                lstSocialFeeds = null;

                string str = ex.Message;

            }

            return result;

        }

    }

}

Write AJAX script and view in HTML to load the feeds 

@model Sitecore.Feature.Social.Models.JSONOutput

<div id="SocialFeedsDiv">

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

    <script>

        $(document).ready(function () {

            var b = null;

            $("#all").click(function () {

                loadfeeds("All")

            });

            $("#Facebook").click(function () {

                loadfeeds("Facebook")

            });

            $("#Twitter").click(function () {

                loadfeeds("Twitter")

            });

            $("#Youtube").click(function () {

                loadfeeds("Youtube")

            });

            $("#Instagram").click(function () {

                loadfeeds("Instagram")

            });

            function loadfeeds(channelName) {

                $.get("/api/sitecore/social/GetSocialFeeds?channelName=" + channelName + "&token=accesstoken", function (data, status) {

                    var index = 1;

                    $("#feedlist").html("");

                    $.each(data.responseResult, function (i, field) {

                        var imageurl = "";

                        if (field.ImageURL !="" || field.ImageURL != null) {

                            imageurl = "ImageURL : <a href='" + field.FeedURL + "' ><img src='" + field.ImageURL + "' alt='" + field.Title + "' /> </a> <br>";

                        }

                        $("#feedlist").append(

                            "<li>Sr No :" + index + "<br>" +

                            "FeedID :" + field.FeedID + "<br>" +

                            "PlatformName :" + field.PlatformName + "<br>" +

                            "Title :" + field.Title + "<br>" +

                            "Description :" + field.Description + "<br>" + imageurl

                            +

                            "FeedURL :" + field.FeedURL + "<br>" +

                            "Timestamp :" + field.Timestamp + "<br>" +

                            "From :" + field.From + "<br><br><br>" + "</li>"

                        );

                        index++;

                    });

                });

            }

        });

    </script>

     <br />

    <a href="javascript:void(0)" id="all">All</a><br />

    <a href="javascript:void(0)" id="Facebook">Facebook</a><br />

    <a href="javascript:void(0)" id="Twitter">Twitter</a><br />

    <a href="javascript:void(0)" id="Youtube">Youtube</a><br />

    <a href="javascript:void(0)" id="Instagram">Instagram</a><br />

    <div id="feed">

    </div>

    <br />

    <br />

    <div id="HTMLfeed">

        <ul id="feedlist"></ul>

    </div>

</div>

We can access this action method from URL AJAX as

http://habitat.dev.local/api/sitecore/social/GetSocialFeeds?channelName=youtube&token=accesstoken

This will return JSON result which include individual feeds

{

    "success": true,

    "responseResult": [

        {

            "FeedID": "111111111_2222222",

            "PlatformName": "Youtube",

            "Title": "Title of video",

            "Description": " We all love free things. If you apply same concept on study, its great for professional life as well as for future",

            "ImageURL": "https://i.ztimg.com/vi/111111/default.jpg",

            "FeedURL": "https://www.youtube.com/watch?v=111111",

            "Timestamp": "/Date(1530689661000)/",

            "From": " Bill Gates"

        }

    ]

}

Once we have JSON, we can show it in page in any desired HTML format.

That’s it.

I hope you enjoy this Sitecore article. Stay tuned for more Sitecore related articles.

Till that happy Sitecoring :)

Please leave your comments or share this article if it’s useful for you.