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

В выпуске SpreadJS v14.1 (Service Pack 1) мы добавили эту функцию в SpreadJS.

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

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

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

Цель состоит в том, чтобы создать отчет, чтобы обобщить эти необработанные данные в более полезную информацию. Для этого вы можете использовать сводные таблицы SpreadJS.

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

//tablename is the table from where we get our datasource (you can use range as well)

let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1,/*pivot layout*/,/*pivot theme*/,/*pivot options*/);
pivotTable.suspendLayout();
//row fields
pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
//column fields
pivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
pivotTable.group(groupInfo);
//value fields
pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.resumeLayout();

Другой способ создания сводной таблицы — использование сводной панели, доступной в SpreadJS:

//code to add pivot panel in the working spreadsheet.
var panel = new GC.Spread.Pivot.PivotPanel("myPivotPanel", myPivotTable, document.getElementById("panel"));

После этого добавление полей «Строка», «Столбец», «Фильтр» или «Значение» в сводную таблицу будет таким же, как в Excel. Вы перетаскиваете нужные поля в соответствии с логикой вашего отчета.

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

Ниже мы объясним каждый из этих параметров, используемых для улучшения внешнего вида сводных таблиц и многого другого.

Сводная компоновка

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

Вы можете использовать три макета сводных таблиц.

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

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

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

Функция, используемая для изменения макета сводной таблицы, имеет целочисленный аргумент, соответствующий макету формы (0 — компактный, 1 — контурный, 2 — табличный).

pivotTable.layoutType(type);

По умолчанию используется макет Outline Form.

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

Сводная таблица поддерживает три типа тем: светлую, среднюю и темную. Всего поддерживается 85 различных тем.

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

let pivotTable = sheet.pivotTables.add("PivotTable", table.name(), 0, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium2);
 //pivotTable.theme(GC.Spread.Pivot.PivotTableThemes.dark2);
 //pivotTable.theme("dark2");

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

Параметры поворота

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

В приведенном ниже коде показан способ добавления параметра объекта, который позволит вам изменить любой из доступных параметров визуализации для вашей сводной таблицы:

let option = {
        allowMultipleFiltersPerField: true, //whether multiple filters can be used in one field
        insertBlankLineAfterEachItem: true, //whether a blank row should be inserted at end of each item
        grandTotalPosition: GC.Spread.Pivot.GrandTotalPosition.row, //show the grand total in the row, column, or both
        subtotalsPosition: GC.Spread.Pivot.SubTotalsPosition.top, //show subtotal top, bottom, or not
        displayFieldsInPageFilterArea: GC.Spread.Pivot.DisplayFields.downThenOver, //display the page area fields first over then down or first down then over
        reportFilterFieldsPerColumn: 1, //the number of report filter fields per column
        bandRows:true, //show banded rows or not
        bandColumns: true, //show banded columns or not
        showRowHeader: true, //show row header styles or not
        showColumnHeader: true, //show column header styles or not
        showDrill: true, //show expand/collapse button or not
        showMissing: true, //show missing caption or not
        showToolTip: true, //show tool tip or not
        missingCaption: false, //replace empty cell in content area to custom string or number
        fillDownLabels: false, //show repeat label items or not
        repeatAllItemLabels: false, //show repeat label items or not
        rowLabelIndent: 4, //set the indent of each level of title in compact layout
        mergeItem: false //merge and center cells with labels
    };

let pivotTable = sheet.pivotTables.add("PivotTable", table.name(), 0, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium2, option);

Сводная таблица AutofitColumn

Функция AutofitColumn используется для настройки макета сводной таблицы, чтобы она выглядела лучше. Он регулирует ширину столбца, чтобы вместить самое длинное значение в поле. В целом это полезная функция, но она может быть неудобной при работе с длинными текстовыми полями.

pivotTable.autoFitColumn();

Вот пример сводной таблицы, когда мы не использовали изменение размера столбца.

Вместо этого ниже показана та же сводная таблица, когда мы применили функцию autoFitColumn().

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

Как видно из видео ниже, с помощью контекстного меню сводной таблицы вы можете:

  • Сортировка и фильтрация различных полей в сводной таблице
  • Удалить определенные строки, столбцы или поля фильтра
  • Развернуть и свернуть набор данных
  • Поля «Группировать» и «Разгруппировать» (например, даты группируются по кварталам)
  • Решите, как вы будете обобщать свои данные (по сумме, среднему, количеству и т. д.).
  • Решите, как будут отображаться значения (в процентах, в виде разницы)
  • Форматировать значения с определенными заданными форматами или настроенными форматами

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

Сводные стили

Если вы хотите добавить определенный стиль к определенным ячейкам сводной таблицы, вы можете сделать это с помощью функции setStyle(pivotArea, style).

pivotArea — указывает область сводки, которая будет отформатирована.

стиль — определяет стиль форматируемых ячеек.

let style = new GC.Spread.Sheets.Style();
style.backColor = "red";
style.foreColor = "white";
style.font = "16px Arial";
style.formatter = "$ #,##0";
style.hAlign= GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign= GC.Spread.Sheets.VerticalAlign.center;

let pivotArea = {
    dataOnly: true, //labelOnly property would assign the style to pivot field labels
    references: [{
        fieldName: "Cars",
        items: ["BMW","Mercedes"]
    },
    {
        fieldName: "Salesperson",
        items: ["Alan","Serena"]
    },
    {
        fieldName: "Qt",
        items: ["Qtr1", "Qtr3"]
    }]
};
pivotTable.setStyle(pivotArea, style);

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

Вы можете увидеть, как были отформатированы определенные поля меток (с зеленым шрифтом и подчеркиванием), а некоторые поля значений были отформатированы с красным фоном, определенными настройками шрифта и форматом.

Точно так же вы можете применять свои собственные правила и стили и форматировать определенные поля значений или меток в своей сводной таблице.

Сводное условное форматирование

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

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

Чтобы применить условное форматирование к сводной таблице, вы должны определить масштабное правило условного форматирования и область сводной таблицы, в которой находятся ячейки, которые вы хотите отформатировать. Затем вы добавляете форматирование с помощью функции addConditionalRule(area, scaleRule).

var scaleRule = new GC.Spread.Sheets.ConditionalFormatting.ScaleRule(11 /* ThreeScaleRule */, 1 /* LowestValue */, 0, "green",
                                                                      0 /* Number */, 2000, "yellow",
                                                                      2 /* HighestValue */, 10000, "red");
pivotTable.addConditionalRule([{ dataOnly: true,
                                    references: [{fieldName: "Salesperson", items:["Alan"]},{fieldName: "Cars"}]
                               }], scaleRule);// add the rule

//pivotTable.removeConditionalRule(scaleRule);
// remove the rule

При необходимости вы можете удалить условное правило с помощью removeConditionalRule(scaleRule).

Сводные таблицы — это удивительные инструменты, которые помогут вам изучить большой и подробный набор данных и увидеть важные закономерности и тенденции, которые в противном случае вы бы упустили.

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

Сводные таблицы — это дополнительная функция SpreadJS, включенная в нашу версию 14.1.0.

Дополнительный компонент сводной таблицы для разработчиков включен в пробную версию SpreadJS. Обратите внимание, что вам потребуется отдельная лицензия на развертывание сводной таблицы, чтобы распространять ее вместе с вашим приложением.

Первоначально опубликовано на https://www.grapecity.com 11 мая 2022 г.