We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
| OrderID | Cust_ID | City |
| 12345 | 1001 | Solan |
| 12346 | 1002 | Ghana |
On page load i have to load this table data as it is in Spreadsheet and after making changes to table save this table back to sql server as :
| OrderID | Cust_ID | City |
| 12345 | 1001 | Miami |
| 12346 | 1002 | FIZI and adding some chages or adding more data to this table |
|
<input type="button" id="save" value="Save" onclick="saveData()" />
<ej:DataManager ID="dataManager1" runat="server" URL="SpreadsheetFeatures.aspx/Data" BatchURL="SpreadsheetFeatures.aspx/Save" Adaptor="WebMethodAdaptor" />
<ej:Spreadsheet ID="Spreadsheet1" runat='server' ClientIDMode="Static">
<ClientSideEvents LoadComplete="loadComplete" />
<ScrollSettings Width="100%" Height="540" IsResponsive="true" />
<Sheets>
<ej:Sheet DataManagerID="dataManager1" Query="ej.Query().take(10).select(['OrderID', 'Quantity', 'Price', 'Amount', 'Discount'])" PrimaryKey="OrderID"></ej:Sheet>
</Sheets>
</ej:Spreadsheet>
<script type="text/javascript">
function saveData() {
var xlObj = $('#Spreadsheet1').data("ejSpreadsheet"), changes = xlObj.getSheet(xlObj.getActiveSheetIndex()).batchChanges.changed;
if (changes.length) {
$.ajax({
type: "POST",
url: "SpreadsheetFeatures.aspx/Save",
data: JSON.stringify({ changed: JSON.stringify(changes) }),
contentType: "application/json; charset=utf-8",
dataType: 'json',
success: function (data) {
if (data.d == "Success")
alert("Edited data updated successfully!!!");
else
alert("Failed!!!");
}
});
}
}
</script>
|
|
[WebMethod()]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static object Data(DataManager value)
{
string dbConnectionStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\SpreadsheetData.mdf;Integrated Security=True";
SqlConnection sqlCon = new SqlConnection(dbConnectionStr);
SqlCommand sqlComm = new SqlCommand("SELECT [OrderID],[Quantity],[Price],[Amount],[Discount] FROM [dbo].[Table]", sqlCon);
sqlCon.Open();
List<Orders> order = new List<Orders>();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
while (sqlDR.Read())
{
order.Add(new Orders() { OrderID = (int)sqlDR.GetValue(0), Quantity = (int)sqlDR.GetValue(1), Price = (int)sqlDR.GetValue(2), Amount = (int)sqlDR.GetValue(3), Discount = (int)sqlDR.GetValue(4) });
}
sqlCon.Close();
return new { result = order, count = order.Count };
}
[WebMethod]
public static string Save(string changed)
{
string changedData = HttpContext.Current.Request.Params["changed"];
JavaScriptSerializer js = new JavaScriptSerializer();
try
{
List<Orders> changes = js.Deserialize<List<Orders>>(changed);
string dbConnectionStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\SpreadsheetData.mdf;Integrated Security=True";
SqlConnection sqlCon = new SqlConnection(dbConnectionStr);
foreach (var i in changes)
{
SqlCommand sqlComm = new SqlCommand("UPDATE [Table] SET [Quantity] = @Quantity, [Price] = @Price, [Amount] = @Amount, [Discount] = @Discount Where [OrderID] = @OrderID", sqlCon);
sqlComm.Parameters.AddWithValue("@OrderID", i.OrderID);
sqlComm.Parameters.AddWithValue("@Quantity", i.Quantity);
sqlComm.Parameters.AddWithValue("@Price", i.Price);
sqlComm.Parameters.AddWithValue("@Amount", i.Amount);
sqlComm.Parameters.AddWithValue("@Discount", i.Discount);
sqlCon.Open();
sqlComm.ExecuteNonQuery();
sqlCon.Close();
}
return "Success";
}
catch (Exception ex)
{
return "Failed";
}
}
|
|
<label>File name:</label>
<input type="text" id="filename" value="Sample1" placeholder="Specify file name">
<button id="LoadFromDataBase" class="e-btn">LoadFromDataBase</button>
<button id="saveToServer" class="e-btn">saveToServer</button>
<ejs-spreadsheet id="spreadsheet" openUrl="Home/LoadFromDataBase" saveUrl="Home/SaveFiletoDB">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Car Sales Report">
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
document.getElementById("saveToServer").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
spreadsheetObj.saveAsJson().then((Json) => {
var formData = new FormData();
formData.append('JSONData', JSON.stringify(Json.jsonObject.Workbook));
formData.append('FileName', document.getElementById("filename").value);
formData.append('saveType', 'Xlsx');
fetch('Home/SaveFiletoDB', {
method: 'POST',
body: formData
}).then((basePath) => {
console.log("file saved");
})
}
);
}
document.getElementById("LoadFromDataBase").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
fetch('/Home/LoadFromDataBase', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ Name: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
spreadsheetObj.openFromJson({ file: data });
})
}
</script>
//Open saved Spreadsheet data from database
public IActionResult LoadFromDataBase([FromBody]FileOptions file)
{
// Convert Spreadsheet data as Stream.
OpenRequest open = new OpenRequest();
MemoryStream dataStream = new MemoryStream();
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table1] WHERE [filename] = '" + file.Name + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
while (sqlDR.Read())
{
dataStream = new MemoryStream((byte[])sqlDR.GetValue(1));
IFormFile formFile = new FormFile(dataStream, 0, dataStream.Length, "", file.Name + ".xlsx"); // converting MemoryStream to IFormFile
open.File = formFile;
}
sqlCon.Close();
return Content(Workbook.Open(open));
}
// Save the Spreadsheet data as byte array to database.
public IActionResult SaveFiletoDB(SaveSettings saveSettings)
{
//save the file to database
MemoryStream dataStream = Workbook.Save<MemoryStream>(saveSettings);
byte[] dataBytes = new BinaryReader(dataStream).ReadBytes(Convert.ToInt32(dataStream.Length));
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table1]([filename], [databytes]) VALUES (@filename, @databytes)", sqlCon);
sqlComm.Parameters.AddWithValue("@fileName", saveSettings.FileName);
sqlComm.Parameters.AddWithValue("@dataBytes", dataBytes);
sqlComm.ExecuteNonQuery();
sqlCon.Close();
return Content("Saved in Server");
} |
|
<script>
var json = [];
document.getElementById('save').addEventListener('click', function () {
var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
json = [];
var obj = {};
var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
var usedRange = ssObj.getActiveSheet().usedRange;
var selIndex = [1, 0, usedRange.rowIndex, usedRange.colIndex];
var range =
ssObj.getActiveSheet().name +
"!" +
ejs.spreadsheet.getRangeAddress([1, 0, selIndex[2], selIndex[3]]);
ssObj.getData(range).then(
(value) => {
(value).forEach(
(cell, key) => {
if (cell) {
// constructing the key value object
var indexes = ejs.spreadsheet.getRangeIndexes(key);
if (key.indexOf("A") > -1) {
obj["employeeID"] = cell.value;
} else if (key.indexOf("B") > -1) {
obj["lastName"] = cell.value;
} else if (key.indexOf("C") > -1) {
obj["firstName"] = cell.value;
} else if (key.indexOf("D") > -1) {
obj["title"] = cell.value;
} else if (key.indexOf("E") > -1) {
obj["titleOfCourtesy"] = cell.value;
}
if (indexes[1] === selIndex[3]) {
// row last index
json.push(obj);
obj = {};
}
}
}
);
console.log(json);
var formData = new FormData();
formData.append('JSONData', JSON.stringify(json));
fetch("https://localhost:44355/Home/SaveChangesToDatabase", { method: 'Post', body: formData })
.then((response) => {
var spreadObj = ej.base.getComponent(document.getElementById("spreadsheet"), "spreadsheet");
response.blob().then((response) => {
if (response.ok) {
return response.json();
} else {
return Promise.reject({
message: response.statusText
});
}
}).then((data) => {
alert(data);
});
})
}
);
});
</script>
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 };
}
[AcceptVerbs("Post")]
public string SaveChangesToDatabase(string JSONData)
{
JavaScriptSerializer serialize = new JavaScriptSerializer();
List<EmployeeData> editedData = serialize.Deserialize<List<EmployeeData>>(JSONData);
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
for (int i = 0; i < editedData.Count; i++)
{
var order = editedData[i];
SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Employees] WHERE [EmployeeID] = '" + order.employeeID + "'", sqlCon);
var hasFileInDB = selectComm.ExecuteScalar();
if (hasFileInDB == null)
{
SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Employees]([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy]) VALUES (@EmployeeID, @LastName, @FirstName, @Title, @TitleOfCourtesy)", sqlCon);
sqlComm.Parameters.AddWithValue("@EmployeeID", order.employeeID);
sqlComm.Parameters.AddWithValue("@LastName", order.lastName);
sqlComm.Parameters.AddWithValue("@FirstName", order.firstName);
sqlComm.Parameters.AddWithValue("@Title", order.title);
sqlComm.Parameters.AddWithValue("@TitleOfCourtesy", order.titleOfCourtesy);
sqlComm.ExecuteNonQuery();
}
else
{
SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Employees] SET LastName=@LastName, FirstName=@FirstName, Title=@Title, TitleOfCourtesy=@TitleOfCourtesy WHERE EmployeeID=@EmployeeID", sqlCon); // Update edited data to DB.
updateComm.CommandTimeout = 0;
updateComm.Parameters.AddWithValue("@EmployeeID", order.employeeID);
updateComm.Parameters.AddWithValue("@LastName", order.lastName);
updateComm.Parameters.AddWithValue("@FirstName", order.firstName);
updateComm.Parameters.AddWithValue("@Title", order.title);
updateComm.Parameters.AddWithValue("@TitleOfCourtesy", order.titleOfCourtesy);
updateComm.ExecuteNonQuery();
}
}
sqlCon.Close();
return "Successfully updated";
} |
