Формулы в XMLExcelReport_RU MS Dynamics AX 2012

Данная статья описывает корректную вставку формул в таблицы Excel посредством модификации стандартного класса MS Dynamics AX 2012 XMLExcelReport_RU. Для быстрого поиска формулы на английском языке приведена таблица с названиями формул на русском и английском. Также описан инструмент OpenXMLSDKTool от Microsoft для работы с файлами формата .xlsx, в частности часть кода на C# сгенерированного при помощи OpenXMLSDKTool, который использовался как шаблон для написания методов на X++.

Вставка формулы стандартным методом

Когда пытаешься вставить формулу в ячейку отчета, например, в виде строки » =СУММ(A1:A2)», то после открытия файла в Excel в ячейке отображается сама формула вместо значения, и только после того как сделаешь эту ячейку активной Excel производит расчет по формуле. В стандарте MS Dynamics AX 2012 (семейство классов XMLExcelReport_RU) метода для вставки формулы нет, нужно писать отдельный метод. И для того чтобы формула работала нужно писать на английском («AVERAGE(A2,A1)»).

Метод для вставки формулы

Пример метода в классе XMLExcelReport_RU. Для этого нужно модифицировать все семейства классов, работающие с классами DocumentFormat.OpenXml.

Класс OXMLCell_RU

public void setFormula(str _formula)
{
    DocumentFormat.OpenXml.OpenXmlElementList       oxmlElementList;
    DocumentFormat.OpenXml.OpenXmlElement           oxmlElement;
    DocumentFormat.OpenXml.Spreadsheet.CellFormula  cellFormula;

    System.Collections.IEnumerator                  enumerator;
    ;

    oxmlElementList = cell.get_ChildElements();
    enumerator      = oxmlElementList.GetEnumerator();

    while (enumerator.MoveNext())
    {
        oxmlElement = enumerator.get_Current();

        if (oxmlElement is DocumentFormat.OpenXml.Spreadsheet.CellFormula)
        {
            cellFormula = oxmlElement as DocumentFormat.OpenXml.Spreadsheet.CellFormula;
            break;
        }
    }
    if (!cellFormula)
    {
        //BP deviation documented
        cellFormula = new DocumentFormat.OpenXml.Spreadsheet.CellFormula();
        cell.set_CellFormula(cellFormula);
    }

    cellFormula.set_Text(_formula);
}

Класс OXMLWorkBook_RU

public void insertFormula(Bookmark _bookmark, str _formula, int _workSheet = 1)
{
    str worksheetName;

    int colNumber,
        rowNumber;

    [worksheetName, rowNumber, colNumber] = this.parseBookmark(_bookmark);

    currentWorksheet = worksheetName ? this.getWorksheet(worksheetName) : this.getWorksheet(_worksheet);

    if (templateMode)
    {
        currentWorksheet.rowTemplate(rowNumber).cell(colNumber).setFormula(_formula);
    }
    else
    {
        currentWorksheet.row(rowNumber).cell(colNumber).setFormula(_formula);
    }
}

Класс XMLExcelDocument_RU

public void insertFormula(Bookmark _bookmark, str _formula, int _workSheet = 1)
{
    workbook.insertFormula(_bookmark, _formula, _worksheet);
}

Класс XMLExcelReport_RU

protected void insertFormulaToSection(Bookmark _bookmark, str _formula)
{
    container           rangeInfo;
    int                 row, column;

    if (document.workbook().definedNames().exists([ _bookmark, #localSheetIdUndefined ]))
    {
        rangeInfo = document.workbook().definedNames().lookup([_bookmark, #localSheetIdUndefined]);
    }
    else
    {
        infolog.clear(infologLine() - 1);
        throw error(strFmt("Именованный диапазон не найден" , _bookmark));
    }

    row    = conPeek(rangeInfo, #posFromRow);
    column = conPeek(rangeInfo, #posFromCol);

    document.insertFormula(ComExcelDocument_RU::numToNameCell(column, row), _formula, curWorksheetNum);

    if (this.parmProgress())
    {
        this.parmProgress().incCount(1, 2);
    }
}

Формулы в файле шаблона.

Если формулы были добавлены в файл шаблона, то нужно после добавления всех данных (например в конце метода XMLExcelReport_RU.createReport) вставить метод.

protected void setWorkbookParms()
{
    DocumentFormat.OpenXml.Packaging.WorkbookPart   			workbookPart;
    DocumentFormat.OpenXml.Spreadsheet.Workbook     			workbook;
    DocumentFormat.OpenXml.Spreadsheet.CalculationProperties    calcProperties;
    DocumentFormat.OpenXml.BooleanValue             			trueValue;
    ;

    workbookPart 	= document.workbook().workbookPart();
    workbook 		= workbookPart.get_Workbook();
    calcProperties 	= workbook.get_CalculationProperties();
    trueValue 		= new DocumentFormat.OpenXml.BooleanValue(true);
    calcProperties.set_FullCalculationOnLoad(trueValue);
    calcProperties.set_ForceFullCalculation(trueValue);
}

Формулы на английском и русском

Для удобства ниже список соответствия формул

Формулы в C#

Формула в Open XML SDK это отдельный объект DocumentFormat.OpenXml.Spreadsheet.CellFormula который хранит текст самой формулы и кэшированное значение (CellValue) предыдущего вычисления. Это экономит время при открытии рабочей таблицы.

Для того чтобы понять, как работает формула в OXML очень удобно использовать утилиту OpenXMLSDKTool.

Код сгенерированный утилитой на языке C#.

using DocumentFormat.OpenXml.Packaging;
using Ap = DocumentFormat.OpenXml.ExtendedProperties;
using Vt = DocumentFormat.OpenXml.VariantTypes;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using X15 = DocumentFormat.OpenXml.Office2013.Excel;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;
using A = DocumentFormat.OpenXml.Drawing;
using Thm15 = DocumentFormat.OpenXml.Office2013.Theme;
        private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
        {
            Worksheet worksheet1 = new Worksheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" }  };
            worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
            SheetDimension sheetDimension1 = new SheetDimension(){ Reference = "A1:B3" };

            SheetViews sheetViews1 = new SheetViews();

            SheetView sheetView1 = new SheetView(){ TabSelected = true, WorkbookViewId = (UInt32Value)0U };
            Selection selection1 = new Selection(){ ActiveCell = "B2", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B2" } };

            sheetView1.Append(selection1);

            sheetViews1.Append(sheetView1);
            SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties(){ DefaultRowHeight = 15D, DyDescent = 0.25D };

            SheetData sheetData1 = new SheetData();

            Row row1 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:2" }, DyDescent = 0.25D };

            Cell cell1 = new Cell(){ CellReference = "A1" };
            CellValue cellValue1 = new CellValue();
            cellValue1.Text = "1";

            cell1.Append(cellValue1);

            row1.Append(cell1);

            Row row2 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:2" }, DyDescent = 0.25D };

            Cell cell2 = new Cell(){ CellReference = "A2" };
            CellValue cellValue2 = new CellValue();
            cellValue2.Text = "2";

            cell2.Append(cellValue2);

            Cell cell3 = new Cell(){ CellReference = "B2" };
            CellFormula cellFormula1 = new CellFormula();
            cellFormula1.Text = "AVERAGE(A2,A1)";
            CellValue cellValue3 = new CellValue();
            cellValue3.Text = "1.5";

            cell3.Append(cellFormula1);
            cell3.Append(cellValue3);

            row2.Append(cell2);
            row2.Append(cell3);

            Row row3 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:2" }, DyDescent = 0.25D };

            Cell cell4 = new Cell(){ CellReference = "A3" };
            CellFormula cellFormula2 = new CellFormula();
            cellFormula2.Text = "SUM(A1:A2)";
            CellValue cellValue4 = new CellValue();
            cellValue4.Text = "3";

            cell4.Append(cellFormula2);
            cell4.Append(cellValue4);

            Cell cell5 = new Cell(){ CellReference = "B3" };
            CellFormula cellFormula3 = new CellFormula();
            cellFormula3.Text = "A2+A1";
            CellValue cellValue5 = new CellValue();
            cellValue5.Text = "3";

            cell5.Append(cellFormula3);
            cell5.Append(cellValue5);

            row3.Append(cell4);
            row3.Append(cell5);

            sheetData1.Append(row1);
            sheetData1.Append(row2);
            sheetData1.Append(row3);
            PageMargins pageMargins1 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

            worksheet1.Append(sheetDimension1);
            worksheet1.Append(sheetViews1);
            worksheet1.Append(sheetFormatProperties1);
            worksheet1.Append(sheetData1);
            worksheet1.Append(pageMargins1);

            worksheetPart1.Worksheet = worksheet1;
        }

Формулы XML

Формулы в виде XML хранятся в файле <FileName>.docx/xl/worksheets/sheet<Number>.xml.

XML представление формул.

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
 <dimension ref="A1:B3"/>
 <sheetViews>
  <sheetView tabSelected="1" workbookViewId="0">
   <selection activeCell="B2" sqref="B2"/>
  </sheetView>
 </sheetViews>
 <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
 <sheetData>
  <row r="1" spans="1:2" x14ac:dyDescent="0.25">
   <c r="A1">
    <v>
     1
    </v>
   </c>
  </row>
  <row r="2" spans="1:2" x14ac:dyDescent="0.25">
   <c r="A2">
    <v>
     2
    </v>
   </c>
   <c r="B2">
    <f>
     AVERAGE(A2,A1)
    </f>
    <v>
     1.5
    </v>
   </c>
  </row>
  <row r="3" spans="1:2" x14ac:dyDescent="0.25">
   <c r="A3">
    <f>
     SUM(A1:A2)
    </f>
    <v>
     3
    </v>
   </c>
   <c r="B3">
    <f>
     A2+A1
    </f>
    <v>
     3
    </v>
   </c>
  </row>
 </sheetData>
 <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>