#include <excel.au3>
Local $oExcel = _Excel_Open(Default, Default, Default, Default, True) ;_Excel_Open(1)
If @error Then Exit MsgBox(0, "提示", "无法建立Excel实例,请检查是否安装ooffce 2003以上版本")
$wb = _Excel_BookOpen($oExcel, @ScriptDir & "\成绩表.xlsx")
If @error Then
_Excel_Close($oExcel)
Exit MsgBox(0, "提示", "无法打开成绩表.xlsx")
EndIf
$NewSheet=_Excel_SheetAdd($wb, Default, Default, Default, "平均值")
$range=$wb.sheets("成绩详单").usedrange
$PivotTable = $wb.PivotCaches.Add(1, $range).CreatePivotTable($NewSheet.Cells(3, 1), '数据透视表1') ;
$PivotTable.PivotFields('名字').Orientation = 1 ; =1 行 =2 列
$PivotTable.PivotFields('名字').Position = 1 ;
$PivotTable.AddDataField($PivotTable.PivotFields('语文'), '语文平均', -4106)
$PivotTable.AddDataField($PivotTable.PivotFields('数学'), '数学平均', -4106)
$PivotTable.AddDataField($PivotTable.PivotFields('外语'), '外语平均', -4106)
$PivotTable.DataPivotField.Orientation=2
_Excel_BookSave($wb)
_Excel_BookClose($wb)
_Excel_Close($oExcel)
|