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.
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Opening and Saving spreadsheet files to server

Thread ID:

Created:

Updated:

Platform:

Replies:

127416 Nov 15,2016 12:08 AM Nov 29,2017 04:26 AM JavaScript 5
loading
Tags: ejSpreadSheet
Seyha Phul
Asked On November 15, 2016 12:08 AM

Hello,

Do you sample code for opening a spreadsheet from the server and saving a spreadsheet to the server?

Do you have sample source code for reading and writing to the spreadsheet on the serverside?


Sy Phu

Silambarasan I [Syncfusion]
Replied On November 16, 2016 05:39 AM

Hi Seyha, 
 
Thank you for using Syncfusion products. 
 
Query #1 “Do you sample code for opening a spreadsheet from the server and saving a spreadsheet to the server?” 
 
We have checked your requirement “To open and Save Spreadsheet in server” and it has been achieved by using ‘Open()’ & ‘Save()’ server-side methods in Spreadsheet.  Please refer the following code example. 
 
HTML 
 
<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> 
 
 
SCRIPT 
 
$(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!"); 
        } 
    }); 
}); 
 
 
C# 
 
 
//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(); 
    } 
} 
 
 
 
Query #2 “Do you have sample source code for reading and writing to the spreadsheet on the serverside?” 
 
Your requirement “To read and write Spreadsheet on server side” can be achievable in Spreadsheet.  Please refer the following code example.  
 
C# 
 
[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!"); 
} 
 
 
 
 
For your convenience, we have prepared a sample to demonstrate these requirements and the same can be downloaded from the below link, 
 
 
 
Regards, 
Silambarasan I 


Seyha Phul
Replied On November 16, 2016 11:08 AM

The code seems to be getting data from the database.  I would like to be able to open and save microsoft spreadsheets on server.


Let me clarify:

Open:
1. We have Microsoft Spreadsheets stored on our server in a specific directory. 
2. We would like to be open those microsoft spreadsheets with the Syncfusion Spreadsheets. 

Save:
2. When we have completed  editing the microsoft spreadsheet through syncfusion spreadsheet, we would like to save the syncfusion spreadsheet to our server in a specific directory. 


Silambarasan I [Syncfusion]
Replied On November 17, 2016 05:32 AM

Hi Seyha, 
 
Thanks for your update. 
 
Your requirement “To open and save Microsoft spreadsheets on server” can be achievable in ejSpreadsheet.  Please refer the following code example. 
 
HTML 
 
<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> 
 
 
SCRIPT 
 
$("#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); 
        } 
    }); 
}); 
 
 
C# 
 
//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!"); 
} 
 
 
 
For your convenience, we have modified the given sample and the same can be downloaded from the below link, 
 
 
 
Regards, 
Silambarasan I 


vivek
Replied On November 28, 2017 06:20 AM

This same feature (to read and write spreadsheet to the server) is available for JSP?

Silambarasan I [Syncfusion]
Replied On November 29, 2017 04:26 AM

Hi Sunbio, 
 
We have checked your query and currently, the support for Import/Export for JSP platform is not available in Spreadsheet. However, we have already added it to our feature request list, and it’ll be available in any of our upcoming releases. 
 
Regards, 
Silambarasan 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;