Prerequisites:
1. Check previous tutorial, part 2
2. Check documentation for Ag-Grid, I will link it under the right point
3. App is online: https://ag-grid3.zoltanhalasz.net/
4. Repository for this tutorial: https://drive.google.com/open?id=1WAbKlYsg3lpbfwE-NfYOmHFFtYPOck_f
5. Intermediate Javascript and Asp.Net Core Razor Pages
As a continuation of my study for this grid system, I checked the following options:
1. Row selection, and removal of rows using Ajax
https://www.ag-grid.com/javascript-grid-selection/
The goal here is to select the rows and remove them from the list and the database table.
The selection is described in above link and implemented in the javascript part of the index page.
function deleteSelectedRows() {
var selectedNodes = gridOptions.api.getSelectedNodes()
var selectedData = selectedNodes.map(function (node) { return node.data })
if (selectedData) {
postData('./api/InvoiceModels', selectedData)
.then((data) => {
console.log("response", data); // JSON data parsed by `response.json()` call
});
}
In order to do this, I created an api controller, for the InvoiceModel class and placed it in the Controllers Folder. Then rewrote the post routing, which takes a list of invoicemodel as an input, and then deletes them from the table:
// POST: api/InvoiceModels
[HttpPost]
public async Task<ActionResult<List<InvoiceModel>>> PostInvoiceModel(List<InvoiceModel> invoiceModels)
{
if (invoiceModels==null) return NoContent();
foreach (var model in invoiceModels)
{
var myinvoiceModel = await _context.InvoiceTable.FindAsync(model.ID);
if (myinvoiceModel != null)
{
_context.InvoiceTable.Remove(myinvoiceModel);
}
}
await _context.SaveChangesAsync();
return await _context.InvoiceTable.ToListAsync();
}
There is a Fetch Command in the index page which communicates with this Api route :
async function postData(url = '', data = {}) {
console.log(JSON.stringify(data), url);
await fetch(url, {
method: 'POST', // *GET, POST, PUT, DELETE, etc.
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify(data) // body data type must match "Content-Type" header
}).then(function (response) {
console.log(response.json());
}).then(function (response) {
agGrid.simpleHttpRequest({ url: './Index?handler=ArrayData' }).then(function (data) {
gridOptions.api.setRowData(data);
});
}).catch(() => console.log("err"));
}
2. Filtering Options
please study: https://www.ag-grid.com/javascript-grid-filter-provided-simple/
a. with filter set to false the filter is invisible for that column
b. for numeric filters, we can use the below (actually this will be with a range)
{
headerName: "Amount", field: "Amount",
filter: 'agNumberColumnFilter', filterParams: {
filterOptions: ['inRange']
}
},
c. for the category, a text filter will be used:
{
headerName: "CostCategory", field: "CostCategory", checkboxSelection: true,
filter: 'agTextColumnFilter', filterParams: {
defaultOption: 'startsWith',
resetButton: true,
applyButton: true,
debounceMs: 200
}
},
3. Data import
a. I saved the import.xlsx file under wwwroot/uploads, having the following structure
(below data is tab delimited)
ID InvoiceNumber Amount CostCategory Period
1 1 500.00 Utilities 2019_11
2 2 121.69 Telephone 2019_12
3 3 342.61 Services 2019_11
4 4 733.21 Consultancy 2019_11
5 5 107.79 Raw materials 2019_10
6 6 161.44 Raw materials 2019_11
7 7 334.48 Raw materials 2019_11
8 8 504.63 Services 2019_11
9 8 869.44 Services 2019_11
10 9 401.57 Services 2019_11
b. I checked the upload documentation and took their example https://www.ag-grid.com/example-excel-import/
c. please see the implementation of point b, it is almost copy-paste, just adding my columns instead:
After pushing the import button, the grid is filled in with the values from import.xlsx placed in wwwroot/uploads.
result will be:
The contents of the import page:
@page
@model DataTables.ImportModel
@{
ViewData["Title"] = "Import";
Layout = "~/Pages/Shared/_Layout.cshtml";
}
<h1>Import from pre-uploaded excel file (import.xlsx)</h1>
<script src="https://unpkg.com/xlsx-style@0.8.13/dist/xlsx.full.min.js"></script>
<script src="~/js/ag-grid-community.min.js"></script>
<div class="container">
<div class="row">
<button onclick="importExcel()" class="btn-outline-info">Import Excel</button>
</div>
<div class="row">
<div id="myGrid" style="height: 500px;width:650px;" class="ag-theme-balham-dark"></div>
</div>
</div>
<script>
// XMLHttpRequest in promise format
function makeRequest(method, url, success, error) {
var httpRequest = new XMLHttpRequest();
httpRequest.open("GET", url, true);
httpRequest.responseType = "arraybuffer";
httpRequest.open(method, url);
httpRequest.onload = function () {
success(httpRequest.response);
};
httpRequest.onerror = function () {
error(httpRequest.response);
};
httpRequest.send();
}
// read the raw data and convert it to a XLSX workbook
function convertDataToWorkbook(data) {
/* convert data to binary string */
console.log(data);
var data = new Uint8Array(data);
var arr = new Array();
for (var i = 0; i !== data.length; ++i) {
arr[i] = String.fromCharCode(data[i]);
}
var bstr = arr.join("");
return XLSX.read(bstr, { type: "binary" });
}
// pull out the values we're after, converting it into an array of rowData
function populateGrid(workbook) {
// our data is in the first sheet
//console.log(workbook);
var firstSheetName = workbook.SheetNames[0];
var worksheet = workbook.Sheets[firstSheetName];
// we expect the following columns to be present
var columns = {
'A': 'ID',
'B': 'InvoiceNumber',
'C': 'Amount',
'D': 'CostCategory',
'E': 'Period'
};
var rowData = [];
// start at the 2nd row - the first row are the headers
var rowIndex = 2;
// iterate over the worksheet pulling out the columns we're expecting
while (worksheet['A' + rowIndex]) {
var row = {};
Object.keys(columns).forEach(function (column) {
row[columns[column]] = worksheet[column + rowIndex].w;
});
rowData.push(row);
console.log(row);
rowIndex++;
}
// finally, set the imported rowData into the grid
gridOptions.api.setRowData(rowData);
}
function importExcel() {
makeRequest('GET',
'/uploads/import.xlsx',
// success
function (data) {
var workbook = convertDataToWorkbook(data);
populateGrid(workbook);
},
// error
function (error) {
throw error;
}
);
}
// specify the columns
var columnDefs = [
{ headerName: "ID", field: "ID", width: 50 },
{ headerName: "InvoiceNumber", field: "InvoiceNumber", width: 80 },
{ headerName: "Amount", field: "Amount", width: 100 },
{ headerName: "CostCategory", field: "CostCategory" },
{ headerName: "Period", field: "Period" },
];
// no row data to begin with
var rowData = [];
// let the grid know which columns and what data to use
var gridOptions = {
columnDefs: columnDefs,
rowData: rowData,
onGridReady: function () {
gridOptions.api.sizeColumnsToFit();
}
};
// wait for the document to be loaded, otherwise
// ag-Grid will not find the div in the document.
document.addEventListener("DOMContentLoaded", function () {
console.log('before create grid');
// lookup the container we want the Grid to use
var eGridDiv = document.querySelector('#myGrid');
console.log(eGridDiv);
// create the grid passing in the div to use together with the columns & data we want to use
new agGrid.Grid(eGridDiv, gridOptions);
});
</script>
