I have huge excel files that I have to open from web browser. It takes several minutes to load huge file. Is it possible to open a single worksheet (single tab) at a time from excel file that contains many worksheets? I have to do this using C# / asp.net MVC
Asked
Active
Viewed 2,812 times
5
-
If you need to open the file on the client, then the server has nothing to do with it. – John Saunders Feb 27 '10 at 17:30
-
You could use the OpenXML Format SDK to extract specific spreadsheets and serve those to the client as their own workbook. I'm not sure what you mean by "open a single spreadsheet at a time from excel file that contains many spreadsheets" but you can check out the OpenXML usage and see if it works for you: http://msdn.microsoft.com/en-us/library/dd440953.aspx – Joseph Yaduvanshi Feb 27 '10 at 18:04
-
To make it clear I change my question slightly. Thanks for the replies so far... they are all helpful. – Venkata Uma Lakkakula Feb 28 '10 at 15:54
-
If a client has to upload an excel file and you have to read it server side, I would recommend http://npoi.codeplex.com/ Give it a try. If this is what you want to do (It is not clear from your question) and you want more info, send me a msg – Francisco Mar 04 '10 at 06:15
6 Answers
1
I'm assuming you have the excel workbook on the server and just want to send a single worksheet to the client. Does the user then edit the worksheet? Will they be uploading it back?
Assuming this is just a report then why not use the OpenXML sdk to read the workbook, extrac the sheet in question and send it back to the client? This is what @Jim in the comments was suggesting. You can get the SDK here: Open XML SDK 2.0 for Microsoft Office . However, I'm not sure if it will work with the 'old' excel format. I assume you'll need to save the template workbook in the new Office formats (xslx).
Jake
- 906
- 10
- 23
0
Your question is slightly unclear as to where the spreadsheet is stored.
If it's on a server you control, process it, extracting sheets you need, and create other sheets which are smaller in size. (Or possibly save them in a different format.).
If they're not on a server you control, download the file using C#, then go through a similiar process of extracting the sheet before opening it.
Having said that, I've dealt with some largish spreadsheets (20MB or so), and haven't really had a problem processing the entire spreadsheet as a whole.
So where is the bottleneck? Your network or possibly the machine you're running?
Bravax
- 10,453
- 7
- 40
- 68
-
BTW, when you process it on the server, do not use the Excel automation API to do so. It is designed for a desktop application, and does not work properly in a server environment. – John Saunders Feb 27 '10 at 18:01
0
Use third party components.
We are fighting with server side Excel generation for years and has been defeated.
We bought third party components and all problems gone.
Sergey Mirvoda
- 3,209
- 2
- 26
- 30
-
@AMissico Why? Anyway good luck with server side Excel. I sure this is dead end. It never will work because of simple reason - Excel is CLIENT app. – Sergey Mirvoda Mar 02 '10 at 14:19
-
1
-
Excel Automation is "client application". Excel Workbook is "data store". He wants to serve data, not automate Excel to retrieve data. Therefore, you answer is not relevant to question. – AMissico Mar 02 '10 at 14:38
-
@AMissico Excel OleDb provider is far from ideal. Globalization is the root of all evil. Decimal point and thousand separator are biggest problems for us non US users. TypeGuessRows another one. – Sergey Mirvoda Mar 02 '10 at 15:16
-
This is not a discussion about technology. I am only explaining my down vote. Provide an answer. Don't throw out words. – AMissico Mar 02 '10 at 20:44
0
Take a look at this question in StackOverflow:
Create Excel (.XLS and .XLSX) file from C#
I think you can open your workbook on the server (inside your ASP.NET MVC application) and process only the specific worksheet you want. You can then send such worksheet to the user using NPOI.
The following post shows you how to do that using an ASP.NET MVC application:
Community
- 1
- 1
Leniel Maccaferri
- 100,159
- 46
- 371
- 480
0
From your question, it seems you want to improve load time by using (opening) the data from one worksheet instead of the whole workbook. If this is the case and you only want the data, then access the workbook using ADO.NET with OLEDB provider. (You can use threading to load each worksheet to improve load performance. For instance, loading three large data sets in three worksheets took 17 seconds. Loading each worksheet on a separate thread, loaded same data sets in 5 seconds.)
From experience, performance starts to really suffer with workbooks of 40MB or more. Especially, if workbooks contain many formulas. My largest workbook of 120MB takes several minutes to load. Using OLEDB access, I can load, access, and process the same data in a few seconds.
If you want the client to open data in Excel, gather data via ADO.NET/OLEDB, get XML and transform into XMLSS using Xslt. Which is easy and there is much documentation and samples.
If you just want to present the data, gather data via ADO.NET/OLEDB, get XML and transform into HTML using Xslt. Which is easy and there is much documentation and samples.
Be aware that the browser and computer become non-responsive with large data sets. I had to set limit upper limit. If limit was reaced, I notified user of truncated results, otherwise, user thought computer was "locked".
AMissico
- 21,470
- 7
- 78
- 106
0
You can't "say" to Excel, even via Interop that you only want a single worksheet. There are a lot of explanations, like formulas, references and links between them, which makes the task impossible.
If you only want to read the data from the worksheet, maybe OLEDB Data Provider is the best option for you. Here is a full example: Reading excel file using OLEDB Data Provider
Otherwise, you will need to load the entire workbook in memory before do anything with it.
