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 browser. 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)
.Height(350)
.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='" + awef.encode(val) + "'/>";
}

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

$.when(xhr).done(function () {
var row = $(api.renderRow({ Id: 0 }));
g.find('.awe-row:first').before(row); // insert the row
row.find('input:first').trigger('focus');
});
}

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

// using awe.ajx instead of $.ajax to get the antiforgery token added automatically + error handling
awe.ajx('@(Url.Action("Save"))', awe.srl({ id: model.Id, name: prop, value: model[prop] }))
.done(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');
});
});

$('#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: .5em .7em;
}
</style>
Demos/Grid/GridSpreadsheetDemoController.cs
public IActionResult GridGetItems(GridParams g)
{
var query = Db.Spreadsheets.OrderByDescending(o => o.Id).AsQueryable();

var gmb = new GridModelBuilder<Spreadsheet>(query, g);

return Json(gmb.Build());
}

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

public IActionResult Save(int id, string name, string value)
{
Spreadsheet row;

// create
if (id == 0)
{
row = new Spreadsheet();
Db.Add(row);
}
else
{
row = Db.Find<Spreadsheet>(id);
}

typeof(Spreadsheet).GetProperty(name)?.SetValue(row, value, null);



return Json(row);
}


Add columns and bound to array

GridSpreadsheetDemo/Index.cshtml
<div class="bar">
<button type="button" class="awe-btn" id="btnArrAddRow">add row</button>
<button type="button" class="awe-btn" onclick="awe.oc('addCol')">add column</button>
</div>
@{
var gridId = "SpreadArrGrid";
}
@(Html.Awe().Grid(gridId)
.Mod(o => o.ColumnsSelector())
.Url(Url.Action("MultiColGrid"))
.CssClass("spreadsh")
.SendColumns(true))

@Html.Awe().InitCall("addCol").Url(Url.Action("AddColumn")).Success("aweUtils.refreshGrid('" + gridId + "')")

<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='" + awef.encode(val) + "'/>";
};
}

$(function () {
var g = $('#@(gridId)');

$('#btnArrAddRow').click(function () {
var row = $(g.data('api').renderRow({ Id: 0, Values: [] }));
g.find('.awe-row:last').after(row); // insert the row
g.data('o').lrso = 1; // flag last result not matching the rendered rows
row.find('input:first').trigger('focus');
});

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

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

$('#log').prepend(val + ' saved \n');
});
});
});
</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 IActionResult 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 IActionResult 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 IActionResult MultiColGrid(GridParams g)
{
Check.NotNull(g.Columns, "g.Columns");

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


Comments