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. Image for the cookie policy date

Opening and Saving spreadsheet files to server

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

11 Replies

SI Silambarasan I Syncfusion Team November 16, 2016 10:39 AM UTC

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 



SP Seyha Phul November 16, 2016 04:08 PM UTC

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. 



SI Silambarasan I Syncfusion Team November 17, 2016 10:32 AM UTC

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 



VI vivek replied to Silambarasan I November 28, 2017 11:20 AM UTC

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


SI Silambarasan I Syncfusion Team November 29, 2017 09:26 AM UTC

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 



LO Louis February 7, 2020 09:12 AM UTC

Is the feature to import and export of excel files in jsp available now?

Is is possible to open an excel file in a client browser js using syncfusion js files?

We have a product that is built for Tomcat platform and so it's not feasible to deploy .NET Application Server.

Your quick feedback will be appreciated.


SD Saranya Dhayalan Syncfusion Team February 10, 2020 12:26 PM UTC

Hi Sunbio,  
 
Thanks for the update. 
 
Query – 1: Is the feature to import and export of excel files in jsp available now? 
 
Yes. We can achieve import and export functionality by using our online web services. Please refer the below code example. 
 
<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> 
 
Please find the below link: 
 
 
 
Note: Our online web services only for Demo purposes. 
 
Query -2: Is it possible to open an excel file in a client browser js using Syncfusion js files? 
 
We haven’t provided support to client-side import/export support in our ej Spreadsheet. 
 
Query-3: We have a product that is built for Tomcat platform and so it's not feasible to deploy .NET Application Server. 
 
We would like to let you know that we need to create separate web API using .NET web application to perform import and export functionalities. 
 
Please let us know if you need further assistance. 
 
Regards, 
Saranya D 



ER Erhan May 6, 2020 07:28 AM UTC

Hi,

Can we have sample code (to read and write spreadsheet to the server) for asp.net web forms. Thanks in advance.



SD Saranya Dhayalan Syncfusion Team May 7, 2020 12:56 PM UTC

Hi Sunbio, 
 
Query: Can we have sample code (to read and write spreadsheet to the server) for asp.net web forms 
 
Please find the below kb link to save and retrieve the spreadsheet data to server 
 
 
 
Please check and get back to us if you need further assistance on this. 
 
Regards, 
Saranya D 



MB Muthukumaran Balasubramanian August 7, 2020 11:50 AM UTC

Can you provide sample code(Open and Save Spreadsheet from Server) for Angular 10 and WebAPI(Server Side)?



SD Saranya Dhayalan Syncfusion Team August 13, 2020 11:18 AM UTC

Hi Muthukumar, 
 
Query: Can you provide sample code(Open and Save Spreadsheet from Server) for Angular 10 and WebAPI(Server Side)? 
 
We have checked your reported query, we suspect you have asked the query related to EJ2 spreadsheet component. So, we will provide the below response 
 
We have checked your reported scenario, you can achieve this by using saveAsJson and openFromJson method in the button click event. Please find the below code snippet: 
 
Client side 
 
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 }); 
                }) 
        } 
 
Server side: 
 
    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 
        } 
 
 
For your convenience we have prepared a sample. please find the below sample link: 
 
 
 
Could you please check the above sample and get back to us if you need further assistance on this? 
 
Regards, 
Saranya D 


Loader.
Up arrow icon