$340.92


$981.45


$208.44


Работа с 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

 

Интересное

Что моя бабушка говорила...
Закон о возмещении ущерба в случае похищения персональных данных 2007 года получил единодушное согласие сената. Как это часто бывает с нашей законодательной властью, две палаты конгресса —...
Подробнее...
Определение наличия...
По статистике, около 90% пользователей в наши дни пользуются Internet Explorer (5-й или 6-й версией). В этих браузерах Flash, как правило, уже установлен (если же нет, то, при наличии...
Подробнее...
Outlook Express,...
Для этого нужно сохранить в другом месте файлы с нужной корреспонденцией (например, Входящие.dbx и другие с таким же расширением) и гдебы даже и на бумажке. После того как вы переустановите...
Подробнее...
Протоколы интернет
Прародителем сети интернет была сеть ARPANET. Первоначально её разработка финансировалась Управлением перспективного планирования (Advanced Research Projects Agency, или ARPA). Проект стартовал...
Подробнее...
Установка свойств для...
Установка свойств для разных объектов на странице является одной из простейших задач при программировании на VBScript. Стандартная схема этого действия выглядит так:
Подробнее...
Внимание! Отказ в...
Описание: Уязвимость позволяет удаленному пользователю вызвать отказ в обслуживании приложения.Уязвимость существует из-за ошибки при обработке пакетов, отправленных на порт 48000 UDP/TCP....
Подробнее...
Как обнаружить утечку памяти
При разработке больших приложений, оперирующих большими объемами информации на первое место при отладке встает проблема обнаружения неправильного распределения памяти. Суть проблемы состоит в том,...
Подробнее...
Требования к CMS в эпоху...
По поводу перспектив Веб 2.0 существует множество мнений и часто диаметрально противоположных, однако едва ли кто-либо станет спорить с тем, что эта концепция заметно сказалась на современном...
Подробнее...
Протоколы интернет
Прародителем сети интернет была сеть ARPANET. Первоначально её разработка финансировалась Управлением перспективного планирования (Advanced Research Projects Agency, или ARPA). Проект стартовал...
Подробнее...
Windows Vista - архивация...
В Windows Vista добавлены новые ценные возможности, помогающие избежать потери важной информации. Windows Vista предлагает несколько уровней архивации и восстановления данных, обеспечивающих...
Подробнее...