This is a migrated thread and some comments may be shown as answers.

Loading an Excel File from the Server and Saving it to the Server

18 Answers 764 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Cory
Top achievements
Rank 1
Cory asked on 17 Apr 2018, 05:02 PM

I have a situation where an administrative user creates an Excel file using her desktop copy of Excel and saves it to a location on our server. What I want to do is load that file from the server into a Spreadsheet control, have a user make changes in the Spreadsheet, and then click a button to save it back to a location on the server.

I see plenty of examples of importing files from the user's local system and exporting them to the user' local system. However, I don't see any examples of loading a file from the SERVER when the spreadsheet first loads. Similarly, I can't find an example of saving the spreadsheet contents as an Excel file on the SERVER.

Can somebody please provide an example of this?

Thank you!

18 Answers, 1 is accepted

Sort by
0
Accepted
Veselin Tsvetanov
Telerik team
answered on 19 Apr 2018, 11:17 AM
Hello Cory,

The particular example for loading from and saving to the server an xlsx file would depend on the server technology of choice. Nevertheless, in all cases the principles from the Server import / export demo will apply:

- An AJAX request will be made initially, so the xlsx data gets delivered to the client as JSON;

- On the server, the xlsx file should be converted to JSON;

- The fromJSON() method of the Spreadsheet will be used to load the initial data to the widget;

- Upon save, the toJSON() method of the Spreadsheet will be used;

- That JSON will be submitted with a form or with an AJAX request to the server;

- The JSON sent to the server will be processed, converted to xlsx and saved on the server;

Attached you will find a .NET MVC project, following the above scenario and using the Telerik.Web.Spreadsheet and the Telerik Document Processing library to manipulate the file on the server.

I hope, that this helps. In case you have any other questions, please do not hesitate to contact us.

Regards,
Veselin Tsvetanov
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Cory
Top achievements
Rank 1
answered on 20 Apr 2018, 03:57 AM
Thank you very much! That step of converting to/from JSON was exactly what I needed.
0
Franz
Top achievements
Rank 1
answered on 16 Dec 2019, 06:29 AM

Hello,

this is exactly what I need but is it also available for asp.NET Core 3 ?

Regards

Franz

0
Veselin Tsvetanov
Telerik team
answered on 17 Dec 2019, 03:50 PM

Hello Franz,

I am afraid that the approach described above could not be applied in a .Net Core scenario. The reason for that is the fact that there is no .Net Core implementation of the Telerik.Web.Spreadsheet package (the one which holds the definition of the Workbook class, the workbook.Save() and workbook.ToJson() methods). Even if we are planning to include the Telerik SpreadProcessing within our very next release, the Web.Spreadsheet package would not be available.

Having that said, I would suggest you continue the communication in the support thread that you have opened on the same topic. Peter is currently working on a possible alternative approach that would allow communication between server and client using XLSX files.

Regards,
Veselin Tsvetanov
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Daniel
Top achievements
Rank 1
answered on 15 Jan 2020, 11:51 PM

 

Hi Veselin,

I am in the same boat here, we are a .NET Core environment (version 2.1 to be specific), is there an option for us as you mentioned in this post?

Thanks,

Daniel

 

[quote]Veselin Tsvetanov said:

Hello Franz,

I am afraid that the approach described above could not be applied in a .Net Core scenario. The reason for that is the fact that there is no .Net Core implementation of the Telerik.Web.Spreadsheet package (the one which holds the definition of the Workbook class, the workbook.Save() and workbook.ToJson() methods). Even if we are planning to include the Telerik SpreadProcessing within our very next release, the Web.Spreadsheet package would not be available.

Having that said, I would suggest you continue the communication in the support thread that you have opened on the same topic. Peter is currently working on a possible alternative approach that would allow communication between server and client using XLSX files.

Regards,
Veselin Tsvetanov
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.

[/quote]

 

 

0
Veselin Tsvetanov
Telerik team
answered on 17 Jan 2020, 02:11 PM

Hello Daniel,

Attached you will find an alternative approach to the discussed scenario in .Net Core. The content is sent to and received from the server as an XLSX file. Here is how the file is requested:

var spread = $('#spreadsheet').getKendoSpreadsheet();

function b64toBlob(dataURI) {
	var byteString = atob(dataURI.split(',')[1]);
	var ab = new ArrayBuffer(byteString.length);
	var ia = new Uint8Array(ab);

	for (var i = 0; i < byteString.length; i++) {
		ia[i] = byteString.charCodeAt(i);
	}

	return new Blob([ab], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
}

$.ajax({
    url: '@Url.Action("GetFile", "Home")',
    method: "get",
    success:function(data){
        spread.fromFile(b64toBlob(data));
    }
})

And here is how the AJAX call is handled on the server:

[HttpGet]
public string GetFile()
{
    byte[] fileByteArray = System.IO.File.ReadAllBytes(_env.ContentRootPath + "\\App_data\\sample.xlsx");
    string file = Convert.ToBase64String(fileByteArray);
    return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + file;
}

As per saving the file, the SPreadsheet is configured to force its proxy call upon Excel export:

$('#spreadsheet').kendoSpreadsheet({
    excel: {
        proxyURL: '@Url.Action("SaveFile", "Home")',
        forceProxy: true
    }
});

Here is the implementation of the proxy action on the server:

public ActionResult SaveFile(string contentType, string base64, string fileName)
{
    var fileContents = Convert.FromBase64String(base64);
    System.IO.File.WriteAllBytes(_env.ContentRootPath + "\\App_data\\sample.xlsx", fileContents);
    return View("Index");
}

I hope that this helps.

Regards,
Veselin Tsvetanov
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Daniel
Top achievements
Rank 1
answered on 23 Jan 2020, 03:16 AM

Hi Veselin,

Thank you so much, this was exactly what we needed!

Daniel

0
Kieran
Top achievements
Rank 1
answered on 25 Sep 2020, 01:16 PM

is it possible to load a CSV file in the same way? I have tried changing :-

 

return new Blob([ab], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });

to 

return new Blob([ab], { type: text/csv' });

and 

return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + file;

to

return "data:text/csv;base64," + file;

but I get the following error :-

 

Uncaught Error: Can't find end of central directory : is this a zip file ? If it is, see http://stuk.github.io/jszip/documentation/howto/read_zip.html
    at d.readEndOfCentral (jszip.min.js:12)
    at d.load (jszip.min.js:12)
    at new d (jszip.min.js:12)
    at d.b.exports [as load] (jszip.min.js:12)
    at new d (jszip.min.js:12)
    at FileReader.o.onload (kendo.all.js:139185)

 

am I missing something?

0
Ivan Danchev
Telerik team
answered on 29 Sep 2020, 12:31 PM

Hello Kieran,

We are not aware of an available way to apply the same approach to a CSV file.

The exception you are getting is in the jszip third party library, so the devs working on it might be able to provide further insight into the issue: https://github.com/Stuk/jszip/issues

Regards,
Ivan Danchev
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Heiko
Top achievements
Rank 1
Veteran
answered on 05 Nov 2020, 03:27 PM

Hi Veselin,

thank you for your example, it is very much appreciated. However, the project in the ZIP-file does not work, because there is a link to "C:\Users\ptodorov\AppData\Roaming\Telerik\Updates\telerik.ui.for.aspnet.core.hotfix.2019.3.1023.commercial\wrappers\aspnetcore\Binaries\AspNet.Core" in nuget.config. Maybe you could update the solution?

Next problem: I used your code, the excel file is displayed, but all cell values are gone. Formulas, named cells etc. are correct, but no simple number values. What is going wrong?

Regards
Heiko

0
Heiko
Top achievements
Rank 1
Veteran
answered on 05 Nov 2020, 07:01 PM
My fault, I mixed the javascript files from 2019.3 with the actual Telerik.UI.for.AspNet.Core 2020.3 assemblies...sorry!
0
Edward
Top achievements
Rank 1
Veteran
Iron
Iron
answered on 11 Dec 2020, 07:30 AM

When I was performing such task in Excel file I was frequently getting Excel file not loaded completely error. When I searched for the reason i came to know that I have crossed the row limitation in Excel.  Well, the maximum number of rows allowed in an Excel single worksheet is 1,048,576.

So you are also getting this kind of issue meanwhile loading of Excel file then have a look over the fixes listed in this post.

source:  Fix Excel File Not Loaded Completely Error 

0
Petar
Telerik team
answered on 14 Dec 2020, 09:52 AM

Hi Edward,

Thank you for sharing additional information about the current issue! It can help someone in the community. 

Regards,
Petar
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
greg
Top achievements
Rank 1
answered on 15 Jan 2021, 02:17 AM

Hello,

I was wondering if you have an example of how to remotely upload an xlsx file as raw data to the browser first using AJAX, and then subsequently convert the xlsx raw file to JSON to show in the browser?

And vice versa saving the file by first converting the json to xlsx on the browser and then sending via ajax back to the server?

Thanks

Greg

0
greg
Top achievements
Rank 1
answered on 15 Jan 2021, 06:37 PM
Oops, just realized that you had already posted the solution to this problem above last year...great and thanks!
0
greg
Top achievements
Rank 1
answered on 16 Jan 2021, 10:04 PM

Trying to implement code as outlined above:

Error I'm having in trying to remotely load xlsx file into Kendo Spreadsheet is that upon trying to load the xlsx file, the Kendo.all.js seems to be looking for a zip file? Error as follows:

jszip.min.js:12 Uncaught Error: Can't find end of central directory : is this a zip file ? If it is, see http://stuk.github.io/jszip/documentation/howto/read_zip.html
    at d.readEndOfCentral (jszip.min.js:12)
    at d.load (jszip.min.js:12)
    at new d (jszip.min.js:12)
    at d.b.exports [as load] (jszip.min.js:12)
    at new d (jszip.min.js:12)
    at FileReader.o.onload (kendo.all.js:144098)

Was wondering if there are any ideas on why it would be looking for a zip file?

Code I used:
Client side code:

$.ajax({
    url: "https://example.com/restservice/KendoExcelFileSourceEndPoint.sim?ID=test.xlsx",
    method: "get",
    success:function(data){
var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.fromFile(b64toBlob(data));  // this throws and error 
    }
})


Server side:
    byte[] fileByteArray = System.IO.File.ReadAllBytes(_env.ContentRootPath + "\\App_data\\sample.xlsx");
    string file = Convert.ToBase64String(fileByteArray);
    return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + file;

0
Petar
Telerik team
answered on 18 Jan 2021, 11:13 AM

Hi Greg,

Is the reported issue replicable in the FromFileToFileCore.zip project provided by my colleague Veselin? Or the shared error appears in your project? 

If the error appears in a project different from the one linked in the current forum thread, can you check if you have the JSZip library included in your project? If you check the _Layout file of the FromFileToFileCore project you will see the following definition that includes the JSZip scripts.

<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>@ViewData["Title"] - FromFileToFileCore</title>

    <link href="https://kendo.cdn.telerik.com/2020.1.114/styles/kendo.bootstrap-v4.min.css" rel="stylesheet" type="text/css" />

    <script src="https://kendo.cdn.telerik.com/2020.1.114/js/jquery.min.js"></script>
    <script src="https://kendo.cdn.telerik.com/2020.1.114/js/jszip.min.js"></script>
    <script src="https://kendo.cdn.telerik.com/2020.1.114/js/kendo.all.min.js"></script>
    <script src="https://kendo.cdn.telerik.com/2020.1.114/js/kendo.aspnetmvc.min.js"></script>
</head>

If you have the JSZip library included in your project, what I can suggest is to check this link:https://github.com/Stuk/jszip/issues/632. The other approach you can try is the one suggested by my colleague Ivan - ask the JSZip community about the issue as it is not one that we are aware of, or is related to the Kendo UI suite. 

Regards,
Petar
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
greg
Top achievements
Rank 1
answered on 20 Jan 2021, 07:05 AM

Hi Petar,

Thanks for your suggestions and details. I finally got it to work...it was an error in my custom code. Thanks again,

Greg

Tags
Spreadsheet
Asked by
Cory
Top achievements
Rank 1
Answers by
Veselin Tsvetanov
Telerik team
Cory
Top achievements
Rank 1
Franz
Top achievements
Rank 1
Daniel
Top achievements
Rank 1
Kieran
Top achievements
Rank 1
Ivan Danchev
Telerik team
Heiko
Top achievements
Rank 1
Veteran
Edward
Top achievements
Rank 1
Veteran
Iron
Iron
Petar
Telerik team
greg
Top achievements
Rank 1
Share this question
or