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

Import to RadGrid from Uploaded spreadsheet

6 Answers 1396 Views

Grid

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

6 Answers, 1 is accepted

Accepted

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.

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">

<html xmlns="http://www.w3.org/1999/xhtml">

<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

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

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()

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