How to bind RadSpreadsheet control with file stream

4 posts, 0 answers
  1. Yash
    Yash avatar
    3 posts
    Member since:
    Aug 2013

    Posted 07 Apr Link to this post

    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
  2. Anna
    Admin
    Anna avatar
    126 posts

    Posted 11 Apr Link to this post

    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.
  3. Julie
    Julie avatar
    1 posts
    Member since:
    Aug 2012

    Posted 10 Jul in reply to Anna Link to this post

    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
  4. Peter Milchev
    Admin
    Peter Milchev avatar
    383 posts

    Posted 13 Jul Link to this post

    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.
Back to Top