Дополнительные сведения о программировании для MS Excel

16.3. Работа с базами данных

В примерах этого раздела используется файл Database.accdb, который должен быть расположен в корневом каталоге диска C.

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

16.3.1. OpenDatabase и QueryTable

16-04-Excel OpenDatabase.xlsm - пример к п. 16.3.1.

Самый простой и доступный способ импортировать информацию из базы данных в Microsoft Excel, это - воспользоваться специальным методом рабочего листа. Речь идет о методе OpenDatabase. Он предназначен для создания новой книги, которая содержит лист с информацией, полученной из базы данных. Получение информации из базы данных в Excel может быть полезным, например, для анализа этой информации средствами Excel.

Полный вызов метода выглядит так:

OpenDatabase(Filename, CommandText, CommandType, BackgroundQuery, ImportDataAs)

Рассмотрим параметры метода.

• Filename - имя и расположение базы данных.
• CommandText - Текст запроса к базе данных. Здесь можно указать имя таблицы базы данных, которая должна быть открыта.
• CommandType - тип запроса - xlCmdCube (куб), xlCmdList (список), xlCmdSql (SQL), xlCmdTable (таблица).
• BackgroundQuery - если установлен в True - обработка данных ведется в фоновом режиме, если в False - в обычном.
• ImportDataAs - способ импорта данных. Может принимать два значения - первое - xlPivotTableReport (данные будут импортированы в виде сводной таблицы - Pivot Table ), второе - xlQueryTable (данные будут импортированы с помощью QueryTable - в виде обычной таблицы).

Чтобы рассмотреть пример использования этой команды, создадим простую базу данных, состоящую из двух таблиц. Первая таблица представляет собой список клиентов, вторая - список их покупок, где учитывается лишь сумма покупки на определенную дату. Таблица клиентов имеет имя Клиенты, таблица покупок - имя Покупки. Импортируем с помощью метода OpenDatabase таблицу Покупки в документ MS Excel. Предположим, что база данных хранится на диске C:, ее имя - Database.accdb. Добавим на лист MS Excel кнопку, содержащую такой код (листинг 16.5.)

Workbooks.OpenDatabase _
        Filename:="C:\Database.accdb", _
        CommandText:="Покупки", _
        CommandType:=xlCmdTable, _
        BackgroundQuery:=True, _
        ImportDataAs:=xlQueryTable

После нажатия на кнопку будет создана новая книга, лист которой, названный по имени базы данных, будет содержать импортированные данные (рис. 16.1.).

Рис. 16.1. QueryTable в документе

Чтобы импортировать данные как PivotTable, нам понадобится такой код (листинг 16.6.) - его мы добавим в обработчик события Click другой кнопки на рабочем листе книги-примера.

Workbooks.OpenDatabase _
        Filename:="C:\Database.accdb", _
        CommandText:="Покупки", _
        CommandType:=xlCmdTable, _
        BackgroundQuery:=True, _
        ImportDataAs:=xlPivotTableReport

На рис. 16.2. вы можете видеть результат выполнения команды - сводную таблицу, с которой можно продолжать дальнейшую работу.

увеличить изображение
Рис. 16.2. Сводная таблица в документе MS Excel

Теперь рассмотрим еще один метод получения информации из БД.

16.3.2. ADO

16-05-Excel ADODB Query.xlsm - пример к п. 16.3.2.

QueryTable можно добавить на рабочий лист, предварительно настроив ее параметры.

Объекты QueryTable объединены в коллекцию QueryTables. Важнейший метод этой коллекции - Add - он добавляет новую таблицу в указанную позицию на листе. Вызов метода Add выглядит так:

WorkBook.QueryTables.Add(Connection, Destination)

В качестве параметра Connection обычно используют объект ADODB.Recordset, о котором ниже, а Destination - это объект Range, который указывает на диапазон (или ячейку), куда будет добавлена QueryTable. Если в Destination задана ячейка, левая верхняя ячейка вставляемой таблицы таблицы совпадет с ячейкой.

Для работы с базами данных используется объектная модель ADO. Чтобы подключить ее к проекту, выберите в окне References пункт Microsoft ActiveX Data Object 2.8 Library - обращаться к ней можно, используя имя объекта ADODB.

ADO - это очень мощный механизм для доступа к источникам данных. Здесь мы рассмотрим методику получения информации из БД с использованием ADO. Нас будут интересовать несколько ключевых объектов ADO.

Во-первых - это объект ADODB.Connection, который позволяет установить соединение с базой данных и работать с ней. У объекта Connection есть свойство ConnectionString - оно представляет собой строку, содержащие параметры подключения к базе данных, в частности - адрес файла базы данных и имя драйвера. Метод Open объекта Connection используется для открытия соединения, заданного свойством ConnectionString.

Во-вторых - объект ADODB.RecordSet - он позволяет получать из открытой базы данных определенные порции информации.

Для получения данных используется метод объекта Open, которому передается запрос на получение данных, а так же - открытое соединение.

Давайте рассмотрим пример. Здесь мы подключаемся к базе данных и создаем Query Table на основе объекта RecordSet, в котором хранится информация, полученная из базы (листинг 16.7.).

'Для хранения ссылки на
    'Query Table
    Dim obj_Query As QueryTable
    'Для ссылки на соединение с
    'базой данных
    Dim obj_ADOConn As ADODB.Connection
    'Для ссылки на набор записей,
    'полученный из БД
    Dim obj_ADORec As ADODB.Recordset
    'Если в ячейке A5 есть данные     'значит мы уже вставляли сюда Query Table
    'если данных нет - начинаем работу с БД
    If ActiveSheet.Range("A5") <> "" Then
        MsgBox "Уже есть Query Table в этом диапазоне"
    Else
    'Создаем новое соединение
    Set obj_ADOConn = New ADODB.Connection
    'Вносим в ConnectionString параметры
    'соединения. В Provider - имя драйвера,
    'который используется для доступа к
    'БД, а так же - параметр Data Source
    'который отвечает за адрес источника данных
    obj_ADOConn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Database.accdb"
    'Подключаемся к базе данных
    obj_ADOConn.Open
    'Создаем новый объект RecordSet     'Он хранит результат запроса к БД
    Set obj_ADORec = New ADODB.Recordset
    'Выполняем запрос
    'В параметре Source хранится строка
    'SQL-запроса
    'В ActiveConnnection - открытое соединение
    obj_ADORec.Open _
        Source:="SELECT * FROM Покупки", _
        ActiveConnection:=obj_ADOConn
    'Создаем новую Query Table, в качестве
    'источника данных передаем заполненный
    'данными RecordSe
    Set obj_Query = _
        ActiveSheet.QueryTables.Add _
        (obj_ADORec, Range("A5"))
    'Используем метод Refresh для того
    'чтобы таблица, заполненная данными
    'была отображена на листе
    obj_Query.Refresh
    End If

Если вы хотите эффективно работать с базами данных - вам придется научиться строить SQL-запросы, изучить особенности взаимодействия с различным видами БД и так далее.

16.4. Работа с диаграммами

16-06-Excel Chart.xlsm - пример к п. 16.4.

Для работы с диаграммами используют объект Chart. Чтобы добавить диаграмму на лист можно применить методом AddChart коллекции Shapes..

Такой код (листинг 16.8.) добавляет диаграмму на активный лист:

ActiveSheet.Shapes.AddChart

Когда диаграмма добавлена, можно настроить ее свойства, в частности, при помощи метода SetSourceData задать диапазон (объект типа Range ), содержащий информацию, которая должна быть визуализирована. Этот метод принимает два параметра. Первый - Source - отвечает за источник данных, второй - PlotBy - определяет, как берутся данные для диаграммы - по столбцам ( xlColumns ) или по строкам ( xlRows ).

Так же после добавления диаграммы обычно настраивают ее тип - это делается с помощью свойства CharType. Оно может принимать одно из более чем 70 значений типа xlChartType. Например, xlConeCol - это трехмерная коническая диаграмма, xlPie - круговая диаграмма, xlLineMarkers - график с маркерами.

Рассмотрим пример (листинг 16.9.). Добавим на рабочий лист обычную линейную диаграмму, используя диапазон значений, выделенных пользователем.

'Для хранения ссылки
    'на диаграмму
    Dim obj_Chart As Chart
    'Для хранения ссылки на
    'диапазон входных значений
    Dim obj_Range As Range
    Set obj_Range = Selection
    'Добавляем новую диаграмму и
    'тут же выделяем ее
    ActiveSheet.Shapes.AddChart.Select
    Set obj_Chart = ActiveChart
    'Настраиваем исходные данные
    'для диаграммы
    obj_Chart.SetSourceData _
        Source:=obj_Range, _
        PlotBy:=xlRows
    'Устанавливаем тип для диаграммы
    obj_Chart.ChartType = xlLine

16.5. Выводы

В этой лекции мы рассмотрели некоторые дополнительные возможности программирования для MS Excel. Наше следующее занятие посвящено практическим примерам программирования для MS Excel.