<apex:page controller="CertificateMessageEXCEL" contentType="application/x-excel# GenExcel.xls" cache="true" showheader="false">
<apex:outputText value="{!xlsHeader}"/>
<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">
<Styles>
<Style ss:ID="s1">
<Alignment/>
<Borders/>
<Font ss:Bold="1"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="BasicInfo">
<Table x:FullColumns="1" x:FullRows="1">
<Column ss:Width="100"/>
<Column ss:Width="100"/>
<apex:repeat value="{!AllList}" var="wo">
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >证书客户</Data></Cell>
<Cell><Data ss:Type="String">{!wo.workOrder.CertificateCustmer__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >证书地址</Data></Cell>
<Cell><Data ss:Type="String">{!wo.workOrder.CertificateAddress__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >订单号</Data></Cell>
<Cell><Data ss:Type="String">{!wo.workOrder.Case.CaseNumber__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >仪器名称</Data></Cell>
<Cell><Data ss:Type="String">{!wo.workOrder.Asset.ApparatusName__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >生产厂</Data></Cell>
<Cell><Data ss:Type="String">{!wo.workOrder.Asset.Factory__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >型号</Data></Cell>
<Cell><Data ss:Type="String">{!wo.workOrder.Asset.InstrumentModel__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >序列号</Data></Cell>
<Cell><Data ss:Type="String">{!wo.workOrder.Asset.Name}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >接收日期</Data></Cell>
<Cell><Data ss:Type="String">{!wo.ReceivedDate}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >校准程序</Data></Cell>
<Cell><Data ss:Type="String">{!wo.serQuote.CarriedStandard__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >证书编号</Data></Cell>
<Cell><Data ss:Type="String">{!wo.calRecord.CertificateNumber__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >原始记录编号</Data></Cell>
<Cell><Data ss:Type="String">{!wo.calRecord.OriginalRecordType__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >校准类型</Data></Cell>
<Cell><Data ss:Type="String">{!wo.calRecord.JZType__c}</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >校准日期</Data></Cell>
<Cell><Data ss:Type="String">{!wo.JZDate}</Data></Cell>
</Row>
</apex:repeat>
</Table>
</Worksheet>
<Worksheet ss:Name="StandardsInfo">
<Table x:FullColumns="1" x:FullRows="1">
<Column ss:Width="100"/>
<Column ss:Width="100"/>
<Column ss:Width="100"/>
<Column ss:Width="100"/>
<Column ss:Width="100"/>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >名称</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >型号</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >序列号</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >证书编号</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >有效期限</Data></Cell>
</Row>
<apex:repeat value="{!ItemList}" var="li">
<Row>
<Cell><Data ss:Type="String">{!li.ModelName}</Data></Cell>
<Cell><Data ss:Type="String">{!li.standard.Model__c}</Data></Cell>
<Cell><Data ss:Type="String">{!li.standard.SerialNumber__c}</Data></Cell>
<Cell><Data ss:Type="String">{!li.tracking.CertificateNo__c}</Data></Cell>
<Cell><Data ss:Type="String">{!li.ExpiredDate}</Data></Cell>
</Row>
</apex:repeat>
</Table>
</Worksheet>
</Workbook>
</apex:page>
public with sharing class CertificateMessageEXCEL {
public {get;set;} //标准器
public {get;set;} //溯源历史
public {get;set;} //工单明细
public {get;set;} //报价单
public {get;set;} //记录类型
public {get;set;} //工单
public List<ItemObject> ItemList{get;set;}
public List<ItemAll> AllList{get;set;}
public String xlsHeader {
get {
String strHeader = '';
strHeader += '<?xml version="1.0"?>';
strHeader += '<?mso-application progid="Excel.Sheet"?>';
return strHeader;
}
}
public CertificateMessageEXCEL(){
//获取页面上的值
String woId = ApexPages.currentPage().getParameters().get('id');
List<String> woIds= woId.split(',');
//查询与页面id匹配的校准记录
CalRecordList = [select Id,
StandardEquipment__r.Model__c,StandardEquipment__r.SerialNumber__c,
StandardEquipment__c,InstrumentName__c
from CalRecord__c
Where Id in:woIds LIMIT 50];
system.debug(CalRecordList);
String SetStr;
For(CalRecord__c cal: CalRecordList){
SetStr = cal.InstrumentName__c;
}
system.debug(SetStr);
List<String> strlist= SetStr.split(';');
system.debug(SetStr);
//标准器(子查询)
StandardEquipmentList = [Select Id,Name,Model__c,SerialNumber__c,
(Select Id,Name,ExpiredDate__c,CertificateNo__c From TrackingHistory__r order by createddate desc limit 1)
From StandardEquipment__c
Where Name in:strlist];
system.debug(StandardEquipmentList);
//溯源历史
TrackingHistoryList = [Select Id,Name,CertificateNo__c,ExpiredDate__c
From TrackingHistory__c
where StandardEquipment__r.Name in:strlist];
System.debug(TrackingHistoryList);
//查询明细
WorkOrderItemList = [select Id,WorkOrder__c,CalibrationDate__c,CalibrationMode__c
From WorkOrderItem__c
Where Id =: CalRecordList[0].WorkOrderItem__c];
system.debug(WorkOrderItemList);
// 查询工单
WorkOrderList = [Select Id,CertificateAddress__c,CertificateCustmer__c ,ReceivedDate__c ,Asset.ApparatusName__c,Asset.Name,
Asset.Factory__c,Asset.InstrumentModel__c,Case.CaseNumber__c
from WorkOrder
Where Id =: WorkOrderItemList[0].WorkOrder__c ];
system.debug(WorkOrderList);
//工单明细等于校准记录里面的工单编号
ServiceQuoteList = [Select Id,WorkOrderItem__c,CarriedStandard__c
From ServiceQuote__c
Where WorkOrderItem__c =:CalRecordList[0].WorkOrderItem__c];
ItemList = new List<ItemObject>();
for(StandardEquipment__c se: StandardEquipmentList){
system.debug(se);
if(se.Name.contains(',') == true){
ItemObject itemObj = new ItemObject(se,se.Name.substring(0,se.Name.lastIndexOf(',')));
system.debug(se);
for(TrackingHistory__c th: se.TrackingHistory__r){
itemObj.tracking = th;
itemObj.ExpiredDate = th.ExpiredDate__c.format().replace('.','/');
}
ItemList.add(itemObj);
}else if(se.Name.contains(',') == true){
ItemObject itemObj = new ItemObject(se,se.Name.substring(0,se.Name.lastIndexOf(',')));
system.debug(se);
for(TrackingHistory__c th: se.TrackingHistory__r){
itemObj.tracking = th;
itemObj.ExpiredDate = th.ExpiredDate__c.format().replace('.','/');
}
ItemList.add(itemObj);
}else{
ItemObject itemObj = new ItemObject(se,se.Name);
system.debug(se);
for(TrackingHistory__c th: se.TrackingHistory__r){
itemObj.tracking = th;
itemObj.ExpiredDate = th.ExpiredDate__c.format().replace('.','/');
}
ItemList.add(itemObj);
}
}
system.debug(ItemList);
AllList = new List<ItemAll>();
for(CalRecord__c cr: CalRecordList){
ItemAll itemcr = new ItemAll(cr);
itemcr.JZDate = cr.JZDate__c.format().replace('.','/');
for(WorkOrder wo: WorkOrderList ){
itemcr.workOrder = wo;
itemcr.ReceivedDate = wo.ReceivedDate__c .format().replace('.','/');
}
for(ServiceQuote__c sq:ServiceQuoteList){
itemcr.serQuote = sq;
}
AllList.add(itemcr);
}
}
Class ItemObject{
public StandardEquipment__c standard{get; set;}
public TrackingHistory__c tracking{get; set;}
public String ExpiredDate{get; set;}
public String ModelName{get; set;}
public ItemObject(StandardEquipment__c standard,String ModelName){
this.ModelName = ModelName;
this.standard = standard;
}
public ItemObject(StandardEquipment__c standard,String ModelName,TrackingHistory__c tracking,String ExpiredDate) {
this.standard = standard;
this.ModelName = ModelName;
this.tracking = tracking;
this.ExpiredDate = ExpiredDate;
}
}
Class ItemAll{
public CalRecord__c calRecord{get; set;}
public String JZDate{get; set;}
public WorkOrder workOrder{get; set;}
public String ReceivedDate{get; set;}
public ServiceQuote__c serQuote{get; set;}
public ItemAll(CalRecord__c calRecord){
this.calRecord= calRecord;
}
public ItemAll(WorkOrder workOrder){
this.calRecord= calRecord;
}
public ItemAll(CalRecord__c calRecord,String JZDate,WorkOrder workOrder,String ReceivedDate,ServiceQuote__c serQuote) {
this.calRecord = calRecord;
this.JZDate = JZDate;
this.ReceivedDate = ReceivedDate;
this.workOrder = workOrder;
this.serQuote = serQuote;
}
}
public Pagereference exportAll(){
return new Pagereference('/apex/exportAll');
}
}