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.
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:
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.
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.
Hope, You enjoy it.