Применение формул листа данных диаграммы в презентациях с использованием Java
Общие сведения о формулах таблицы данных диаграммы в презентациях
Таблица данных диаграммы (или лист данных диаграммы) в презентации является источником данных для диаграммы. Таблица данных диаграммы содержит данные, которые отображаются на диаграмме в графической форме. При создании диаграммы в PowerPoint автоматически создаётся соответствующий лист данных. Лист данных создаётся для всех типов диаграмм: линейных, гистограмм, радиальных, круговых и т.д. Чтобы увидеть таблицу данных диаграммы в PowerPoint, дважды щёлкните по диаграмме:
Таблица данных диаграммы содержит имена элементов диаграммы (Имя категории: Category1, Имя серии) и таблицу с числовыми данными, соответствующими этим категориям и сериям. По умолчанию, при создании новой диаграммы в таблице данных задаются стандартные значения. Затем вы можете изменить данные в листе вручную.
Обычно диаграмма представляет сложные данные (например, финансовые аналитики, научные исследователи), содержащие ячейки, рассчитываемые из значений других ячеек или из динамических данных. Ручной ввод расчётного значения в ячейку усложняет будущие изменения. Если изменить значение определённой ячейки, все зависимые от неё ячейки также потребуют обновления. Более того, данные таблицы могут зависеть от данных других таблиц, образуя сложную схему данных презентации, которую необходимо обновлять просто и гибко.
Формула таблицы данных диаграммы в презентации — это выражение для автоматического расчёта и обновления данных таблицы диаграммы. Формула определяет логику вычисления данных для определённой ячейки или набора ячеек. Формула может быть математической или логической, используя ссылки на ячейки, математические функции, логические операторы, арифметические операторы, функции преобразования, строковые константы и т.д. Определение формулы записывается в ячейку, и эта ячейка не содержит простого значения. Формула рассчитывает значение и возвращает его, после чего значение присваивается ячейке. Формулы таблицы данных диаграмм в презентациях фактически такие же, как формулы Excel, и поддерживают те же стандартные функции, операторы и константы.
В Aspose.Slides таблица данных диаграммы представлена методом Chart.getChartData.getChartDataWorkbook типа IChartDataWorkbook. Формула может быть назначена и изменена с помощью метода IChartDataCell.setFormula. Для формул в Aspose.Slides поддерживаются следующие возможности:
- Логические константы
- Числовые константы
- Строковые константы
- Константы ошибок
- Арифметические операторы
- Операторы сравнения
- Ссылки на ячейки в стиле A1
- Ссылки на ячейки в стиле R1C1
- Предопределённые функции
Обычно листы хранят последние вычисленные значения формул. Если после загрузки презентации данные диаграммы не изменялись, метод IChartDataCell.getValue возвращает эти значения при чтении. Однако если данные листа были изменены, при чтении свойства ChartDataCell.Value будет выброшено исключение CellUnsupportedDataException для неподдерживаемых формул. Это происходит потому, что при успешном разборе формул определяются зависимости ячеек и проверяется корректность последних значений. Если формула не может быть разобрана, корректность значения ячейки гарантировать нельзя.
Добавление формулы таблицы данных диаграммы в презентацию
Сначала добавьте диаграмму на первый слайд новой презентации с помощью IShapeCollection.getShapes.addChart. Лист диаграммы создаётся автоматически и доступен через метод Chart.getChartData.getChartDataWorkbook:
Presentation pres = new Presentation();
try {
IChart chart = pres.getSlides().get_Item(0).getShapes().addChart(ChartType.ClusteredColumn, 150, 150, 500, 300);
IChartDataWorkbook workbook = chart.getChartData().getChartDataWorkbook();
// ...
} finally {
if (pres != null) 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 они будут вычислены:
Object value1 = cell1.getValue(); // 7.8
Object value2 = cell2.getValue(); // 2.1
Логические константы
Можно использовать логические константы FALSE и TRUE в формулах ячеек:
workbook.getCell(0, "A2").setValue(false);
IChartDataCell cell = workbook.getCell(0, "B2");
cell.setFormula("A2 = TRUE");
Object 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! — неожиданный тип значения, например, строка в числовой ячейке.
IChartDataCell cell = workbook.getCell(0, "A2");
cell.setFormula("2 / 0");
Object 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"). Ссылки в стиле A1 применяются так:
| Ссылка на ячейку | Пример | ||
|---|---|---|---|
| Абсолютная | Относительная | Смешанная | |
| Ячейка | $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:$C4 |
Пример использования ссылки в стиле A1 в формуле:
workbook.getCell(0, "A2").setFormula("C3 + SUM(F2:H5)");
Ссылки на ячейки в стиле R1C1
Ссылки в стиле 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.