How to load very large file into Spreadsheet control?

[Wayne Wood]

i have a very large xls file whose size is nearly 30 M, i can export it
into XML format via Excel, and the target XML file is nearly 90 M. then
you can imagine that when i use Spreadsheet.XMLData to load the file
into Spreadsheet control, the program hangs...

the detailed code is listed here:

Dim reader As StreamReader = New StreamReader("xml_file_name")
Dim buffer As String = reader.ReadToEnd()
AxSpreadsheet1.XMLData = buffer

you can imagine the memory consumption is so big, and the code runs so
slow. i know reading such a large file into only one string variable
will be terrible, but if i do it several times, how can i give them into
Spreadsheet.XMLData property to load? do you have some good suggestions
thanks

in fact, i tried to copy the content from Excel into Spreadsheet, and
the process was also very slow. what's interesting is that when i
export the same content from Spreadsheet, also XML format, this file
achieved 160 M or so, which is so big, and is of so much redundant
information, because if i compressed the file, it fell into a very small
file, which is only 4 M or so.

thanks for listenning! if you have some good idea, please tell me

 

[Alvin Bruney [MVP - ASP.NET]]

neither excel nor the web components were designed to handle such data. this
behaviour is by design. you should look elsewhere for a scalable solution.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc

 

[Wayne Wood]

thanks but obviously excel can handle the large file i mentioned.
whatever, the capacity of OWC is really lower than excel to some extent.

Alvin Bruney [MVP - ASP.NET] дµÀ:

 

[Alvin Bruney [MVP - ASP.NET]]

I see what you are saying. But desktop excel is limited to 60+ records,
after that it will choke.

If it makes any difference, it's the rendering part that defeats the OWC
specifically. The control will contain the data, but the rendering phase is
what pegs the CPU and causes it to hang. You see the same behaviour in a
datagrid control with a large dataset.

The trick here, and it is a hack, is to force the large dataset to render a
very small portion of the data. For instance, with the datagrid, you page
the control and limit it to displaying 10 records on the first page and the
large dataset will load in the datagrid. With excel OWC, you force the
visible range to be very small so that only a small portion of the data is
rendered.

In any case,you should see that if you have that much data to display you
really ought to be thinking of an application redesign instead of a cheap
hack.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc

thanks but obviously excel can handle the large file i mentioned.
whatever, the capacity of OWC is really lower than excel to some extent.

Alvin Bruney [MVP - ASP.NET] дµÀ:

neither excel nor the web components were designed to handle such data.
this
behaviour is by design. you should look elsewhere for a scalable
solution.