DataReader to Excel 2007 Stream to Download

Aspose.Cells Product Family
1

Hi


I am using Aspose Cells to read a DataReader into an Excel 2007 file and then stream to the user (instead of saving the file to the server).

I have it working, however, on large amounts of Data, it seems that the ImportDataReader takes too much time and things time out.

Here is the function that returns the stream of xlsx file:

Dim workbook As Workbook

workbook = New Workbook(FileFormatType.Xlsx)
Dim worksheet = workbook.Worksheets(0)

worksheet.Cells.ImportDataReader(DirectCast(DirectCast(dr, RefCountingDataReader).InnerReader, System.Data.SqlClient.SqlDataReader), 0, 0, False)

Dim ms As New System.IO.MemoryStream
workbook.Save(ms, SaveFormat.Xlsx)
ms.Seek(0, SeekOrigin.Begin)

HttpContext.Current.Response.Clear()
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache)
HttpContext.Current.Response.ContentType = Enums.GetDescription(_mimeType)
HttpContext.Current.Response.AppendHeader(“Content-Disposition”, String.Format(“attachment; filename={0}{1}”, _fileName, If(Path.HasExtension(_fileName), “”, Enums.GetDescription(_fileExportFormat))))
HttpContext.Current.Response.Charset = “”
HttpContext.Current.Response.BinaryWrite(ms.ToArray())
…close response

My question is, is there a way with Aspose to stream the xlsx file as it is being created? This would allow the code to begin streaming before the whole entire datareader is imported into the workbook.

Thanks for all your help!

Daniel
2

Hi,


Please try the latest version/fix: Aspose.Cells for .NET v7.1.0.1 and let me if it makes any difference.

Also, we recommend if you could try to use ImportDataTable() method instead (you have to put your data from back end to DataTable before using the method), it is more optimized/enhanced regarding performance.

If you still find the significant performance or other issue, I am afraid, we need a sample application to reproduce the issue on our end. You can create a sample console application (you may also use MS Access database accordingly) with v7.1.0.1, zip it and post it here to reproduce the issue on end. We will check it soon.

Thank you.
3

Thanks for the reply Amjad.


I can use ImportDataTable, but the reason I tried ImportDataReader was because the DataReader allows me to process data before the whole dataset is present. This is what I am aiming at.

In an ASP.NET environment, can you tell me that ImportDataTable will work reasonably fast for large amounts of data, such as 650,000 rows of 60 columns each. What kind of performance can be expected when we need to gather the whole DataTable into a worksheet and workbook, and then stream that whole workbook? Isn’t there a better way to do this - to stream row by row or something?

Thanks

Daniel

4

Hi,


Well, in ASP.NET or Desktop environment, it should work fine for your huge data list e.g “650,000 rows of 60 columns”. But, since this is big process, so you should have sufficient amount of memory available. Also, the generated file would be also huge when you save the file.

I am afraid, we do not support to import data streaming line by line. Anyways, please try it with latest version. If you find any significant performance issue, let us know with a simulation project/application here, we will check it soon.

Thank you.