Solved

Excel export not happening for more than 4 mb data in ASP and vbscript.

Posted on 2009-06-29
9
819 Views
Last Modified: 2012-05-07
Hello Experts,
Here is the scenario:
I have a website in ASP/ vbscript, IIS 6.0
I run a query in Oracle 10g and get the data in an array on the front end (ASP).
The array is then used to export data in excel and shown to users.
It works fine for excel size less than 4 mb but if the excel size increases to more than 4 mb I get the error: Page cannot be displayed.
I was wondering if there is some setting that i need to do for allowing the export of data to excel which is more than 4 mb.
Any suggestions are welcomed.
Thank You.
0
Comment
Question by:Faheem Shaikh
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 11

Expert Comment

by:tobzzz
ID: 24736557
Have you tried these type of setting? Note, figures are just example numbers.
<%
Session.Timeout=30
Server.ScriptTimeout=90
Response.Expires=0
%>

Open in new window

0
 
LVL 8

Author Comment

by:Faheem Shaikh
ID: 24736696
Hi TOBZZZ-
Thanks for looking into this, but its not a time out issue. But I will give this a try too.
 
0
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24736750
Also check if the data you are exporting is exceeding excel's limit of 65536 rows in a worksheet
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 8

Author Comment

by:Faheem Shaikh
ID: 24736779
The max number of records is just 2000. It will never exceed to more than that. So excel's limit is never reached.
0
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24736798
If the data results in more than 65536 rows when exported to excel, you may have to partition the data into multiple excel sheets. Excel can only take a maximum of 65536 rows in a worksheet and 256 worksheets in a workbook.
0
 
LVL 11

Expert Comment

by:tobzzz
ID: 24737177
if you just write it to screen, not create as an excel doc, does it still time out? If it was me, I would run the query and display on screen to eliminate if it is writing to excel that's the problem.
0
 
LVL 11

Expert Comment

by:tobzzz
ID: 24737191
sorry, not time out, but fail...... does it still fail?
0
 
LVL 8

Accepted Solution

by:
lharrispv earned 500 total points
ID: 24737536
I think I might know what your problem is.  Google for AspMaxRequestEntityAllowed and Asp Response buffer.

A few years back I was trying to do something very similar and I had to up those values in the metabase.xml file (IIS 6).  For IIS 5 it is not as easy but can still be changed.

In IIS 6 MS made smaller the amount of data that can be transfered via the http response for security reasons.  I think 4 mg is around near the new default.
0
 
LVL 8

Author Comment

by:Faheem Shaikh
ID: 24742971
Hello lharrispv,
That is exactly what i was looking for. My Sys Admin also pointed me towards the same.
I have set the AspBufferingLimit="26214400" --> 25 MB in metabase.xml file at c:\Windows\System32\Inetsrv. The default was 4 MB. AspBufferingLimit is used to set the Response limit.
Also, AspMaxRequestEntityAllowed  is actualy used to set the upload file limit to the server i.e. Request limit.
Thank You.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question