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.
|
<div>
//...
<input type="button" value="Open" id="openFileFromDatabase" />
//...
</div>
<div>
//...
<input type="text" id="saveFileName" />
//...
<input type="button" value="Save to DB" id="saveSpreadsheetToDatabase" />
//...
</div>
|
|
$(function () {
$("#Spreadsheet").ejSpreadsheet({
//...
});
});
$("#openFileFromDatabase").bind("click", function () {
var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#ImportFileName").val();
xlObj.showWaitingPopUp();
$.ajax({
type: "POST",
data: { filename: fileName },
url: "/Home/OpenSpreadsheetFromDB",
success: function (data) {
xlObj.loadFromJSON(JSON.parse(data));
xlObj.hideWaitingPopUp();
}
});
});
$("#saveSpreadsheetToDatabase").bind("click", function () {
var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), saveFileName = $("#saveFileName").val(), exportProps = xlObj.XLExport.getExportProps();
//...
$.ajax({
type: "POST",
data: { fileName: saveFileName, sheetModel: exportProps.model, sheetData: exportProps.data },
url: "/Home/SaveSpreadsheetToDB",
success: function (data) {
//...
alert("Saved Successfully!");
}
});
});
|
|
//File Open from database
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult OpenSpreadsheetFromDB(string filename)
{
ImportRequest importRequest = new ImportRequest();
string dbConnectionStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\spreadsheetDB.mdf;Integrated Security=True";
SqlConnection sqlCon = new SqlConnection(dbConnectionStr);
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));
}
sqlCon.Close();
return Content(Spreadsheet.Open(importRequest));
}
//File upload to database
[AcceptVerbs(HttpVerbs.Post)]
public void SaveSpreadsheetToDB(string fileName, string sheetModel, string sheetData)
{
if (fileName.Length > 0)
{
string dbConnectionStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\spreadsheetDB.mdf;Integrated Security=True";
MemoryStream fileStream = (MemoryStream)Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013);
Byte[] dataBytes = fileStream.ToArray();
SqlConnection sqlCon = new SqlConnection(dbConnectionStr);
sqlCon.Open();
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();
sqlCon.Close();
}
}
|
|
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ReadWriteDBSpreadheetData(string filename)
{
MemoryStream stream = null;
string dbConnectionStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\spreadsheetDB.mdf;Integrated Security=True";
SqlConnection sqlCon = new SqlConnection(dbConnectionStr);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [FileName] = '" + filename + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
if (sqlDR.Read())
{
stream = new MemoryStream((byte[])sqlDR.GetValue(1));
}
sqlCon.Close();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (stream != null)
{
stream.Position = 0; //Reset reader position
IWorkbook workbook = application.Workbooks.Open(stream);
IWorksheet worksheet = workbook.Worksheets[0]; //Sheet 1
worksheet.Cells[0].Value = "Values modified from Server"; //A1 cell
MemoryStream mStream = new MemoryStream();
workbook.SaveAs(mStream);
Byte[] dataBytes = ((MemoryStream)mStream).ToArray();
sqlCon.Open();
SqlCommand sqlUpdateComm = new SqlCommand("UPDATE [dbo].[Table] SET FileData=@FileData WHERE FileName=@FileName", sqlCon);
sqlUpdateComm.CommandTimeout = 0;
sqlUpdateComm.Parameters.AddWithValue("@FileName", filename);
sqlUpdateComm.Parameters.AddWithValue("@FileData", dataBytes);
sqlUpdateComm.ExecuteNonQuery();
sqlCon.Close();
}
return Content("A1 cell modified & saved successfully. Please open the '" + filename + "' to check it!");
}
|
|
<div>
//...
<input type="button" value="Open" id="openFileFromServer" />
//...
<input type="button" value="Read & Write Selected file in Server" id="ReadWriteSpreadheetData" />
</div>
<div>
//...
<input type="text" id="saveFileName" />
//...
<input type="button" value="Save to server" id="saveSpreadsheetToServer" />
//...
</div>
|
|
$("#Spreadsheet").ejSpreadsheet({
//...
});
$("#openFileFromServer").bind("click", function () {
var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#ImportFileName").val();
xlObj.showWaitingPopUp();
$.ajax({
type: "POST",
data: { filename: fileName },
url: "/Home/OpenXLFileInSpreadsheet",
success: function (data) {
xlObj.loadFromJSON(JSON.parse(data));
xlObj.hideWaitingPopUp();
}
});
});
$("#saveSpreadsheetToServer").bind("click", function () {
var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), saveFileName = $("#saveFileName").val(), exportProps = xlObj.XLExport.getExportProps();
//...
$.ajax({
type: "POST",
data: { fileName: saveFileName, sheetModel: exportProps.model, sheetData: exportProps.data },
url: "/Home/SaveSpreadsheetToServer",
success: function (data) {
//...
alert("File Saved Successfully!");
}
});
});
$("#ReadWriteSpreadheetData").bind("click", function () {
var fileName = $("#ImportFileName").val();
$.ajax({
type: "POST",
data: { filename: fileName },
url: "/Home/ReadWriteDBSpreadheetData",
success: function (data) {
alert(data);
}
});
});
|
|
//File Open
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult OpenXLFileInSpreadsheet(string filename)
{
ImportRequest importRequest = new ImportRequest();
string filePath = Server.MapPath("~/Files/" + filename);
Stream fileStream = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read);
importRequest.FileStream = fileStream;
var spreadsheetData = Spreadsheet.Open(importRequest);
fileStream.Close();
return Content(spreadsheetData);
}
//File Save
[AcceptVerbs(HttpVerbs.Post)]
public void SaveSpreadsheetToServer(string fileName, string sheetModel, string sheetData)
{
if (fileName.Length > 0)
{
string filePath = Server.MapPath("~/Files/" + fileName);
MemoryStream fileStream = (MemoryStream)Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013);
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
fileStream.Position = 0; //Reset reader position
IWorkbook workbook = application.Workbooks.Open(fileStream);
workbook.SaveAs(filePath);
}
}
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ReadWriteDBSpreadheetData(string fileName)
{
if (fileName.Length > 0)
{
string filePath = Server.MapPath("~/Files/" + fileName);
FileStream stream = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read);
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(stream);
IWorksheet worksheet = workbook.Worksheets[0]; //Sheet 1
worksheet.Range["A1"].Text = "Values modified from Server"; //A1 cell
stream.Close();
workbook.SaveAs(filePath);
}
return Content("A1 cell modified & saved successfully. Please open the '" + fileName + "' to check it!");
}
|
This same feature (to read and write spreadsheet to the server) is available for JSP?
|
<ej:spreadsheet-exportSettings excelUrl="//js.syncfusion.com/demos/ejservices/api/Spreadsheet/ExcelExport" pdfUrl="//js.syncfusion.com/demos/ejservices/api/Spreadsheet/PdfExport" csvUrl="//js.syncfusion.com/demos/ejservices/api/Spreadsheet/CsvExport"></ej:spreadsheet-exportSettings>
<ej:spreadsheet-importSettings importMapper="//js.syncfusion.com/demos/ejservices/api/Spreadsheet/Import"></ej:spreadsheet-importSettings> |
|
App.component.html
<div>
<button class="e-btn" (click)="saveToServer()">Save Back to Server</button>
<button class="e-btn" (click)="LoadFromDataBase()">Load Data</button>
<label>File name:</label>
<input type="text" id="filename" value="Sample" placeholder="Specify file name">
</div>
App.component.ts
saveToServer() {
this.spreadsheetObj.saveAsJson().then(Json => (fetch('https://localhost:44376/Home/SaveFiletoDB', {
method: 'POST', // or 'PUT'
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ name: (document.getElementById("filename") as HTMLInputElement).value, JSONData: JSON.stringify((Json as any).jsonObject) }),
})
.then((response) => response.json())
.then((data) => {
console.log(data);
})))
}
LoadFromDataBase() {
fetch('https://localhost:44376/Home/LoadFromDataBase', {
method: 'POST', // or 'PUT'
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ name: (document.getElementById("filename") as HTMLInputElement).value }),
})
.then((response) => response.json())
.then((data) => {
this.spreadsheetObj.openFromJson({ file: data });
})
} |
|
public IActionResult SaveFiletoDB([FromBody]SaveOptions saveSettings)
{
//Save JSON to database
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[SpreadsheetData]([filename], [data]) VALUES (@filename, @data)", sqlCon);
sqlComm.Parameters.AddWithValue("@filename", saveSettings.Name);
sqlComm.Parameters.AddWithValue("@data", saveSettings.JSONData);
sqlComm.ExecuteNonQuery();
sqlCon.Close();
return Content("Saved in Server");
}
[HttpPost]
public string LoadFromDataBase([FromBody]FileOptions file)
{
OpenRequest open = new OpenRequest();
//Load JSON from database
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[SpreadsheetData] WHERE [filename] = '" + file.Name + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
string JSONData = "";
while (sqlDR.Read())
{
JSONData = sqlDR.GetValue(1).ToString();
open.File = JSONData;
}
sqlCon.Close();
string data = JSONData ;
return data; // returning JSON data to spreadsheet control
}
|
