Page:
<apex:page standardController="Quote" extensions="QuoteExportController" contentType="txt/xml#{!quote.Name}.xls" cache="true" id="quoteId" showheader="false">
<html xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink">
<head>
{!xmlheader}
<apex:includeLightning />
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
</head>
<body>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Worksheet ss:Name="Mine1">
<table id="monthly" style="width:95%; height: 5%;" cellpadding="2" border="1" cellspacing="0">
<tr align="center" style="height: 60px">
<th colspan="10" style="font-size: 40px">
{!quote.Name}
</th>
</tr>
<tr align="left" style="height: 55px;color: #F4F4F4;border:1rem;">
<th style="width:100px;background-color:#17428C;">LINE</th>
<th style="width:150px;background-color:#17428C;">REFERENCE</th>
<th style="width:150px;background-color:#17428C;">ITEM DESCRIPTION</th>
<th style="width:150px;background-color:#17428C;">ITEM</th>
<th style="width:150px;background-color:#17428C;">CUSTOM CODE</th>
<th style="width:150px;background-color:#17428C;">TOTAL WEIGHT TON</th>
<th style="width:100px;background-color:#17428C;">QUANTITY</th>
<th style="width:130px;background-color:#17428C;">UNIT PRICE {!currency1}</th>
<th style="width:100px;background-color:#17428C;">VALUE {!currency1}</th>
</tr>
<apex:variable var="index" value="{!0}" />
<apex:repeat value="{!QuoteLineItemInfoList}" var="LineItem">
<apex:variable var="index" value="{!index+1}" />
<tr align="center" style="height: 60px;">
<td>{!index}</td>
<td>{!LineItem.lineItem.product2.ProductCode}</td>
<td>{!LineItem.lineItem.product2.Name}</td>
<td>{!LineItem.lineItem.Customer_Code__c}</td>
<!-- <td>{!LineItem.customCode}</td>-->
<td>730300</td>
<td>
<apex:outputText value="{0, number, 0,000}" rendered="{!IF(LineItem.totalWeight>999,true,false)}">
<apex:param value="{!ROUND(LineItem.totalWeight,0)}" />
</apex:outputText>
<apex:outputText value="{!LineItem.totalWeight}" rendered="{!IF(LineItem.totalWeight>999,false,true)}">
</apex:outputText>
</td>
<!-- <td>{!LineItem.totalWeight}</td>-->
<!-- <td>{!LineItem.Quantity} M</td>-->
<td>
<apex:outputText value="{0, number, 0,000.00}" rendered="{!IF(LineItem.Quantity>999,true,false)}">
<apex:param value="{!ROUND(LineItem.Quantity,0)}" />
</apex:outputText>
<apex:outputText value="{!LineItem.Quantity}" rendered="{!IF(LineItem.Quantity>999,false,true)}">
</apex:outputText>
M
</td>
<td>{!LineItem.lineItem.Sales_Price_M__c}</td>
<!-- <td >{!LineItem.totalValue}</td>-->
<td>
<apex:outputText value="{0, number, 0,000.00}" rendered="{!IF(LineItem.totalValue>999,true,false)}">
<apex:param value="{!ROUND(LineItem.totalValue,0)}" />
</apex:outputText>
<apex:outputText value="{!LineItem.totalValue}" rendered="{!IF(LineItem.totalValue>999,false,true)}">
</apex:outputText>
</td>
</tr>
</apex:repeat>
<tr align="center" style="height: 30px;">
<td colspan="5"><span style="font-weight: 500;font-size:15px ">SUMMARISE</span></td>
<td>
{!S_Ton}
<!-- <apex:outputText value="{0, number, 0,000.00}" rendered="{!IF(S_Ton>999,true,false)}">-->
<!-- <apex:param value="{!ROUND(S_Ton,0)}" />-->
<!-- </apex:outputText>-->
<!-- <apex:outputText value="{{0, number, 0000.00}}" rendered="{!IF(S_Ton>999,false,true)}">-->
<!-- <apex:param value="{!ROUND(S_Ton,0)}" />-->
<!-- </apex:outputText>-->
</td>
<td>
<apex:outputText value="{0, number, 0,000.00}" rendered="{!IF(S_Quantity>999,true,false)}">
<apex:param value="{!ROUND(S_Quantity,0)}" />
</apex:outputText>
/M
<!-- <apex:outputText value="{{0, number, 0000.00}}" rendered="{!IF(S_Quantity>999,false,true)}">-->
<!-- <apex:param value="{!ROUND(S_Quantity,0)}" />-->
<!-- </apex:outputText>-->
</td>
<td>
<!-- {!S_Price}/M-->
<!-- <apex:outputText value="{0, number, 0,000.00}" rendered="{!IF(S_Price>999,true,false)}">-->
<!-- <apex:param value="{!S_Price}" />-->
<!-- </apex:outputText>-->
<!-- <apex:outputText value="{{0, number, 0000.00}}" rendered="{!IF(S_Price>999,false,true)}">-->
<!-- <apex:param value="{!ROUND(S_Price,0)}" />-->
<!-- </apex:outputText>-->
</td>
<td>
<apex:outputText value="{0, number, 0,000.00}" rendered="{!IF(Total_Value>999,true,false)}">
<apex:param value="{!ROUND(Total_Value,0)}" />
</apex:outputText>
<!-- <apex:outputText value="{{0, number, 0000.00}}" rendered="{!IF(Total_Value>999,false,true)}">-->
<!-- <apex:param value="{!ROUND(Total_Value,0)}" />-->
<!-- </apex:outputText>-->
</td>
</tr>
<!-- <tr style="height: 15px;border: 0">-->
<!-- <th style="width:100px;border: 0"></th>-->
<!-- <th style="width:150px;border: 0"></th>-->
<!-- <th style="width:150px;border: 0;background-color:#C2D6F5">TOTAL WEIGHT</th>-->
<!-- <th style="width:150px;border: 0;background-color:#C2D6F5">:</th>-->
<!-- <th style="width:100px;border: 0;background-color:#C2D6F5">{!allTotalWeight}</th>-->
<!-- <th style="width:100px;border: 0;background-color:#C2D6F5">TOTAL AMOUNT</th>-->
<!-- <th style="width:100px;border: 0;background-color:#C2D6F5">:</th>-->
<!-- <th style="width:150px;border: 0;background-color:#C2D6F5">{!allTotalAmount}</th>-->
<!-- <th style="width:150px;border: 0"></th>-->
<!-- </tr>-->
</table>
</Worksheet>
<table>
<tr></tr>
<tr></tr>
<tr></tr>
<tr></tr>
<tr></tr>
<tr></tr>
</table>
<Worksheet ss:Name="Mine2">
<table id="monthly" style="width:95%; height: 5%;" cellpadding="2" border="1" cellspacing="0">
<tr align="center" style="height: 40px">
<th colspan="10" style="font-size: 25px">
{!quote.Name}`s Freight,Expenses and Profit Details
</th>
</tr>
<tr align="left" style="height: 55px;color: rgba(169,162,175,0.55);border:1rem;">
<th rowspan="2" style="width: 120px;font-weight: 600;">Freight</th>
<th style="background-color:rgba(78,175,69,0.55);">Local Transportation(CNY/T)</th>
<th style="background-color:rgba(78,175,69,0.55);">Port Charge (CNY/T)</th>
<th style="background-color:rgba(78,175,69,0.55);">Sea Freight (USD/T)</th>
<!-- <th style="background-color:rgba(78,175,69,0.55);">PRS Manual Correction</th>-->
<th style="background-color:rgba(78,175,69,0.55);">Destination freight (USD/T)</th>
<th rowspan="2" style="width: 120px;font-weight: 600;">Expenses</th>
<th style="background-color:rgba(78,175,69,0.55);">Import Duty (%)</th>
<th style="background-color:rgba(78,175,69,0.55);">Expense (%)</th>
<th style="background-color:rgba(78,175,69,0.55);">Commission (%)</th>
<th style="background-color:rgba(78,175,69,0.55);">FOB Rate (%)</th>
<th style="background-color:rgba(78,175,69,0.55);">Insurance (%)</th>
<th rowspan="2" style="width: 120px;font-weight: 600;">Profit</th>
<th style="background-color:rgba(78,175,69,0.55);">Default RBE (%)</th>
<th style="background-color:rgba(78,175,69,0.55);">PRS Correction</th>
</tr>
<tr>
<th>{!quote.Approach_CNY_T__c}</th>
<th>{!quote.Boarding_CNY_T__c}</th>
<th>{!quote.Sea_Freight_T__c}</th>
<!-- <th>{!quote.PRS_Manual_Correction__c}</th>-->
<th>{!quote.Local_Transportation_USD_T__c}</th>
<th>{!quote.Import_Duty__c}</th>
<th>{!quote.Expense__c}</th>
<th>{!quote.Commission__c}</th>
<th>{!quote.Tax_Rate__c}</th>
<th>{!quote.Insurance__c}</th>
<th>{!quote.Default_RBE__c}</th>
<th>{!PRS_Correction}</th>
</tr>
</table>
</Worksheet>
</Workbook>
<apex:outputText value="</Workbook>" escape="false"/>
</body>
</html>
</apex:page>
Apex :
/*
* Created by Administrator on 2019/12/9.
*/
public class QuoteExportController {
public String xlsHeader {
get {
String strHeader = '';
strHeader += '<?xml version="1.0"?>';
strHeader += '<?mso-application progid="Excel.Sheet"?>';
return strHeader;
}
}
public String xmlheader {
get;set;
}
public Quote quote {
get;set;
}
public List<QuoteLineItem> quoteLineItem {
get;set;
}
public List<QuoteLineItemInfo> QuoteLineItemInfoList {
get;set;
}
public Decimal allTotalWeight {
get;set;
}
public Integer allTotalAmount {
get;set;
}
public String currency1 {
get;set;
}
public Integer QuoteLineSize{
get;set;
}
public Double Total_Value{
get;set;
}
public Double S_Quantity{
get;set;
}
public Double S_Ton{
get;set;
}
public Double S_Price{
get;set;
}
public Decimal PRS_Correction{
get;set;
}
public QuoteExportController(ApexPages.StandardController controller) {
xmlHeader = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
Id recordId = ((Quote)controller.getRecord()).Id;
Quote quote = [
SELECT Id, Name, AccountId, Account.Name,OpportunityId
,Approach_CNY_T__c,Boarding_CNY_T__c,Import_Duty__c,Sea_Freight_T__c,PRS_Manual_Correction__c,Local_Transportation_USD_T__c,
Expense__c,Commission__c,Tax_Rate__c,Insurance__c,
Default_RBE__c,PRS_Correction__c
FROM Quote
WHERE Id =:recordId
];
this.quote = quote;
String oppID=quote.OpportunityId;
Opportunity opp=[Select CurrencyIsoCode FROM Opportunity WHERE Id=:oppID];
this.currency1=opp.CurrencyIsoCode;
List<QuoteLineItem> quoteLineItemList = [
SELECT Id, Product2Id,X111111__c, Product2.Description, Product2.Name,Product2.ProductCode,Product2.SAP_Description_In_Short__c,Product2.Diameter__c, Customer_Code__c, Quantity, Pipe_Weight_I_C__c, Sales_Price_M__c,PRS_Correction__c
FROM QuoteLineItem
WHERE QuoteId=:quote.Id
];
this.quoteLineItem = quoteLineItemList;
if(quoteLineItemList.size()>0){
this.PRS_Correction=quoteLineItemList.get(0).PRS_Correction__c;
}
List<QuoteLineItemInfo> infoList = new List<QuoteLineItemInfo>();
for (QuoteLineItem line : quoteLineItemList) {
QuoteLineItemInfo linItemInfo = new QuoteLineItemInfo();
linItemInfo.lineItem = line;
if (line.Quantity != null && line.Pipe_Weight_I_C__c != null) {
linItemInfo.totalWeight =line.Quantity * line.Pipe_Weight_I_C__c;
//linItemInfo.totalWeight =line.X111111__c;
}
linItemInfo.quantity = line.Quantity;
linItemInfo.price = line.Sales_Price_M__c;
if (line.Sales_Price_M__c != null && line.Quantity != null) {
linItemInfo.totalValue = line.Sales_Price_M__c * line.Quantity;
}
System.debug('linItemInfo------>' + JSON.serialize(linItemInfo));
// if(line.Product2.Diameter__c>=100&&line.Product2.Diameter__c<=600){
// linItemInfo.customCode=730300;
// }
infoList.add(linItemInfo);
}
Decimal weight = 0;
Decimal amount = 0;
for (QuoteLineItemInfo info : infoList) {
weight += info.totalWeight;
amount += info.totalValue;
}
this.allTotalAmount = Integer.valueOf(amount);
this.allTotalWeight =weight ;
System.debug('asdfasf--->'+this.allTotalWeight);
this.QuoteLineItemInfoList = infoList;
//saveFile(this.x);
this.QuoteLineSize=infoList.size();
Decimal t_value=0;
Decimal t_ton=0;
Decimal t_quantity=0;
Decimal t_price=0;
for(QuoteLineItemInfo quoteLineItemInfo : QuoteLineItemInfoList){
t_value+=quoteLineItemInfo.totalValue;
t_ton+=quoteLineItemInfo.totalWeight;
t_quantity+=quoteLineItemInfo.quantity;
t_price+=quoteLineItemInfo.price;
}
this.Total_Value=(Double)t_value;
this.S_Price=(Double)t_price;
this.S_Ton=(Double)t_ton;
this.S_Quantity=(Double)t_quantity;
System.debug('this is total ---- '+this.Total_Value);
}
public with sharing class QuoteLineItemInfo {
public QuoteLineItem lineItem {
get; set;
}
public Decimal totalWeight{
get;set;
}
public Decimal quantity{
get;set;
}
public Decimal price{
get;set;
}
public Decimal totalValue{
get;set;
}
public Decimal customCode{
get;set;
}
}
public String formattedNumber(Integer num) {
if (num != null && num != 0) {
List<String> stringList = String.valueOf(num).split('');
List<String> reverseList = new List<String>();
List<String> newStringList = new List<String>();
String numStr = '';
for (Integer i = stringList.size() - 1; i >= 0; i--) {
reverseList.add(stringList[i]);
}
for (Integer i = 0;i < reverseList.size();i++) {
if (i != 0 && MATH.mod(i, 3) == 0) {
newStringList.add(',');
}
newStringList.add(reverseList.get(i));
}
for (Integer i = newStringList.size() - 1; i >= 0; i--) {
numStr += newStringList.get(i);
}
return numStr;
} else {
return '';
}
}
public void saveFile(Integer x) {
PageReference ExportExcel = page.ExportExcel;
Attachment attachment = new Attachment();
Blob body = ExportExcel.getContent();
attachment.Body = body;
attachment.Name = this.quote.Name;
attachment.IsPrivate = false;
attachment.ParentId = Quote.Id;
insert attachment;
}
}
Test;
/*
* Created by Administrator on 2019/12/9.
*/
@isTest
public class QuoteExportControllerTest {
@isTest
static void doTest(){
User user = new User();
user.Username='testusername@163.com';
user.LastName='Test LastName';
user.Email='123456789@163.com';
user.Alias='Test';
user.TimeZoneSidKey='Asia/Tokyo';
user.LocaleSidKey='en_US';
user.EmailEncodingKey='ISO-8859-1';
user.ProfileId=UserInfo.getProfileId();
user.LanguageLocaleKey='zh_TW';
insert user;
//Test Account
Account account = new Account();
account.Name='Test Account Name';
account.Regional_Manager__c=user.Id;
account.Section_Sales_Director__c=user.Id;
insert account;
//Test Opportunity
Opportunity opportunity=new Opportunity();
opportunity.Name='Test Opportunity Name';
opportunity.StageName='Test Opportunity Stage Name';
opportunity.CloseDate=Date.today();
opportunity.RecordTypeId=Schema.SObjectType.Opportunity.getRecordTypeInfos().get(0).getRecordTypeId();
opportunity.AccountId=account.Id;
insert opportunity;
//price-rbe
Quote price_rbe=new Quote();
price_rbe.RecordTypeId=Schema.SObjectType.Quote.getRecordTypeInfosByName().get('Price-RBE').getRecordTypeId();
price_rbe.Name='price-rbe';
price_rbe.OpportunityId=opportunity.Id;
price_rbe.Gasket__c='SBR';
price_rbe.Default_RBE__c=1;
// price_rbe.Currency__c='USD';
price_rbe.Local_Transportation_USD_T__c=1;
price_rbe.Expense__c=1;
price_rbe.Insurance__c=1;
price_rbe.Extra_Gasket__c=1;
price_rbe.Approach_CNY_T__c=1;
price_rbe.Boarding_CNY_T__c=1;
price_rbe.Commission__c=1;
price_rbe.Tax_Rate__c=1;
price_rbe.Import_Duty__c=1;
price_rbe.Standard_Freight_USD_T__c=1;
price_rbe.Sea_Freight_T__c=1;
insert price_rbe;
Pricebook2 pricebook2=new Pricebook2();
pricebook2.Name = 'PriceBook';
pricebook2.IsActive=true;
insert pricebook2;
//Product2
Product2 product2 = new Product2();
product2.Name='Product2';
product2.Length__c=10;
product2.Weight__c=10;
insert product2;
//PriceBookEntry
PricebookEntry entry = new PricebookEntry();
entry.UnitPrice=1;
entry.Product2Id=product2.Id;
entry.Pricebook2Id=Test.getStandardPricebookId();
entry.IsActive=true;
entry.UseStandardPrice=false;
insert entry;
PricebookEntry pbe = new PricebookEntry(
Pricebook2Id = pricebook2.Id,
Product2Id = product2.Id,
UnitPrice = 1020,
IsActive = true,
UseStandardPrice = false
);
Insert pbe;
price_rbe.Pricebook2Id=pricebook2.Id;
update price_rbe;
//Insert QuoteLineItem
QuoteLineItem quoteLineItem=new QuoteLineItem();
quoteLineItem.QuoteId=price_rbe.Id;
quoteLineItem.PricebookEntryId=pbe.Id;
quoteLineItem.Product2Id=product2.Id;
quoteLineItem.Quantity=10;
quoteLineItem.UnitPrice=10;
insert quoteLineItem;
PageReference page=Page.ExportExcel;
Test.setCurrentPage(page);
ApexPages.currentPage().getParameters().put('Id',price_rbe.Id);
ApexPages.StandardController controller = new ApexPages.StandardController(price_rbe);
QuoteExportController quoteExportController = new QuoteExportController(controller);
// quoteExportController.saveFile(0);
quoteExportController.formattedNumber(1);
}
}