Telerik Forums / UI for ASP.NET AJAX
This is a migrated thread and some comments may be shown as answers.

Import to RadGrid from Uploaded spreadsheet

6 Answers 941 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Matt
Top achievements
Rank 1
Matt asked on 12 Nov 2014, 04:08 PM
Greetings,

Here is a good example of how to use an Excel spreadsheet as the data source for a RadGrid...

http://www.telerik.com/forums/rad-grid-import-from-excel

Is there a way to modify this example to use a spreadsheet data source that was uploaded with RadAsyncUpload without saving the xlsx to the server first - like a file stream or something?  Any advice or examples would be appreciated.

Thanks!

Matt

6 Answers, 1 is accepted

Sort by
0
Accepted
Kostadin
Telerik team
answered on 17 Nov 2014, 11:54 AM
Hello Matt,

Thank you for contacting us.

A possible solution is to use RadSpreadProcessing control to achieve your scenario. Generally you after the file is uploaded you can use the XlsxFormatProvider Import property which will hold the file data. Then you can use MemoryStream to download the file locally and not on the server. Please check out the following live example which demonstrates that.

I hope this information helps.

Regards,
Kostadin
Telerik
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Matt
Top achievements
Rank 1
answered on 19 Nov 2014, 09:48 PM
Thank you for the reply.  I tried adapting the example to my situation, but I am not sure how to use the memory stream as my data source fro the RadGrid.  Here is what I have...

Markup...
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SpreadsheetToRadGrid.aspx.cs"
Inherits="SpreadsheetToRadGrid" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  <head runat="server">
    <title>Spreadsheet to RadGrid</title>
  </head>
  <body>
    <form id="form1" runat="server">
      <telerik:RadScriptManager ID="RadScriptManager1" runat="server">
      </telerik:RadScriptManager>
      <telerik:RadFormDecorator ID="RadFormDecorator1" runat="server" DecoratedControls="All" />
      <table>
        <tr>
          <td>
            <telerik:RadAsyncUpload runat="server" ID="uplGetSpreadsheet" HideFileInput="true"
                                    OnClientFileUploaded="OnClientFileUploaded" OnClientValidationFailed="OnClientValidationFailed"
                                    OnFileUploaded="uplGetSpreadsheet_FileUploaded" AllowedFileExtensions=".xlsx,.csv"
                                    MaxFileInputsCount="1">
            </telerik:RadAsyncUpload>
          </td>
        </tr>
        <tr>
          <td>
            <telerik:RadButton runat="server" ID="btnPopulate" Text="Populate RadGrid" OnClientClicking="CheckSpreadsheet"
                               Icon-PrimaryIconCssClass="rbUpload" OnClick="btnPopulate_Click">
            </telerik:RadButton>
          </td>
        </tr>
        <tr>
          <td>
            <asp:Label ID="lblFile" runat="server" ForeColor="Red"></asp:Label>
          </td>
        </tr>
        <tr>
          <td>
            <telerik:RadGrid ID="grdImportedRecords" runat="server">
            </telerik:RadGrid>
          </td>
        </tr>
      </table>
      <telerik:RadScriptBlock ID="RadScriptBlock1" runat="server">
        <script type="text/javascript">
          var controlClientIDs = {
            fileLabel: '<%= lblFile.ClientID %>',
            importedRecordGrid: '<%= grdImportedRecords.ClientID %>'
          };
 
          function OnClientFileUploaded(sender, args) {
            $get(controlClientIDs.fileLabel).innerHTML = args.get_fileName();
          }
 
          function OnClientValidationFailed(sender, args) {
            var fileExtention = args.get_fileName().substring(args.get_fileName().lastIndexOf('.') + 1, args.get_fileName().length);
            if (args.get_fileName().lastIndexOf('.') != -1) {
              if (!new RegExp(sender.get_allowedFileExtensions().split(", ").join("|")).test("." + fileExtention)) {
                alert("Please load a file with one of the following extensions: .xlsx or .csv");
              }
            } else {
              alert("Not correct extension!");
            }
          }
 
          function CheckSpreadsheet(sender, args) {
            if ($get(controlClientIDs.fileLabel).innerHTML == "") {
              alert("Please load a file to convert!");
              args.set_cancel(true)
            }
          }
        </script>
      </telerik:RadScriptBlock>
    </form>
  </body>
</html>

Code behind...
using System;
using System.IO;
using System.Linq;
using Telerik.Web.UI;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders.TextBased.Csv;
using Telerik.Windows.Documents.Spreadsheet.Model;
public partial class SpreadsheetToRadGrid : System.Web.UI.Page
{
  private Workbook workbook;
  private IWorkbookFormatProvider fileFormatProvider;
  private string fileExtension;
  public static IWorkbookFormatProvider GetFormatProvider(string extension)
  {
    IWorkbookFormatProvider formatProvider;
    switch (extension)
    {
      case ".xlsx":
        formatProvider = new XlsxFormatProvider();
        break;
      case ".csv":
        formatProvider = new CsvFormatProvider();
        ((CsvFormatProvider)formatProvider).Settings.HasHeaderRow = true;
        break;
      default:
        formatProvider = null;
        break;
    }
    return formatProvider;
  }
 
  private string SetMimeType(string fileExt)
  {
    string mimeType = String.Empty;
    switch (fileExt.ToLower())
    {
      case ".xlsx":
        mimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        break;
      case ".csv":
        mimeType = "text/csv";
        break;
    }
    return mimeType;
  }
 
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack)
    {
      fileExtension = "";
    }
  }
 
  protected void uplGetSpreadsheet_FileUploaded(object sender, Telerik.Web.UI.FileUploadedEventArgs e)
  {
    UploadedFile file = e.File;
    fileFormatProvider = GetFormatProvider(file.GetExtension());
    workbook = fileFormatProvider.Import(file.InputStream);
    fileExtension = file.GetExtension();
  }
 
  protected void btnPopulate_Click(object sender, EventArgs e)
  {
    byte[] renderedBytes = null;
    IWorkbookFormatProvider formatProvider = GetFormatProvider(fileExtension);
    using (MemoryStream ms = new MemoryStream())
    {
      formatProvider.Export(workbook, ms);
      renderedBytes = ms.ToArray();
    }
    grdImportedRecords.DataSource = renderedBytes;
    grdImportedRecords.DataBind();
    //Response.Clear();
    //Response.AppendHeader("Content-Disposition", "attachment; filename=ConvertedFile" + fileExtension);
    //Response.ContentType = SetMimeType(fileExtension);
    //Response.BinaryWrite(renderedBytes);
    //Response.End();
  }
}


Can you tell me how to set grdImportedRecords.DataSource?  Much appreciated!

Matt
0
Matt
Top achievements
Rank 1
answered on 21 Nov 2014, 04:49 PM
I ended up opening a support ticket for this.  Here is the updated btnPopulate_Click method...

protected void btnPopulate_Click(object sender, EventArgs e)
{
  DataTable dt = new DataTable("SourceSpreadsheet");   
  Worksheet activeWorksheet = workbook.Worksheets[0];
  CellRange usedCellRange = activeWorksheet.UsedCellRange;
  for (int rec = 0; rec < usedCellRange.RowCount; rec++)
  {
    DataRow row = dt.NewRow();
    for (int col = 0; col < usedCellRange.ColumnCount; col++)
    {
      ICellValue cellValue = activeWorksheet.Cells[rec, col].GetValue().Value;
      if (rec == 0)
      {
        DataColumn dc = new DataColumn(cellValue.RawValue);
        dc.DataType = Type.GetType("System.String");
        dt.Columns.Add(dc);
      }
      else
      {
        row[col] = cellValue.RawValue;
      }       
    }
    if (rec > 0)
    {
      dt.Rows.Add(row);
    }
  }
  grdImportedRecords.DataSource = dt;
  grdImportedRecords.DataBind();
}

Thank you to Petya for the help with this.

Matt 
0
Kallie
Top achievements
Rank 1
answered on 21 Apr 2017, 08:02 AM

Hi Matt.

For some obscure reason I get a an error on the line :

workbook = fileFormatProvider.Import(file.InputStream);

Any Ideas on a workaround on this one?

 

 

System.OutOfMemoryException occurred
  HResult=0x8007000E
  Source=mscorlib
  StackTrace:
   at System.IO.MemoryStream.set_Capacity(Int32 value)
   at System.IO.MemoryStream.EnsureCapacity(Int32 value)
   at System.IO.MemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.Stream.InternalCopyTo(Stream destination, Int32 bufferSize)
   at System.IO.Stream.CopyTo(Stream destination)
   at Telerik.Windows.Documents.FormatProviders.OpenXml.OpenXmlImporter`1.GetStreamFromZipPackage(ZipArchiveEntry entry, Stream& stream)
   at Telerik.Windows.Documents.FormatProviders.OpenXml.OpenXmlImporter`1.ImportXlsxPartFromArchive(ZipArchiveEntry zipEntry, OpenXmlPartBase part, IOpenXmlImportContext context)
   at Telerik.Windows.Documents.FormatProviders.OpenXml.OpenXmlImporter`1.Import(Stream input, IOpenXmlImportContext context)
   at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider.ImportOverride(Stream input)
   at Telerik.Windows.Documents.Spreadsheet.FormatProviders.WorkbookFormatProviderBase.Import(Stream input)
   at DBFUpload.fuWeeklyHarvest_FileUploaded(Object sender, FileUploadedEventArgs e) in e:\DROPBOX_OLD\VSS_WD\CMC.root\CMC\CMC_V2\Uploads\DBFUpload.aspx.cs:line 68
   at Telerik.Web.UI.RadAsyncUpload.OnFileUploaded(FileUploadedEventArgs e)
   at Telerik.Web.UI.RadAsyncUpload.RaiseDataChangedEvent()
   at Telerik.Web.UI.RadAsyncUpload.RaisePostDataChangedEvent()
   at Telerik.Web.UI.RadWebControl.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent()

0
Matt
Top achievements
Rank 1
answered on 21 Apr 2017, 02:30 PM

Hi Kallie,

I am guessing here, but it looks like your spreadsheet may be too big for the memory on the machine you are trying to do this on.  The only advice I could give is to add additional RAM to that machine.

 

Matt

0
Kallie
Top achievements
Rank 1
answered on 23 Apr 2017, 12:13 PM

Hi Matt.

That is a negative. It is on my dev notebook, 32gb of RAM. It is a 27mb spreadsheet, 140k lines, and about 15 columns. I opened up resource monitor before uploading, error comes up about 2 sec after hitting that line, ram never goes up above 8gb. So not sure ram is the issue. If i close, re-open solution, it jumps that line. Second time it hits the error. Not sure of the Asyncupload dumps/flush the memory stream after processing the opened file. It will continue until i unload and reload the whole project by opening and closing the solution, rebuild and run again.

Kind Regards

Kallie

 

 

 

 

Tags
Grid
Asked by
Matt
Top achievements
Rank 1
Answers by
Kostadin
Telerik team
Matt
Top achievements
Rank 1
Kallie
Top achievements
Rank 1
Share this question
or