$280.17


$444.25


$21.94


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

 

Интересное

Будущее - за Ethernet DSLAM
Популярность существующих методов широкополосного интернет-доступа (DSL) во многом обеспечивает стабильное состояние рынка решений DSLAM (Digital Subscriber Line Access Multiplexer). Современные...
Подробнее...
ШРИФТ
Удобочитаемость шрифта.Удобочитаемость является одним из важнейших достоинств хорошего шрифта. Это не только общая оценка пригодности его формы, но и показатель красоты. На удобочитаемость влияют...
Подробнее...
Виртуальный выделенный сервер
В последнее время некоторые российские провайдеры стали предоставлять услуги хостинга на виртуальных выделенных серверах. Во всём мире эта услуга не нова. Там, наряду с обычным хостингом, она...
Подробнее...
Уменьшаем траффик вдвое
Заставила меня написать эту статью интересная возможность. Это возможность пользоваться сетью, используя любую технологию, в которой оплата взимается по траффику(например, ADSL или GPRS), снижая...
Подробнее...
Установка Windows Server...
Семейство продуктов Windows Server 2003 берет все самое лучшее от технологии ОС Windows 2000 Server, упрощая при этом развертывание, управление и использование.В результате пользователь получает...
Подробнее...
Советы по графике в Delphi
Вы наверное часто видели довольно хитроумные картины, на которых непонятно что изображено, но все равно необычность их форм завораживает и приковывает внимание. Как правило, это хитроумные формы...
Подробнее...
Учимся работать с DelphiX
Говоря техническим языком, DirectX — набор объектов COM (Component Object Model), которые реализуют интерфейсы для облегчения работы с видеоаппаратурой, звуком, межкомпьютерными соединениями и...
Подробнее...
DNS в Windows 2003
DNS — не роскошь, а необходимостьПротокол, определявший порядок обмена информацией в Интернете, описывал в том числе и систему адресации компьютеров, объединенных в эту Сеть. Согласно этой...
Подробнее...
Новый троян использует...
Специалисты по вопросам компьютерной безопасности предупреждают о появлении новой вредоносной программы под названием Mebroot, при помощи которой злоумышленники теоретически могут захватить полный...
Подробнее...
Пишем описания и титлы
Как же повысить эффективность регистрации в каталогах? Ну, во-первых, регистрировать только в беляках (каталоги не требующие за размещение обратной ссылки или денег), во-вторых, использовать как...
Подробнее...