Kendo Spreadsheet Overview:
The Kendo Spreadsheet allows you to edit and see data by using cell formatting options, styles, and themes.
The Kendo Spreadsheet supports:
1. Adding of multiple sheets.
2. Insert, update and delete rows and columns.
3. Alignment of cells vertically and horizontally.
4. Merging of cells.
5. Formatting of data, including the provision of the number, currency, accounting, date, time, percentage, fraction, scientific, text, special, and custom options.
6. Formatting of cells, including the provision of fonts and font sizes, application of borders, rendering of content in bold, italics and underline styles.
7. Applying formulas in categories such as logical, text, date, and time.
8. Applying of background colors.
9. Frozen rows.
10. Wrapping of text.
Prerequisite:
Below are the points required to before implementing Kendo Spreadsheet with .Net Web Forms.
1. ASP.NET C#
2. AJAX
3. CSS
4. Kendo UI
5. Angular JS
6. JS
Follow the steps for the implementation:
1. Create an Employee table with fields Id, Name, Designation and Salary in database.
CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](max) NOT NULL,
[Designation] [nvarchar](max) NOT NULL,
[Salary] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2. Create a web application and give name as Spreadsheet.aspx.
3. Create a web form named Spreadsheet.aspx.
4. Include css and js in Spreadsheet.aspx file and add angular ng-app to body tag and add div for adding angular ng-controller.
<link rel="stylesheet" href="styles/kendo.common.min.css" />
<link rel="stylesheet" href="styles/kendo.default.min.css" />
<link rel="stylesheet" href="styles/kendo.default.mobile.min.css" />
<script src="js/jquery.min.js"></script>
<script src="js/jszip.min.js"></script>
<script src="js/kendo.all.min.js"></script>
<body ng-app="SpreadSheet">
<form id="form1" runat="server">
<div ng-controller="SpreadSheetController">
</div>
</form>
</body>
5. Add div to render Spreadsheet and two buttons, Save and Cancel to save and cancel changes under ng-controller div.
<div class="spreadSheetDiv" style="display: none;">
<button class="k-button" id="save">Save</button>
<button class="k-button" id="cancel">Cancel</button>
<div id="spreadsheet" style="width: 100%;"></div>
</div>
6. Add dropdowns which contain options as fields for sorting purpose under ng-controller div.
<div>
<select ng-options="item for item in columns" id="firstSortBy"
ng-model="firstOrderBy">
<option value="">First SortBy</option>
</select>
<select ng-options="item for item in columns" id="secondSortBy"
ng-model="secondOrderBy">
<option value="">Second SortBy</option>
</select>
<select ng-options="item for item in columns" id="thirdSortBy"
ng-model="thirdOrderBy">
<option value="">Third SortBy</option>
</select>
<button id="generateSpreadSheetBtn">Generate Spreadsheet</button>
</div>
7. Add one class file. In that file add one class for Spreadsheet flags and add another for an Employee table.
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public string Designation { get; set; }
public string Salary { get; set; }
}
public class SpreadSheetClass
{
public List Updated { get; set; }
public List Created { get; set; }
public List Destroyed { get; set; }
}
8. Add one Spreadsheet.js file and include that file in Spreadsheet.aspx page.
9. Add a web method for getting list of employees and call that method from Spreadsheet.js file by ajax call and file the data source for binding the Spreadsheet.
Web Method in C#:
[WebMethod]
public static string GetEmployees()
{
SqlConnection con = new SqlConnection(
ConfigurationManager
.ConnectionStrings["myConnectionString"]
.ConnectionString
);
SqlCommand cmd = new SqlCommand("select * from Employee", con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
con.Close();
return JsonConvert.SerializeObject(dt);
}
Data Source in js:
var dataSource = new kendo.data.DataSource({
transport: {
read: onRead,
submit: onSubmit
},
batch: true,
change: function () {
$("#cancel, #save").toggleClass("k-state-disabled",!this.hasChanges());
},
schema: {
model: {
id: "Id",
fields: {
Id: { type: "number" },
Name: { type: "string" },
Designation: { type: "string" },
Salary: { type: "string" }
}
}
}
});
10. Use onRead() method for binding data source and applying sorting.
function onRead(options) {
$.ajax({
url: "/Spreadsheet.aspx/GetEmployees",
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (result) {
var orderByExpression = [];
if ($scope.firstOrderBy &&
$scope.firstOrderBy != "First SortBy") {
orderByExpression.push($scope.firstOrderBy);
}
if ($scope.secondOrderBy &&
$scope.secondOrderBy != "Second SortBy") {
orderByExpression.push($scope.secondOrderBy);
}
if ($scope.thirdOrderBy &&
$scope.thirdOrderBy != "Third SortBy") {
orderByExpression.push($scope.thirdOrderBy);
}
var data = JSON.parse(result.d);
data = $filter('orderBy')(data,
orderByExpression,
false);
options.success(data);
},
error: function (result) {
options.error(result);
}
});
}
11. Use onSubmit() method for saving data from Spreadsheet.
function onSubmit(e) {
$.ajax({
url: "/Spreadsheet.aspx/SaveEmployees",
data: "{'model':" + JSON.stringify(e.data) + "}",
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (result) {
e.success(result.Updated, "update");
e.success(result.Created, "create");
e.success(result.Destroyed, "destroy");
},
error: function (xhr, httpStatusMessage, customErrorMessage) {
alert(xhr.responseText);
}
});
}
12. Add below code to set the data of Spreadsheet.
$("#spreadsheet").kendoSpreadsheet({
excel: {
// Required to enable saving files in older browsers
proxyURL: "https://demos.telerik.com/kendo-ui/service/export"
},
pdf: {
proxyURL: "https://demos.telerik.com/kendo-ui/service/export"
},
sheets: [
{
name: "Employee Details",
dataSource: dataSource,
rows: ]{
height: 40,
cells: [
{
bold: "true",
background: "green",
textAlign: "center",
color: "white",
}, {
bold: "true",
background: "green",
textAlign: "center",
color: "white"
}, {
bold: "true",
background: "green",
textAlign: "center",
color: "white"
}, {
bold: "true",
background: "green",
textAlign: "center",
color: "white"
}]
}[,
columns: ]
{ width: 100 },
{ width: 200 },
{ width: 200 },
{ width: 145 },
[
}]
});
13. Code to hide a column
var sheet = $("#spreadsheet").data("kendoSpreadsheet");
sheet = sheet.activeSheet();
sheet.hideColumn(0);
14. Add below code to on click of save and cancel button.
$("#save").click(function (e) {
e.preventDefault();
if (!$(this).hasClass("k-state-disabled")) {
dataSource.sync();
}
});
$("#cancel").click(function (e) {
e.preventDefault();
if (!$(this).hasClass("k-state-disabled")) {
dataSource.cancelChanges();
}
});
15. Add below code to save changes made in spreadsheet to database.
[WebMethod]
public static void SaveEmployees(SpreadSheetClass model)
{
SqlConnection con = new SqlConnection(ConfigurationManager
.ConnectionStrings["myConnectionString"]
.ConnectionString);
con.Open();
if (model.Created.Count() > 0)
{
for (int i = 0; i < model.Created.Count(); i++)
{
SqlCommand cmd = new SqlCommand("insert into Employee values('"
+ model.Created[i].Name + "','"
+ model.Created[i].Designation + "','"
+ model.Created[i].Salary + "')", con);
cmd.ExecuteNonQuery();
}
}
if (model.Updated.Count() > 0)
{
for (int i = 0; i < model.Updated.Count(); i++)
{
SqlCommand cmd = new SqlCommand("update Employee set Name='" +
model.Updated[i].Name + "',Designation='" +
model.Updated[i].Designation + "',Salary='" +
model.Updated[i].Salary +
"' where Id=" + model.Updated[i].Id, con);
cmd.ExecuteNonQuery();
}
}
if (model.Destroyed.Count() > 0)
{
for (int i = 0; i < model.Destroyed.Count(); i++)
{
SqlCommand cmd = new SqlCommand("delete Employee where Id=" + model.Destroyed[i].Id, con);
cmd.ExecuteNonQuery();
}
}
con.Close();
}
Output:.
1. Select values from sort by dropdown and click on generate spreadsheet button.
2. Insert data in new row and click on save changes button to add data.
3. Update any row/rows and click on save changes button to update data.
4. After making changes, if you want to cancel changes then click on cancel button to cancel changes.
