Click here to Skip to main content
16,015,504 members
Articles / Database Development / SQL Server

Real time Spreadsheet

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
27 May 2016CPOL5 min read 21.3K   14   6
Reat time Spreadsheet, updating and retrieving data through a Web Service. Using SQL Server DB.If two or more users are working on the same spreadsheet, they can see what the other users are doing. Mouse pointing is highlighted by filling the cell with diagonal stripes, while data saved is highlight

Application demo - YouTube video 

Please find a live demonstration of the features of the application described into this article here:

https://www.youtube.com/watch?v=DO6BQpYu--0

Introduction

A brief introduction to a Real time Spreadsheet, developed in C#, using VS2015, SQL Server 2014, SignalR and Telerik UI components:

The UI component is the Telerik RadSpreadsheet for Silverlight. Exactly the same concepts can be extended to the Web version of the same UI component.

Data is updated and retrieved through a Web Service, responsible to accepts requests and sends responses back.

The request  is forwarded to an Application Service Layer (C#) who is responsible to manage the proper Update/Insert/Delete operations.

Finally, the Entity Framework is then used to map entities into the Data Layer.

Main features described into this article:

If two or more users are working on the same spreadsheet, they can see what the other users are doing. Mouse pointing is highlighted by filling the cell with diagonal stripes, while data saved is highlighted via green filling.

The system is capable of calling actions from the Database itself as well. The actions are triggered by proper cells. Once an action is triggered, the database is able to call the Web Service and do proper data changes.

Also those actions are simultaneously broadcast to all the users working on the same spreadsheet.

Background

I would recommend to read a couple of articles regarding SignalR:

http://www.asp.net/signalr/overview/getting-started/tutorial-getting-started-with-signalr

http://stackoverflow.com/questions/15128125/how-to-use-signalr-hub-instance-outside-of-the-hubpipleline

SignalR Initialization - Client Side

Web is full of articles explaining how to setup SignalR. I created the connection and the hub using the following instructions. Methods are appended to the hub; they can be invoked by the Web Service (i.e. "broadCastUpdateResponse" later described) , or by the MainHub from the Server (i.e. "HighlightCell" later described)

JavaScript
//SignalR                
string serverUri = WebServer + "/signalr/hubs";
connection = new HubConnection(serverUri, true);
hub = connection.CreateHubProxy("MainHub");

It is important to include the following scripts before:

ASP.NET
<script src="Scripts/jquery-1.6.4.min.js" type="text/javascript"></script>
<script src="Scripts/jquery.signalR-2.2.0.js"></script>
<script src="signalr/js" type="text/javascript"></script>

Jquery must be included before SignalR, otherwise the following error would appear:

 JavaScript runtime error: jQuery was not found. Please ensure jQuery is referenced before the SignalR client JavaScript file.

Connection is established by calling the Start() method, which is an asynchronous call. Needs then to use the await prefix.  

In order to debug connection state changes, it is convenient also to add the following lines

ASP.NET
connection.StateChanged += (change) =>
                {
                    System.Diagnostics.Debug.WriteLine("hubConnection.StateChanged {0} => {1}", change.OldState, change.NewState);  
                    if (change.NewState == ConnectionState.Connecting)
                    {
                        statusCallBack callBack = new statusCallBack(UpdateStatus);
                        this.Dispatcher.BeginInvoke(callBack, "hubConnection.Connecting");
                    }
                    if (change.NewState == ConnectionState.Connected)
                    {
                        
                        statusCallBack callBack = new statusCallBack(UpdateStatus);
                        this.Dispatcher.BeginInvoke(callBack, "hubConnection.Connected");
                    }
                    if (change.NewState == ConnectionState.Disconnected)
                    {
                        connection.Start().Wait();                        
                        statusCallBack callBack = new statusCallBack(UpdateStatus);
                        this.Dispatcher.BeginInvoke(callBack, "hubConnection.Disconnected.. reconnectin");
                    }
                }; 

SignalR Initialization - Server Side

It is important to get the Microsoft.Owin.Cors package, in order to be able to  Cross-Origin Resource Sharing (CORS) in OWIN middleware. 

Create the following OWINStartup class, as explained in the first article

ASP.NET
public class OWINStartup
   {
       public void Configuration(IAppBuilder app)
       {
           // For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=316888
           // Branch the pipeline here for requests that start with "/signalr"
           app.Map("/signalr", map =>
           {
               // Setup the CORS middleware to run before SignalR.
               // By default this will allow all origins. You can
               // configure the set of origins and/or http verbs by
               // providing a cors options with a different policy.
               map.UseCors(CorsOptions.AllowAll);
               var hubConfiguration = new Microsoft.AspNet.SignalR.HubConfiguration
               {
                   // You can enable JSONP by uncommenting line below.
                   // JSONP requests are insecure but some older browsers (and some
                   // versions of IE) require JSONP to work cross domain
                   // EnableJSONP = true
               };
               // Run the SignalR pipeline. We're not using MapSignalR
               // since this branch already runs under the "/signalr"
               // path.
               map.RunSignalR(hubConfiguration);
           });
       }
   }

Application features explained

User updates the cell - Client Side

User can navigate through the Spreadsheet, and once he finds a cell that he needs to update, he enters the proper value. Since the Spreadsheet cells have a CellPropertyChanged event, once this event is fired, the following code will simply call the Web Service to update the desired data into the Database:

ASP.NET
private async void  Cells_CellPropertyChanged(object sender, CellPropertyChangedEventArgs e)
      {
       int i = e.CellRange.FromIndex.RowIndex;
       int j = e.CellRange.FromIndex.ColumnIndex;
       for (i = e.CellRange.FromIndex.RowIndex; i <= e.CellRange.ToIndex.RowIndex; i++)
             for (j = e.CellRange.FromIndex.ColumnIndex; j <= e.CellRange.ToIndex.ColumnIndex; j++)
             {
               CellRange cr = radSpreadsheet.ActiveWorksheet.Cells[i, j].CellRanges.FirstOrDefault();
               if (cr != null)
               {
                    bool updated = await ViewModel.UpdateValue(cr);
               }
             }
      }

User updates the cell - Server Side

The Web Service accepts the request and calls the ApplicationService which  updates the proper tables.

Once the update is finished, the WebService serializes the Response back to the Client.

ASP.NET
      [HttpPost]
      [Route("Instances/Update/{id}")]
      public async Task<IHttpActionResult> Update(int id, UpdateInstanceRequest request)
        {         
            UpdateInstanceResponse response = await _service.UpdateInstance(request);
          
            string serializedResponse =  Microsoft.AspNet.SignalR.Json.JsonSerializerExtensions.Stringify(new Newtonsoft.Json.JsonSerializer(), response);
            
            // Get SignalR context
            var context = Microsoft.AspNet.SignalR.GlobalHost.ConnectionManager.GetHubContext<MainHub>();
            
            // Invoke the Client method
            context.Clients.All.updateResponse(serializedResponse);
            
            if (response == null)
            {
                return NotFound();
            }
            else
            {
                return Ok(response);
            }
        }

Notice that the SignalR allows to call the methods on the Client, and eventually to initialize the context from the Server.

In this case i broadcast the message to all clients. It in general possible to divide Clients in Groups and then send to message to a subset of them.

Response back - Client Side

The action invoked by the server ("updateResponse") is added to the hub  and implemented on the client side, in order to respond to the Web Service call when an cell has been updated by a user

ASP.NET
hub.On<string>("updateResponse", (output) =>
{
       this.Dispatcher.BeginInvoke(() =>
       {
        //Parse Json
        var getAnonymousType = new { Instance = new { Attribute = new { Id = 0, Type = 0 }, ModifiedTime = new DateTime(), Id = (long)0, Value = "", Locked = false } };
        var instanceUpdated = Newtonsoft.Json.JsonConvert.DeserializeAnonymousType(output, getAnonymousType);

        //Get ViewModel and Cell
        SpreadsheetViewModel svm = (SpreadsheetViewModel)this.DataContext;

        //Updating Cell via SignalR
        svm.updateCellSignalR(instanceUpdated.Instance.Id, instanceUpdated.Instance.Value, instanceUpdated.Instance.ModifiedTime, instanceUpdated.Instance.Locked);

              });
        });

The object is deserialized using JsonConvert, and the result is passed to the ViewModel. 

The method "updateCellSignalR" in the Spreadsheet View Model, is called in order to find if there exists a cell with the Instance Id just updated. In this case, the actual cell is updated and filled with the proper colour ("green" in the example)

ASP.NET
public void updateCellSignalR(long inst_id, object value, DateTime modified_time, bool locked)
{
     Cell refreshCell = CurrentSheet.Cells.Where(w => w.ID == attr_inst_id).FirstOrDefault();
     if (refreshCell != null)
     {
         refreshCell.ModifiedTime = modified_time;
         refreshCell.IsLocked = locked;
         setCellValue(refreshCell);
         setCellPropertyColour(refreshCell, true);
      }
 }

Cells highlight - Client Side - part 1

User is free to move around the Spreadsheet cells using the arrow keys or the mouse click. In this case i added a method which will highlight the cell if this is pointed by the mouse click (the same principle applies to the arrow keys case)

 

The method just retrieves Cell row and column and, using the Hub, it invokes the "SelectedCell" method on the Server, passing the ActiveSheet Name and the position of the cell.

ASP.NET
private async void PresenterMouseDown(object sender, MouseButtonEventArgs e)
 {
         int row = this.radSpreadsheet.ActiveWorksheetEditor.Selection.Cells.CellRanges.FirstOrDefault().FromIndex.RowIndex;
         int column = this.radSpreadsheet.ActiveWorksheetEditor.Selection.Cells.CellRanges.FirstOrDefault().FromIndex.ColumnIndex;

         if(connection.State == ConnectionState.Connected)
             hub.Invoke("SelectedCell", this.radSpreadsheet.Workbook.Sheets.ActiveSheet.Name, row, column );
        }

Cells highlight - Server Side

The MainHub contains the implementation of the "SelectedCell" method invoked by the Client.

This method simply forwards the informations to all the "Other" clients. Where, by "other", i mean all the Clients except the caller.

ASP.NET
public void SelectedCell(string sheetname, int row, int col)
{
    // Call the broadcastMessage method to update clients.
    Clients.Others.HighlightCell( sheetname,  row,  col);
}

Cells highlight - Client Side - part 2

The hub implementation on the Client side contains the implementation of the "HighlightCell" method invoked by the Server.

This method simply checks if the current sheetname is  the same , and fills the proper cell with diagonal stripes

ASP.NET
hub.On<string, int, int>("HighlightCell",
   (sheetname, row, col) =>
     {
         this.Dispatcher.BeginInvoke(() =>
         {
            //Get ViewModel and Cell
            SpreadsheetViewModel svm = (SpreadsheetViewModel)this.DataContext;
            svm.highlightCellSignalR(sheetname, row, col);
        });
     });

<string, int="">

 

ASP.NET
    private Cell _previousHighlight;
        internal void highlightCellSignalR(string sheetname, int row, int col)
        {
            Cell refreshCell;
            if (SpreadSheetWorkbook.ActiveSheet.Name == sheetname)
            {
                refreshCell = CurrentSheet.Cells.Where(w => w.RowIndex == row && w.ColumnIndex == col).FirstOrDefault();
                if (refreshCell != null)
                {                  
                    TelerikExcel.PatternFill diagonalStripePatternFill = new TelerikExcel.PatternFill(TelerikExcel.PatternType.DiagonalStripe, Color.FromArgb(120, 231, 76, 60), Color.FromArgb(120,241, 196, 15));
                    _spreadSheetWorkbook.ActiveWorksheet.Cells[row, col].SetFill(diagonalStripePatternFill);
          
                     if (_previousHighlight != null)
                        setCellPropertyColour(_previousHighlight, false);

                    this._previousHighlight = refreshCell;                    
                }
            }
        }

Notice that it is needed to store the previous cell highlighted, in order to revert its fill back to the original one.

 

Points of Interest

The previous points describe how to setup SignalR on both client and server. It points out to use Cors in order to enable Cross-Origin Resource Sharing (CORS) in OWIN middleware. 

It then explains how to use SignalR to build a real time application, using a Telerik RadSpreadsheet.

The hub is used in a 'classic' way, where the Client uses the call the server, and the server broadcasts the message back to all the clients (like the simple 'chat' room mechanism)

Then, the same hub is used by a WebService to broadcast the message to all Clients connected, in order to share the data updated. It must instantiate the hub context using the Globalhost, but it demonstrates how it is possible to instance the hub outside of the hubpipeline.

In the last part of the video, the Web Service is called by the Database itself, so you can see how the spreadsheet values change as soon as the web service receives the request from the database, and sends back the response to all clients.

Isn't it awesome? :) 

 

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Ireland Ireland
Summary
I am an Informatics Engineer with a University Degree in Informatics Engineering and a PhD in Information Engineering.
I have more than 9 years of working experience ICT (from Development to Team Leader) with and In-depth technical / IT know-how.
Practical competences in development, analysis and project management in terms of coordination and content.

Keywords / Skill Set
C# 7.0, NET Core, ASP NET Core, EF Core; HTML(5), CSS(3), Typescript, JavaScript, jQuery,Telerik Kendo, AngularJS, SignalR; MVC, MVVM; SQL, T-SQL, Sql Server; Azure cloud based services, Amazon Web Services
Relevant Web Applications / Tools:
Visual Studio 2017, SQL Server 2016; TFS, Git

Languages: Italian (mother tongue), English (fluent).

Education:

1998 – 2004, University Of Siena (IT), Tuscany

BSc in Informatics Engineering

2004 – 2007, Superior School of Doctorate, University of Siena (IT), Tuscany

PhD in Information Engineering

Comments and Discussions

 
Questionsource code ? Pin
amigoface23-Nov-19 9:09
amigoface23-Nov-19 9:09 
Hi,

can you post some source code or snippet for this please ?

i have a good experience with signalr but i don't know how to use it to pull / push realtime data
from an excel spreadsheet .

any help is welcome .

thanks again .
QuestionBTW: Where is the source code? Pin
Dewey28-May-16 15:58
Dewey28-May-16 15:58 
AnswerRe: BTW: Where is the source code? Pin
Aless Alessio30-May-16 6:00
Aless Alessio30-May-16 6:00 
GeneralRe: BTW: Where is the source code? Pin
Dewey31-May-16 4:11
Dewey31-May-16 4:11 
QuestionI don't know how useful, but have a 5 Pin
Dewey28-May-16 15:55
Dewey28-May-16 15:55 
AnswerRe: I don't know how useful, but have a 5 Pin
Aless Alessio30-May-16 5:58
Aless Alessio30-May-16 5:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.