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.
|
<table>
<tr>
//...
@Html.DropDownList("ImportFileName", ViewBag.FileNameList as List<SelectListItem>, new { @style = "width:130px;" })
<td>
<input type="button" value="Import" id="openFileFromDatabase" />
</td>
</tr>
</table>
<table>
//...
<tr>
//...
<td>
<input type="button" value="Save" id="saveFileToDatabase" />
</td>
<td>
<input type="button" value="Save Changes" id="saveChangesOnly" />
</td>
</tr>
</table>
@(Html.EJ().Spreadsheet<object>("Spreadsheet")
//...
.ClientSideEvents(eve =>
{
eve.LoadComplete("onLoadComplete");
})
)
<script type="text/javascript">
function onLoadComplete(args) {
if (this.isImport)
this.XLEdit.saveEditingValue();//Get or reset the saved changes collection on importing data from DB
}
$("#openFileFromDatabase").bind("click", function () {
var ssObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#ImportFileName").val();
if (fileName.length) {
ssObj.showWaitingPopUp();
$.ajax({
type: "POST",
data: { filename: fileName },
url: "/Home/OpenFileFromDB",
success: function (data) {
ssObj.loadFromJSON(JSON.parse(data));
ssObj.hideWaitingPopUp();
}
});
}
});
$("#saveFileToDatabase").bind("click", function () {
var ssObj = $("#Spreadsheet").data("ejSpreadsheet"), exportProp = ssObj.XLExport.getExportProps(), filename = $("#exportFileName").val();
$.ajax({
type: "POST",
data: { fileName: filename, sheetModel: exportProp.model, sheetData: exportProp.data },
url: "/Home/SaveAndUpdateToDatabase",
success: function (data) {
//...
}
});
});
$("#saveChangesOnly").bind("click", function () {
var ssObj = $("#Spreadsheet").data("ejSpreadsheet"), filename = $("#exportFileName").val(), editedChanges = ssObj.XLEdit.saveEditingValue();
$.ajax({
type: "POST",
data: { fileName: filename, editedValues: JSON.stringify(editedChanges.EditedData) },
url: "/Home/SaveChangesToDatabase",
success: function (data) {
//...
}
});
});
</script>
|
|
//File Open from database
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult OpenFileFromDB(string filename)
{
ImportRequest importRequest = new ImportRequest();
SqlConnection sqlCon = new SqlConnection(connetionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [FileName] = '" + filename + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
if (sqlDR.Read())
{
importRequest.FileStream = new MemoryStream((byte[])sqlDR.GetValue(1)); // Get binary values from DB.
}
sqlCon.Close();
return Content(Spreadsheet.Open(importRequest));
}
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult SaveAndUpdateToDatabase(string fileName, string sheetModel, string sheetData)
{
MemoryStream dataStream = (MemoryStream)Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013);
byte[] dataBytes = dataStream.ToArray();
SqlConnection sqlCon = new SqlConnection(connetionString);
sqlCon.Open();
SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [FileName] = '" + fileName + "'", sqlCon);
var hasFileInDB = selectComm.ExecuteScalar();
if (hasFileInDB == null)
{
SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([FileName], [FileData]) VALUES (@FileName, @FileData)", sqlCon);
sqlComm.Parameters.AddWithValue("@FileName", fileName);
sqlComm.Parameters.AddWithValue("@FileData", dataBytes);
sqlComm.ExecuteNonQuery();
}
else
{
SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET FileData=@nFileData WHERE FileName=@nFileName", sqlCon); // Update edited data to DB.
updateComm.CommandTimeout = 0;
updateComm.Parameters.AddWithValue("@nFileName", fileName);
updateComm.Parameters.AddWithValue("@nFileData", dataBytes);
updateComm.ExecuteNonQuery();
}
sqlCon.Close();
return Content(fileName);
}
//Save changes only
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult SaveChangesToDatabase(string fileName, string editedValues)
{
JavaScriptSerializer serialize = new JavaScriptSerializer();
List<CellDetail> editedData = serialize.Deserialize<List<CellDetail>>(editedValues);
MemoryStream dataStream = new MemoryStream();
SqlConnection sqlCon = new SqlConnection(connetionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [FileName] = '" + fileName + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
if (sqlDR.Read())
{
dataStream = new MemoryStream((byte[])sqlDR.GetValue(1)); // Get Stream values from DB.
}
sqlDR.Close();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(dataStream);
IWorksheet sheet;
for (int i = 0; i < editedData.Count; i++)
{
CellDetail data = editedData[i];
sheet = workbook.Worksheets[data.SheetIndex - 1];
// C# backend edit process by using XlsIo Library.
sheet.Rows[data.CellIndex.RowIndex].Cells[data.CellIndex.ColIndex].Value = data.Value;
}
MemoryStream newDataStream = new MemoryStream();
workbook.Version = ExcelVersion.Excel2013;
workbook.Application.DefaultVersion = ExcelVersion.Excel2013;
workbook.SaveAs(newDataStream);
SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET FileData=@nFileData WHERE FileName=@nFileName", sqlCon);
byte[] dataBytes = newDataStream.ToArray();
updateComm.Parameters.AddWithValue("@nFileName", fileName);
updateComm.Parameters.AddWithValue("@nFileData", dataBytes);
updateComm.ExecuteNonQuery();
sqlCon.Close();
return Content("Successfully updated");
}
|
|
<div class="control">
<button class="e-btn" id="saveChanges">Save Changes to DB</button>
<button class="e-btn" id="openFromDB">Open from DB</button>
@Html.EJS().Spreadsheet("spreadsheet").CellSave("cellSave").OpenUrl("Home/Open").SaveUrl("Home/Save").Render()
</div>
<script type="text/javascript">
var dataChanged = [];
document.getElementById("saveChanges").addEventListener("click", () => {
var formData = new FormData();
formData.append("fileName", "Sample");
formData.append("cellDetail", JSON.stringify(dataChanged));
fetch("Home/SaveChangesToDB", {
method: "POST",
body: formData
}).then((response) => {
dataChanged = [];
});
});
document.getElementById("openFromDB").addEventListener("click", () => {
var formData = new FormData();
formData.append("fileName", "Sample");
fetch("Home/LoadFromDataBase", {
method: "POST",
body: formData
}).then((response) => {
response.json().then((data) => {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
spreadsheetObj.openFromJson({ file: data });
});
});
});
function cellSave(args) {
dataChanged.push({ value: args.value, address: args.address.split('!')[1], sheetIdx: this.activeSheetIndex });
}
</script>
|
|
public ActionResult LoadFromDataBase(String fileName)
{
//Load file from database
OpenRequest openRequest = new OpenRequest();
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table1] WHERE [filename] = '" + fileName + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
while (sqlDR.Read())
{
HttpPostedFileBase objFile = (HttpPostedFileBase)new HttpPostedFile((byte[])sqlDR.GetValue(1), fileName);
HttpPostedFileBase[] theFiles = new HttpPostedFileBase[1];
theFiles[0] = objFile;
openRequest.File = theFiles;
}
sqlCon.Close();
return Content(Workbook.Open(openRequest));
}
public string SaveChangesToDB(string fileName, string cellDetail)
{
JavaScriptSerializer serialize = new JavaScriptSerializer();
List<CellDetail> editedData = serialize.Deserialize<List<CellDetail>>(cellDetail);
MemoryStream dataStream = new MemoryStream();
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table1] WHERE [filename] = '" + fileName + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
while (sqlDR.Read())
{
dataStream = new MemoryStream((byte[])sqlDR.GetValue(1));
}
sqlDR.Close();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(dataStream);
IWorksheet sheet;
for (int i = 0; i < editedData.Count; i++)
{
CellDetail cell = editedData[i];
sheet = workbook.Worksheets[cell.sheetIdx];
// C# backend edit process by using XlsIo Library.
sheet.Range[cell.address].Value = cell.value;
}
MemoryStream newDataStream = new MemoryStream();
//workbook.Version = ExcelVersion.Excel2013;
//workbook.Application.DefaultVersion = ExcelVersion.Excel2013;
workbook.SaveAs(newDataStream);
SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table1] SET databytes=@dataBytes WHERE fileName=@filename", sqlCon);
byte[] dataBytes = newDataStream.ToArray();
updateComm.Parameters.AddWithValue("@filename", fileName);
updateComm.Parameters.AddWithValue("@dataBytes", dataBytes);
updateComm.ExecuteNonQuery();
sqlCon.Close();
return "success";
} |
|
<ejs-spreadsheet id="spreadsheet" created="createdHandler" cellSave="cellSave">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Shipment Details">
<e-spreadsheet-ranges>
<e-spreadsheet-range>
<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=220></e-spreadsheet-column>
<e-spreadsheet-column width=150></e-spreadsheet-column>
<e-spreadsheet-column width=180></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
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', {
method: 'POST',
body: formData
}).then((response) => console.log(response));
});
});
});
});
}
</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 };
}
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";
} |
Startup.csProgram.csSystem.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action
HomeController.csHomeController.cs|
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] - 1, 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));
$.ajax({
type: "POST",
data: { JSONData: JSON.stringify(json) },
success: function (data) {
console.log(data);
}
});
}
);
});
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();
SqlCommand sqlComm = new SqlCommand("SET IDENTITY_INSERT Employees ON", sqlCon);
sqlComm.ExecuteNonQuery();
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)
{
// insert newly inserted row data in database.
sqlComm = new SqlCommand("INSERT INTO [dbo].[Employees]([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy]) VALUES (@EmployeeID, @LastName, @FirstName, @Title, @TitleOfCourtesy)", sqlCon);
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";
} |