function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
ckellieckellie 

Visualforce to excel and convert range to table

The below visualforce page renders in excel appropriately, but I would like to take the functionality further.


 

<apex:page controller="QuoteProductCostSalesSheetExcel" tabStyle="Opportunity" contenttype="application/vnd.ms-excel#Cost-Sales-Sheet.xls" cache="true" standardstylesheets="false" >
 <apex:form > 
 
 <apex:pageBlock title="SE Margin Spreadsheet for {!opname}"  >
<h3>All currency is in {!currencyisocode}.</H3>
<apex:dataTable title="SE Margin Spreadsheet for {!opname}" border="1" value="{!TheExcelWrapper}" var="Wrapper" cellpadding="5" id="theTable3" rowClasses="odd,even" styleClass="tableClass">

           <apex:column >
                <apex:facet name="header">Sort Order</apex:facet>
                <apex:outputText value="{!Wrapper.column1}"/>
          </apex:column>
          
          <apex:column >
                <apex:facet name="header">Product Name</apex:facet>
                <apex:outputText value="{!Wrapper.column2}"/>
          </apex:column>
    
          <apex:column style="text-align:right;" >
                <apex:facet name="header">Unit Price</apex:facet>
                <apex:outputtext value="{!if(Wrapper.column3==null,'',Currsign)}{!
                                                    If(len(text(Wrapper.column3))==6, left(text(Wrapper.column3),3)&','&right(text(Wrapper.column3),3),
                                                        If(len(text(Wrapper.column3))==5, left(text(Wrapper.column3),2)&','&right(text(Wrapper.column3),3),
                                                            If(len(text(Wrapper.column3))==4, left(text(Wrapper.column3),1)&','&right(text(Wrapper.column3),3),Wrapper.column3)))}"/>

          </apex:column>

          <apex:column style="text-align:right;" >
                <apex:facet name="header">Quantity</apex:facet>
                <apex:outputText value="{!If(right(text(Wrapper.column4),3)=='.00',left(text(Wrapper.column4), Len(text(Wrapper.column4))-3),text(Wrapper.column4))}"/>

          </apex:column>

          <apex:column style="text-align:right;" >
                <apex:facet name="header">Cost Each</apex:facet>
                
                <apex:outputText value="{!if(Wrapper.column5==null,'',Currsign)}{!If(len(text(Wrapper.column5))==6, left(text(Wrapper.column5),3)&','&right(text(Wrapper.column5),3),
                                                        If(len(text(Wrapper.column5))==5, left(text(Wrapper.column5),2)&','&right(text(Wrapper.column5),3),
                                                            If(len(text(Wrapper.column5))==4, left(text(Wrapper.column5),1)&','&right(text(Wrapper.column5),3),Wrapper.column5)))}"/>

          </apex:column>

          <apex:column style="text-align:right;" >
                <apex:facet name="header">Before Discount FM (%)</apex:facet>
                <apex:outputText value="=[Quantity]"/>
                <!--      <apex:outputText value="{!Wrapper.column6}"/>-->

          </apex:column>

          <apex:column style="text-align:right;" >
                <apex:facet name="header">Subtotal</apex:facet>
                <apex:outputText value="{!Wrapper.column7}"/>

          </apex:column>
                    
          <apex:column style="text-align:right;" >
                <apex:facet name="header">Discount(%)</apex:facet>
                <apex:outputText value="{!Wrapper.column8}"/>

          </apex:column>  

          <apex:column style="text-align:right;" >
                <apex:facet name="header">After Discount Amount</apex:facet>
                                <apex:outputtext value="{!Wrapper.column9}"/>
          </apex:column>  

          <apex:column style="text-align:right;" >
                <apex:facet name="header">After Discount FM (%)</apex:facet>
                <apex:outputText value="{!Wrapper.column10}"/>
          </apex:column>  

                         
          <apex:column style="text-align:right;" >
                <apex:facet name="header">Total Price</apex:facet>
                <apex:outputtext value="{!Wrapper.column11}"/>
          </apex:column>

          <apex:column style="text-align:right;" >
                <apex:facet name="header">Discount Error Notification</apex:facet>

          </apex:column>

    </apex:dataTable>
<apex:dataTable title="SE Margin Spreadsheet for {!opname}" border="1" value="{!TheTotalWrapper}" var="tWrapper" cellpadding="5" id="theTable2" rowClasses="odd,even" styleClass="tableClass">



           <apex:column >

                <apex:outputText value="{!tWrapper.column1}"/>

          </apex:column>
          
          <apex:column >

                <apex:outputText value="{!tWrapper.column2}"/>

          </apex:column>
          

          <apex:column style="text-align:right;" >

                <apex:outputtext value="{!If(len(tWrapper.column3)==6, left(tWrapper.column3,3)&','&right(tWrapper.column3,3),
                                                        If(len(tWrapper.column3)==5, left(tWrapper.column3,2)&','&right(tWrapper.column3,3),
                                                            If(len(tWrapper.column3)==4, left(tWrapper.column3,1)&','&right(tWrapper.column3,3),tWrapper.column3)))}"/>

          </apex:column>


           <apex:column style="text-align:right;"  >

                <apex:outputText value="{!tWrapper.column4}"/>

          </apex:column>


           <apex:column style="text-align:right;"  >

                <apex:outputText value="{!tWrapper.column5}"/>

          </apex:column>


           <apex:column style="text-align:right;"  >

            <apex:outputText value="{!tWrapper.column6}"/>

          </apex:column>          


           <apex:column style="text-align:right;"  >
            
            <apex:outputText value="{!tWrapper.column7}"/>

          </apex:column>          

  <!--After discount amount            -->

           <apex:column style="text-align:right;"  >

            <apex:outputText value="{!tWrapper.column8}"/>


          </apex:column>

           <apex:column style="text-align:right;" >

                <apex:outputText value="{!tWrapper.column9}"/>

          </apex:column>


           <apex:column style="text-align:right;" >

                <apex:outputText value="{!tWrapper.column10}"/>

          </apex:column>

           <apex:column style="text-align:right;" >

                <apex:outputText value="{!tWrapper.column11}"/>

          </apex:column>

           <apex:column style="text-align:right;" >

                <apex:outputText value="{!tWrapper.column12}"/>

          </apex:column>

      </apex:datatable>
      <apex:facet name="Footer">All numbers are displayed in {!Currsign}</apex:facet>
    </apex:pageblock>
    </apex:form>
</apex:page>
In excel, if I highlight the rendered datatable, I can click insert table in the excel menu to convert the cells to a table. Is there any way I can programmically make the datatable an excel table?