Визуализируйте данные Excel с помощью условного форматирования в JavaScript

Таблица продаж с двадцатью колонками чисел точна, но нечитаема. Глаз не может достаточно быстро сравнить 43 210 и 38 900 в строке, чтобы найти слабый квартал, и человек, читающий отчёт, знает это — именно поэтому он просит диаграмму. Но диаграмма для каждой колонки означает двадцать диаграмм, и теперь рабочий лист превращается в галерею, а не в таблицу.

Гистограммы, цветовые шкалы и наборы значков решают эту задачу внутри самих ячеек. Полоса растёт пропорционально значению. Цвет меняется от бледного к насыщенному по мере роста числа. Значок меняет форму, когда значение пересекает порог. Ни один из них не добавляет строки, столбцы или плавающие объекты — визуализация находится в ячейке, которая уже содержит число. Все три являются разновидностями условного форматирования Excel, и Spire.XLS for JavaScript применяет их через единый API в браузере на WebAssembly, при этом файлы перемещаются через виртуальную файловую систему (VFS), а серверная часть не требуется.

По настройке проекта см. Интеграция Spire.XLS for JavaScript в проект React. Примеры ниже предполагают, что пакет установлен, а модуль WebAssembly инициализирован.

Почему бы просто не добавить диаграмму

Диаграммы и визуализация внутри ячеек отвечают на один и тот же вопрос — "как эти значения соотносятся?" — но подходят для разных ситуаций:

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

Предварительные требования

Вам нужен проект React с установленным Spire.XLS for JavaScript и инициализированным модулем WebAssembly, доступным по адресу window.wasmModule.spirexls. Пример загружает шрифт и файл с данными о продажах в VFS и сохраняет с флагом версии Excel 2010 — это самая ранняя версия, поддерживающая эти типы условного форматирования.

Один API, три визуализации

Все три типа используют одну и ту же цепочку вызовов. Меняется только строка с присваиванием FormatType:

sheet.ConditionalFormats.Add()  →  xcfs.AddRange(range)  →  format = xcfs.AddCondition()  →  format.FormatType = ???

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

Гистограммы: величина с первого взгляда

Гистограмма рисует горизонтальную цветную полосу внутри каждой ячейки, и длина полосы пропорциональна значению ячейки относительно остальной части выбранного диапазона. Наибольшее значение заполняет ячейку целиком; наименьшее — лишь тонкую полоску. Просмотр строки гистограмм — та же мыслительная операция, что и просмотр столбчатой диаграммы, только числа остаются видимыми под ними.

Шаги:

1. Загрузите шрифт и файл тестовых данных в VFS.
2. Загрузите книгу и получите рабочий лист.
3. Вызовите ConditionalFormats.Add, чтобы создать условный формат, и привяжите диапазон данных с помощью AddRange.
4. Вызовите AddCondition, чтобы добавить условие, установите FormatType в значение DataBar и задайте цвет полосы.
5. Сохраните книгу.

function App() {
  const applyDataBars = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Select the data range that receives the data bars
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add a data bar condition and set the bar color
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.DataBar;
    format.DataBar.BarColor = xlsModule.Color.get_CadetBlue();

    // Save the workbook
    const outputFileName = "ApplyDataBars.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Apply Data Bars</h1>
      <button onClick={applyDataBars}>Start</button>
    </div>
  );
}

export default App;

Гистограммы, применённые к таблице показателей продаж; длина полосы пропорциональна значению ячейки

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

Цветовые шкалы: тепловая карта без диаграммы

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

Шаги те же, что и для гистограмм, но FormatType устанавливается в значение ColorScale и дополнительные свойства не задаются:

1. Загрузите шрифт и файл тестовых данных в VFS.
2. Загрузите книгу и получите рабочий лист.
3. Вызовите ConditionalFormats.Add, чтобы создать условный формат, и привяжите диапазон данных с помощью AddRange.
4. Вызовите AddCondition, чтобы добавить условие, и установите FormatType в значение ColorScale.
5. Сохраните книгу.

function App() {
  const applyColorScales = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Select the data range that receives the color scales
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add a color scale condition; colors transition with the values
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.ColorScale;

    // Save the workbook
    const outputFileName = "ApplyColorScales.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Apply Color Scales</h1>
      <button onClick={applyColorScales}>Start</button>
    </div>
  );
}

export default App;

Цветовые шкалы, применённые к таблице показателей продаж; заливка от оранжевого к бледно-жёлтому

Там, где гистограммы показывают абсолютную величину через длину полосы, цветовые шкалы показывают относительную позицию через оттенок. Значение в середине диапазона получает средний тон независимо от того, охватывает диапазон от 1 до 100 или от 10 000 до 50 000 — заливка зависит от позиции, а не от абсолютной величины.

Наборы значков: диапазоны статусов

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

Шаги отличаются только значением FormatType и выбором стиля значков:

1. Загрузите шрифт и файл тестовых данных в VFS.
2. Загрузите книгу и получите рабочий лист.
3. Вызовите ConditionalFormats.Add, чтобы создать условный формат, и привяжите диапазон данных с помощью AddRange.
4. Вызовите AddCondition, чтобы добавить условие, установите FormatType в значение IconSet и укажите тип набора значков.
5. Сохраните книгу.

function App() {
  const applyIconSets = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Select the data range that receives the icon sets
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add an icon set condition and set the icon style to three traffic lights
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.IconSet;
    format.IconSet.IconSetType = xlsModule.IconSetType.ThreeTrafficLights1;

    // Save the workbook
    const outputFileName = "ApplyIconSets.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Apply Icon Sets</h1>
      <button onClick={applyIconSets}>Start</button>
    </div>
  );
}

export default App;

Наборы значков, применённые к таблице показателей продаж; значки светофора на основе диапазонов значений

Набор значков делит диапазон на полосы, поэтому один и тот же значок охватывает разный интервал значений в разных диапазонах. В диапазоне от 10 до 90 зелёный значок охватывает примерно от 60 до 90; в диапазоне от 10 до 900 — примерно от 600 до 900. Полосы относительны, а не абсолютны — это правильное поведение по умолчанию для таблицы, где у каждой колонки своя шкала, но об этом стоит знать, если вы ожидаете фиксированный порог.

Выбор между тремя

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

Эти три не исключают друг друга. Рабочий лист может содержать гистограммы в колонках выручки и наборы значков в колонке темпов роста при одном сохранении, поскольку каждый вызов ConditionalFormats.Add создаёт независимый формат, привязанный к своему диапазону.

Настройка внешнего вида гистограмм

Цвет заливки гистограммы задаётся через DataBar.BarColor. Если задать только FormatType без BarColor, получится синий цвет по умолчанию. Также доступна граница, но у неё есть зависимость: тип границы должен быть задан до того, как вступит в силу цвет границы.

// Set the border type first so that the border color takes effect
format.DataBar.BarBorder.Type = xlsModule.DataBarBorderType.DataBarBorderSolid;
format.DataBar.BarBorder.Color = xlsModule.Color.get_Red();

// Fill color of the bar
format.DataBar.BarColor = xlsModule.Color.get_GreenYellow();

Если задать только BarBorder.Color, не установив предварительно BarBorder.Type, это не даст эффекта — граница не будет нарисована, поскольку тип границы не объявлен. У цветовых шкал и наборов значков нет аналогичных свойств внешнего вида; их стиль определяется типом формата, а для наборов значков — перечислением IconSetType.

Распространённые проблемы

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

Все гистограммы синие по умолчанию. DataBar.BarColor не был задан после присваивания FormatType. Установите любое значение xlsModule.Color, чтобы изменить заливку.

Цвет границы гистограммы не отображается. Тип границы не был задан первым. Присвойте DataBar.BarBorder.Type до DataBar.BarBorder.Color — цвет вступает в силу только после объявления сплошного типа границы.

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

Часто задаваемые вопросы

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

Да. Каждый вызов ConditionalFormats.Add создаёт независимый формат. Два формата могут нацеливаться на один и тот же диапазон, хотя визуальный результат наложения гистограммы и цветовой шкалы на одни и те же ячейки может запутать — обычно понятнее применять разные типы к разным диапазонам.

Какие версии Excel поддерживают эти типы условного форматирования?

Гистограммы, цветовые шкалы и наборы значков появились в Excel 2007. Пример сохраняется с ExcelVersion.Version2010, чтобы обеспечить совместимость как с Excel 2010, так и с более поздними версиями.

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

Нет. Механизм работы с электронными таблицами входит в состав пакета и работает как WebAssembly в браузере. Условное форматирование записывается как стандартный XML внутри файла .xlsx, и Excel отображает его при открытии файла.

Можно ли задать собственные пороги для наборов значков?

Перечисление IconSetType выбирает предопределённый стиль значков с предопределёнными границами полос. В примере используется ThreeTrafficLights1, который делит диапазон на три равные полосы.

Сохранится ли условное форматирование, если файл открыть и пересохранить в Excel?

Да. Условное форматирование является частью сохранённых правил формата рабочего листа, а не артефактом отображения. Excel читает, сохраняет и повторно применяет те же правила при пересчёте.

См. также

• Интеграция Spire.XLS for JavaScript в проект React
• Страница продукта Spire.XLS for JavaScript