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";
} |
Dear Sangeetha M
Greetings,
I observe is that, your sample demo and my work sample difference are, I am confused to map to urs one but your sample working;
Connection string style in appsetting.json
Dependacy state in the start up configuration service method
Database table placing and style and
With in controllers connectionpath defined, set value....
I use simple standard mysql connection string, but yours have (slash), attachment file, ....
I use simple dbcontext dependency in the configure service method, but yours have with if, connectiopath....
I use database of mysql at separate place but connected and not with in project like you app-data with mdf file I think.
In my controller there is no connectionpath defined connectionstring, and configuration but yours.
And this also last, Ihostenviroment evn, is Iwebhostenvironment evn, scafolding view and controllers.
Best regards,
Export to SQL Server
Open up SQL Server Management Studio (SSMS) and connect to a Database Engine.
Right-click on a Database and under Tasks, select "Import Data".
Click on "Next", and select "Microsoft Excel" from the dropdown menu of Data sources.
Click on the "Next" button and if it works for you
Regards,
Will
Hi Will,
As suggested earlier please check with the
below attached link to load sql to the spreadsheet.
If we misunderstood your mentioned query / issue please provide the below requested details to proceed further.
Provide detailed video demonstration of the
issue you were facing at your end.
Share any screenshots and images regarding your
reported issue.
Share
the detailed
information on the issue you were facing and the steps to replicate
it.
