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

How to bind RadSpreadsheet control with file stream

5 Answers 201 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Yash
Top achievements
Rank 1
Yash asked on 07 Apr 2018, 09:59 AM

i have excel file saved in SQL database as byte format, now i want to convert this bytes into excel and render it over RadSpreadsheet

 

Below is my code by getting exception "Value of type 'Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider' cannot be converted to 'Telerik.Web.UI.SpreadsheetProviderBase'"

Please help

01.Dim fileName As String = "Book1.xlsx"
02.Dim data() As Byte
03.Dim cmd As SqlCommand = New SqlCommand("select data from tblFiles")
04.data = GetData(cmd)
05. 
06.Dim memStream = New MemoryStream()
07.Dim binForm = New BinaryFormatter()
08.memStream.Write(data, 0, data.Length)
09.memStream.Seek(0, SeekOrigin.Begin)
10. 
11.Dim formatProvider As XlsxFormatProvider = New XlsxFormatProvider()
12.formatProvider.Import(memStream)
13.RadSpreadsheet1.Provider = formatProvider

5 Answers, 1 is accepted

Sort by
0
Anna
Telerik team
answered on 11 Apr 2018, 12:37 PM
Hi,

It seems that you are trying to use the ASP.NET RadSpreadsheet with the XlsxFormatProvider. This format provider is designed for the Silverlight / WPF RadSpreadsheet control and the Document Processing Library (DPL). The ASP.NET RadSpreadsheet has a separate format provider which should be used in this case.

Please, let me know if you have any further questions or comments.

Regards,
Anna
Progress Telerik
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Julie
Top achievements
Rank 1
answered on 10 Jul 2018, 02:18 PM
In the example you point to it Sets the provider to an excel file.  This is great but how to you set the provider property to a memory stream?  The only examples given are setting the provider to an excel file.  Please advise on how to set the provider to a memory stream
0
Peter Milchev
Telerik team
answered on 13 Jul 2018, 12:10 PM
Hello Julie, 

For convenience, I am sharing the answer from the support ticket here. Let us continue the discussion there and if we have other solutions and suggestions, we will share them here.

The provided SpreadsheetDocumentProvider can load only files from a file by passing a path to the file in the provider's constructor.

To use a MemoryStream for the Spreadsheet, you should create a Custom Provider - https://docs.telerik.com/devtools/aspnet-ajax/controls/spreadsheet/data-binding/providers/custom-database-provider.

Another possible approach is to load the MemoryStream to a provider from the DocumentProcessingLibrary and then to convert the DocumentProcessingLibrary Workbook to a Spreadsheet Workbook - https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/formats-and-conversion/xlsx/xlsxformatprovider.

Then, from the Spreadsheet Workbook, you can get the sheets and return them from the GetSheetsMethod of the Custom provider or populate the Spreadsheet programmatically - https://docs.telerik.com/devtools/aspnet-ajax/controls/spreadsheet/data-binding/programmatic-binding.

Telerik.Windows.Documents.Spreadsheet.Model.Workbook dplWorkbook;
IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
 
// the memory stream is a dummy one, for demo purposes
using (Stream input = new MemoryStream(new byte[1]))
{
    dplWorkbook = formatProvider.Import(input);
}
 
Telerik.Web.Spreadsheet.Workbook telerikWorkbook = Telerik.Web.Spreadsheet.Workbook.FromDocument(dplWorkbook);
 
RadSpreadsheet1.Sheets.AddRange(telerikWorkbook.Sheets);

If you would like to save the workbook to a MemoryStream, you can get the workbook as demonstrated here https://www.telerik.com/support/kb/aspnet-ajax/spreadsheet/details/access-the-spreadsheet-workbook-on-the-server and then convert it to a stream.

protected void Page_Load(object sender, EventArgs e)
{
    if (IsCallback && Request.Params["__CALLBACKID"] == RadSpreadsheet1.UniqueID)
    {
        Telerik.Web.Spreadsheet.Workbook telerikWorkbook = Telerik.Web.Spreadsheet.Workbook.FromJson(Request.Params["__CALLBACKPARAM"]);
        Telerik.Windows.Documents.Spreadsheet.Model.Workbook dplWorkbook = telerikWorkbook.ToDocument();
 
        IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
                          // the memory stream is a dummy one, for demo purposes
        using (Stream output = new MemoryStream(new byte[1]))
        {
            formatProvider.Export(dplWorkbook, output);
            // use the Memory stream here
        }
    }
}

Regards,
Peter Milchev
Progress Telerik
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
LeBuisson
Top achievements
Rank 1
answered on 21 Mar 2021, 12:52 PM

Hi Peter

We cannot translate your code in vb.net (SkippedTokensTrivia)

 

using (Stream input = new MemoryStream(new byte[1]))
{
    dplWorkbook = formatProvider.Import(input);
}

Can you suggest an implementation for vb.net please?

Regards

 

 

0
Peter Milchev
Telerik team
answered on 24 Mar 2021, 04:47 PM

Hello,

The problem with the convertor is probably the fact that it considers the using() as a method. With that said, if you wrap this in a simple method declaration it will convert it properly to:

    Using input As Stream = New MemoryStream(byteArr)
        dplWorkbook = formatProvider.Import(input)
    End Using

Regards,
Peter Milchev
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/.

Tags
Spreadsheet
Asked by
Yash
Top achievements
Rank 1
Answers by
Anna
Telerik team
Julie
Top achievements
Rank 1
Peter Milchev
Telerik team
LeBuisson
Top achievements
Rank 1
Share this question
or