Jan 17, 2013

Saving SkyDrive Excel Survey Data To Sql Server Database with ASP.NET MVC

SkyDrive provides the excel survey feature which allows you to create online surveys and analyze results using the free Excel Web App or Excel on your desktop. You can display the survey results by embedding the excel in a web page. ExcelMashup.com allows you show a workbook directly in your website. It also lets you easily sort, filter, and interact with the workbook as in Web Excel with powerful Javascript API. We will create a sample survey and save result data to database using ASP.NET MVC and Entity Framework.

Creating A Survey:

1. Log-in to sky drive. You can create survey using either way:

Create > Excel Survey > Enter name > add questions

OR

Create >Excel Workbook > Enter name > In Home Tab > Survey > New Survey > add questions

After adding questions, click 'Save and View' to test.

skydrive-excel-survey

2. You can share survey using Survey > Share Survey > Done and send the generated link to audience to submit answers without signing in.

DB Structure:

skydrive-excel-survey

We will use SurveyData table to store survey results and RowNum column to store the index of row in excel. You can imagine row index as primary key of excel data. We are always inserting data through survey so it will be unique. Here our objective is to select and save NEW records only each time and for this, RowNum column is used. when any record is inserted, its row index will be inserted in RowNum column. Next time, Only higher row index records are selected from the excel to save.

Entity Framework:

3. In ASP.NET MVC Project, Right click on Models folder, Add New Item, select "ADO.NET Entity Data Model", select the database, enter proper name and other information, finish.

4. First, we have to get the existing max RowNum value(will use in view), so in default HomeController, we get and set value to ViewBag variable.


	public ActionResult Index()
        {
            using (SurveyDBEntities objContext = new SurveyDBEntities())
            {
                ViewBag.SavedRowIndex = objContext.SurveyDatas.Select(x => (int?)x.RowNum).Max() ?? 0;       
            }
            return View();
        }     

To save data:


		[HttpPost]
        public JsonResult SaveData(string[][] data) {
            
                if (data != null && data.Length > 0)
                {
                    using (SurveyDBEntities objContext = new SurveyDBEntities())
                    {
                        int rowNumColIndex = data[0].Length - 1;
                        for (int row = 0; row < data.Length; row++)
                        {
                            SurveyData obj = new SurveyData()
                            {
                                Name = data[row][0],
                                Email = data[row][1],
                                CommentType = data[row][2],
                                Comment = data[row][3],
                                RowNum = Convert.ToInt32(data[row][rowNumColIndex])
                            };
                            objContext.SurveyDatas.AddObject(obj);
                        }
                        objContext.SaveChanges();
                    }
                }
                else
                {                   
                    return Json("No data found" );
                }
             
            return Json("Saved Successfully");
        }

Displaying Excel Survey Results:

Open excel file in SkyDrive > File > Share > Embed

click on javascript > Copy

Open View in asp.net project and paste it where you want to display.



<div id="loadingdiv">
			<h3>Please Wait...</h3>
</div>	
<div id="myExcelDiv" style="width: 650px; height:500px;"></div>
<input onclick="JavaScript:saveValues();" type="button" value="Save" />
<script type="text/javascript" src="http://r.office.microsoft.com/r/rlidExcelWLJS?v=1&kip=1"></script>
<script type="text/javascript">
    // Use this file token to reference Sample.xlsx in Excel's APIs
    var fileToken = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
    var ewa = null;
    var ewaCht = null;
    var savedRowNum = @ViewBag.SavedRowIndex;

    // run the Excel load handler on page load
    if (window.attachEvent) {
        window.attachEvent("onload", loadEwaOnPageLoad);
    } else {
        window.addEventListener("DOMContentLoaded", loadEwaOnPageLoad, false);
    }

    function loadEwaOnPageLoad() {
        var props = {

            uiOptions: {
                showDownloadButton: false,
                showGridlines: false,
                showRowColumnHeaders: false,
                showParametersTaskPane: false
            },
            interactivityOptions: {
                allowTypingAndFormulaEntry: true,
                allowParameterModification: true,
                allowSorting: false,
                allowFiltering: false,
                allowPivotTableInteractivity: false
            }
        };
        // Embed workbook using loadEwaAsync
        document.getElementById("loadingdiv").innerHTML = "<h3>Loading...</h3>";
        Ewa.EwaControl.loadEwaAsync(fileToken, "myExcelDiv", props, OnDataLoaded);
    }


    function OnDataLoaded(asyncResult) {
        if (asyncResult.getSucceeded()) {
            ewaCht = asyncResult.getEwaControl();           
            document.getElementById("loadingdiv").style.display = "none";
        }
    } 
</script>

In above code, we added Save button and set savedRowNum variable from viewbag parameter which has the existing max RowNum value.

skydrive-excel-survey

To save data:


 function saveValues() {
        ewaCht.getActiveWorkbook().getRangeA1Async('A1:E1000', getRangeForControls, null);
    }

    function getRangeForControls(asyncResult) {
        var range = asyncResult.getReturnValue();
        if (range) {
            range.getValuesAsync(0, saveRangeValues, null);
        }
    }

    function saveRangeValues(asyncResult) {
        var range = asyncResult.getReturnValue();      

        var row, col, str = [], newData = [],lastRowNum = savedRowNum;
        for (row = savedRowNum+1; row < range.length; row++) {
            //check if email field exists
            if (range[row][1]) {
                range[row].push(row);
                newData.push(range[row]);
                lastRowNum =row;
            }
        }

        $.ajax({
            url: '@Url.Action("SaveData", "Home")',
            type: 'POST',
            contentType: 'application/json',
            data: JSON.stringify({ data: newData })
        }).done(function(result){   
            //Set savedRowNum for next processing
            savedRowNum = lastRowNum;            
            alert(result);
        }).fail(function(jqXHR, textStatus) {
            alert( "Request failed: " + textStatus);
        });
    }

In above code, we create a 2d array (newData) which contains the new records only with row index and it is posted to SaveData method which saves the data in database using entity framework.

skydrive-excel-survey

Hope, You enjoy it.