ADO в Microsoft Excel
Достаточно часто требуется сформировать отчет и вывести его не только на печать, но и выгрузить в Excel. Последнего можно добиться 2 способами, либо получить данные средствами языка Navision C/Al и затем выгрузить их в Excel, подключив его как ActiveX-компонент, или в csv-файл. Либо непосредственно из Microsoft Excel выполнить запрос к базе данных Navision. У последнего способа то преимущество, что при этом можно использовать возможности Excel для расчета данных и формирования отчета.В этой статье рассмотрены основные принципы ADO - AxctiveX Data Objects, и приведен пример выборки данных из SQL Server в Excel.
Объектная модель ADO.
В ADO для работы с данными существуют три типа объектов:
- Connection - для подключений к базе данных.
- Command - для выцполнения запросов к базе данных, не возвращающих результатов (создание таблиц - СREATE TABLE, операции вставки - INSERT, обновления - UPDATE, удаления данных - DELETE и т.д.)
- RecordSet - набор данных. Он позволяет получать данные из базы данных и обновлять их.
'Объект Connection
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
'Строка подключения
connString = "Provider=SQLOLEDB;Data Source=(local);" + _
"Initial Catalog=myDatabase;user id=userName;password=passwd"
conn.Open connString
Самое интересное здесь это строка подключения. В данной случае указан провайдер SQLOLEDB, который используется для создания подключений к СУБД MS SQL Server. Параметр Data Source используется для указания конкретного SQL Server'a, в примере используется локальный SQL Server. Initial Catalog - база данных SQL Server (для Attain - база данных Attain, расположенная на SQL Server). Остальные параметры, я думаю, не нуждаются в объяснении.
Кроме SQL Server можно подключаться и к другим источникам данных, скажем пример строки подключения для MS Access:
connString = "Provider= Microsoft.Jet.OLEDB.4.0;Data Source= E:\AccessBases\base1.mdb";
Либо, если стоит пароль на базе данных Access:
string connString = "Provider= Microsoft.Jet.OLEDB.4.0;Data Source= E:\AccessBases\base1.mdb;JetOLEDB:Database Password=passwd";
Provider | Поставщик OLEDB. Для MS Access: Microsoft.Jet.OLEDB.4.0 |
Data Source | Путь к базе данных |
Jet OLEDB:Database Password | Пароль |
Аналогично можно подключаться и к базе данных формата dBase:
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\apr\;Extended Properties=dBase IV;mode=Read|Write|Share Deny None"
В данном примере предпологается, что база данных - это набор файлов в папке D:\APR\. Название файла - это, по сути, название таблицы. Параметром Extended Properties указывается стандарт базы данных, список всех возможных значений этого параметра можно посмотреть в реестре:
[HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\ISAM Formats]
Следующим мы рассмотрим объект Command.
С его помощью можно выполнять запросы к базе данных, не возвращающие записей. Самым важным его свойством является, пожалуй ActiveConnection. Это свойство используется для указания подключения к базе данных, которое будет использоваться для выполнения запроса. И, собственно для выполнеия запроса, используется метод Execute, который в качестве параметра принимает строку запроса, написанного на языке SQL.
Приведем пример вставки данных в таблицу Attain (в данном случае - это физический файл D:\APR\Attain.dbf)
'Объект Connection Dim conn As ADODB.Connection Set conn = New ADODB.Connection 'Объект Command Dim comm As ADODB.Command Set comm = New ADODB.Command 'Строка подключения connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\apr\;" + _ Extended Properties=dBase IV;mode=Read|Write|Share Deny None" 'Открытие подключения conn.Open connString 'Указания активного подключения comm.ActiveConnection = conn 'Строка запроса на SQL CommandText = "insert into Attain (ID, DESCRIPT) values(4, 'четыре')" 'Выполнение команды conn.Execute CommandText 'Закрытие подключения conn.Close
Ну и самый самый важный объект - RecordSet. Он позволяет производить любые операции с данными. Алгоритм работы с ним следующий:- Создание объекта Connection
- С помощью свойства ActiveConnection указывается активное подключение к базе данных (как у Command)
- Открытие объекта RecordSet. Метод Open принимает в качестве параметра строку запроса на SQL.
- Для перемещения по выбранным записям существуют методы: MoveNext, Movepvious, MoveFirst, MoveLast, Move
- С помощью скобок и указанного в них порядкового номера поля можно обращаться к конкретному полю текущей записи, например records(0) - позволяет обратиться к первому полю текущей записи.
- Свойства EOF и BOF принимают значение true в случае, если достигнут либо конец, либо начало таблицы соответственно
- Для обновления данных предназначен метод Update
- Для вставки - AddNew
- Для удаления - Delete
- Закрываем RecordSet - метод Close
Хочется еще напомнить как отображаются названия таблиц Navision Attain на сиквельные таблицы. Точки при этом заменяются на подчеркивания, и спереди через знак $ добавляется наименование фирмы. К примеру у нас есть таблица "Gen. Journal Line" и фирма CRONUS Россия Extended. В SQL Server эта таблица будет выглядеть как [CRONUS Россия Extended$Gen_ Journal Line].
Ну и пример выборки данных:
'База данных Db = "devel" 'Имя пользователя user = "sa" 'Пароль Password = "password" 'Объект Connection Dim conn As ADODB.Connection Set conn = New ADODB.Connection 'Строка подключения connString = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=" + _ Db + ";user id=" + user + ";password=" + Password conn.Open connString 'набор строк Dim records As ADODB.Recordset Set records = New ADODB.Recordset records.ActiveConnection = conn sqlString = "select top 10 No_, [Name] from [CRONUS Россия Extended$Customer]" records.Open sqlString 'Просмотр строк в цикле и вывод в книгу Excel i = 1 While Not records.EOF Sheet1.Cells(i, 1) = records(0) Sheet1.Cells(i, 2) = records(1) records.MoveNext i = i + 1 Wend records.Close conn.Close