Category / Section
How to load and save a Sql table in JavaScript Spreadsheet using cellSave event?
3 mins read
Description
This Knowledge Base explains how to save and retrieve the JavaScript Spreadsheet data from SQL table using the cellSave event.
Solution
You can use the getData method to get the spreadsheet data and send this data to the server-side and update it into the SQL database. In the below sample, you can send the field name, key, and value from the client to the server by using the cellSave event. On the server-side, you can update the field name and value in the database.
MVC Solution
[CSHTML]
@Html.EJS().Spreadsheet("spreadsheet").OpenUrl("Open").SaveUrl("Save").Created("createdHandler").CellSave("cellSave").Sheets(sheet =>
{
sheet.Name("Employee Details").Ranges(ranges =>
{
ranges.DataSource(dataManger =>
{
// To load the data from database
dataManger.Url("https://localhost:44370/Home/LoadFromTable").CrossDomain(true);
}).Add();
}).Columns(column =>
{
column.Width(100).Add();
column.Width(130).Add();
column.Width(100).Add();
column.Width(120).Add();
column.Width(150).Add();
}).Add();
}).Render()
<script>
// Triggers once the control is loaded
function createdHandler() {
// Applies cell and number formatting to specified range of the active sheet
this.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
this.numberFormat('$#,##0.00', 'F2:F31');
}
// To save the spreadsheet data in cell save event
function cellSave(args) {
var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
var indices = ssObj.getAddressInfo(args.address).indices;
var fieldName;
var key;
ssObj.getData(ej.spreadsheet.getRangeAddress([0, indices[1]])).then((cells) => {
cells.forEach((cell) => {
fieldName = cell.value;
ssObj.getData(ej.spreadsheet.getRangeAddress([indices[0], 0])).then((cells) => {
cells.forEach((cell) => {
key = cell.value;
var formData = new FormData();
formData.append('fieldName', fieldName);
formData.append('key', key);
formData.append('value', args.value);
fetch('https://localhost:44370/Home/SaveToTable', { // To send data from client to server side
method: 'POST',
body: formData
}).then((response) => console.log(response));
});
});
});
});
}
</script>
[Controller]
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
//Retrieve the Spreadsheet data from database
public object LoadFromTable()
{
List<EmployeeData> datas = new List<EmployeeData>();
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Employees]", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
while (sqlDR.Read())
{
EmployeeData data = new EmployeeData();
data.EmployeeID = (int)sqlDR.GetValue(0);
data.LastName = sqlDR.GetValue(1).ToString();
data.FirstName = sqlDR.GetValue(2).ToString();
data.Title = sqlDR.GetValue(3).ToString();
data.TitleOfCourtesy = sqlDR.GetValue(4).ToString();
datas.Add(data);
}
sqlCon.Close();
return new { Result = datas, Count = datas.Count };
}
// saved the Spreadsheet data to database
public string SaveToTable(string value, string fieldName, string key)
{
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlCommand sqlComm = new SqlCommand("UPDATE [dbo].[Employees] SET " + fieldName + "=@field WHERE EmployeeID=" + key, sqlCon);
sqlComm.CommandTimeout = 0;
sqlComm.Parameters.AddWithValue("@field", value);
sqlComm.ExecuteNonQuery();
sqlCon.Close();
return "Saved succefully";
}
public class EmployeeData
{
public int EmployeeID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public string Title { get; set; }
public string TitleOfCourtesy { get; set; }
}
Core Solution
[CSHTML]
<ejs-spreadsheet id="spreadsheet" created="createdHandler" cellSave="cellSave">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Employee Details">
<e-spreadsheet-ranges>
<e-spreadsheet-range>
// To load the data from database
<e-data-manager url="https://localhost:44355/Home/LoadFromTable" crossdomain=true></e-data-manager>
</e-spreadsheet-range>
</e-spreadsheet-ranges>
<e-spreadsheet-columns>
<e-spreadsheet-column width=100></e-spreadsheet-column>
<e-spreadsheet-column width=130></e-spreadsheet-column>
<e-spreadsheet-column width=100></e-spreadsheet-column>
<e-spreadsheet-column width=120></e-spreadsheet-column>
<e-spreadsheet-column width=150></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
function createdHandler() {
var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
ssObj.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:G1');
}
// To save the spreadsheet data in cell save event
function cellSave(args) {
var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
var indices = ssObj.getAddressInfo(args.address).indices;
var fieldName;
var key;
ssObj.getData(ej.spreadsheet.getRangeAddress([0, indices[1]])).then((cells) => {
cells.forEach((cell) => {
fieldName = cell.value;
ssObj.getData(ej.spreadsheet.getRangeAddress([indices[0], 0])).then((cells) => {
cells.forEach((cell) => {
key = cell.value;
var formData = new FormData();
formData.append('fieldName', fieldName);
formData.append('key', key);
formData.append('value', args.value);
fetch('https://localhost:44355/Home/SaveToTable', { // To send data from client to server side
method: 'POST',
body: formData
}).then((response) => console.log(response));
});
});
});
});
}
</script>
[Controller]
public class HomeController : Controller
{
private readonly IConfiguration _configuration;
private string connectionString;
public static IServiceProvider ServiceProvider;
public HomeController(IConfiguration configuration, IWebHostEnvironment env)
{
_configuration = configuration;
connectionString = _configuration.GetConnectionString("DefaultConnection");
connectionString = connectionString.Replace("%CONTENTROOTPATH%", env.ContentRootPath);
}
//Retrieve the Spreadsheet data from database
public object LoadFromTable()
{
List<EmployeeData> datas = new List<EmployeeData>();
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Employees]", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
while (sqlDR.Read())
{
EmployeeData data = new EmployeeData();
data.EmployeeID = (int)sqlDR.GetValue(0);
data.LastName = sqlDR.GetValue(1).ToString();
data.FirstName = sqlDR.GetValue(2).ToString();
data.Title = sqlDR.GetValue(3).ToString();
data.TitleOfCourtesy = sqlDR.GetValue(4).ToString();
datas.Add(data);
}
sqlCon.Close();
return new { Result= datas, Count= datas.Count };
}
// saved the Spreadsheet data to database
public string SaveToTable(string value, string fieldName, string key)
{
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlCommand sqlComm = new SqlCommand("UPDATE [dbo].[Employees] SET " + fieldName + "=@field WHERE EmployeeID=" + key, sqlCon);
sqlComm.CommandTimeout = 0;
sqlComm.Parameters.AddWithValue("@field", value);
sqlComm.ExecuteNonQuery();
sqlCon.Close();
return "Saved succefully";
}
}
public class EmployeeData
{
public int EmployeeID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public string Title { get; set; }
public string TitleOfCourtesy { get; set; }
}
Screenshot:
Conclusion
I hope you enjoyed learning about how to load and save a Sql table in JavaScript Spreadsheet using cellSave event.
You can refer to our JavaScript Spreadsheet feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our JavaScript Spreadsheet example to understand how to create and manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
