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

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

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

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

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

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