3

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'

4

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.

With Apex repeat we are able to export more than 10 K however if records are more than 50K it will give SOQL error

  • 2
    There used to be a limit to collections in Apex but that has been gone for a while. The only limit to Apex collections/lists now is the governor heap limit. – E.J. Wilburn Aug 15 '12 at 20:56
  • Edited, Just realized that stackexchange ate some of my formatting, I was trying to suggest a nested collection of collections at 1,000 elements each for output in VF. – ca_peterson Aug 15 '12 at 21:18
2

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?

  • The SOQL query rows limit is, and has been for quite some time, 50,000 (although it was long ago 10,000). You're right that visualforce repeat tags do limit iterating over individual collections to 1,000 elements. – ca_peterson Aug 15 '12 at 20:22
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

2

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!

1

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.

1

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<Asset__c> lstAsset = new List<Asset__c>();
   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<AssetInfo> lstAssetInfo = new List<AssetInfo>();
   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

  • if you are looking for a 1 click download of 30k raw records, and I think you are, there is no adequate first party solution. I would implement your specific case by making a post to a third party webservice to export the records from the batch API and then converts the results into an excel file and sends the file back to browser. the round trip time on this sort of thing would probably be 30s to 2 minutes, a progress bar might be in order as well. – ebt Aug 15 '12 at 7:48
  • Hi ebt. thanks for your quick response.. I was able to query 10000 rows by using standardsetcontroller and Database.getquerylocator() I used this for Jquery grid paging. But I need to query some 78000 records now :( Am trying to call this same database.getquerylocator() from a batchable apex class and i have populated the list inside batchable class. Am unable to get the data from the list inside the batchable class, from my caller apex class.. I have even used actionfunction to check the status and get the data from the list. but its returning null.. can u pls help? :( – Sathya Aug 21 '12 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.