How to bind RadSpreadsheet control with file stream

[Yash]

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

[Anna]

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

[Julie]

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

[Peter Milchev]

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

[LeBuisson]

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

 

 

[Peter Milchev]

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