Visualforce to excel for more than 20K records

Question

I need to export a VF page with contentType="application/vnd.ms-excel#Patents.xls" with a datatable fetching from a list with more than 20000 or 30000 records to an excel.. i changed the type to readonly=true to support 10000 records how to support for 20K or 30K records? getting this error : 'Collection size 10001 exceeds maximum size of 1,000'

Answer 1

I am afraid this is a hard coded limit of 10000 for you to retrieve and then page through with a maximum display of 1000 (hence why the second number in your error is 1000).

My recommendation would be that you just export this data in a data export run? Why do you need it to be displayed through Visualforce in Excel?

Answer 2

If you have EE or higher you should look at using the Salesforce Data Loader or the Jitterbit Dataloader to export the data in to CSV format.

This will support an arbitrary number of rows (up to the API limit available to you). I recall it may be able to export to Excel directly

http://www.jitterbit.com/Files/Product/JitterbitDataLoaderDatasheet.pdf

Answer 3

Why not simply run a report and use the built in export to Excel? Is the data that you want to export not "reportable" in the combination you want?

If you are doing some complex joins, aggregations, etc that requires processing you may want to look at more advanced Business Intelligence reporting tools on the AppExchange, think about getting the data into a data warehouse for which you already have a BI tool running (can use as @Steven Herod suggested a tool like Apex Data Loader, Jitterbit, Informatica Cloud, Pervasive, Boomi, ForceAmp, you get the idea lots of options...), you could write some Java or .Net code that runs those queries locally, you could write some Apex Web Services that could do your queries/aggregations on the server and then spit out results to either some client side Javascript in the VF page or other, for that matter you could use the AJAX toolkit and do it all client side Javascript - lots of data over the wire and lots of memory consumed on the client though!

Answer 4

Are you summarizing the data into a report or exporting all of the data?

If summarizing
Try creating a batch class, aggregate the data into a single record, then when the batch is done, call the record and generate your VF page.

If exporting raw data: create a batch record and limit the batch size to one, use a callout to send records individually to a third party web-service. collect the data into a single excel and export.

Limitations:
- only 5 batches can be running at one time.
- batch is asynchronous

you can poll using javascript to determine when the batch request is done and then initiate the download.

Answer 5

Thanks for all your replies. Actually Karan posted this question for me..
Thanks Karan.

Ebt :-

    Actually the requirement is like I need to export about 30000 records (Assets). 
Its a wrapper class data, Am showing the same in the page by converting to JSON format 
and showing about 100 records per page in JQGrid using SOQL offset 
and framing the wrapper class set.

Ex:- 

    Public String returnJSON {get;set;}
    // Wrapper class
    class AssetInfo
    {
       public string AssetId{get;set;}
       public string Name{get;set;}
       public Date ModifiedDate{get;set;}
       public string Status{get; set;}
    }

//All variables mentioned below starting with Param are populated 
//from PageParameters (Querystring). I havent mentioned the code for that here
    populateAssetInfo()
    {
       List lstAsset = new List();
       if (ParamAction == 'ExcelExport')
       {
        // This would return 30000 records when clicked on export button from the page
        lstAsset = [Select ID, Name,LastModifiedDate from Asset__c];
       }
       else
       {
          // This would return the records from the given offset based on 
          // which page the user is currently in the JQGrid
          String SOQL = 'Select ID,Name,LastModifiedDate from Asset__c';
          SOQL += ' offset ' + ParamOffset;
          SOQL += ' Limit ' + ParamPageSize;
          lstAsset = Database.Query(SOQL);
       }
       List lstAssetInfo = new List();
       for (Asset__c asst : lstAsset)
       {
           // Actually the reason for having wrapper class is that it 
           //has more logic involved in it. 
           //I have just given a sample for easy understanding
           AssetInfo asstInfo = new AssetInfo();
           asstInfo.Name = asst.Name;
           asstInfo.Status = (LastModifiedDate > System.Today())?'New':'Expired';
           lstAssetinfo.Add(asstInfo);
       }    

       // using the below JSON and some additional formatting needed for JQGrid 
       // am populating the returnJSON variable and showing in the Grid
       retrunJSON = JSON.Serialize(lstAssetInfo);

    }

I have another Page called AssetExport. On clicking the export button from the page 
bound to this controller, the code inside this block : 
if (ParamAction == 'ExcelExport') will be called. 
And even the AssetExport Page uses the same controller and it has a 
datatable / pageblocktable showing data from this lstAssetInfo.

Now if this lstAssetInfo has about 30000 rows.. How can I make it work without 
any viewstate error and collectionsize error? Hope you all can understand my question..
The problem here is I cant use a data loader here... this page is exposed to the 
customers through authenticated sites (via partner portal access).
Please suggest me a right approach...

Regards
Sathya

Answer 6

The collection limit of 1,000 is applied per-collection by the visualforce system when rendering. In effect it means that the apex:repeat tag doesn't support more than 1,000 iterations.

However you can nest collections in apex, List<List<sObject>>, and ensure that the inner lists contain no more than 1,000 elements each.

In your visualforce you can then nest repeat tags within repeat tags (assuming there List<List<sObject>> structure is accessible as a variable named "uglyHack"):

<apex:repeat value="{!uglyHack}" var="subList">
  <apex:repeat value="{!subList}" var="record">
    {!record[someField]}
  </apex:repeat>
</apex:repeat>

Ah the joys of CollectionLimiter.