Применение формул листа диаграммы в презентациях с использованием PHP

О формулах листов диаграмм в презентациях

Лист диаграммы (или лист рабочей книги диаграммы) в презентации является источником данных для диаграммы. Лист диаграммы содержит данные, которые отображаются на диаграмме графически. Когда вы создаёте диаграмму в PowerPoint, связанный с ней лист автоматически создаётся тоже. Лист диаграммы создаётся для всех типов диаграмм: линейная, столбчатая, sunburst, круговая и т.д. Чтобы увидеть лист диаграммы в PowerPoint, дважды щелкните по диаграмме:

todo:image_alt_text

Лист диаграммы содержит имена элементов диаграммы (Category Name: Category1, Series Name) и таблицу с числовыми данными, соответствующими этим категориям и сериям. По умолчанию, когда вы создаёте новую диаграмму, данные листа диаграммы заполняются значениями по умолчанию. Затем вы можете изменить данные листа вручную.

Обычно диаграмма представляет сложные данные (например, финансовые аналитики, научные аналитики), где ячейки рассчитываются из значений других ячеек или из динамических данных. Ручной ввод вычисленного значения в ячейку усложняет последующее изменение. Если изменить значение определённой ячейки, все зависящие от неё ячейки тоже потребуют обновления. Кроме того, данные таблицы могут зависеть от данных других таблиц, создавая сложную схему данных презентации, которую необходимо легко и гибко обновлять.

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

В Aspose.Slides лист диаграммы представлен методом Chart.getChartData.getChartDataWorkbook типа IChartDataWorkbook. Формулу листа можно задать и изменить с помощью метода IChartDataCell.setFormula. Для формул в Aspose.Slides поддерживается следующая функциональность:

  • Логические константы
  • Числовые константы
  • Строковые константы
  • Константы ошибок
  • Арифметические операторы
  • Операторы сравнения
  • Ссылки на ячейки в стиле A1
  • Ссылки на ячейки в стиле R1C1
  • Предопределённые функции

Обычно листы хранят последние вычисленные значения формул. Если после загрузки презентации данные диаграммы не изменялись, метод IChartDataCell.getValue возвращает эти значения при чтении. Но если данные листа были изменены, при чтении свойства ChartDataCell.Value бросается исключение CellUnsupportedDataException для неподдерживаемых формул. Это происходит потому, что при успешном разборе формул определяются зависимости ячеек и проверяется корректность последних значений. Если формулу нельзя разобрать, корректность значения ячейки гарантировать нельзя.

Добавление формулы листа диаграммы в презентацию

Сначала добавьте диаграмму на первый слайд новой презентации с помощью IShapeCollection.getShapes.addChart. Лист диаграммы создаётся автоматически и к нему можно обратиться методом Chart.getChartData.getChartDataWorkbook:

  $pres = new Presentation();
  try {
    $chart = $pres->getSlides()->get_Item(0)->getShapes()->addChart(ChartType::ClusteredColumn, 150, 150, 500, 300);
    $workbook = $chart->getChartData()->getChartDataWorkbook();
    # ...
  } finally {
    if (!java_is_null($pres)) {
      $pres->dispose();
    }
  }

Запишем несколько значений в ячейки с помощью свойства IChartDataCell.setValue типа Object, что означает возможность установить любое значение:

  $workbook->getCell(0, "F2")->setValue(-2.5);
  $workbook->getCell(0, "G3")->setValue(6.3);
  $workbook->getCell(0, "H4")->setValue(3);

Чтобы записать формулу в ячейку, используйте метод IChartDataCell.setFormula:

Примечание: Метод IChartDataCell.setFormula используется для задания ссылок в стиле A1.

Чтобы задать ссылку в стиле R1C1Formula, используйте метод IChartDataCell.setR1C1Formula:

Тогда при чтении значений из ячеек B2 и C2 они будут рассчитаны:

  $value1 = $cell1->getValue();// 7.8

  $value2 = $cell2->getValue();// 2.1

Логические константы

Вы можете использовать логические константы FALSE и TRUE в формулах ячеек:

  $workbook->getCell(0, "A2")->setValue(false);
  $cell = $workbook->getCell(0, "B2");
  $cell->setFormula("A2 = TRUE");
  $value = $cell->getValue();// значение содержит логическое "false"

Числовые константы

Числа могут использоваться в обычной или научной нотации для создания формул листа диаграммы:

  $workbook->getCell(0, "A2")->setFormula("1 + 0.5");
  $workbook->getCell(0, "B2")->setFormula(".3 * 1E-2");

Строковые константы

Строковая (или литеральная) константа — это конкретное значение, используемое как есть и не меняющееся. Строковыми константами могут быть: даты, тексты, числа и т.п.:

  $workbook->getCell(0, "A2")->setFormula("\"abc\"");
  $workbook->getCell(0, "B2")->setFormula("\"2/3/2020 12:00\"");

Константы ошибок

Иногда результат формулы не может быть вычислен. В этом случае в ячейке отображается код ошибки вместо значения. Каждый тип ошибки имеет свой код:

  • #DIV/0! — формула пытается делить на ноль.
  • #GETTING_DATA — может появиться в ячейке, пока её значение ещё вычисляется.
  • #N/A — информация отсутствует или недоступна. Причины: пустые ячейки, лишний пробел, опечатка и т.д.
  • #NAME? — указанный объект (ячейка или другая формула) не найден по имени.
  • #NULL! — ошибка в формуле, например, неправильное использование запятой или пробела вместо двоеточия (:).
  • #NUM! — недопустимое числовое значение в формуле (слишком длинное, слишком короткое и т.п.).
  • #REF! — недопустимая ссылка на ячейку.
  • #VALUE! — неожиданный тип значения, например, строка в числовой ячейке.
  $cell = $workbook->getCell(0, "A2");
  $cell->setFormula("2 / 0");
  $value = $cell->getValue();// значение содержит строку "#DIV/0!"

Арифметические операторы

Все арифметические операторы поддерживаются в формулах листа диаграммы:

Оператор Значение Пример
+ (плюс) Сложение или унарный плюс 2 + 3
- (минус) Вычитание или унарный минус 2 - 3
-3
* (звёздочка) Умножение 2 * 3
/ (слеш) Деление 2 / 3
% (процент) Процент 30%
^ (карет) Возведение в степень 2 ^ 3

Примечание: Чтобы изменить порядок вычислений, заключите в скобки часть формулы, которую нужно вычислить первой.

Операторы сравнения

Сравнивать значения ячеек можно с помощью операторов сравнения. При их использовании результат всегда логический: TRUE или FALSE:

Оператор Значение Пример
= (равно) Равно A2 = 3
<> (не равно) Не равно A2 <> 3
> (больше) Больше A2 > 3
>= (больше или равно) Больше или равно A2 >= 3
< (меньше) Меньше A2 < 3
<= (меньше или равно) Меньше или равно A2 <= 3

Ссылки на ячейки в стиле A1

Ссылки в стиле A1 используются, когда столбец обозначается буквой (например, “A"), а строка — цифрой (например, “1"). Примеры:

Ссылка Пример
Абсолютная Относительная Смешанная
Ячейка $A$2 A2

A$2

$A2
Строка $2:$2 2:2 -
Столбец $A:$A A:A -
Диапазон $A$2:$C$4 A2:C4

$A$2:C4

A$2:$C$4

Пример использования ссылки A1 в формуле:

  $workbook->getCell(0, "A2")->setFormula("C3 + SUM(F2:H5)");

Ссылки на ячейки в стиле R1C1

Ссылки в стиле R1C1 используют числовые идентификаторы и для строк, и для столбцов. Примеры:

Ссылка Пример
Абсолютная Относительная Смешанная
Ячейка R2C3 R[2]C[3] R2C[3]
R[2]C3
Строка R2 R[2] -
Столбец C3 C[3] -
Диапазон R2C3:R5C7 R[2]C[3]:R[5]C[7] R2C3:R[5]C[7]
R[2]C3:R5C[7]

Пример использования ссылки R1C1 в формуле:

  $workbook->getCell(0, "A2")->setR1C1Formula("R2C4 + SUM(R5C6:R7C9)");

Предопределённые функции

Существует набор предопределённых функций, которые упрощают создание формул. Они покрывают часто используемые операции, такие как:

  • ABS
  • AVERAGE
  • CEILING
  • CHOOSE
  • CONCAT
  • CONCATENATE
  • DATE (система дат 1900)
  • DAYS
  • FIND
  • FINDB
  • IF
  • INDEX (по ссылке)
  • LOOKUP (векторный вариант)
  • MATCH (векторный вариант)
  • MAX
  • SUM
  • VLOOKUP

FAQ

Поддерживаются ли внешние файлы Excel в качестве источника данных для диаграммы с формулами?

Да. Aspose.Slides поддерживает внешние книги как источник данных диаграммы, что позволяет использовать формулы из XLSX‑файла, находящегося вне презентации.

Могут ли формулы диаграммы ссылаться на листы внутри той же книги по имени листа?

Да. Формулы следуют стандартной модели ссылок Excel, поэтому вы можете ссылаться на другие листы в той же книге или во внешней книге. Для внешних ссылок указывайте путь и имя книги, используя синтаксис Excel.

Форматирование диаграмм презентаций в PHP Анимация диаграмм PowerPoint в PHP