1

There is a VF Page where we have Export button There is no issue when exporting single record excel opens but while exporting multiple records excel is getting corrupted .

Please Help!!! This Class contains preparation of worksheet

global class AnalyzeObjectExportCtrl {

  public AnalyzeObjectExportCtrl(){

  }
    public static void exportMultipleToExcel(List<ID> selectedIDs){
        List<Object_Analysis__c> objAnalysisList = new List<Object_Analysis__c>();
        objAnalysisList =  [select name,Field_Count__c,Filter__c,Object_Label__c,Object_Name__c,Record_Count__c,Record_Types_Count__c,
                        (select name,Label__c,Populated_On__c,Populated_On_per__c,Type__c from Field_Analysis__r order by Populated_On__c DESC ) 
                            from Object_Analysis__c where id IN :selectedIDs ]; 
        string excelXML = prepareHeaderAndStyles();
        for(Object_Analysis__c obj : objAnalysisList){
            excelXML += prepareWorkSheet(obj);
        }
        excelXML += '</ss:Workbook>';  
        Messaging.EmailFileAttachment attach = new Messaging.EmailFileAttachment();
        attach.setFileName('MultipleObjectAnalysis.xls');
        attach.setInline(false);
        attach.setBody(Blob.valueof(excelXML));

        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
        mail.setUseSignature(false);

        User u = [select email from user where id=:userinfo.getUserId()];
      mail.setToAddresses(new List<string>{u.email});
        mail.setSubject('Object Analysis : Multiple Record Analysis Export');
        string mailBody = '<p> Hi, </p><p> Your Request for Multiple Object Analysis export to Excel file is completed. please find the attachment. </p> ';
        mailBody += 'Regards, <br/> Object Analysis';
        mail.setHtmlBody(mailBody);
        mail.setFileAttachments(new Messaging.EmailFileAttachment[] { attach }); 

        // Send the email
        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

        /*Blob bv = Blob.valueof(excelXML);
        Attachment d = new Attachment(); 
        d.name = 'MultipleObjectAnalysis.xls';
        d.ContentType = 'application/vnd.ms-excel';
        d.Body = bv;
        d.ParentId = objAnalysisList[0].id;
        insert d;*/

    }
  WebService static String exportToExcel(String objAnlyID) {

  if(String.isNotBlank(objAnlyID)) {
      try{
            Object_Analysis__c objAny = [select name,Field_Count__c,Filter__c,Object_Label__c,Object_Name__c,Record_Count__c,Record_Types_Count__c,
                                   (select name,Label__c,Populated_On__c,Populated_On_per__c,Type__c from Field_Analysis__r order by Populated_On__c DESC ) from Object_Analysis__c where id =:objAnlyID ]; 
        string filter  = (!string.isBlank(objAny.Filter__c)) ? objAny.Filter__c : ''; 
            string s = prepareHeaderAndStyles();
                s += prepareWorkSheet(objAny);
        s += '</ss:Workbook>'; 

        Blob bv = Blob.valueof(s);
        Attachment d = new Attachment(); 
        d.name = objAny.name + '.xls';
        d.ContentType = 'application/vnd.ms-excel';
        d.Body = bv;
        d.ParentId = objAny.id;
        insert d;

            return 'OK';    
          }catch (Exception e) {
                return 'ERROR Message:' + e.getMessage();
          }
        }else {
            return 'ERROR Message: Object Analysis record ID is not specified ';
        }
    }
    public static string prepareWorkSheet(Object_Analysis__c objAny){
        string filter  = (!string.isBlank(objAny.Filter__c)) ? objAny.Filter__c : ''; 
        string s = '<ss:Worksheet ss:Name="'+  objAny.name + '">';
           s += '<ss:Table ss:StyleID="st2"  x:FullColumns="1" x:FullRows="1"><ss:Column ss:Width="170"/><ss:Column ss:Width="200"/><ss:Column ss:Width="100"/><ss:Column ss:Width="100"/><ss:Column ss:Width="100"/>';
          //Object Analysis details
          s += '<ss:Row ss:Height="35"><ss:Cell ss:MergeAcross="4" ss:StyleID="st4"><ss:Data ss:Type="String" >Object Analysis : ' + objAny.name   + '  </ss:Data></ss:Cell></ss:Row>';
          s += '<ss:Row><ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Object Analysis Name  </ss:Data></ss:Cell><ss:Cell ss:MergeAcross="3"><ss:Data ss:Type="String" > ' + objAny.name  + ' </ss:Data></ss:Cell></ss:Row>';
            s += '<ss:Row><ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Object Label  </ss:Data></ss:Cell><ss:Cell ss:MergeAcross="3"><ss:Data ss:Type="String" > ' + objAny.Object_Label__c  + ' </ss:Data></ss:Cell></ss:Row>';
          s += '<ss:Row><ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Object Name  </ss:Data></ss:Cell><ss:Cell ss:MergeAcross="3"><ss:Data ss:Type="String" > ' + objAny.Object_Name__c  + ' </ss:Data></ss:Cell></ss:Row>';
          s += '<ss:Row><ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Field Count  </ss:Data></ss:Cell><ss:Cell ss:MergeAcross="3"><ss:Data ss:Type="Number" >' + objAny.Field_Count__c  + '</ss:Data></ss:Cell></ss:Row>';
            s += '<ss:Row><ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Row Count  </ss:Data></ss:Cell><ss:Cell ss:MergeAcross="3"><ss:Data ss:Type="Number" >' + objAny.Record_Count__c  + '</ss:Data></ss:Cell></ss:Row>';
            s += '<ss:Row><ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Filter  </ss:Data></ss:Cell><ss:Cell ss:MergeAcross="3"><ss:Data ss:Type="String" > ' + filter  + ' </ss:Data></ss:Cell></ss:Row>';
            s += '<ss:Row ss:Height="35"><ss:Cell ss:MergeAcross="4" ss:StyleID="st4"><ss:Data ss:Type="String" >Field Analysis </ss:Data></ss:Cell></ss:Row>';
            // header row START ==================>
          s += '<ss:Row><ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Field Label</ss:Data></ss:Cell>';
          s += '<ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Field Name</ss:Data></ss:Cell>';
          s += '<ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Field Type</ss:Data></ss:Cell>';
          s += '<ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Populated On</ss:Data></ss:Cell>';
          s += '<ss:Cell ss:StyleID="st1"><ss:Data ss:Type="String" >Populated On %</ss:Data></ss:Cell>';
          s += '</ss:Row>';
          // header row END ==================>
          // DATA ROWS
          for(Field_Analysis__c fieldAny : objAny.Field_Analysis__r){
            s += '<ss:Row><ss:Cell><ss:Data ss:Type="String">' + fieldAny.Label__c + '</ss:Data></ss:Cell>';
            s += '<ss:Cell><ss:Data ss:Type="String">' + fieldAny.Name  + '</ss:Data></ss:Cell>';
            s += '<ss:Cell><ss:Data ss:Type="String">' + fieldAny.Type__c  + '</ss:Data></ss:Cell>';
            s += '<ss:Cell ss:StyleID="st3"><ss:Data ss:Type="Number">' + fieldAny.Populated_On__c  + '</ss:Data></ss:Cell>';
          s += '<ss:Cell ss:StyleID="st3"><ss:Data ss:Type="Number">' + fieldAny.Populated_On_per__c + '</ss:Data></ss:Cell></ss:Row>';
          }
        s += '</ss:Table>';
        s += '<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">';
            s += '<Selected/>';
            //s += '<DoNotDisplayGridlines/>';
            s += '<ProtectObjects>False</ProtectObjects>';
            s += '<ProtectScenarios>False</ProtectScenarios>';
            s += '</WorksheetOptions>';
        s += '</ss:Worksheet>';
        return s;
    }
    public static string prepareHeaderAndStyles(){
        string s = '<?xml version="1.0"?>';
            s += '<?mso-application progid="Excel.Sheet"?>';
            s += '<ss:Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'; //+ '\n';
          s += ' xmlns:o="urn:schemas-microsoft-com:office:office"'; // + '\n';
          s += ' xmlns:x="urn:schemas-microsoft-com:office:excel"' ; //+ '\n';
          s += ' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"' ; //+ '\n';
          s += ' xmlns:html="http://www.w3.org/TR/REC-html40">';
          s += '<ss:Styles>';
            s += '<ss:Style ss:ID="st1" ss:Name="Normal">';       
            s += '<ss:Font ss:Bold="1"/>';
            s += '<ss:Alignment  ss:Horizontal="Left"  ss:Vertical="Center"/>';
            s += '<ss:Interior ss:Color="#C7C7C1" ss:Pattern="Solid"/>';
            s += '</ss:Style>';
            s += '<ss:Style ss:ID="st2">';
            s += '<ss:Alignment ss:WrapText="1" ss:Horizontal="Left"  ss:Vertical="Center"/>';      
            s += '</ss:Style>';
            s += '<ss:Style ss:ID="st3">';
            s += '<ss:Alignment ss:Horizontal="Right"  ss:Vertical="Center"/>';      
            s += '</ss:Style>';
            s += '<ss:Style ss:ID="st4">';
            s += '<ss:Font ss:Size="14"  ss:Bold="1"/>';
            s += '<ss:Alignment ss:Horizontal="Center"  ss:Vertical="Center"/>';      
            s += '</ss:Style>';
            s += '</ss:Styles>';
        return s;
    }
}

Below is the Extension class

public class AnalyzeObjMultiExportCtrlExt {

    public ApexPages.StandardSetController ctrl;
   public AnalyzeObjMultiExportCtrlExt(ApexPages.StandardSetController controller) {
        //controller.setPageSize(10);
        ctrl = controller;
         if(ctrl.getSelected().size()==0)
             ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.info, 'Please Select atleast one Record to Export'));
    }
    public PageReference Export(){
        List<Id> objAnyIDs = new List<Id>();
        //system.debug(ctrl.getSelected()); 
        try{
            if(ctrl.getSelected().size()>0){
                for(sObject sObj : ctrl.getSelected()){
                    objAnyIDs.add(sObj.Id);
                }
                AnalyzeObjectExportCtrl.exportMultipleToExcel(objAnyIDs);
            }
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.CONFIRM, ' Your Request is being processed. once it is completed you will receive a mail with Excel Attachment'));
        }catch(system.Exception e){
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ' There is an Error while processing your request. Please try later or Contact your Administrtor '));
        }
        return null;
    }
}

Below is the Visualforce Page Code

<apex:page standardController="Object_Analysis__c" recordSetVar="ObjectsAnalysis" extensions="AnalyzeObjMultiExportCtrlExt" tabStyle="Object_Analysis__c"> 
    <apex:form >
        <apex:pageBlock title="Export To Excel" >
            <apex:pageMessages />
            <apex:pageBlockButtons location="top">
                <apex:commandButton value="Export" action="{!Export}"/>
                <apex:commandButton value="Go Back" action="{!cancel}"/>
            </apex:pageBlockButtons>
            <apex:pageBlockTable value="{!selected}" var="obj">
                <apex:column value="{!obj.name}"/>
                <apex:column value="{!obj.Object_Label__c}"/>
                <apex:column value="{!obj.Object_Name__c}"/>
                 <apex:column value="{!obj.Field_Count__c}"/>
            </apex:pageBlockTable>      
        </apex:pageBlock>
    </apex:form>
</apex:page>
  • 1
    What do you mean by corrupted, could you provide an image of what the generated excel document looks like? – Marc Zaharescu Mar 23 '16 at 12:02
  • Hi Marc Thank you for reply When I open the Excel it says 'Your File is corrupt and can't be opened.' – Rajesh Mar 23 '16 at 13:37
  • I beleive the problem exists in below code... string excelXML = prepareHeaderAndStyles(); for(Object_Analysis__c obj : objAnalysisList){ excelXML += prepareWorkSheet(obj); } excelXML += '</ss:Workbook>'; – Rajesh Mar 23 '16 at 13:47
  • hmm possible, could you perhaps try to write some debug statements to see if the expected values are the actual values? For example you could start with the objAny.size() – Marc Zaharescu Mar 23 '16 at 13:54
  • this problem is amenable to selective reduction -- remove almost all of the excel generation until you get something to work, then add bits back in – cropredy Mar 23 '16 at 15:50

Your Answer

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

Browse other questions tagged or ask your own question.