Си шарп построение графиков в экселе
Обновлено: 07.07.2024
Введение. Решение и выбор способа связывания
Введение алиаса позволяет не только сократить количество кода при обращении к объектам, но и дает возможность использовать весь код, который мы напишем ниже, для любого способа
Рис.1. Проект решения AppWordExcel
Создадим обработчик нажатия для кнопки 1 и замкнем на него обработчики нажатия всех кнопок. Запишем следующий, общий для всех примеров, приводимых ниже, код:
В case 1 будем размещать код, в котором выполняется какое-либо действие по старту Excel и выводу информации, в case 2 - дополнительные действия и действия при закрытии приложения, в default - поместим код выхода из приложения.
Немного обособленно от этой иерархической структуры объектов находится свойство Windows объекта Excel.Application, предназначенное для управления окнами сервера Excel. Свойство Windows содержит набор объектов Window, которые имеют, в свою очередь, набор свойств и методов для управления размерами, видом, масштабом и упорядочиванием открытых окон, отображением заголовков, цветами и т.д. Эти же возможности доступны и для свойств и методов объекта Excel.Application - ActiveWindow (ссылка на активное окно). (Подробно об управлении окнами см. в параграфе "Некоторые возможности по управлению параметрами Excel").
Все эти объекты принято определять глобально для того, чтобы обеспечить доступ к ним из любой функции проекта. Определим глобально основной объект Excel.Application и, сразу, хотя он и понадобится нам значительно позже - объект Excel.Window:
Следующий код позволяет выполнять запуск Excel и его закрытие при нажатии соответственно кнопок 1 и 2.
Параграф 2. Создание рабочих книг
Вторым в иерархии объектов Excel.Application является объект Workbook. Информация об объектах Workbook хранится в виде ссылок на открытые рабочие книги в свойстве Workbooks. Книга в приложение может быть добавлена только через добавление ссылки в совокупность Workbooks, а ссылка на открытую книгу может быть получена различным образом (по имени, номеру, как ссылка на активную книгу).
Рассмотрим создание двух рабочих книг из 3х и 5ти листов.
Свойство SheetsInNewWorkbook возвращает или устанавливает количество листов, автоматически помещаемых Excel в новые рабочие книги.
Из других свойств отметим свойство TemplatesPath. С его помощью, зная имя файла шаблона, можно напрямую задавать имя шаблона (правда, в этом нет необходимости, если мы не хотим использовать, например, свой собственный шаблон). Свойство StartupPath возвращает путь к папке, которая содержит надстройки, выполняемые при запуске Excel и, хотя свойство для отображения информации нам ничего не дает, все же порой бывает необходимо найти имя файла настроек и удалить его для того, чтобы приложение работало только с собственными настройками.
Книги могут быть не только добавлены, но и закрыты. Следующие вызовы закрывают все или конкретную рабочую книгу:
Остановимся более подробно на втором методе. Его параметры:
Параграф 3. Сохранение документов
3.1. Подготовка к сохранению документов
Документы Excel можно сохранить программно и обычным для Excel способом. В любом случае перед выходом из Excel необходимо вызвать метод Quit. Если свойство Excel.Application DisplayAlerts имеет значение true, Excel предложит сохранить несохраненные данные, если после старта в документ были внесены какие либо изменения. Excel автоматически не возвращает это свойство в значение по умолчанию, поэтому его рекомендуется возвращать в исходное состояние.
Добавим в область глобального определения переменных еще две строчки, определив массив ссылок на созданные книги и на объект - конкретную книгу.
Теперь, если выйти на конкретную книгу, как показано в примере, приведенном ниже, и присвоить свойству Saved объекта Workbook значение true, Excel согласно документации не должен предлагать сохранение независимо от того, были или нет изменения в данной книге (к сожалению, это не всегда работает).
На некоторых вариациях версий Windows и Office запрос на сохранение может все равно присутствовать, хотя мы, и отключаем его в свойстве Saved.
Из примера видно, что кроме Item, у набора Workbooks, как и у всех наборов в Microsoft Office, есть свойство Count, которое возвращает число элементов в наборе (в данном случае - объектов Workbook).
Следующий вопрос, - в каком формате сохранять документ. Для получения формата открываемого документа и задания формата сохраняемого служит свойство Excel.Application DefaultSaveFormat. Свойство имеет много значений типа XlFileFormat (какие могут быть легко посмотреть в диалоговом окне "Сохранение документа" в поле "Тип файла", открыв Excel и выбрав пункт меню "Файл" | "Сохранить как").
Например, в case 1 допишем строчку:
и в окне диалога сохранения файла будет установлен тип файла "Web страница".
3.2. Сохранение документа
Для сохранения документов можно использовать методы Excel.Workbook Save и SaveAs. Метод Save сохраняет рабочую книгу в папке "Мои документы" с именами, присваиваемыми документу по умолчанию ("Книга1.xls", "Книга2.xls" . ) или в директорию и с именем под которым документ уже был сохранен.
Изменим код в case 2, на следующий и продемонстрируем пример сохранения по умолчанию:
При значении свойства DisplayAlerts=true Excel будет спрашивать - записать ли сохраняемый документ поверх существующего, при значении false - нет.
Метод SaveAs позволяет сохранить документ с указанием имени, формата файла, пароля, режим доступа и т. д. Данный метод, как и метод Save, присваивает свойству Saved значение true. Метод SaveAs имеет следующий синтаксис:
Для доступа к книге используются значение AccessMode xlShared - общая рабочая книга, xlExclusive - монопольный доступ или xlNoChange - запрет изменения режима доступа.
Параметр ConflictResolution - способ разрешения конфликтов при одновременном внесении несколькими пользователями изменений в один документ - может иметь значения: xlUserResolution - отображение диалогового окна разрешения конфликтов (параметр по умолчанию), xlLocalSessionChanges - принятие изменений, внесенных пользователем или xlOtherSessionChanges - принятие изменений, внесенных другими пользователями.
Следующий пример сохраняет открытые выше документы в формате .html и .xls, причем большинство параметров для первого документа передаются как не имеющие значения Type.Missing, для второго документа использовано паролирование и запрет изменения режима доступа (Excel.XlSaveAsAccessMode.xlNoChange):
Рис.2. Вызов документа с установленным паролем на доступ и запись
Для сохранения документа может быть использован метод SaveCopyAs, который сохраняет копию рабочей книги в файле. Следующий пример, использующий метод SaveCopyAs полностью аналогичен примеру, использующему метод SaveAs при задании всех параметров как Type.Missing.
Метод SaveAs не производит преобразование документа и, поэтому, приведенный код вместо Web страницы a.html сохранит копию xls документа (изменит только расширение).
Параграф 4. Открытие существующего документа
Для открытия существующего документа основным методом является метод Open набора Excel.Workbooks. Для открытия текстовых файлов как рабочих книг, баз данных, файлов в формате .XML, используются методы OpenText, OpenDatabase или OpenXml. Об использовании методов OpenDatabase и OpenXml речь будет вестись в других темах. В данном параграфе рассмотрим метод Open.
После выполнения предыдущего примера у нас осталось два рабочих файла a.html и a.xls, которые мы и откроем как две рабочие книги в Excel, изменив код в case 1:
Метод Open имеет много параметров. Но, большинство из них, как видно из примера, необязательны. Рассмотрим параметры метода Open:
UpdateLinks - позволяет задать способ обновления ссылок в файле. Если данный параметр не задан, то выдается запрос на указание метода обновления. Значения: 0 - не обновлять ссылки; 1 - обновлять внешние ссылки; 2 - обновлять только удаленные ссылки; 3 - обновлять все ссылки.
Format - при работе с текстовыми файлами определяет символ разделителя для полей, заносимых в различные ячейки документа. Значения параметра: 1 - символ табуляции; 2 - запятая; 3 - пробел; 4 - точка с запятой; 5 - нет разделителя; 6 - другой символ, определенный в параметре Delimiter.
Приведем еще один пример - пример открытия текстового файла с использованием метода OpenText. Метод загружает в Excel текстовый файл как рабочую книгу с одиночным листом и производит его парсинг по ячейкам листа в соответствии с параметрами.
В современном мире разработки приложений нередко встает необходимость работы с Excel документами. Чаще всего это разного рода отчеты, но иногда xls/x файлы используются в качестве хранилища данных. Например, если пользователь должен иметь возможность загрузить данные в приложение или выгрузить, в человеко-читаемом виде, Excel де-факто является стандартом. Относительно дружелюбный интерфейс, прозрачная структура, в купе с его распространенностью. трудно навскидку назвать решение лучше.
Историческая справка
Времена, когда доминировал проприетарный формат .xls(Excel Binary File Format) давно прошли и сейчас мы имеем только .xlsx(Excel Workbook), в рамках Office Open XML. Последний представляет собой обычный .zip архив с XML файлами. Не будем углубляться в его структуру, я искренне надеюсь что вам это никогда не понадобится.
На github, и не только, можно найти ряд библиотек, бесплатных и не только. Пожалуй самой популярной является EPPlus. До определенной степени, она довольно хорошо отражает концепцию Excel, именно по этому я всегда использую EPPlus. Версия 4 полностью бесплатна, начиная с 5‐й версии вам потребуется приобрести лицензию для коммерческого использования.
Задача
Итак, предположим, продукт-мэнеджеру ударила в голову идея того, что возможность выгружать некий отчет в формате Excel увеличит кол-во пользователей на 100500%. Проджет-менеджер решает выкатить эту киллер-фичу как хотфикс прямо сегодня — ведь работы всего на пару часов.
Сам по себе, отчет содержит краткое описание компании и историю изменения некоторых экономических показателей. Для простоты все свойства компании — строки. Экономические показатели — большие целые числа и числа с плавающей точкой, а также даты. Предположим, что где-то в недрах микросервисного backend-да есть сервис-генератор подобных отчетов, например по id компании. Однако, поскольку id нет смысла выводить пользователю, идентификатор отсутствует в самой модели отчета.
Аналитик, в свою очередь, выдает задачу с феноменально точным описанием - "Сгенерировать excel отчет на базе данных MarketReport". Что ж, для нашего примера, создадим заглушку — генератор фейковых данных:
Первый запуск
Подключим EPPlus версии 4.5.3.3 и создадим базовую обвязку для будущего генератора.
Сердцем генератора будет метод Generate. ExcelPackage это модель документа, через которую мы и будем осуществлять все взаимодействия с ним. Также имеется конструктор для передачи пути к файлу или потока.
В методе main создается генератор отчетов, а также генератор Excel файлов. Далее полученный файл просто записывается на диск.
При попытке запустить приложение, получаем exception: InvalidOperationException: The workbook must contain at least one worksheet
Все правильно, Excel документ не может существовать без страниц, должна быть хотя бы одна. Добавляем ее, все интуитивно понятно:
Запускаем снова и. вот оно! Теперь наше приложение генерирует документ и, хотя там еще ничего нет, он уже весит 2,5KB - значит мы работаем с Excel правильно и все идет как надо.
Вывод данных
Давайте выведем основную информацию по компании в шапку. Для доступа к конкретной ячейки объект Cells на странице пакета снабжен удобным индексатором. При этом, до конкретной ячейки можно достучаться как через номер строки и столбца, так и по привычному всем буквенно-числовому коду:
Полный код вывода шапки.
Для вывода исторических данных понадобится как минимум шапка таблицы и цикл по массиву History:
Предлагаю обратить внимание на метод LoadFromArrays, который заполняет диапазон ячеек рваным(зубчатым) массивом. Здесь мы можем видеть, что типизация теряется и передавая массив object мы ожидаем что EPPlus в конечном итоге использует ToString, чтобы записать переданное в ячейки.
Стилизация
Если вы прямо сейчас откроете документ, то вы возможно увидите не то, что хотелось бы отдать в продакшн в пятницу вечером.
Как это выглядит
Во-первых, шапка никак не выделяется, во-вторых таблица не имеет границ. выравнивание пляшет, даты отображаются магическими числами, а капитализация "уходит в какую-то математику" - как это прокомментировал аналитик.
Да, на все эти красивости у нас уйдет больше года кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности. но, мы же backend разработчики, так давайте сверстаем Excel Sheet!
Размер ячеек
Из коробки у нас есть возможность сделать автофит а так же вручную выставить ширину в соответствии с нашей ситуацией. А ситуация у нас не самая хорошая — по задумке аналитика в шапке у ячеек должен быть автофит, а у ячеек таблицы — тоже автофит. Так в чем же подвох?
Если вы когда-нибудь до этого открывали Excel, то возможно знаете, что ширина ячеек не может отличаться в рамках столбца и автофит будет по самому широкому контенту ячейки. Однако, простые вещи бывает нетак то просто объяснить. Но если вы справитесь, то вот как это будет выглядеть в коде:
Формат данных
Как и большая часть стиля ячейки, он задается через одноименное свойство Style. Обратите внимание на вычисление 3-го аргумента индексатора. Это звоночек некачественного кода, но к этому мы вернемся в позже.
Выравнивание
Его можно задать как на ячейке, так и на диапазоне. На самом деле, для EPPlus, это одна и та же сущность — некий ExcelRange, описывающий диапазон ячеек, в том числе и со всего 1 ячейкой.
Стиль текста
Также легко задается, используя Style.Font, кстати, здесь, на 2-й строчке, мы впервые указываем диапазон так, как привыкли его видеть пользователи Excel:
Границы
Задаем стиль линии, а также ее толщину. К этому моменту от кол-ва магических чисел-параметров индексатора уже рябит в глазах, но мы уже на финишной прямой. не так ли?
График
"Ну что за отчет без графиков, верно, Карл?" - ловко подметит специалист по тестированию, и не важно, что этого не было в ТЗ а на часах уже половина 9-го.
Хотя график как сущность сам по себе сложнее таблиц и с графиками мы не работаем каждый день, EPPlus предоставляет довольно понятный API. Давайте добавим простейший график, отражающий рост капитализации:
Еще, может понадобиться защитить страницу от редактирования:
На этом все, репозиторий с рабочим приложением находится здесь.
Заключение
Во-первых, прежде всего, о том, что мы успешно справились с задачей, а именно, сгенерировали свой первый Excel отчет, поработали со стилями и даже решили пару попутных проблем.
Читайте также: