Формулы в 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);
}

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

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

Функции баз данных — Database

ДСРЗНАЧ DAVERAGE Вычисляет среднее значение выбранных записей базы данных.
БСЧЁТ DCOUNT Подсчитывает количество числовых ячеек в базе данных.
БСЧЁТА DCOUNTA Подсчитывает количество непустых ячеек в базе данных.
БИЗВЛЕЧЬ DGET Извлекает из базы данных одну запись, удовлетворяющую заданному условию.
ДМАКС DMAX Находит максимальное значение среди выделенных записей базы данных.
ДМИН DMIN Находит минимальное значение среди выделенных записей базы данных.
БДПРОИЗВЕД DPRODUCT Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию.
ДСТАНДОТКЛ DSTDEV Оценивает стандартное отклонение по выборке из выделенных записей базы данных.
ДСТАНДОТКЛП DSTDEVP Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных.
БДСУММ DSUM Суммирует числа в поле для записей базы данных, удовлетворяющих условию.
БДДИСП DVAR Оценивает дисперсию по выборке из выделенных записей базы данных
БДДИСПП DVARP Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных.

Функции даты и времени — Date & Time

ДАТА DATE Возвращает заданную дату в числовом формате Microsoft Excel.
ДАТАЗНАЧ DATEVALUE Преобразует дату из текстового формата в числовой.
ДЕНЬ DAY Преобразует дату в числовом формате в день месяца.
ДНЕЙ360 DAYS360 Вычисляет количество дней между двумя датами на основе 360-дневного года.
ДАТАМЕС EDATE Находит дату, отстоящую на заданное число месяцев вперед или назад от начальной даты.
КОНМЕСЯЦА EOMONTH Определяет дату для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
ЧАС HOUR Преобразует дату в числовом формате в часы.
МИНУТЫ MINUTE Преобразует дату в числовом формате в минуты.
МЕСЯЦ MONTH Преобразует дату в числовом формате в месяцы.
ЧИСТРАБДНИ NETWORKDAYS Находит количество рабочих дней между двумя датами.
ТДАТА NOW Выдает текущую дату и время.
СЕКУНДЫ SECOND Преобразует дату в числовом формате в секунды.
ВРЕМЯ TIME Выдает заданное время в числовом формате.
ВРЕМЗНАЧ TIMEVALUE Преобразует время из текстового формата в числовой формат.
СЕГОДНЯ TODAY Выдает текущую дату.
ДЕНЬНЕД WEEKDAY Преобразует дату в числовом формате в день недели.
НОМНЕДЕЛИ WEEKNUM Определяет номер рабочей недели года для указанной даты.
РАБДЕНЬ WORKDAY Находит дату, отстоящую от данной на заданное количество рабочих дней.
ГОД YEAR Находит год для заданной даты.
ДОЛЯГОДА YEARFRAC Возвращает долю года, которую составляет количество дней между начальной и конечной датами.

Инженерные функции — Engineering

БЕССЕЛЬ.I BESSELI Возвращает модифицированную функцию Бесселя In (x).
БЕССЕЛЬ.J BESSELJ Возвращает функцию Бесселя Jn (x).
БЕССЕЛЬ.K BESSELK Возвращает модифицированную функцию Бесселя Kn (x).
БЕССЕЛЬ.Y BESSELY Возвращает функцию Бесселя Yn (x).
ДВ.В.ДЕС BIN2DEC Преобразует двоичное число в десятичное.
ДВ.В.ШЕСТН BIN2HEX Преобразует двоичное число в шестнадцатеричное.
ДВ.В.ВОСЬМ BIN2OCT Преобразует двоичное число в восьмеричное.
КОМПЛЕКСН COMPLEX Преобразует коэффициенты при вещественной и мнимой частях комплексного числа в комплексное число.
ПРЕОБР CONVERT Преобразует число из одной системы мер в другую.
ДЕС.В.ДВ DEC2BIN Преобразует десятичное число в двоичное.
ДЕС.В.ШЕСТН DEC2HEX Преобразует десятичное число в шестнадцатеричное.
ДЕС.В.ВОСЬМ DEC2OCT Преобразует десятичное число в восьмеричное.
ДЕЛЬТА DELTA Проверяет равенство двух значений.
ФОШ ERF Возвращает функцию ошибки.
ДФОШ ERFC Возвращает дополнительную функцию ошибки.
ДВФАКТР FACTDOUBLE Вычисляет двойной факториал числа.
ПОРОГ GESTEP Проверяет, не превышает ли данное число порогового значения.
ШЕСТН.В.ДВ HEX2BIN Преобразует шестнадцатеричное число в двоичное.
ШЕСТН.В.ДЕС HEX2DEC Преобразует шестнадцатеричное число в десятичное.
ШЕСТН.В.ВОСЬМ HEX2OCT Преобразует шестнадцатеричное число в восьмеричное.
МНИМ.ABS IMABS Возвращает абсолютную величину (модуль) комплексного числа.
МНИМ.ЧАСТЬ IMAGINARY Возвращает коэффициент при мнимой части комплексного числа.
МНИМ.АРГУМЕНТ IMARGUMENT Возвращает значение аргумента комплексного числа (тета) — угол, выраженный в радианах.
МНИМ.СОПРЯЖ IMCONJUGATE Возвращает комплексно-сопряженное комплексное число.
МНИМ.COS IMCOS Возвращает косинус комплексного числа.
МНИМ.ДЕЛ IMDIV Возвращает частное от деления двух комплексных чисел.
МНИМ.EXP IMEXP Возвращает экспоненту комплексного числа.
МНИМ.LN IMLN Возвращает натуральный логарифм комплексного числа.
МНИМ.LOG10 IMLOG10 Возвращает обычный (десятичный) логарифм комплексного числа.
МНИМ.LOG2 IMLOG2 Возвращает двоичный логарифм комплексного числа.
МНИМ.СТЕПЕНЬ IMPOWER Возвращает комплексное число, возведенное в целую степень.
МНИМ.ПРОИЗВЕД IMPRODUCT Возвращает произведение двух комплексных чисел.
МНИМ.ВЕЩ IMREAL Возвращает коэффициент при вещественной части комплексного числа.
МНИМ.SIN IMSIN Возвращает синус комплексного числа.
МНИМ.КОРЕНЬ IMSQRT Возвращает значение квадратного корня из комплексного числа.
МНИМ.РАЗН IMSUB Возвращает разность двух комплексных чисел.
МНИМ.СУММ IMSUM Возвращает сумму комплексных чисел.
ВОСЬМ.В.ДВ OCT2BIN Преобразует восьмеричное число в двоичное.
ВОСЬМ.В.ДЕС OCT2DEC Преобразует восьмеричное число в десятичное.
ВОСЬМ.В.ШЕСТН OCT2HEX Преобразует восьмеричное число в шестнадцатеричное.

Финансовые функции — Financial

НАКОПДОХОД ACCRINT Определяет накопленный доход по ценным бумагам с периодической выплатой процентов.
НАКОПДОХОДПОГАШ ACCRINTM Находит накопленный доход по ценным бумагам, процент по которым выплачивается в срок вступления в силу.
АМОРУМ AMORDEGRC Вычисляет величину амортизации для каждого периода, используя коэффициент амортизации (французская система бухучета).
АМОРУВ AMORLINC Вычисляет величину амортизации для каждого отчетного периода (французская система бухучета).
ДНЕЙКУПОНДО COUPDAYBS Определяет количество дней между началом периода купона и датой соглашения.
ДНЕЙКУПОН COUPDAYS Определяет число дней в периоде купона, который содержит дату соглашения.
ДНЕЙКУПОНПОСЛЕ COUPDAYSNC Находит число дней от даты соглашения до срока следующего купона.
ДАТАКУПОНПОСЛЕ COUPNCD Находит следующую дату купона после даты соглашения.
ЧИСЛКУПОН COUPNUM Определяет количество купонов, которые могут быть оплачены между датой соглашения и сроком вступления в силу.
ДАТАКУПОНДО COUPPCD Выдает предыдущую дату купона перед датой соглашения.
ОБЩПЛАТ CUMIPMT Вычисляет общую выплату, произведенную между двумя периодическими выплатами.
ОБЩДОХОД CUMPRINC Вычисляет общую выплату по займу между двумя периодами.
ФУО DB Вычисляет амортизацию имущества на заданный период, используя метод постоянного учета амортизации.
ДДОБ DDB Вычисляет величину амортизации имущества для указанного периода при использовании метода двукратного учета амортизации или иного явно указанного метода.
СКИДКА DISC Вычисляет норму скидки для ценных бумаг.
РУБЛЬ.ДЕС DOLLARDE Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом.
РУБЛЬ.ДРОБЬ DOLLARFR Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби.
ДЛИТ DURATION Находит ежегодную продолжительность действия ценных бумаг с периодическими выплатами по процентам.
ЭФФЕКТ EFFECT Вычисляет действующие ежегодные процентные ставки.
БС FV Вычисляет будущее значение вклада.
БЗРАСПИС FVSCHEDULE Вычисляет будущее значение начального вклада при изменяющихся сложных процентных ставках.
ИНОРМА INTRATE Определяет ставку доходности полностью обеспеченной ценной бумаги.
ПРПЛТ IMPT Вычисляет величину выплаты прибыли на вложения за данный период.
ВСД IRR Вычисляет внутреннюю ставку доходности (отдачи) для серии потоков денежных средств.
ПРОЦПЛАТ ISPMT Вычисляет выплаты за указанный период инвестиции.
МДЛИТ MDURATION Определяет модифицированную длительность Маколея для ценных бумаг с предполагаемой номинальной стоимостью 100 рублей.
МВСД MIRR Определяет внутреннюю ставку доходности, при которой положительные и отрицательные денежные потоки имеют разную ставку.
НОМИНАЛ NOMINAL Определяет номинальную годовую процентную ставку.
КПЕР NPER Определяет общее количество периодов выплаты для данной ссуды.
ЧПС NPV Вычисляет чистую приведенную стоимость инвестиции, основанной на серии периодических денежных потоков и ставке дисконтирования.
ЦЕНАПЕРВНЕРЕГ ODDPRICE Находит цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным первым периодом.
ДОХОДПЕРВНЕРЕГ ODDFYIELD Находит доход по ценным бумагам с нерегулярным первым периодом.
ЦЕНАПОСЛНЕРЕГ ODDLPRICE Определяет цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным последним периодом.
ДОХОДПОСЛНЕРЕГ ODDFYIELD Определяет доход по ценным бумагам с нерегулярным последним периодом.
ПЛТ PMT Вычисляет величину выплаты по ссуде за один период.
ОСПЛТ PPMT Вычисляет величину выплат на основной капитал для вклада в заданный период.
ЦЕНА PRICE Вычисляет цену за 100 рублей нарицательной стоимости ценных бумаг, по которым производится периодическая выплата процентов.
ЦЕНАСКИДКА PRICEDISC Вычисляет цену за 100 рублей нарицательной стоимости ценных бумаг, на которые сделана скидка.
ЦЕНАПОГАШ PRICEMAT Вычисляет цену за 100 рублей нарицательной стоимости ценных бумаг, по которым выплачивается прибыль в момент вступления в силу.
ПС PV Вычисляет приведенную (к настоящему моменту) стоимость инвестиции.
СТАВКА RATE Вычисляет процентную ставку по аннуитету за один период.
ПОЛУЧЕНО RECEIVED Вычисляет сумму, полученную в срок вступления в силу полностью обеспеченных ценных бумаг.
АПЛ SLN Вычисляет величину непосредственной амортизации имущества за один период.
АСЧ SYD
РАВНОКЧЕК TBILLEQ Вычисляет эквивалентный облигации доход по казначейскому чеку.
ЦЕНАКЧЕК TBILLPRICE Вычисляет цену за 100 рублей нарицательной стоимости для казначейского чека.
ДОХОДКЧЕК TBILLYIELD Вычисляет доход по казначейскому чеку.
ПУО VDB Вычисляет величину амортизации имущества для явно указанного или соответствующего периода при использовании метода разового учета амортизации.
ЧИСТВНДОХ XIRR Вычисляет внутреннюю ставку доходности запланированных непериодических денежных потоков.
ЧИСТНЗ XNPV Вычисляет чистую текущую стоимость инвестиции, вычисляемую на основе ряда поступлений наличных, которые не обязательно являются периодическими.
ДОХОД YIELD Вычисляет доход от ценных бумаг, по которым производятся периодические выплаты процентов.
ДОХОДСКИДКА YIELDDISC Вычисляет годовой доход по ценным бумагам, на которые сделана скидка. Пример — казначейские чеки.
ДОХОДПОГАШ YIELDMAT Вычисляет годовой доход от ценных бумаг, процент по которым выплачивается в срок погашения.

Проверка свойств и значений и Информационные функции — Information

ЯЧЕЙКА CELL Определяет информацию о формате, местоположении или содержимом ячейки.
ТИП.ОШИБКИ ERROR.TYPE Определяет номер, соответствующий одному из типов ошибок Microsoft Excel.
ИНФОРМ INFO Выдает информацию о текущей операционной среде.
ЕПУСТО ISBLANK Выдает логическое значение ИСТИНА, если аргумент является ссылкой на пустую ячейку.
ЕОШ ISERR Выдает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки, кроме #Н/Д.
ЕОШИБКА ISERROR Выдает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки.
ЕЧЁТН ISEVEN Выдает логическое значение ИСТИНА, если аргумент — четное число.
ЕЛОГИЧ ISLOGICAL Выдает логическое значение ИСТИНА, если аргумент ссылается на логическое значение.
ЕНД ISNA Выдает логическое значение ИСТИНА, если аргумент ссылается на значение ошибки #Н/Д (значение недоступно).
ЕНЕТЕКСТ ISNONTEXT Выдает логическое значение ИСТИНА, если аргумент ссылается на значение, которое не является текстом.
ЕЧИСЛО ISNUMBER Выдает логическое значение ИСТИНА, если аргумент ссылается на число.
ЕНЕЧЁТ ISODD Выдает логическое значение ИСТИНА, если аргумент — нечетное число.
ЕССЫЛКА ISREF Выдает логическое значение ИСТИНА, если аргумент ссылается на ссылку.
ЕТЕКСТ ISTEXT Выдает логическое значение ИСТИНА, если аргумент ссылается на текст.
Ч N Преобразует заданное значение в число.
НД NA Выдает значение ошибки #Н/Д.
ТИП TYPE Выдает тип значения.

Логические функции — Logical

И AND Выдает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.
ЛОЖЬ FALSE Вставляет логическое значение ЛОЖЬ.
ЕСЛИ IF Выполняет проверку условия.
НЕ NOT Меняет на противоположное логическое значение своего аргумента.
ИЛИ OR Выдает значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.
ИСТИНА TRUE Вставляет логическое значение ИСТИНА.

Ссылки и массивы — Lookup & Reference

АДРЕС ADDRESS Выдает ссылку на отдельную ячейку рабочего листа в виде текста.
ОБЛАСТИ AREAS Определяет количество областей в ссылке.
ВЫБОР CHOOSE Выбирает значение из списка значений по индексу.
СТОЛБЕЦ COLUMN Определяет номер столбца, на который указывает ссылка.
ЧИСЛСТОЛБ COLUMNS Определяет количество столбцов в массиве или ссылке.
ПОЛУЧИТЬ.ДАННЫЕ.
СВОДНОЙ.ТАБЛИЦЫ
GETPIVOTDATA Возвращает данные, хранящиеся в сводной таблице.
ГПР HLOOKUP Ищет значение в первой строке массива и выдает значение из ячейки в найденном столбце и указанной строке.
ГИПЕРССЫЛКА HYPERLINK Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете.
ИНДЕКС INDEX По индексу получает значение из ссылки или массива.
ДВССЫЛ INDIRECT Определяет ссылку, заданную текстовым значением.
ПРОСМОТР LOOKUP Ищет значения в векторе или массиве.
ПОИСКПОЗ MATCH Ищет значения в ссылке или массиве.
СМЕЩ OFFSET Определяет смещение ссылки относительно заданной ссылки.
СТРОКА ROW Определяет номер строки, определяемой ссылкой.
ЧСТРОК ROWS Определяет количество строк в ссылке.
ДРВ RTD Извлекает данные реального времени из программ, поддерживающих автоматизацию COM.
ТРАНСП TRANSPOSE Выдает транспонированный массив.
ВПР VLOOKUP Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце.

Математические и тригонометрические функции — Math & Trig

ABS ABS Находит модуль (абсолютную величину) числа.
ACOS ACOS Вычисляет арккосинус числа.
ACOSH ACOSH Вычисляет гиперболический арккосинус числа.
ASIN ASIN Вычисляет арксинус числа.
ASINH ASINH Вычисляет гиперболический арксинус числа.
ATAN ATAN Вычисляет арктангенс числа.
ATAN2 ATAN2 Вычисляет арктангенс для заданных координат x и y.
ATANH ATANH Вычисляет гиперболический арктангенс числа.
ОКРВВЕРХ CEILING Округляет число до ближайшего целого или до ближайшего кратного указанному значению.
ЧИСЛКОМБ COMBIN Находит количество комбинаций для заданного числа объектов.
COS COS Вычисляет косинус числа.
COSH COSH Вычисляет гиперболический косинус числа.
ГРАДУСЫ DEGREES Преобразует радианы в градусы.
ЧЁТН EVEN Округляет число до ближайшего четного целого.
EXP EXP Вычисляет число e, возведенное в указанную степень.
ФАКТР FACT Вычисляет факториал числа.
ОКРВНИЗ FLOOR Округляет число до ближайшего меньшего по модулю целого.
НОД GCD Находит наибольший общий делитель.
ЦЕЛОЕ INT Округляет число до ближайшего меньшего целого.
НОК LCM Находит наименьшее общее кратное.
LN LN Вычисляет натуральный логарифм числа.
LOG LOG Вычисляет логарифм числа по заданному основанию.
LOG10 LOG10 Вычисляет десятичный логарифм числа.
МОПРЕД MDETERM Вычисляет определитель матрицы, хранящейся в массиве.
МОБР MINVERSE Определяет обратную матрицу (матрица хранится в массиве).
МУМНОЖ MMULT Вычисляет произведение матриц, хранящихся в массивах.
ОСТАТ MOD Вычисляет остаток от деления.
ОКРУГЛТ MROUND Находит число, округленное с требуемой точностью.
МУЛЬТИНОМ MULTINOMIAL Вычисляет мультиномиальный коэффициент множества чисел.
НЕЧЁТ ODD Округляет число до ближайшего нечетного целого.
ПИ PI Вставляет число «пи».
СТЕПЕНЬ POWER Вычисляет результат возведения числа в степень.
ПРОИЗВЕД PRODUCT Вычисляет произведение аргументов.
ЧАСТНОЕ QUOTIENT Вычисляет целую часть частного при делении.
РАДИАНЫ RADIANS Преобразует градусы в радианы.
СЛЧИС RAND Выдает случайное число в интервале от 0 до 1.
СЛУЧМЕЖДУ RANDBETVEEN Выдает случайное число в заданном интервале.
РИМСКОЕ ROMAN Преобразует число в арабской записи к числу в римской как текст.
ОКРУГЛ ROUND Округляет число до указанного количества десятичных разрядов.
ОКРУГЛВНИЗ ROUNDDOWN Округляет число до ближайшего меньшего по модулю целого.
ОКРУГЛВВЕРХ ROUNDUP Округляет число до ближайшего по модулю большего целого.
РЯД.СУММ SERIESSUM Вычисляет сумму степенного ряда по заданной формуле.
ЗНАК SIGN Определяет знак числа.
SIN SIN Вычисляет синус заданного угла.
SINH SINH Вычисляет гиперболический синус числа.
КОРЕНЬ SQRT Вычисляет положительное значение квадратного корня.
КОРЕНЬПИ SQRTPI Вычисляет значение квадратного корня из числа «пи».
ПРОМЕЖУТОЧНЫЕ.
ИТОГИ
SUBTOTAL Вычисляет промежуточные итоги.
СУММ SUM Суммирует аргументы.
СУММЕСЛИ SUMIF Суммирует ячейки, удовлетворяющие заданному условию.
СУММПРОИЗВ SUMPRODUCT Вычисляет сумму произведений соответствующих элементов массивов.
СУММКВ SUMSQ Вычисляет сумму квадратов аргументов.
СУММРАЗНКВ SUMX2MY2 Вычисляет сумму разностей квадратов соответствующих значений в двух массивах.
СУММСУММКВ SUMX2PY2 Вычисляет сумму сумм квадратов соответствующих элементов двух массивов.
СУММКВРАЗН SUMXMY2 Вычисляет сумму квадратов разностей соответствующих значений в двух массивах.
TAN TAN Вычисляет тангенс числа.
TANH TANH Вычисляет гиперболический тангенс числа.
ОТБР TRUNC Отбрасывает дробную часть числа.

Статистические функции — Statistical

СРОТКЛ AVEDEV Вычисляет среднее абсолютных значений отклонений точек данных от среднего.
СРЗНАЧ AVERAGE Вычисляет среднее арифметическое аргументов.
СРЗНАЧА AVERAGEA Вычисляет среднее арифметическое аргументов, включая числа, текст и логические значения.
БЕТАРАСП BETADIST Определяет интегральную функцию плотности бета-вероятности.
БЕТАОБР BETAINV Определяет обратную функцию к интегральной функции плотности бета-вероятности.
БИНОМРАСП BINOMDIST Вычисляет отдельное значение биномиального распределения.
ХИ2РАСП CHIDIST Вычисляет одностороннюю вероятность распределения хи-квадрат.
ХИ2ОБР CHIINV Вычисляет обратное значение односторонней вероятности распределения хи-квадрат.
ХИ2ТЕСТ CHITEST Определяет тест на независимость.
ДОВЕРИТ CONFIDENCE Определяет доверительный интервал для среднего значения по генеральной совокупности.
КОРРЕЛ CORREL Находит коэффициент корреляции между двумя множествами данных.
СЧЁТ COUNT Подсчитывает количество чисел в списке аргументов.
СЧЁТЗ COUNTA Подсчитывает количество значений в списке аргументов.
СЧИТАТЬПУСТОТЫ COUNTBLANK Подсчитывает количество пустых ячеек в заданном диапазоне.
СЧЁТЕСЛИ COUNTIF Подсчитывает количество непустых ячеек, удовлетворяющих заданному условию внутри диапазона.
КОВАР COVAR Определяет ковариацию, то есть среднее произведений отклонений для каждой пары точек.
КРИТБИНОМ CRITBINOM Находит наименьшее значение, для которого биномиальная функция распределения меньше или равна заданному значению.
КВАДРОТКЛ DEVSQ Вычисляет сумму квадратов отклонений.
ЭКСПРАСП EXPONDIST Находит экспоненциальное распределение.
FРАСП FDIST Находит F-распределение вероятности.
FРАСПОБР FINV Определяет обратное значение для F-распределения вероятности.
ФИШЕР FISHER Находит преобразование Фишера.
ФИШЕРОБР FISHERINV Находит обратное преобразование Фишера.
ПРЕДСКАЗ FORECAST Вычисляет значение линейного тренда.
ЧАСТОТА FREQUENCY Находит распределение частот в виде вертикального массива.
ФТЕСТ FTEST Определяет результат F-теста.
ГАММАРАСП GAMMADIST Находит гамма-распределение.
ГАММАОБР GAMMAINV Находит обратное гамма-распределение.
ГАММАНЛОГ GAMMALN Вычисляет натуральный логарифм гамма функции.
СРГЕОМ GEOMEAN Вычисляет среднее геометрическое.
РОСТ GROWTH Вычисляет значения в соответствии с экспоненциальным трендом.
СРГАРМ HARMEAN Вычисляет среднее гармоническое.
ГИПЕРГЕОМЕТ HYRGEOMDIST Определяет гипергеометрическое распределение.
ОТРЕЗОК INTERCEPT Находит отрезок, отсекаемый на оси линией линейной регрессии.
ЭКСЦЕСС KURT Определяет эксцесс множества данных.
НАИБОЛЬШИЙ LARGE Находит k-ое наибольшее значение из множества данных.
ЛИНЕЙН LINEST Находит параметры линейного тренда.
ЛГРФПРИБЛ LOGEST Находит параметры экспоненциального тренда.
ЛОГНОРМОБР LOGINV Находит обратное логарифмическое нормальное распределение.
ЛОГНОРМРАСП LOGNORMDIST Находит интегральное логарифмическое нормальное распределение.
МАКС MAX Определяет максимальное значение из списка аргументов.
МАКСА MAXA Определяет максимальное значение из списка аргументов, включая числа, текст и логические значения.
МЕДИАНА MEDIAN Находит медиану заданных чисел.
МИН MIN Определяет минимальное значение из списка аргументов.
МИНА MINA Определяет минимальное значение из списка аргументов, включая числа, текст и логические значения.
МОДА MODE Определяет значение моды множества данных.
ОТРБИНОМРАСП NEGBINOMDIST Находит отрицательное биномиальное распределение.
НОРМРАСП NORMDIST Выдает нормальную функцию распределения.
НОРМОБР NORMINV Выдает обратное нормальное распределение.
НОРМСТРАСП NORMSDIST Выдает стандартное нормальное интегральное распределение.
НОРМСТОБР NORMSINV Выдает обратное значение стандартного нормального распределения.
ПИРСОН PEARSON Определяет коэффициент корреляции Пирсона.
ПЕРСЕНТИЛЬ PERCENTILE Определяет k-ую перцентиль для значений из интервала.
ПРОЦЕНТРАНГ PERCENTRANK Определяет процентную норму значения в множестве данных.
ПЕРЕСТ PERMUT Находит количество перестановок для заданного числа объектов.
ПУАССОН POISSON Выдает распределение Пуассона.
ВЕРОЯТНОСТЬ PROB Определяет вероятность того, что значение из диапазона находится внутри заданных пределов.
КВАРТИЛЬ QUARTILE Определяет квартиль множества данных.
РАНГ RANK Определяет ранг числа в списке чисел.
КВПИРСОН RSQ Находит квадрат коэффициента корреляции Пирсона.
СКОС SKEW Определяет асимметрию распределения.
НАКЛОН SLOPE Находит наклон линии линейной регрессии.
НАИМЕНЬШИЙ SMALL Находит k-ое наименьшее значение в множестве данных.
НОРМАЛИЗАЦИЯ STANDARDIZE Вычисляет нормализованное значение.
СТАНДОТКЛОН STDEV Оценивает стандартное отклонение по выборке.
СТАНДОТКЛОНА STDEVA Оценивает стандартное отклонение по выборке, включая числа, текст и логические значения.
СТАНДОТКЛОНП STDEVP Определяет стандартное отклонение по генеральной совокупности.
СТАНДОТКЛОНПА STDEVPA Определяет стандартное отклонение по генеральной совокупности, включая числа, текст и логические значения.
СТОШYX STEYX Определяет стандартную ошибку предсказанных значений y для каждого значения x в регрессии.
СТЬЮДРАСП TDIST Выдает t-распределение Стьюдента.
СТЬЮДРАСПОБР TINV Выдает обратное t-распределение Стьюдента.
ТЕНДЕНЦИЯ TREND Находит значения в соответствии с линейным трендом.
УРЕЗСРЕДНЕЕ TRIMMEAN Находит среднее внутренности множества данных.
ТТЕСТ TTEST Находит вероятность, соответствующую критерию Стьюдента.
ДИСП VAR Оценивает дисперсию по выборке.
ДИСПА VARA Оценивает дисперсию по выборке, включая числа, текст и логические значения.
ДИСПР VARP Вычисляет дисперсию для генеральной совокупности.
ДИСПРА VARPA Вычисляет дисперсию для генеральной совокупности, включая числа, текст и логические значения.
ВЕЙБУЛЛ WEIBULL Выдает распределение Вейбулла.
ZТЕСТ ZTEST Выдает двустороннее P-значение z-теста.

Текстовые функции — Text

БАТТЕКСТ BAHTTEXT Преобразует чисто в текст (бат) на тайском языке
СИМВОЛ CHAR Определяет знак по заданному коду.
ПЕЧСИМВ CLEAN Удаляет все непечатаемые знаки из текста.
КОДСИМВ CODE Определяет числовой код первого знака в текстовой строке.
СЦЕПИТЬ CONCATENATE Объединяет несколько текстовых элементов в один.
РУБЛЬ DOLLAR Преобразует число в текст, используя денежный формат доллара.
СОВПАД EXACT Проверяет идентичность двух текстов.
НАЙТИ FIND Ищет вхождение одного текста в другой (с учетом регистра).
ФИКСИРОВАННЫЙ FIXED Форматирует число и преобразует его в текст с заданным числом десятичных знаков.
ЛЕВСИМВ LEFT Выдает нужное количество самых левых знаков в строке.
ДЛСТР LEN Определяет количество знаков в текстовой строке.
СТРОЧН LOWER Делает все буквы в тексте строчными.
ПСТР MID Выдает определенное число знаков из строки текста, начиная с указанной позиции.
ПРОПНАЧ PROPER Делает прописной первую букву в каждом слове текста.
ЗАМЕНИТЬ REPLACE Заменяет знаки в тексте.
ПОВТОР REPT Повторяет текст заданное число раз.
ПРАВСИМВ RIGHT Выдает самые правые знаки текстовой строки.
ПОИСК SEARCH Ищет вхождение одного текста в другой (без учета регистра).
ПОДСТАВИТЬ SUBSTITUTE Заменяет в текстовой строке старый текст новым.
Т T Преобразует аргумент в текст.
ТЕКСТ TEXT Форматирует число и преобразует его в текст.
СЖПРОБЕЛЫ TRIM Удаляет из текста лишние пробелы.
ПРОПИСН UPPER Делает все буквы в тексте прописными.
ЗНАЧЕН VALUE Преобразует текстовый аргумент в число.

Формулы в 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>

 

Comments

So empty here ... leave a comment!

Добавить комментарий

Sidebar