Достаточно часто требуется сформировать отчет и вывести его не только на печать, но и выгрузить в 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 — набор данных. Он позволяет получать данные из базы данных и обновлять их.

Первое, что мы должны сделать это подключить Microsoft Data Objects Library 2.6 (для офисовского Microsoft Visual Basic — Tools->References). Следующим шагом является создание объекта Connection:

«Объект 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»;

Параметры подключения к MS AccessProvider    Поставщик 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.0\ISAM Formats]

Следующим мы рассмотрим объект Command.

С его помощью можно выполнять запросы к базе данных, не возвращающие записей. Самым важным его свойством является, пожалуй ActiveConnection. Это свойство используется для указания подключения к базе данных, которое будет использоваться для выполнения запроса. И, собственно для выполнеия запроса, используется метод Execute, который в качестве параметра принимает строку запроса, написанного на языке SQL.

Приведем пример вставки данных в таблицу Attain (в данном случае — это физический файл D:\APR\Attain.dbf)

»Объект Connection
Dim conn As ADODB.ConnectionSet conn = New ADODB.Connection
«Объект Command
Dim comm As ADODB.CommandSet 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, MovePrevious, MoveFirst, MoveLast, Move
С помощью скобок и указанного в них порядкового номера поля можно обращаться к конкретному полю текущей записи, например records(0) — позволяет обратиться к первому полю текущей записи.

Свойства EOF и BOF принимают значение true в случае, если достигнут либо конец, либо начало таблицы соответственно
Для обновления данных предназначен метод Update
Для вставки — AddNew
Для удаления — Delete
Закрываем RecordSet — метод Close
Для работы с базой данных Attain методы Update, AddNew и Delete, как правило не нужны, лучше и безопасней использовать встроенные средства языка C/Al обновления данных — метод Validate для вставки и т.д. А вот использовать их для операций выборки данных  сам Бог велел — намного быстрее (в десятки-сотни раз). Но и тут не следует забывать, что при этом создается новое подключение к базе данных (вопрос лицензирования количества активных сессий), но зато ненадолго. Думаю, всегда есть несколько свободных подключений, и использовать одно из них на несколько секунд-минут не так уж и страшно.

Хочется еще напомнить как отображаются названия таблиц 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.ConnectionSet conn = New ADODB.Connection
»Строка подключения
connString = »Provider=SQLOLEDB;Data Source=(local);Initial Catalog=« + _ Db + »;user id=« + user + »;password=« + Passwordconn.Open connString
«набор строк
Dim records As ADODB.RecordsetSet records = New ADODB.Recordset records.ActiveConnection = connsqlString = »select top 10 No_, [Name] from [CRONUS Россия Extended$Customer]"records.Open sqlString»Просмотр строк в цикле и вывод в книгу Excel
i = 1While Not records.EOF Sheet1.Cells(i, 1) = records(0) Sheet1.Cells(i, 2) = records(1) records.MoveNext i = i + 1Wend records.Closeconn.Close