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