Integration between Telerik Document Processing and Web UI (for JQuery, ASP.NET Core...)

[Tommaso]

Hi all, 

I'm trying to understand if it is possible to create a Spreadsheet with the RadSpreadProcessing library and work with it in a web environment. I saw from older posts that there was a Telerik.Web.Spreadsheet library that supported this integration, but it seems that it is not directly supported now with .NET 6.

I need to show a spreadsheet to the user on a browser, with support for formulas, macros, excel graphs, workbook/worksheet protection, autofit of columns, basically everything that can be done with excel. From the documentation it seems that many of the features that I need are not supported by Telerik UI Web, but are supported by the RadSpreadProcessing. Is it correct? Is there a way to implement all this with the Web UI libraries?

Thank you everyone in advance!

Tommaso

[Aleksandar]

Hello Tommaso,

You can use the Telerik UI for ASP.NET Core Spreadsheet to display a spreadsheet to the user, that was created/modified via the RadSpreadProcessing library. Telerik.Web.Spreadsheet will work with .NET 6 as well. You can review this forum post that clarifies the process of loading a spreadsheet from the server and especially this comment that also contains a sample application, in case the above is not sufficient. In addition you can pass a complete workbook to the view and bind it to the Spreadsheet component:

View

@model Telerik.Web.Spreadsheet.Workbook

@(Html.Kendo().Spreadsheet()
    .Name("spreadsheet")
    .BindTo(Model)
)

 

Controller

        public IActionResult Index()
        {
            // Generate workbook using Telerik Document Processing Libraries

            //var document = new Telerik.Windows.Documents.Spreadsheet.Model.Workbook();
            //var worksheet = document.Worksheets.Add();
            //worksheet.Cells[0, 0].SetValue("1.23");
            //worksheet.Name = "Worksheet 1";
            //return View(Workbook.FromDocument(document));

            // or use Telerik Document Processing Libraries to open (and edit) the document
            //Workbook book = GetWorkbook();
            
            // or read xlsx file and pass workbook to view
            var rootPath = "wwwroot/files/Book1.xlsx";
            Workbook book = Workbook.Load(rootPath);
            
            return View(book);
        }

        public Workbook GetWorkbook()
        {
            var path = Path.Combine("wwwroot/files/Book1.xlsx");
            var provider = new XlsxFormatProvider();

            using (FileStream input = System.IO.File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var wb = provider.Import(input);

                var output = provider.Export(wb);

                Workbook book = Workbook.Load(new MemoryStream(output), "xlsx");
                return book;
            }
        }

 

With all that said there are indeed some limitations for the Telerik UI for ASP.NET Core Spreadsheet and the Document Processing Libraries:

• While the Document Processing Libraries preserve Macros during import and export (They cannot be executed or changed in the code) the Spreadsheet component does not support macros. References to external sheets are also not supported. For a list of supported formulas and functions you can refer to this article.
• Importing a spreadsheet that contains charts is currently not supported in the Spreadsheet component, though we have a Feature Request on such functionality.

I hope the above answers your questions.

Regards,
Aleksandar
Progress Telerik

[Tommaso]

Hi Aleksandar,

Thank you very much for the excellent response! So just to recap: we can create the workbook server side with the Telerik.Windows library, and then in the view it is automatically converted to the Telerik.Web version, and viceversa on save/submit, is it correct?

Then to implement client-side behavior (let's say, add a row with custom formatting and validation rules) do we need to override the event of the Telerik UI ASP.NET Core, inside the event work with the Telerik.Web workbook, and reload the view to see the changes? Or do we need a custom action that needs to be performed server-side?

[Aleksandar]

Attached is a sample application demonstrating the options from the above snippet. This is indeed a way to load the data in the Spreadsheet component. Make sure to restore the NuGet packages before running the application. The Server Import/Export Demo shows additionally possible way to export the spreadsheet to the server. The forum post linked in my previous response provides further options on how data from the Spreadsheet can be submitted to the server.

I am not sure however I understand the last question on client-side behavior. Can you elaborate more on what is desired? Adding a row is possible via the UI, as well as setting custom formatting and validation. Check the Resize Demo for details on resizing a Spreadsheet, and the documentation section for further details. The Validation Demo highlights the Data validation capabilities of the component and details are available in the dedicated documentation section. Here is a short screencast of how you can inspect a set validation rule in the UI or set a custom format for a cell. I should note that if you are looking for conditional formatting that this feature is not yet available.