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

[Cory]

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!

[Veselin Tsvetanov]

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

[Cory]

Thank you very much! That step of converting to/from JSON was exactly what I needed.

[Franz]

Hello,

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

Regards

Franz

[Veselin Tsvetanov]

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

[Daniel]

 

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

[/quote]

 

 

[Veselin Tsvetanov]

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

[Daniel]

Hi Veselin,

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

Daniel

[Kieran]

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?

[Ivan Danchev]

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

[Heiko]

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

[Heiko]

My fault, I mixed the javascript files from 2019.3 with the actual Telerik.UI.for.AspNet.Core 2020.3 assemblies...sorry!

[Edward]

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 

[Petar]

Hi Edward,

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

Regards,
Petar
Progress Telerik

[greg]

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

[greg]

Oops, just realized that you had already posted the solution to this problem above last year...great and thanks!

[greg]

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;

[Petar]

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

[greg]

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