Экспорт данных в EXCEL средствами VB.NET

Продукты компании Microsoft крепко вошли в нашу жизнь. Не смотря на разные нарекания в сторону самой компании и ее продуктов, мало кто может похвастаться столь функциональными и удобными программами. Даже те кто является ярым приверженцем того что Microsoft это зло и использует, например, сторонние операционные системы, как бы это не было парадоксально, ставят себе на компьютер эмуляторы Windows и прочие примочки, которые позволяют работать программам от Microsoft или под Microsoft. И Excel и VB.NET вместе со всей студией являются большими, занимающими лидирующие положения программными обеспечениями. Одним пользуетесь Вы, раз читаете статью, другим пользуются ваши заказчики. Очень часто клиенты хотят видеть результаты работы программ, или как их любят называть «АРМ» и «ИС», в виде документов, с которыми они умеют работать, которые можно корректировать, которые берут на себя часть формирования наглядности. И их выбором становиться файлы в формате Excel. И нет ничего проще, чем написать работу в языке программирования от Microsoft c другой программой от той же фирмы, о чем мы и поговорим.

 

Весь процесс написания экспорта данных из программы в Excel можно свести к нескольким очень простым этапам: Подключения ссылки (Reference) на Excel в IDE студии; организации функции экспорта с объявлением соответствующих объектов, строками вывода и стилизации; эксперименты в самом Excel, а точнее с макросами, для получения значений необходимых констант и отыскания необходимых методов и свойств. Теперь по порядку.

Вначале необходимо подключить к вашему проекту библиотеку (ссылку, reference). Это можно сделать в меню Проект->Добавить ссылку (Project –> Add reference…). После выбора этого пункта меню перед Вами появиться модальное окно, с которым многие из Вас уже знакомы. Для тех, кто видит его впервые, опишу его кратко. В этом окне вы можете подключать к вашему проекту сторонние библиотеки (как правило файлы формата .dll), которые несут в себе какой-либо функционал. Библиотеки написаны на различных языках программирования и с применением различных технологий, но это не мешает их работе в вашем проекте. Особый интерес тут представляют три вкладки, одной из которых мы воспользуемся. Это вкладки .NET, COM, и Browse (Открыть существующие). Назначение последней вкладки понятно из названия. Различия первых двух состоит в различии технологий, которые использовались при написании данных библиотек. Вкладка .NET содержит библиотеки которые написаны с применением технологии .NET, более современной технологии. Библиотеки вкладки COM, это библиотеки написанные по старой технологии, которые не являются родными для студии VS.NET, но которые также могут работать в Вашей программе.

В нашем случае нас интересует вкладка .NET, в списке библиотек которой Вы можете найти Microsoft.Offoce.Interopt.Excel. Если их будет несколько, обратите внимание на версии. После того как вы выберете нужную Вам библиотеку нажмите кнопку Ok, и на этом этап подключения библиотеки будет закончен.

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

Dim app As New Microsoft.Office.Interop.Excel.Application
Dim book As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Add()
Dim sheet As Microsoft.Office.Interop.Excel.Worksheet = book.ActiveSheet
sheet.Range("A1").Value = "Дарова мир!!!"
app.Visible = True

 

В результате выполнения этих строк перед вами, на экране монитора, появиться окно Excel, с открытой книгой содержащей одни лист. В ячейке A1 будет написано «Дарова мир!!!».

Рассмотрим детали кода. Первые три строки объявляют необходимые три объекта для работы с документами Excel. Первая фактически создаёт и запускает копию самого Excel без каких либо открытых книг. Вторая строка создает объект, который является новой книгой в нашей копии программы. Ну и третья строк добавляет один единственный лист в книгу и объявляет объект, при помощи которого мы будем заносить в лист информацию. Четвертая строка, это самая простейшая манипуляция над листом – внесение текстовой информации в ячейку. Последней строкой мы делаем нашу копию Excel видимой. До этого момента она является не видимой с той целью, чтобы пользователи не пугались видя процесс внесения информации (при больших объёмах данных он может быть заметен не вооруженным глазом) и не могли помешать работе функции экспорта.

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

Допустим Вы хотите выделить текст одной из ячеек при экспорте данных полужирным начертанием. Делам следующее. Открываем Excel. Пишем в одной из ячеек произвольный текст, кликаем по другой ячейке. После этого необходимо начать запись макроса. Например в 2007 офисе это можно сделать в меню Вид->Макросы->Запись макроса, после чего появиться окно, в котором необходимо поставить любую букву в поле «Сочетание клавиш» и в списке «Сохранить в» выбрать «Эта книга», нажать кнопку Ок. После этого Excel начнет записывать в макрос в виде строк кода на языке VBA (Visual Basic for Application) все то, что Вы будете проделывать с листами и книгой. Выделите ячейку с произвольным текстом, при помощи меню сделайте ее начертание полужирным. Excel моментально запишит строчки кода в макрос. Чтобы сильно не мусорить в макросе по возможности больше ничего не делайте с ячейками, листами и книгой. Пройдите в меню Вид->Макросы->Остановить запись. Далее в меню Вид->Макросы[->Макросы] выберете последнее из списка и нажмите кнопку изменить. Откроется окно с кодом макроса примерно такого содержания:

Sub Макрос1()
'
' Макрос1 Макрос
'
' Сочетания клавиш: Ctrl+r
'
Range("D8").Select
Selection.Font.Bold = True
End Sub

 

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

Dim app As New Microsoft.Office.Interop.Excel.Application
Dim book As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Add()
Dim sheet As Microsoft.Office.Interop.Excel.Worksheet = book.ActiveSheet
sheet.Range("A1").Value = "Дарова мир!!!"
sheet.Range("A1").Font.Bold = True
app.Visible = True

 

Таким образом, мы можем получить любые интересующие нас фрагменты кода.

Чтобы не затруднять ваши поиски элементарных, обычно используемых вещей я приведу некоторые методы, своийства и значения констант.

 

'Объединение ячеек
sheet.Range("A1:F1").Merge()
'Выравнивание по центру
sheet.Range("A1:F1").Cells.HorizontalAlignment = -4108
'Задаем ширину столбца в условных еденицах
sheet.Range("A:A").ColumnWidth = 40
'Перенос текста в ячейках строки (можно указать конкретную ячейку)
sheet.Range("B:B").WrapText = True
'Рисуем рамку вокруг ячейки или диапозона
sheet.Range("A1").BorderAround()
'Устанавливаем формулу
sheet.Range("D6").Value2 = "=СУММ(D5:D1)"

admin

2 комментария к “Экспорт данных в EXCEL средствами VB.NET”

Вы можетеоставить отзыв или Обратную ссылку эта запись.
  1. ля - 26 декабря, 2020

    на атличненька

  2. л - 15 марта, 2021

    спасибо

Нужно войти чтобы оставить комментарий.