Старт работы с Excel на C#
В современном мире разработки приложений нередко встает необходимость работы с Excel документами. Чаще всего это разного рода отчеты, но иногда xls/x файлы используются в качестве хранилища данных. Например, если пользователь должен иметь возможность загрузить данные в приложение или выгрузить, в человеко-читаемом виде, Excel де-факто является стандартом. Относительно дружелюбный интерфейс, прозрачная структура, в купе с его распространенностью. трудно навскидку назвать решение лучше.
Историческая справка
На github, и не только, можно найти ряд библиотек, бесплатных и не только. Пожалуй самой популярной является EPPlus. До определенной степени, она довольно хорошо отражает концепцию Excel, именно по этому я всегда использую EPPlus. Версия 4 полностью бесплатна, начиная с 5‐й версии вам потребуется приобрести лицензию для коммерческого использования.
Задача
Итак, предположим, продукт-мэнеджеру ударила в голову идея того, что возможность выгружать некий отчет в формате Excel увеличит кол-во пользователей на 100500%. Проджет-менеджер решает выкатить эту киллер-фичу как хотфикс прямо сегодня — ведь работы всего на пару часов.
Сам по себе, отчет содержит краткое описание компании и историю изменения некоторых экономических показателей. Для простоты все свойства компании — строки. Экономические показатели — большие целые числа и числа с плавающей точкой, а также даты. Предположим, что где-то в недрах микросервисного backend-да есть сервис-генератор подобных отчетов, например по id компании. Однако, поскольку id нет смысла выводить пользователю, идентификатор отсутствует в самой модели отчета.
Первый запуск
Подключим EPPlus версии 4.5.3.3 и создадим базовую обвязку для будущего генератора.
Сердцем генератора будет метод Generate. ExcelPackage это модель документа, через которую мы и будем осуществлять все взаимодействия с ним. Также имеется конструктор для передачи пути к файлу или потока.
В методе main создается генератор отчетов, а также генератор Excel файлов. Далее полученный файл просто записывается на диск.
При попытке запустить приложение, получаем exception: InvalidOperationException: The workbook must contain at least one worksheet
Все правильно, Excel документ не может существовать без страниц, должна быть хотя бы одна. Добавляем ее, все интуитивно понятно:
Вывод данных
Давайте выведем основную информацию по компании в шапку. Для доступа к конкретной ячейки объект Cells на странице пакета снабжен удобным индексатором. При этом, до конкретной ячейки можно достучаться как через номер строки и столбца, так и по привычному всем буквенно-числовому коду:
Полный код вывода шапки.
Для вывода исторических данных понадобится как минимум шапка таблицы и цикл по массиву History:
Предлагаю обратить внимание на метод LoadFromArrays, который заполняет диапазон ячеек рваным(зубчатым) массивом. Здесь мы можем видеть, что типизация теряется и передавая массив object мы ожидаем что EPPlus в конечном итоге использует ToString, чтобы записать переданное в ячейки.
Стилизация
Если вы прямо сейчас откроете документ, то вы возможно увидите не то, что хотелось бы отдать в продакшн в пятницу вечером.
Да, на все эти красивости у нас уйдет больше года кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности. но, мы же backend разработчики, так давайте сверстаем Excel Sheet!
Размер ячеек
Из коробки у нас есть возможность сделать автофит а так же вручную выставить ширину в соответствии с нашей ситуацией. А ситуация у нас не самая хорошая — по задумке аналитика в шапке у ячеек должен быть автофит, а у ячеек таблицы — тоже автофит. Так в чем же подвох?
Если вы когда-нибудь до этого открывали Excel, то возможно знаете, что ширина ячеек не может отличаться в рамках столбца и автофит будет по самому широкому контенту ячейки. Однако, простые вещи бывает нетак то просто объяснить. Но если вы справитесь, то вот как это будет выглядеть в коде:
Формат данных
Как и большая часть стиля ячейки, он задается через одноименное свойство Style. Обратите внимание на вычисление 3-го аргумента индексатора. Это звоночек некачественного кода, но к этому мы вернемся в позже.
Выравнивание
Его можно задать как на ячейке, так и на диапазоне. На самом деле, для EPPlus, это одна и та же сущность — некий ExcelRange, описывающий диапазон ячеек, в том числе и со всего 1 ячейкой.
Стиль текста
Также легко задается, используя Style.Font, кстати, здесь, на 2-й строчке, мы впервые указываем диапазон так, как привыкли его видеть пользователи Excel:
Границы
Задаем стиль линии, а также ее толщину. К этому моменту от кол-ва магических чисел-параметров индексатора уже рябит в глазах, но мы уже на финишной прямой. не так ли?
График
Хотя график как сущность сам по себе сложнее таблиц и с графиками мы не работаем каждый день, EPPlus предоставляет довольно понятный API. Давайте добавим простейший график, отражающий рост капитализации:
Еще, может понадобиться защитить страницу от редактирования:
На этом все, репозиторий с рабочим приложением находится здесь.
Заключение
Во-первых, прежде всего, о том, что мы успешно справились с задачей, а именно, сгенерировали свой первый Excel отчет, поработали со стилями и даже решили пару попутных проблем.
Во-вторых, возможно имеет смысл искать новою работу, но, забегая вперед, я бы с этим не спешил. Если данная публикация наберет 1+ просмотров, то во второй части мы поговорим о том, как можно отделить стилизацию от логики заполнения данными, упростить манипуляции над ячейками и в целом сделаем код боле поддерживаемым.
Переключатели командной строки для продуктов Microsoft Office
При запуске продукта Microsoft Office его загрузка выполняется в стандартном режиме. Microsoft Word, например, отображает экран-заставку Word и загружает шаблон «Обычный». Однако предположим, что вы хотите запустить Word без экрана-заставки, а затем загрузить другой шаблон. Кроме того, вам может потребоваться загрузить надстройку, запустив макрос. Для этого можно добавить подкоманды (которые называются переключателями командной строки) в команду запуска приложения Office.
Если вы хотите использовать настройку только один раз, вы можете ввести команду и переключиться в диалоговое окно Выполнить (меню ) в Microsoft Windows. Если вы хотите использовать определенный переключатель несколько раз или каждый раз при запуске приложения, можно создать ярлык для запуска программы с помощью того же переключателя и параметров. В этой статье приводятся инструкции по каждому из них. Кроме того, она содержит таблицу, включаемую все переключатели и параметры, доступные в классических Office приложениях.
Для использования переключателя вовсе не нужно вводить всю команду запуска в командной строке. Вы можете запустить приложение Office как обычно, щелкнув значок на рабочем столе или название программы в меню Пуск. Все способы запуска фактически делают одно и то же: они запускают EXE-файл приложения, даже если вы не вводите саму команду или не видите ее.
Переключатель командной строки представляет собой модификатор, который добавляется к EXE-файлу. Файл загрузки с переключателем выглядит следующим образом:
В этом примере переключатель командной строки добавлен к EXE-файлу Microsoft Outlook. Он включает в себя косую черту и слово или аббревиатуру, которые указывают на его действие. Данный переключатель позволяет отключить область чтения в Outlook.
Имена переключателей нельзя сокращать, но в них не учитывается регистр. Однако параметры иногда вводятся с учетом регистра.
Ниже приведены команды, позволяющие запускать Word, Excel, PowerPoint, средство просмотра PowerPoint, Outlook и Access.
О безопасном режиме Microsoft Office
Безопасный режим Microsoft Office позволяет безопасно использовать приложения Microsoft Office, при запуске которого возникли определенные неполадки. В случае обнаружения неполадки при запуске Microsoft Office либо устраняет или изолирует ее, делая возможным успешный запуск приложения.
Автоматический безопасный режим
При запуске Microsoft Office выполняет проверку на наличие таких неполадок как невозможность запустить надстройку или расширение, либо поврежденность ресурса, файла, реестра или шаблона. При следующем запуске приложения выводится сообщение с вопросом о том, следует ли отключить ту часть программы, в которой обнаружена неполадка.
Список отключенных объектов можно просмотреть в диалоговом окне Отключенные объекты (меню Справка, команда О программе). С помощью этого диалогового окна можно включить перечисленные объекты. Для включения некоторых объектов может потребоваться перезагрузка или переустановка надстройки или повторное открытие файла. Включение объекта не обязательно приводит к устранению неполадки; при следующем запуске приложения включенный объект может быть снова помещен в список отключенных.
Безопасный режим, инициируемый пользователем
Любое приложение Microsoft Office можно открыть в безопасном режиме, нажав клавишу CTRL при его запуске или указав параметр /safe при запуске приложения из командной строки.
При открытии приложения в инициированном пользователем безопасном режиме действуют следующие ограничения. (Некоторые из перечисленных ниже ограничений действуют не для всех приложений Microsoft Office.)
Невозможно сохранение шаблонов.
В Microsoft FrontPage не открывается последний использовавшийся веб-узел.
Помощник по Microsoft Office не отображается автоматически.
Пользовательские настройки панелей инструментов или панели команд не загружаются и сохранение пользовательских настроек невозможно.
Список автозамены не загружается и изменения этого списка не сохраняются.
Восстановленные документы не открываются автоматически.
Смарт-теги не загружаются и сохранение новых смарт-тегов невозможно.
Игнорируются все параметры командной строки, кроме «/a» и «/n».
Невозможно сохранение файлов в дополнительной папке автозагрузки.
Невозможно сохранение личных настроек.
Дополнительные средства и программы не загружаются автоматически.
Замена приложения, запускаемого при открытии файла
Многие типы файлов в Windows открываются только в закрепленных за ними приложениях. Например, при открытии текстового файла (TXT) запускается блокнот. Приложение, запускаемое при открытии файлов определенного типа, можно заменить.
Выполните одно из следующих действий.
Для Windows 2000 и Windows Millennium Edition.
В окне Мой компьютер или проводнике Windows откройте меню Сервис и выберите команду Свойства папки, а затем откройте вкладку Типы файлов.
В списке Зарегистрированные типы файлов выберите тип файлов, для открытия которых требуется использовать другое приложение, и нажмите кнопку Изменить.
В диалоговом окне Выбор программы выберите приложение, которое требуется использовать, и нажмите кнопку OK.
Нажмите кнопку OK в диалоговом окне Свойства папки.
Для Windows 98 и Windows NT 4.0.
В окне Мой компьютер или проводнике Windows откройте меню Вид и выберите команду Свойства папки ( Параметры в Windows NT 4.0), а затем откройте вкладку Типы файлов.
В списке типов файлов выберите нужный тип файлов и нажмите кнопку Изменить.
В поле Действия выберите Open и нажмите кнопку Изменить.
Перейдите в поле Приложение, исполняющее действие и нажмите кнопку Обзор.
В диалоговом окне Открыть с помощью найдите программу, которую требуется использовать для открытия файлов с данным расширением, и нажмите кнопку OK.
Нажмите кнопку OK в диалоговом окне Свойства папки.
Настройка запуска Microsoft Excel
Папки XLStart находятся по адресу «C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Excel» или «C:\Program Files\Microsoft Office\Office10».
Автоматическое открытие книги при запуске Microsoft Excel
В проводнике Windows переместите значок книги, которую требуется открывать, в папку «XLStart».
Если требуется, чтобы книга оставалась в текущем местоположении, воспользуйтесь командой Создать ярлык из меню Файл для создания ярлыка книги, а затем переместите ярлык в папку XLStart.
Перезапустите Microsoft Excel.
Для того чтобы при запуске Microsoft Excel открывать группу связанных книг, сохраните эти книги в файле рабочей области, а затем поместите файл рабочей области в папку XLStart.
Открытие всех файлов в папке при запуске Microsoft Excel
Внимание! Microsoft Excel будет пытаться открыть любой файл, находящийся в дополнительной папке автозагрузки. Следует убедиться, что указана пустая папка или папка, содержащая только те файлы, которые Microsoft Excel может открыть.
В меню Сервис выберите команду Параметры, а затем — вкладку Общие.
В поле Каталог автозагрузки введите путь к дополнительной папке автозагрузки.
Примечание. Если файл с одним именем находится и в папке XLStart, и в дополнительной папке автозагрузки, будет открываться файл из папки XLStart.
Использование ключей автозагрузки при запуске Microsoft Excel
В главном меню (кнопка Пуск) выберите команды Программы, Стандартные и Проводник.
В проводнике Windows перейдите к папке «\Program Files\Microsoft Office\Office10\Shortcut Bar\Office» и удалите ярлык для Microsoft Excel.
В проводнике Windows перейдите к папке «\Program Files\Microsoft Office\Office10», щелкните правой кнопкой мыши файл EXCEL.exe и выберите Создать ярлык.
Правой кнопкой мыши щелкните созданный ярлык, выберите Свойства и измените путь в поле Объект, используя значения из таблицы.
Например, для открытия книги «Страховые списки.xls» введите пробел после пути в поле Объект, а затем введите:
C:\Reference Data\Страховые списки.xls
| Чтобы запустить Microsoft Excel и | Введите |
|---|---|
| Открыть определенную книгу | путь к книге/имя файла |
| Открыть определенную книгу только для чтения (Только чтение. Режим, включающий возможность просмотра и копирования файла, но запрещающий его изменение и сохранение. Для сохранения изменений, внесенных в документ, предназначенный только для чтения, необходимо присвоить документу другое имя.) | /r путь к книге/имя файла |
| Не отображать загрузочный экран и пустую новую книгу | /e |
| Указать рабочую папку Примечание. Папка, определенная в ключе автозагрузки, используется вместо папки, указанной в поле Рабочий каталог вкладки Общие диалогового окна Параметры (меню Сервис). | /p путь к папке/имя папки |
| Указать безопасный режим Microsoft Office | /safe |
Переместите ярлык в «\Windows\Documents and Settings\Главное меню».
Примечание. Пользовательский ярлык может быть заменен ярлыком Windows Installer, если он называется «Microsoft Excel», а также выполнены следующие условия.
Используется команда Microsoft Office «Найти и восстановить» и установлен флажок Восстановить ярлыки.
В режиме поддержки выбран параметр Восстановление Microsoft Office, выполнена команда Исправить ошибки в установке Office и установлен флажок Восстановить ярлыки.
Если пользовательский ярлык заменен ярлыком Windows Installer, пользовательский ярлык необходимо создать заново.
Отмена загрузки книги при запуске Microsoft Excel
В результате описанных ниже действий будут удалены все способы загрузки книги.
В меню Сервис выберите команду Параметры, а затем — вкладку Общие.
Очистите поле Каталог автозагрузки.
Удалите книги, хранящиеся в папке XLStart.
В проводнике Windows удалите значок книги, которую требуется открывать, из папки XLStart.
Удалите ключ автозагрузки из значка Microsoft Excel.
Правой кнопкой мыши щелкните значок Microsoft Excel, который требуется использовать для запуска Microsoft Excel, и очистите поле Объект.
Запуск Microsoft Excel без выполнения автоматических макросов
Автоматические макросы, такие как макрос Авто_открыть, выполняются при запуске Microsoft Excel. Для получения дополнительных сведений о таких макросах обратитесь к справочной системе по языку программирования Microsoft Visual Basic.
Чтобы избежать автоматического запуска макросов, при запуске Microsoft Excel следует нажать и удерживать клавишу SHIFT.
Примечание. При запуске Microsoft Excel с панели Microsoft Office нажмите кнопку Microsoft Excel на панели Microsoft Office, сразу нажмите клавишу SHIFT и удерживайте ее в процессе запуска Microsoft Excel.
Сохранение параметров книги, которые требуется использовать при каждом запуске Microsoft Excel с шаблоном книги (book.xlt)
Форматирование
Форматы ячеек и листов. Устанавливаются с помощью команд меню Формат.
Форматы страниц и параметры области печати (Область печати. Один или несколько диапазонов ячеек, выделенных для печати, если не требуется печатать весь лист. Если на листе задана область печати, будет напечатана только она.) для каждого листа.
Стили (Стиль. Набор параметров форматирования, таких как шрифт, размер шрифта и отступы абзацев, которые хранятся вместе под общим именем. Когда применяется стиль, все содержащиеся в этом стиле инструкции форматирования применяются одновременно.) ячеек.
Число и тип листов в книге.
Защищенные и скрытые области книги. Имеется возможность скрыть страницы, строки и столбцы и тем самым предотвратить внесение в ячейки листа каких-либо изменений.
Текст, данные, рисунки и формулы
Повторяемый текст, например заголовки страниц, подписи строк и столбцов.
Данные, формулы, графика, диаграммы и другие данные.
Параметры проверки данных.
Панели инструментов и параметры настройки
Пользовательские панели инструментов, макросы (Макрос. Макрокоманда или набор макрокоманд, используемый для автоматического выполнения некоторых операций. Макросы записываются на языке программирования Visual Basic для приложений.), гиперссылки (Гиперссылка. Цветной подчеркнутый текст или графический объект, по щелчку которого выполняется переход к файлу, фрагменту файла или странице HTML в интрасети или Интернете. Гиперссылки могут также указывать на группы новостей и узлы Gopher, Telnet и FTP.) и элементы ActiveX (Элемент управления ActiveX. Элемент управления (такой как флажок или кнопка), служащий для выбора параметров, либо для запуска макроса или сценария, автоматизирующего выполнение задачи. Макросы для таких элементов управления можно создавать в редакторе Visual Basic для приложений, а сценарии — в редакторе сценариев.) в формах. Чтобы пользовательская панель инструментов была доступна, следует присоединить ее к шаблону.
Параметры вычислений и просмотра, установленные с помощью команды Параметры (меню Сервис).
Определите тип требуемого шаблона:
Создайте книгу, содержащую один лист. Поместите на этот лист элементы форматирования, стили (Стиль. Набор параметров форматирования, таких как шрифт, размер шрифта и отступы абзацев, которые хранятся вместе под общим именем. Когда применяется стиль, все содержащиеся в этом стиле инструкции форматирования применяются одновременно.), текст и другие данные, которые должны появляться на всех новых листах того же типа.
Чтобы просматривать рисунок первой страницы шаблона в поле Просмотр диалогового окна Шаблоны (область задач Общие шаблоны, Новая книга), в меню Файл выберите команду Свойства, откройте вкладку Документ, а затем установите флажок Создать рисунок для предварительного просмотра.
В меню Файл выберите команду Сохранить как.
В окне Тип файла выберите пункт Шаблон.
В списке Папка укажите папку, в которой должен быть сохранен шаблон.
Чтобы создать шаблон книги для использования по умолчанию (Используемый по умолчанию шаблон книги. Шаблон Книга.xlt, создаваемый для переопределения используемого по умолчанию формата вновь создаваемой пустой книги Microsoft Excel. Именно этот шаблон будет использоваться Microsoft Excel для создания пустой книги при запуске или создании книги без указания шаблона.) или шаблон листа для использования по умолчанию (Используемый по умолчанию шаблон листа. Шаблон Лист.xlt, создаваемый для переопределения используемого по умолчанию формата вновь создаваемого пустого листа Microsoft Excel. Именно этот шаблон будет использоваться Microsoft Excel для создания пустого листа при добавлении листа в книгу.), выберите папку XLStart или другую папку автозагрузки (Другая папка автозагрузки. Папка, дополняющая стандартную папку автозагрузки XLStart, содержащую книги или другие файлы, которые должны открываться автоматически при запуске Microsoft Excel, и шаблоны, которые должны быть доступны при создании книг.). Папка XLStart обычно расположена по следующему пути:
C:\Program Files\Microsoft Office\Office10\XLStart
Чтобы создать специальный шаблон книги или шаблона, выберите папку «Шаблоны», которая обычно расположена по следующему пути:
C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Шаблоны
Введите имя макроса в поле Имя файла. Выполните одно из следующих действий.
Чтобы создать шаблон книги для использования по умолчанию, введите книга.
Чтобы создать специальный шаблон книги, введите любое допустимое имя файла.
Чтобы создать шаблон книги для использования по умолчанию, введите лист
Чтобы создать специальный шаблон листа, введите любое допустимое имя файла.
В меню Файл выберите команду Сохранить, а затем — Закрыть.
Включение объектов, отключенных в безопасном режиме Microsoft Office
В меню Справка выберите команду Опрограмме.
Нажмите кнопку Отключенные объекты.
Выберите объекты, которые требуется включить.
Нажмите кнопку Включить.
Восстановление приложения, не отвечающего на запросы
В меню Microsoft Windows Пуск последовательно выберите команды Программы, Средства Microsoft Office и Восстановление приложений Microsoft Office.
В списке Приложение выберите приложение или документ, которые не отвечают на запросы.
Выполните одно из следующих действий:
чтобы выполнить попытку восстановления файлов, над которыми велась работа, нажмите кнопку Восстановить приложение;
если требуется просто закрыть приложение, утратив последние внесенные в файлы изменения, нажмите кнопку Завершить приложение.
Можно отправить в корпорацию Майкрософт отчет об ошибке, приведшей к возникновению проблемы, который будет использован для улучшения будущих версий приложения. Нажмите кнопку Отправить отчет или Не отправлять.
Excel запуск с параметрами
Из таблицы видно, что для достижения требуемого вида числа необходимо будет после импорта текста изменить формат ячейки (числового формата в FieldInfo нет).
Рис.3 Текстовый файл для импорта
Выполним следующий код:
Результат выполнения фрагмента кода показан на Рис.4.
Рис.4. Импортированный текстовый файл
С этим примером можно поэкспериментировать, меняя системные установки и заданные в программе разделители и меняя формат ячеек с числовой информацией.
Параграф 5. Вывод информации в ячейки Excel
5.1. Вывод без форматирования
Для примеров данного параграфа будем использовать файл «C:\a.xls», созданный в предыдущем пункте.
Вывод информации может быть выполнен на конкретный лист рабочей книги Excel в конкретную ячейку, поэтому нам необходимо определить объекты лист, в коллекции листов и ячейку. В глобальной области определения переменных напишем:
Обратим внимание на разницу задания всех и рабочих листов книги.
Аналогичного определения для ячеек и ячейки мы задать не можем, так как отдельно данные объекты как самостоятельные в C# отсутствуют, а есть понятие области выделенных ячеек, которая может включать одну или более ячеек, с которыми можно выполнять действия. Поэтому для ячеек, с которыми выполняется действие, введем следующее определение:
Выделенные ячейки далее могут быть объединены и с ними действия могут выполняться как с одной ячейкой. Для этого используется метод Merge:
На данном этапе мы уже использовали основные объекты иерархической структуры объектов Excel. Следует также отметить и еще одну особенность группы свойств Excel.Application. Как только мы получили ссылку на конкретный объект (книгу, лист, группу ячеек, диаграмму) их можно сделать активными, применив к ним метод Activate(). С этого момента они доступны через соответствующие свойства ActiveWorkbook, ActiveSheet, ActiveChart, ActiveCell. Кроме того, при выполнении действий с конкретным объектом, он автоматически становится активным и, действия с ним могут далее выполняться с использованием перечисленных свойств.
Точно также можно использовать и свойство Excel.Application Selection. То есть, объект может быть определен как селектированный и, далее, для ссылки на объект использоваться свойство Selection.
В следующем примере объединяется группа ячеек в одну без ссылки на выделенный объект.
Рассмотрим пример различных вариантов вывода в ячейки Excel, на различные страницы рабочей книги:
Рис.5. Результаты выполнения фрагмента кода
Результаты выполнения фрагмента кода показаны на Рис.5.
Заметим, что в C# требуется считывать и присваивать значения свойству Value2, а не Value объекта Range, так как свойство Value содержит параметр. C# не поддерживает свойства с параметрами (за исключением индексных свойств).
Если в приведенном выше примере для вывода на лист 1 выбрать не одну, а сразу несколько ячеек, то результат можно видеть на Рис.5.1. (фрагмент слева).
Однако, как и в приложении Excel, можно объединить ячейки программно, задать выравнивание и получить результат, как на Рис.5.1 (вторай слева фрагмент).
Рис. 5.1 Результаты выполнения примера c объединением ячеек
5.2. Форматированный вывод
При выводе информации часто бывает необходимо изменить формат ячейки, например, задать числовой формат, это можно сделать следующим образом:
Результат будет зависеть от системных установок Windows (Пуск|Настройка|Панель управления|Язык и стандарты) и, для данного примера, результат может быть как на Рис.5.1. (третий слева фрагмент).
Для формата даты и установки общего формата:
Результат выполнения Рис.5.1. (фрагмент справа).
Приведенный ниже пример форматирования текста в ячейках добавлен в качестве ответа на вопрос, поступивший автору 20.01.2009г.
Попробуем выполнить форматирование, показанное на Рис.6.
Рис.6. Пример форматированного вывода
Макрос, реализующий данное форматирование, имеет вид:
Его перевод на C#, будет выглядеть примерно так:
5.3. Вставка формул
Параграф 6. Защита листа и книги документа Excel
Материал прислан Андреем Ковалевым и отредактирован автором.
Для защиты листа документа или книги Excel необходимо установить пароль. В качестве пароля используется любая переменная тира string.
Второй параметр: true, означает защиту структуры (запрет перемещения, удаления, скрытия, показа или переименования, а также вставки новых листов).
Третий параметр: true, означает защиту окна (блокировка окна для восстановления размера и расположения при каждом открытии книги).
Соответственно для снятия защиты книги используем:
Функция Protect определена в Microsoft.Office.Interop.Excel.dll как:
Множество параметров функции, начиная со второго, означают:
allowUsingPivotTables разрешает использование отчетов сводной таблицы.
Для снятия защиты используем:
Параграф 7. Чтение информации из ячеек Excel
Чтение информации из ячеек Excel во многом аналогично выводу (см. выше). На выбранном листе необходимо в выбранной книге выбрать одну ячейку или объединенную группу ячеек (метод get_Range или преобразование к Excel.Range)- после чего достаточно преобразовать значения в выделенных ячейках к нужному типу данных.
Создадим в Excel новую книгу, на первом листе запишем соответственно:
Сохраним документ с именем a1.xls в корне диска C.
Запишем и выполним следующий код:
Параграф 8. Рисуем таблички
Чтобы нарисовать табличку в Excel надо научиться рисовать рамки вокруг выбранной ячейки или объединенной группы ячеек.
Шаги рисования рамки будут следующие:
Выбрать ячейку или группу ячеек на листе документа.
Выбрать стиль линии (Excel.XlLineStyle.xlContinuous). Стиль линии может быть одним из следующих: xlContinuous, xlDash, xlDashDot, xlDashDotot, xlDot, xlDouble, xlSlantDashDot, xlLineStyleNone.
Задать толщину линии (Excel.XlBorderWeight.lHairline). Толщина линии может быть одной из следующих: lHairline, xlMedium, xlThick, xlThin.
Следующий пример демонстрирует выполнение обводки вокруг ячеек (Рис.7. слева), и, тем самым, при использовании для некоторой группы объединенных ячеек, показывает принцип рисования табличек.
Рис.7. Создание рамок вокруг ячеек и выполнение заливки
Добавим две строчки к коду предыдущего примера и можем выполнить заливку ячеек (Рис.7. справа).
Параграф 9. Создание диаграмм
Рис.8. Табличка для диаграммы
8.1. Пошаговое создание диаграмм
Приведенный ниже пример может показаться громоздким, и не всегда нужно будет пройти все шаги, которые требуются для создания диаграмм, и, конечно, всегда может потребоваться еще что-то в смысле дополнительных усовершенствований стилей, надписей и т.п. Однако в примере есть все, чтобы сразу, напрямую используя приведенные ниже коды, можно было использовать в приложении связанные с таблицами диаграммы. Путь создания тоже не бесспорный и ниже приведен еще один пример, как можно вставить диаграмму на лист Excel, но отличие обоих путей не существенны, а после того как диаграмма вставлена, путь ее изменения может быть только таким, как показано в этом примере.
Некоторые общие замечания по коду:
Все глобальные объявления для примера описаны в предыдущих параграфах, здесь мы только их напомним:
Пример оперирует с данными таблицы (Рис.8), в которой специально каждая цифра была размещена в двух ячейках. Это позволило показать способ удаления лишних и пустых серий линий диаграммы.
Код испытан в решении, описанном в начале раздела. Как тип диаграммы можно задать один из предопределенных: xlArea, xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXYScatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut, xlDefaultAutoFormat.
Рис.9. Диаграммы на отдельном листе
Рис.10. Диаграммы на листе 1
Есть возможность поместить диаграмму сразу на выбранном листе. Но, в этом случае, нельзя использовать метод ActiveChart и, поэтому, предыдущий код незначительно изменится. В примере комментарии даны в строках, имеющих принципиальное отличие от кода предыдущего примера.
Код формирует туже диаграмму (Рис.10.), что и предыдущий пример.
9.2. Использование метода ChartWizard для создания диаграмм
Немного изменим код предыдущего примера для построения диаграммы с использованием ChartWizard.
Рис.11. Диаграмма, построенная с использованием ChartWizard
Код может показаться проще, но как видно из Рис.11., метод реализует далеко не все возможности и вновь придется вернуться к методам, которые мы использовали выше.
Параграф 10. Обработка событий сервера Excel
События также необходимы для создания функционального приложения для работы с Excel как и доступ к свойствам и методам. Если рассматривать использование свойств и методов, предоставляемых интерфейсом COM объекта, как прямую связь по управлению сервером, то события обеспечивают обратную связь. Благодаря наличию событий, приложение может обеспечить программную функциональность для отклика на происходящее в приложении.
Для создания обработчика событий необходимо:
1. Уяснить на какое событие, и для какого объекта мы хотим получить отклик (хотим отреагировать).
Основные события объектов Excel:
b.) связанные с поведением окна
— WindowActivate (произошла активизация окна если Excel на данный момент был активен);
— WindowDeactivate (окно потеряло фокус);
— WindowResize (изменился размер окна);
a.) все события объекта Application для пункта a, связанные с поведением объектов на листе. Генерируются только для листов данной рабочей книги.
b.) все события объекта Application для пункта b. Генерируются только для листов данной рабочей книги.
2. Инициализируем объект, для которого необходимо создать обработчик, например:
3. Находим (поставив точку после excelappworkbook . ) в списке отображенных объектов, свойств, методов и событий требуемое событие и добавляем его к объекту, например:
4. Поставив к полученной в пункте 3 строки знак += к полученной в пункте 3 строки, копируем (нажатием Tab) высвеченную подсказку в строку кода и получим:
5. Определение параметров функции кода обработчика для выбранного события.
PS: Еще проще, в контекстном меню делегата WorkbookEvents_SheetActivateEventHandler выбрать пункт Go To Difination и посмотреть параметры определенные в dll Microsoft.Office.Interop.Excel:
Рис.12. Определение параметров функции кода обработчика
Пишем код функции для события:
6. Осталось назначить имя делегату события и сделать его доступными извне (public). Для этого представим назначение обработчика события (п.4.)
Это все шаги, которые необходимо сделать для добавления событий.
Отметим, что в функции обработчиков событий Excel передаются не ссылки на конкретный объект типа Workbook, Worksheet, Chart, а переменная типа Object, которую перед использованием необходимо явно привести к требуемому типу.
Взаимодействие с серверами автоматизации выполняется аналогично выполнению фонового процесса и, поэтому, доступа к элементам, тем, которые представлены контролами или являются свойствами WindowsForm), из функций делегатов нет (хотя при выполнении, например textBox1.Text=»Перешли на лист = » + ((Excel.Worksheet)Sh).Name); прерывания не будет, но и отображения информации также не будет).
Параграф 11. Об особенности использования метода Activate в VS 2005/2008
Кто добросовестно повторял приведенные выше примеры, тот должен был заметить, что при компиляции приложения на строке
Эта двусмысленность в использовании одноименных свойства и метода объявленных в интерфейсе _Worksheet и интерфейсе DocEvents. Оба эти интерфейса наследует класс Worksheet. И, хотя использование метода Activate не приводит к двусмысленности в выполнении кода, для тех, кто привык писать «чистый код» этот «глюк» лучше устранить. Устранение можно выполнить через события Excel (см. предыдущий параграф).
Уберем из предыдущего кода в case 1: две строчки:
Вместо убранных строчек напишем вызов:
Функцию iMySheetActivate запишем следующим образом (как писать код для работы с событиями описано выше):
Делегат может быть и пустым:
Предупреждений компиляции не будет в коде предыдущего параграфа если мы изменим и обработчик нажатия кнопки 4:
Параграф 12. Автозаполнение или работа с диапазонами
Создадим файл 1.xls, в который предварительно запишем информацию, показанную на Рис.13.
Рис 13 Файл xls для автозаполнения
Выполним следующий код:
Результат выполнения кода показан на Рис.14.
Рис 14. Файл xls после автозаполнения
Параграф 13. Некоторые возможности по управлению параметрами Excel
Заключение. О богатстве возможностей
Богатства возможностей по управлению запущенным приложением потрясающе. Практически, все, что можно сделать в автономно запущенном приложении доступно и из приложения на C#. Каждый может достаточно легко выполнить то или иное действия из приложения, если создаст макрос для этого действия, а, затем, «переведет» код VBA в коды C#.
Молчанов Владислав 1.11.2004г.
Адаптировано к VS 2005/2008 14.10.2007г.
Перепечатка материала без разрешения автора не допускается.