06.02.2015, 17:36 | #1 |
Участник
|
Метод Build Pivot Table в ComExcelDocument_RU
Добрый день!
Использую метод buildPivotTable X++: #define.xlDatabase(1) #define.xlPivotTableVersion10(1) #define.PivotTableName("PivotTable") void buildPivotTable(BookMark _sourceBookMark, container _columnFields, container _rowFields, container _dataFields, int _sourceWorkSheet = 1, BookMark _destBookMark = "", int _destWorkSheet = 1) { COM comWorkBook, comWorkSheet; COM comPivotCaches, comPviotCache; COM comPivotTable, comPivotField; COM comCommandBars, comCommandBar; int iPivotField; BookMark destBookMark; ; comWorkBook = m_comApplication.activeWorkBook(); comWorkSheet = this.getWorkSheet(_sourceWorkSheet); comPivotCaches = comWorkBook.PivotCaches(); //xlDatabase means data is in the active workbook comPviotCache = comPivotCaches.Add(#xlDatabase, strFmt("%1!%2", comWorkSheet.name(), _sourceBookMark)); //CreatePivotTable(str Destination (ex. [Книга1].Лист2!R3A1), str Data location, int pivotTableVersion) //Empty String as destination means a new WorkSheet will be created if (_destBookMark) { comWorkSheet = this.getWorkSheet(_destWorkSheet); destBookMark = strFmt("%1!%2", comWorkSheet.name(), _destBookMark); } comPivotTable = comPviotCache.CreatePivotTable(destBookMark, strFmt("%1%2", #PivotTableName, _sourceWorkSheet), #xlPivotTableVersion10); for (iPivotField = 1; iPivotField <= conlen(_columnFields); iPivotField++) { comPivotField = comPivotTable.PivotFields(conPeek(_columnFields, iPivotField)); comPivotField.Orientation(2); //xlColumnField comPivotField.Position(iPivotField); } for (iPivotField = 1; iPivotField <= conlen(_rowFields); iPivotField++) { comPivotField = comPivotTable.PivotFields(conPeek(_rowFields, iPivotField)); comPivotField.Orientation(1); //xlRowField comPivotField.Position(iPivotField); } for (iPivotField = 1; iPivotField <= conLen(_dataFields); iPivotField++) { //AddDataField(PivotField object [, shown name, type of aggregate function ]) //xlSum = -4157 is default comPivotTable.AddDataField(comPivotTable.PivotFields(conPeek(_dataFields, iPivotField))); } //Hide the PivotTable FieldList comWorkBook.ShowPivotTableFieldList(false); //Hide the PivotTable CommandBar comCommandBars = m_comApplication.CommandBars(); comCommandBar = comCommandBars.item(#PivotTableName); comCommandBar.visible(false); } |
|
|
|