Grid Spreadsheet Demo

The grid is used as an excel/google docs spreadsheet. ClientFormatFunc is set for each column to render a textbox for each cell, and some css to hide the borders of the textbox and make the cell look like one. Once the js change event is triggered on the textbox an ajax request is sent to the server to save the value. The Id is not shown and is not editable, but it's used to identify the records when editing them.

Try and edit some cells, after switch pages/refresh broswer. Note you have to exit the cell or hit enter in order for the change to be persisted.

Simple spreadsheet grid


GridSpreadsheetDemo/Index.cshtml
@(Html.Awe().Grid("Spreadsheet")
.Groupable(false)
.Sortable(false)
.Url(Url.Action("GridGetItems"))
.CssClass("spreadsh")
.Columns(
new Column { Bind = "Name", ClientFormatFunc = "txt" },
new Column { Bind = "Location", ClientFormatFunc = "txt" },
new Column { Bind = "Meal", ClientFormatFunc = "txt" }))
<textarea id="log" class="log"></textarea>
<script>
function txt(model, name) {
var val = model[name];
if (!val) val = ""; //replace null with ""
return "<input type='text' name='" + name + "' value='" + utils.escapeChars(val) + "'/>";
}

function add() {
var row = $('#Spreadsheet').data('api').renderRow({ Id: 0 });
var xhr = $('#Spreadsheet').data('api').load({ oparams: { page: 1 } }); // first go to page 1

$.when(xhr).done(function () {
$('#Spreadsheet .awe-row:first').before(row); // insert the row
row.find('input:first').focus();
});
}

$(function () {
$('#Spreadsheet').on('change', 'input', function () {
var model = $(this).closest('.awe-row').data('model');
var prop = $(this).attr("name");
model[prop] = $(this).val();

$.post('@(Url.Action("Save"))', { id: model.Id, name: prop, value: model[prop] }, function (res) {
if (model.Id == 0) {
model.Id = res.Id;
$('#log').prepend('new record added id = ' + res.Id + ' \n');
}
$('#log').prepend(model[prop] + ' saved \n');
}).fail(function (xhr) { awem.notif(xhr.responseText, 0, 'err'); });
});

$('#addrow').on('click', add);
});
</script>

<style>
.spreadsh .awe-row td
{
background: white;
padding: 0;
text-align: center;
}

.spreadsh input[type="text"]
{
width: 100%;
margin: auto;
border: none;
padding: 5px;
height: 27px;
}
</style>
Demos/Grid/GridSpreadsheetDemoController.cs
public ActionResult GridGetItems(GridParams g)
{
return Json(new GridModelBuilder<Spreadsheet>(Db.Spreadsheets.OrderByDescending(o => o.Id).AsQueryable(), g).Build());
}

public ActionResult Add()
{
return Json(new Spreadsheet());
}

public ActionResult Save(int id, string name, string value)
{
var row = id == 0 ? Db.Insert(new Spreadsheet()) : Db.Get<Spreadsheet>(id);

//this is an inmemory object with a real Db you would use UPDATE Spreadsheets SET {name}={val} where id={id}
typeof(Spreadsheet).GetProperty(name).SetValue(row, value, null);

return Json(row);
}


Add columns and bound to array

GridSpreadsheetDemo/Index.cshtml
<div class="bar">
@Html.Awe().Button().Text("add row").OnClick("add2()")
@Html.Awe().Button().Text("add column").OnClick("awe.oc('addCol')")
</div>
@Html.Awe().Grid("SpreadArrGrid").Url(Url.Action("MultiColGrid")).CssClass("spreadsh").Mod(o => o.ColumnsSelector()).SendColumns(true)
@Html.Awe().InitCall("addCol").Url(Url.Action("AddColumn")).Success("utils.refreshGrid('SpreadArrGrid')")

<script>
function txtVal(i) {
return function (model, name) {
var val = model.Values[i];
if (!val) val = ""; //replace null with ""
return "<input type='text' name='" + name + "' value='" + utils.escapeChars(val) + "'/>";
};
}

$(function () {
$('#SpreadArrGrid .awe-content').on('change', 'input', function () {
var model = $(this).closest('.awe-row').data('model');
var col = parseInt($(this).attr("name"));
var val = $(this).val();
model.Values[col] = val;

$.post('@(Url.Action("SaveArr"))', { id: model.Id, col: col, value: val }, function (res) {
if (!model.Values[0]) {
model.Id = res.Id;
$('#log').prepend('new record added id = ' + res.Id + ' \n');
}

$('#log').prepend(val + ' saved \n');
}).fail(function (xhr) { awem.notif(xhr.responseText, 0, 'err'); });
});
});

function add2() {
var row = $('#SpreadArrGrid').data('api').renderRow({ Id: 0, Values:[] });
$('#SpreadArrGrid .awe-row:last').after(row); // insert the row
$('#SpreadArrGrid').data('o').lrso = 1;
row.find('input:first').focus();
}
</script>

Demos/Grid/GridSpreadsheetDemoController.cs
private static IList<List<string>> data = new List<List<string>>{
new List<string> { "Id", "Name", "Meal" },
new List<string> { "1", "Vincenzo", "Pizza" },
new List<string> { "2", "Ella", "French toast" },
new List<string> { "3", "Zazzles", "Pizza" },
new List<string> { "4", "Evan", "Banana" }
};

private static int nextid = 5;

public ActionResult AddColumn()
{
var header = data.First();
var name = "col" + header.Count;
header.Add(name);

for (var i = 1; i < data.Count; i++)
{
data[i].Add(string.Empty);
}

return Json(new { });
}

public ActionResult SaveArr(int id, int col, string value)
{
if (id == 0)
{
id = nextid;
nextid++;

var newRow = new List<string> { id.ToString() };
for (var i = 1; i < data.Last().Count; i++)
{
newRow.Add(i == col ? value : string.Empty);
}

data.Add(newRow);
return Json(new { Id = id });
}

var exRow = data.SingleOrDefault(o => o[0] == id.ToString());

if (exRow == null) throw new Exception("edited row doesn't exist anymore");

exRow[col] = value;
return Json(new { Id = id });
}

public ActionResult MultiColGrid(GridParams g)
{
g.Paging = false;

var items = new List<GridArrayRow>();
for (var i = 1; i < data.Count; i++)
{
items.Add(new GridArrayRow { Id = data[i][0], Values = data[i].ToArray() });
}

// reinit columns at start or when new column has been added
if (g.Columns.Length == 0 || g.Columns.Length != data[0].Count - 1)
{
var columns = new List<Column>();

for (var i = 1; i < data[0].Count; i++)
{
columns.Add(new Column { Header = data[0][i], Bind = i.ToString(), ClientFormatFunc = "txtVal(" + i + ")", Resizable = true });
}

g.Columns = columns.ToArray();
}

var model = new GridModelBuilder<GridArrayRow>(items.AsQueryable(), g).Build();
return Json(model);
}


Autocomplete Cells Spreadsheet Demo