Сводные таблицы в MS Excel на .NET- библиотеке из AX 2012

Сводная таблица (англ. Pivot table) — это статистическая таблица, которая суммирует данные более обширной таблицы (например, из базы данных, электронной таблицы или программы бизнес-аналитики). Эта сводка может включать суммы, средние значения или другие статистические данные, которые сводная таблица группирует значимым образом.

Сводные таблицы — это инструмент обработки данных. Они позволяют человеку упорядочивать и перестраивать (или «сводить») статистику, чтобы привлечь внимание к полезной информации.

В данной статье будут рассмотрены способы создания и заполнения сводных таблиц в MS Excel из Microsoft Dynamics AX 2012 (данные способы также совместимы с Microsoft Dynamics AX 2009). Приведенные в статье примеры основаны на .net-библиотеке Microsoft.Office.Interop.Excel, поэтому для этих целей будет использоваться созданный класс из статьи Шаблон реализации вывода данных в MS Excel .NET.

Contents

1 Подготовка шаблона
2 Создание сводной таблицы
3 Добавление полей
4 Добавление функции
5 Обновление сводной таблицы
6 Пример
7 Примечания
8 См. также

Подготовка шаблона

Для работы сводной таблицы необходим источник данных. В нашем случае в качестве источника данных будет служить заранее созданная и заполненная таблица в MS Excel. Данные таблицы будут основаны на заказах на продажу.

Создание сводной таблицы

public void pivotTableWizard( MSOfficeBookMark_RU _pivotTableName
                            , MSOfficeBookMark_RU _sourceDataRange
                            , MSOfficeBookMark_RU _tableDestination = ""
                            , int                 _worksheet = 1
                            , boolean             _rowTotals = false
                            , boolean             _colTotals = false
                            )
{
    Microsoft.Office.Interop.Excel.Worksheets   xlWorksheets;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheet;
    Microsoft.Office.Interop.Excel.PivotTable   xlPivotTable;
    System.Object                               pivotTableName = _pivotTableName;
    str                                         exception;
    ;

    new InteropPermission(InteropKind::ClrInterop);
    try
    {
        xlWorksheets = xlWorkbook.get_Worksheets();
        xlWorksheet = xlWorksheets.get_Item(_worksheet);

        xlWorksheet.PivotTableWizard(Microsoft.Office.Interop.Excel.XlPivotTableSourceType::xlDatabase, //тип источника данных
            xlWorksheet.get_Range(_sourceDataRange, oMissing), //источник данных
            _tableDestination ? xlWorksheet.get_Range(_tableDestination, oMissing) : oMissing, //верхний левый угол сводной таблицы
            pivotTableName, //имя сводной таблицы
            _rowTotals, //итого по строкам
            _colTotals, //итого по столбцам
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            oMissing);
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();
}

Добавление полей

Данный метод добавляет строки, колонны и фильтры сводной таблицы.

public void addPivotTableFields( MSOfficeBookMark_RU _pivotTableName
                               , int                 _worksheet  = 1
                               , System.Object       _rowLabels  = oMissing //строки
                               , System.Object       _colLabels  = oMissing //колонны
                               , System.Object       _pageFields = oMissing //фильтры
                               )
{
    Microsoft.Office.Interop.Excel.Worksheets   xlWorksheets;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheet;
    Microsoft.Office.Interop.Excel.PivotTable   xlPivotTable;
    str                                         exception;
    ;
    
    new InteropPermission(InteropKind::ClrInterop);
    try
    {
        xlWorksheets  = xlWorkbook.get_Worksheets();
        xlWorksheet   = xlWorksheets.get_Item(_worksheet);
        xlPivotTable  = xlWorksheet.PivotTables(_pivotTableName);

        xlPivotTable.AddFields(_rowLabels, 
            _colLabels, 
            _pageFields, 
            oMissing);
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();
}

Добавление функции

Метод добавляет поле в «Значения», по которому будут происходить вычисления.

public void addPivotTableDataField( MSOfficeBookMark_RU _pivotTableName
                                  , MSOfficeBookMark_RU _fieldName
                                  , int                 _worksheet  = 1
                                  , str                 _fieldCaption  = ""
                                  )
{
    Microsoft.Office.Interop.Excel.Worksheets   xlWorksheets;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheet;
    Microsoft.Office.Interop.Excel.PivotTable   xlPivotTable;
    System.Object                               fieldCaption = _fieldCaption;
    str                                         exception;
    ;

    new InteropPermission(InteropKind::ClrInterop);
    try
    {
        xlWorksheets  = xlWorkbook.get_Worksheets();
        xlWorksheet   = xlWorksheets.get_Item(_worksheet);
        xlPivotTable  = xlWorksheet.PivotTables(_pivotTableName);

        xlPivotTable.AddDataField(xlPivotTable.PivotFields(_fieldName), //поле по которому будет считать функция
            fieldCaption, //заголовок поля 
            Microsoft.Office.Interop.Excel.XlConsolidationFunction::xlSum); //функция
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();
}

Обновление сводной таблицы

Данный метод необходим для обновления вычисляемых данных в сводной таблице.

public void refreshPivotTable( MSOfficeBookMark_RU    _pivotTableName
                             , int                    _workSheet = 1
                             )
{
    Microsoft.Office.Interop.Excel.Worksheets   xlWorksheets;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheet;
    Microsoft.Office.Interop.Excel.PivotTable   xlPivotTable;
    Microsoft.Office.Interop.Excel.PivotCache   xlPivotCache;
    str                                         exception;
    ;
    
    new InteropPermission(InteropKind::ClrInterop);
    try
    {
        xlWorksheets = xlWorkbook.get_Worksheets();
        xlWorksheet = xlWorksheets.get_Item(_workSheet);

        xlPivotTable = xlWorksheet.PivotTables(_pivotTableName);
        xlPivotCache = xlPivotTable.PivotCache();
        xlPivotCache.Refresh();
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();
}

Пример

Данный пример реализован в Microsoft Dynamics AX 2009, но также совместим с Microsoft Dynamics AX 2012.

static void DotNetExcel_Test(Args _args)
{
    DotNetExcel excel = DotNetExcel::construct();
    #define.FilePath("C:\\test.xlsx")
    #define.SourceTableName("SourceTable")
    #define.PivotTableName("PivotTable")
    #define.PivotTableRange("A9")
    #define.DataField("Сумма")

    System.Object getRowLabels()
    {
        System.String[] rowLabels = new System.String[2]();
        ;

        rowLabels.SetValue("Номенклатурная группа", 0);
        rowLabels.SetValue("Наименование", 1);

        return rowLabels;
    }
    System.Object getColLabels()
    {
        System.String[] colLabels = new System.String[1]();
        ;

        colLabels.SetValue("Дата", 0);

        return colLabels;
    }
    ;

    excel.init();
    excel.openDocument(#FilePath);

    excel.pivotTableWizard(#PivotTableName, #SourceTableName, #PivotTableRange);
    excel.addPivotTableFields(#PivotTableName, 1, getRowLabels(), getColLabels());
    excel.addPivotTableDataField(#PivotTableName, #DataField, 1);
    
    excel.closeDocument();
}

В результате была получена сводная таблица, содержащая информацию о суммах на которые был продан товар на тот или иной день, сгруппированная по номенклатурам.

Примечания

Microsoft.Office.Interop.Excel._Worksheet.PivotTableWizard
Microsoft.Office.Interop.Excel.PivotTable

См. также

Шаблон реализации вывода данных в MS Excel .NET