procedure TForm1.Button1Click(Sender: TObject);
var
i:Integer;
v , sheet ,WPT,workbooks ,workbook,PivotCaches ,PivotCache ,PivotTables : variant ;
begin
try
v:=createOleObject('Excel.application');
v.workbooks.add;
sheet:=v.workbooks[1].worksheets[1];
sheet.Range[sheet.cells[1,1],sheet.cells[1,15]].Select;
//SetupFont(FtBiaoKai,xlLeft,xlCenter,12,false);
v.Selection.RowHeight:=48;
v.Selection.HorizontalAlignment:= 1 ;
v.Selection.Font.Size := 20;
sheet.Cells[1,7]:='表';
v.Selection.Merge;
for i := 4 to 5 do
begin
sheet.Columns[i].ColumnWidth:=16;
end ;
sheet.Range[sheet.cells[2,1],sheet.cells[2,6]].Select;
v.Selection.HorizontalAlignment:= 1 ;
v.Selection.Font.Bold:=true ;
sheet.Cells[2,1]:='lei';
sheet.Cells[2,2]:='cang';
sheet.Cells[2,3]:='WEEK';
sheet.Cells[2,4]:='Usable';
sheet.Cells[2,5]:='Actual';
//sheet.Range('A2:E5').Select ;
sheet.Range[sheet.cells[2,1],sheet.cells[5,5]].Select;
workbook := v.ActiveWorkbook;
PivotCaches := workbook.PivotCaches;
PivotCache := PivotCaches.add(1, 'Sheet1!R2C1:R5C5'); //(SourceType:=2);//
PivotTables := PivotCache.CreatePivotTable('' ,'name',1);
// PivotTables.name('AgingReport');
workbook.ActiveSheet.PivotTableWizard ;
//workbook.TableDestination := workbook.ActiveSheet.Cells(3, 1) ;
// workbook.ActiveSheet.Cells(3, 1).Select ;
v.Charts.Add ;
// v.Chart.LocationWhere := '$00000001' ;// xlLocationAsNewSheet ;
workbook.ShowPivotTableFieldList := True ;
v.ActiveChart.PivotLayout.PivotTable.PivotFields('cat).Orientation := 3;//'xlPageField' ;
v.ActiveChart.PivotLayout.PivotTable.PivotFields('cat).Position := 1 ;
v.ActiveChart.PivotLayout.PivotTable.PivotFields('f1').Orientation := 1;//'xlPageField' ;
v.ActiveChart.PivotLayout.PivotTable.PivotFields('f1').Position := 1 ;
v.ActiveChart.PivotLayout.PivotTable.PivotFields('WEEK').Orientation := 1;//'xlPageField' ;
v.ActiveChart.PivotLayout.PivotTable.PivotFields('WEEK').Position := 1 ;
// v.ActiveChart.PivotLayout.PivotTable.PivotFields(' Time').Orientation := 1;//'xlPageField' ;
// v.ActiveChart.PivotLayout.PivotTable.PivotFields(' Time').Position := 1 ;
v.ActiveChart.PivotLayout.PivotTable.AddDataField(
v.ActiveChart.PivotLayout.PivotTable.PivotFields('Usable Time'), '計數 ', -4112 ) ;
Workbook.ShowPivotTableFieldList := False ;
//v.CommandBars('PivotTable').Visible := False ;
v.ActiveChart.PivotLayout.PivotTable.PivotFields('計數 ').Function := -4157 ;
v.Visible:=True;
except
on e:exception
do messagedlg('Create Excel Can''t work!'+#13+#10+e.message,mtWarning,[mbOk],0);
end;
end;