$29.61


$27.52


$474.53


Работа с ADO в Microsoft Excel

Достаточно часто требуется сформировать отчет и вывести его не только на печать, но и выгрузить в Excel. Последнего можно добиться 2 способами, либо получить данные средствами языка Navision C/Al и затем выгрузить их в Excel, подключив его как ActiveX-компонент, или в csv-файл. Либо непосредственно из Microsoft Excel выполнить запрос к базе данных Navision. У последнего способа то преимущество, что при этом можно использовать возможности 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 — набор данных. Он позволяет получать данные из базы данных и обновлять их.

Первое, что мы должны сделать это подключить 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

 

Интересное

Интернет - трагедия или...
Доброго времени суток, уважаемый читатель. Я бы хотел поведать тебе, да и всем людям, мою точку зрения на влияние Интернета на людей, да и вообще, об Интернете в целом.Естественно, такую тему...
Подробнее...
Компьютеры для офиса
На сегодняшний день компьютеры стали самой естественной и неотъемлемой частью жизни практически для каждого современного человека. Это очень удобно при ведении бизнеса, как маленькой фирмы, так и...
Подробнее...
Новый троян использует...
Специалисты по вопросам компьютерной безопасности предупреждают о появлении новой вредоносной программы под названием Mebroot, при помощи которой злоумышленники теоретически могут захватить полный...
Подробнее...
Забавное программирование...
Приведённый здесь материал можно озаглавить не иначе как «Чем заняться программисту, если нечего делать». На самом деле, Delphi настолько интересная среда, что в ней наряду с разработкой серьёзных...
Подробнее...
25 советов как ускорить...
Цель нижеописанных советов избавить установленную Windows XP Pro Russian от совершенно ненужных для домашнего пользователя украшательств и ускорить в целом работу системы. Эти 25 советов, только...
Подробнее...
Дорога в KDE4
Недавно, а именно 11 января наконец-то состоялся долгожданный релиз 4 версии популярного рабочего окружения KDE (KDE – это K Desktop Environment «К-интегрированная рабочая...
Подробнее...
CGI Perl FAQ
Что такое CGI и зачем мне все это нужно, если я не умею программировать, да и учиться на старости лет нет особого желания?CGI (Common Gateway Interface) — это шлюз, с помощью которого...
Подробнее...
Введение в протокол UDP
UDP (User Datagram Protocol, Протокол дейтаграмм пользователя) предназначен для обмена дейтаграммами между процессами компьютеров, входящих в единую сеть с коммутацией пакетов. В качестве...
Подробнее...
Как пользоваться модулем...
Наверное, все из вас знают что такое интернет, сайт и Apache (веб-сервер). Немного углубившись в структуру веб-сервера скажу, что у апача есть еще и много модулей, которые обеспечивают...
Подробнее...
Реальный домен виртуальной...
Краткая хронологияПроцедура регистрации домена .SU была инициирована в середине августа 1990 года, до окончательной регистрации дело дошло 19 сентября. Забавно, что почти ровно через год произошли...
Подробнее...