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

Import to RadGrid from Uploaded spreadsheet

6 Answers 1318 Views

Grid

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

6 Answers, 1 is accepted

Accepted

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