SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excel Export Fail when Number of rows in the Excel sheet exceeded the limit of 65536 rows.


Excel Export Fail when Number of rows in the Excel sheet exceeded the limit of 65536 rows.

Author
Message
rayabharapusuman
rayabharapusuman
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 280
Hi,

I have ran into the below error because of bulk and huge data. The error is given below:

"Error: System.Exception: Excel Rendering Extension : Number of rows in the Excel sheet exceeded the limit of 65536 rows. Message: Excel Rendering Extension : Number of rows in the Excel sheet exceeded the limit of 65536 rows. (Internal) Stack inner exception generated an error."

I know that the error is caused because of the more number of rows exceeding 65536.

Now i want to know how can i handle that in the SSRS 2005. I have a tabular report with header, details and footer section.

I tried assigning "=Int((RowNumber(Nothing) - 1) / 20)" as the expression when i right clicked on details section and selected the edit group option. And when i try to run this i get 9 pages becuase i have 175 records and the strange thing is only the first record is shown per page. And the sheet is broke in excel.

I donno why all other columns in the detail section are invisiible except the first record per page. only 1 record is shown out of 20 records and 21 out of 40 in second page and goes on.

If the records are visible my problem is solved. I will just replace that with 65536 so that the maximum number of records in excel are reached and then page break is applied. I dont know why are the data invisible.

Please tell me why the remaining items are getting invisible.

Any help would be greatly appreciated.

Thanks,
Suman
Bob Griffin
Bob Griffin
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3661 Visits: 702
The page break will not get you around the problem of max rows in excel. If you could maybe post some screen shots we might be able to offer some more information...
rayabharapusuman
rayabharapusuman
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 280
Thanks for the reply. Can you please give me solution to show the records in second worsksheeet in excel if the records excell 65536.

I need solution to show records even if they cross 65536 when exported to excel.

Please answer my question, any help would be appreciateed.

Thanks,
Suman
Bob Griffin
Bob Griffin
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3661 Visits: 702
The excel limit is a hard limit. The only way to work around that is to summarize the data more in your select statement (group by) or select a smaller range of data in your parameters i.e. date range, from/to etc...
GSquared
GSquared
SSC Guru
SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)

Group: General Forum Members
Points: 139045 Visits: 9731
The only other solution I can think of is paginate the query, and limit it to the max number of rows per sheet, and include the "page number" as an input parameter.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
TKD-BB
TKD-BB
Say Hey Kid
Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)

Group: General Forum Members
Points: 677 Visits: 172
In your table, try creating a group (outside of any current grouping you have) with no header or footer, and the page break after value set to true. For the grouping value, use an expression similar to

=ROWNUMBER(Nothing) MOD 60000

This should force a page break after 60000 rows of data, which will translate into a new excel tab when you export. I believe the 65K limit in Excel is on a per worksheet basis.
rayabharapusuman
rayabharapusuman
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 280
can you please let me know how i can paginate the query. It would be great if you can assist me in making this happen.

Is this all to be written on RDL file ?? Can you please make it more clear instead?

Thanks,
Suman
rayabharapusuman
rayabharapusuman
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 280
Thanks for the reply.

I could achieve that as you said:

Create a group outside the table with no headers and having page break at the end and the expression for grouping should be as given. 30 is the number for which you need a break. Similarly we can use 65000 but it is really failing bcoz of the grouping I suppose, not sure what might be the answer.
=ceiling(rownumber(nothing)/30)

when i use a less number like 30 40 100 200 it is ok, but when i use 65000 my RDLC file looks like it is hanged up and no report comes up. But when i remove that and execute the report for the same data it comes it easily on the rdlc files. And exporting to excel from this viewer is really failing i think it is becoz of the memory option.

But page break with 65000 is making the report come up very slowly compared to without pagination. Is there any way we can avoid this.
And export to excel fails in normal (without pagination) when rows exceed like 40 or 50k.

Please let me know if you have any answer.

Any help would be greatly appreciated.
SQLWannabe
SQLWannabe
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1518 Visits: 571
I'm thinking about a different route. I don't know what kind of "good" information can be contained in a +65K line Excel spreadsheet. I don't think people looking at that data could glean any meaningful info. Try to go back to your customer and find out what they're using this for. You may be able to deliver a better solution thru an SSAS report or some kind of Excel Pivot table that reads from an SSAS cube.

If you're just dumping informational rows of data, try using a different format, such as CSV. Then the user can use some other tool (Access, etc.) besides Excel to view the data. But I would still consider the reason for the report request.
GSquared
GSquared
SSC Guru
SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)

Group: General Forum Members
Points: 139045 Visits: 9731
There is a solution to go beyond 65k rows, which is use Excel 2007, which can go MUCH larger than that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search