Remkomplekty.ru

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel через odbc

«MS Excel » через «ODBC»

Постановка задачи: необходимо создать подключение к файлам « MS Excel » через « ODBC ».

Для создания нового подключения в « Windows XP » нужно перейти в раздел «ПУСК – Панель управления – Администрирование – Источники данных ( ODBC )». Далее, на закладке « Пользовательский DSN » нажать кнопку «Добавить…».

Рис. 1. АИД ODBC , закладка «Пользовательский DSN »

Затем надо выбрать драйвер « Driver do Microsoft Excel (*. xls ) » .

Рис. 2. Выбор нужного драйвера

Далее нужно ввести имя и описание для нового подключения.

Рис. 3. Ввод имени и описания

После этого всего новое подключение появится в списке источников данных на закладке «Пользовательский DSN » окна «АИД ODBC ».

Рис. 4. Окно АИД ODBC , после добавления нового подключения

Проверить работу только что созданного подключения можно в среде разработки « Delphi 7». Для этого в новом приложении нужно перетащить на форму компоненты: ADOConnection , ADOTable , DataSource , DBGrid .

Самое важное в этом деле это настроить строку подключения в компоненте « ADOConnection 1». Кликаем по нему 2 раза и запускается мастер создания строки подключения.

На закладке «Поставщик данных» выбираем « Microsoft OLE DB Provider for ODBC Driver ».

Рис. 5. Закладка «Поставщик данных»

Затем на закладке «Подключение» выбираем вариант «Использовать строку подключения» и начинаем использовать ее по полной. Т.к. в нашем подключении не указана конкретная БД, точнее файл. Нажимаем кнопку «Сборка…».

Рис. 6. Закладка «Подключение»

Потом выбираем источник на закладке «Источник данных компьютера» и жмем кнопку « Ок ».

Рис. 7. Закладка «Источник данных компьютера»

Появится диалог выбора конкретного файла на диске. Ищем нужный файл и выбираем.

Рис. 8. Диалог выбора файла

После этого можно проверить подключение, нажав на кнопку «Проверить подключение», но наверняка все в порядке.

Далее донастраиваем настройки компонентов. Параметр « ConnectionString » компонента « ADOConnection 1» можно переделать, а можно и так оставить. Дело в том, что этот параметр заполнился так, что свойство « DeafaultDatabase » по сути пустует, хотя все нормально работает и к файлу «Магазины. xls » доступ есть.

Программное соединение с Excel через ODBC

Всем привет. Сегодня я хочу поделиться своим опытом программного взаимодействия с Excel таблицами через ODBC драйвер. Использовать я буду фреймворк Qt и язык C++. Есть некоторые нюансы взаимодействия и я постараюсь охватить как можно больше.

Итак, что мы имеем: с одной стороны есть приложение с данными в виде модели, с другой стороны Excel файл. Взаимодействие идет через драйвер ODBC(стандартный драйвер ODBC поставляется вместе с фреймворком Qt — qsqlodbc4.dll). Задача — необходимо с проецировать модель с данными в excel файл.

Для того, чтобы у приложения всегда был пустой шаблон Excel файла, готовим его заранее и добавляем в ресурсы(чтобы шаблон был включен в exe файл при сборке). Теперь что касается шаблона Excel файла. Для драйвера ODBC лист Excel файла выглядит как обычная таблица, где есть столбцы и строки, поэтому нам нужно немного подготовить наш шаблон.

Во первых — название листа на латинице. Я пытался писать на русском, но драйвер упорно не хотел видеть лист. Возможно я что-то неверно делал, но я устал от такого поведения драйвера и облегчил нам обоим жизнь , назвал лист — list

Затем, драйверу необходимо иметь названия столбцов для работы. Я пытался использовать столбцы самого Excel, но драйвер их не видит. Он считывает первую строку на листе и считает что эта строка и есть строка с названиями столбцов. Поэтому я сделал небольшую хитрость. Для первой ячейки ввел A1, затем A2 и протягиванием создал строку с именами от А1 и до А100. Такого запаса мне хватило бы для запроса с самым большим количеством столбцов, которое было возможно. Вот примерно все, что касается подготовки шаблона Excel

Шаблон я добавил в ресурсы. Теперь процесс выгрузки данных. Получаем модель с данными и копируем файл из ресурсов туда, куда указал пользователь:

Далее создаем соединение с Excel

Далее считываем информацию с модели, чтобы сформировать цикл вставки данных. Выбираем название столбцов, их количество, количество строк. Затем первым запросом вставляем названия столбцов из модели

Читать еще:  Как создать надстройку в excel

где sSetFields — имена полей из шаблона excel(A1,A2 и т.д.), а sSetValues — это уже реальные имена полей из модели. Эти два массива формируются предварительно перед операцией вставки. Например A1-ID, A2-NAME, A3-TELEPHONE и т.д. Это сделано чтобы можно было заменить наши бессмысленные A1, A2 на реальные поля

После этого в цикле в зависимости от количества строк вставляем данные

После этого закрываем соединение с Excel и открываем Excel файл на просмотр

Файл открывается с нашей злополучной строкой A1, A2 которая уже не нужна. Можно удалить ее прям в Excel. Я для этих целей использовал activeX команду

Можно также попробовать удалить строку через SQL DELETE FROM …. Но у меня почему то не вышло, поэтому использовал activeX

Подключение к InterBase или Firebird из Excel через ODBC

Для начала нужно установить драйвер ODBC для Firebird или InterBase (разумеется, перед этим нужно убедиться, что на компьютер уже установлена клиентская часть InterBase или Firebird).

Затем создать алиас ODBC:

  1. Открыть Панель управления
  2. Администрирование, Источники данных (Data Sources (ODBC)).
  1. ODBC-драйверы InterBase и Firebird не работают напрямую с сервером. Для их работы нужна клиентская часть (gds32.dll, ibclient64.dll, fbclient.dll), причем той же разрядности, что и приложение и ODBC драйвер.
    Например, если приложение 32-разрядное, значит нужен 32-разрядный ODBC и 32-разрядный клиент. Разрядность InterBase или Firebird при этом не имеет значения, он может быть как 32битным, так и 64битным.
  2. Переключиться на закладку System DSN
  3. Нажать кнопку Add. Выбрать драйвер.
  4. Настроить параметры драйвера

Пример для Firebird ODBC

Пример для InterBase DataDirect ODBC

Разумеется, если Firebird или InterBase (как сервер) установлены у вас на компьютере, вы можете указывать параметр «База данных» как c:dirdata.gdb или localhost:c:dirdata.gdb

Указание localhost: избавит вас от проблем, если вы запутались с клиентскими библиотеками Firebird и InterBase (и получили ошибку unavailable database).

Если на локальной машине Firebird или InterBase нет, то вместо localhost вы указываете имя сервера.

Нажмите на кнопку «Проверка соединения». Если все указано правильно, драйвер сообщит, что соединение прошло успешно. Нажмите Ok. В списке System DSN появится алиас EMPLOYEE.

Помните, что в цепочке приложение-драйвер-клиент все должно быть одной разрядности. Например, 32-разрядный Excel не будет работать с 64разрядным ODBC, а 32разрядный ODBC не будет работать с 64-разрядным клиентом Firebird или InterBase.

Запустите Excel. Выберите меню Данные, Импорт внешних данных, Создать запрос.

Выберите алиас EMPLOYEE.

Теперь можно выбрать таблицы и их столбцы, входящие в запрос.

Можно особо не мучиться, выбрать один столбец любой таблицы и пару раз нажать Next. На третий раз появится диалог:

Легче построить запрос в Microsoft Query, чем заполнять столбцы в самом первом диалоге Мастера запросов.

Здесь уже намного легче выбрать столбцы, добавить условия, добавить таблицы, определить между ними связи, или вообще ввести готовый оператор SQL вручную.

После проверки запроса необходимо обязательно сохранить запрос (кнопка с изображением дискеты). Запрос будет сохранен вместе со всеми параметрами алиаса, поэтому для «распространения» запроса на несколько компьютеров вовсе необязательно создавать алиас ODBC на каждом (конечно, ODBC-драйвер и клиентскую часть IB/FB все равно придется устанавливать на этих компьютерах).

По умолчанию Microsoft Query сохраняет запросы в каталог C:Documents and SettingsAdministratorApplication DataMicrosoftЗапросы в виде текстового файла с расширением qry (вместо Administrator в пути может быть имя пользователя данного компьютера).

После формирования запроса (и его сохранения) нужно нажать на кнопку с изображением двери

И после нажатия OK мы получим данные в таблице Excel:

Для изменения запроса можно вернуться в меню Данные, Импорт внешних данных, Изменить запрос. Предупреждающее сообщение про Мастер запросов можно игнорировать – откроется Microsoft Query, в котором можно будет визуально изменить исходный запрос.

Надо заметить, что сделать вид данных ближе к пользователю можно (похоже что) только в третьем диалекте, например, заменив имя столбцов Last_Name и First_Name на имена в двойных кавычках – «Фамилия» и «Имя».

Excel через odbc

Question

I’ve tried everything I can think of and find online (see below) about this subject to no avail. Please help!

My Windows 8.1 Surface Pro 3 does not have the .xlsx ODBC driver, even when I look it up in %windir%SysWOW64odbcad32.exe. It only has the driver for .xls, which will eventually corrupt the .xls file used for the database after a few uses.

Читать еще:  Функция линейн в excel пример

On the Drivers tab in the ODBC Data Source Administrator, it says: «To install new drivers, use the driver’s setup program.»

Where is this driver setup program and where can I download the .xlsx driver?

Also, I just reformatted and started over again, re-installing Office 365 (includes Access) and everything else. It’s a 64-bit Windows system and 32-bit Office.

Please advise! Thanks!

I’ve already tried the advice listed in the links below without any success.

All replies

Thanks for the response.

I tried that already (c:windowssysWOW64odbcad32.exe) and that ODBC Data Source Administrator does not have the .xlsx driver, just the .xls one.

The article you provided states at the top: «After you have installed an ODBC driver from the driver’s setup program. » Do you know where I can find the driver’s setup program and the .xlsx compatible driver?

I tried that already (c:windowssysWOW64odbcad32.exe) and that ODBC Data Source Administrator does not have the .xlsx driver, just the .xls one.

Hmm. on my system this driver has capability for such files:

Have you tried to install the Microsoft Access Database Engine 2010 Redistributable?

My ODBC Data Source Administrator (32-bit) also shows that supposedly has the .xlsx driver on the User DSN tab. The problem is when I click on Add and the drivers available for Excel are only for .xls files, not .xlsx files.

Regarding the https://www.microsoft.com/en-US/download/details.aspx?id=13255 article-

No, I have not tried installing the Microsoft Access Database Engine 2010 Redistributable. Will that help even though I’m working with Excel 2013? Also, I’m not sure how to do the listed install instruction #4 ( If you are an application developer using ODBC to connect to Microsoft Office Excel data, set the Connection String to “Driver=;DBQ=path to xls/xlsx/xlsm/xlsb file”).

I’m trying to create an ODBC database to link data between Excel 2013 and Visio Professional 2013.

Do I need to set the Connection String for my purposes? If so, how do I do that?

9.4 Подключение к таблице на листе Excel

Подключение к таблице Excel средствами ADO, создание именованного диапазона, создание источника данных ODBC

Очень часто в практической работе возникает необходимость подключиться к таблице на листе Excel, как к базе данных. Конечно, можно работать и средствами объектной модели Excel (см. раздел 11 «Программирование в Excel»), но использование объектов ADO дает значительные преимущества:

  • намного проще и удобнее производить поиск записи, вставку новых записей в таблицу, изменение существующих записей. Объекты ADO изначально проектировались именно для этих целей;
  • объектную модель Excel можно использовать только в Excel, а объекты ADO универсальны и могут использоваться для подключения к любым источникам данных. Если вы используете объекты ADO, то вы можете использовать фактически одно и то же приложение как для работы с данными в Excel, так и для работы с информацией в «большой» базе данных — например, SQL Server или Oracle. Ситуация, когда часть информации находится в базе данных, а другая часть — в книге Excel, встречается на практике очень часто.

Подключиться к таблице на листе Excel совсем не сложно, но самостоятельно догадаться до всей последовательности действий бывает трудно. Поэтому ниже приведена пошаговая последовательность действий.

Предположим, что у нас есть книга Excel, которая называется Fact.xls и лежит в корневом каталоге диска C:. На первом листе этой книге есть такая совсем несложная таблица, представленная на рис. 9.7

Рис. 9.7 Таблица в Excel, к которой нужно обратиться средствами ADO

Нам необходимо подключиться к этой таблице, как к базе данных. Что нам нужно сделать?

Первый этап — это подготовка. Иногда можно обойтись и без нее (если лист Excel — это одна таблица). На практике же часто бывает так, что на листе у нас несколько таблиц, или таблица с комментариями, или внизу таблицы посчитаны итоги и т.п. Чтобы не смущать Excel, лучше явно указать нашу таблицу. Сделать это очень просто: нужно ее выделить (в нашем случае — выделить диапазон с B3 по D8) и присвоить выделенному диапазону имя. Для этого в Excel в меню Вставка нужно выбрать Имя -> Присвоить и ввести нужное имя. В нашем случае мы присвоим имя Volumes (см. рис. 9.8)

Читать еще:  Тесты по excel на впр

Рис. 9.8 Присваиваем имя диапазону

Обратите внимание, что нужно выбирать диапазон вместе с названиями столбцов.

После того, как имя присвоено, Excel можно закрывать — он больше нам не нужен.

Дальше по плану нужно было бы создать файл *.UDL и настроить в нем подключение к нашему файлу C:Fact.xls. Однако напрямую из файла UDL можно работать только с драйверами OLE DB, а нужного драйвера, к сожалению, нет (Microsoft JET 4.0 OLE DB Provider хочет работать только с файлами MDB). Поэтому делаем еще один подготовительный шаг — создаем источник данных ODBC (поскольку драйвер ODBC для подключения к Excel есть). Первое действие — в Панели управления открываем Administrative Tools (Средства администрирования) и два раза щелкаем по иконке Data Sources (ODBC) (Источники данных ODBC). Откроется окно, аналогичное представленному на рис. 9.9.

Рис. 9.9 Окно управления источниками данных ODBC

В вашем распоряжении — три типа DSN (Data Source Name, то есть источников данных ODBC):

  • UserDSN — информация об этих источниках данных хранится в части реестра, специфической для пользователя, поэтому эти источники данных доступны только тому пользователю, который их создал;
  • SystemDSN — информация об этих источниках данных хранится в общей части реестра и доступна для всех пользователей на этом компьютере;
  • FileDSN — информация об этих источниках данных записывается в файл в файловой системе.

Чаще всего используются System DSN — системные источники данных, поэтому переходим на вкладку System DSN и нажимаем на кнопку Add (Создать).

Первое, о чем нас спросят — это о типе драйвера, который мы хотим использовать. Выбираем, конечно, Microsoft Excel Driver и нажимаем на кнопку Finish. Но создание источника данных на этом далеко не кончилось.

На следующем экране нам потребуется:

  • в поле Data Source Name ввести имя источника данных. Можно ввести любое имя — главное, чтобы вы его не забыли. Мы введем имя ExcelVolumes;
  • нажать на кнопку SelectWorkbook и выбрать нужную нам рабочую книгу (в нашем случае — C:Fact.xls);
  • нажать на кнопку Options и подумать, будем ли изменять из программы нашу таблицу. Если да, то флажок Read Only нужно снять.

В итоге окно может выглядеть так, как представлено на рис. 9.10.

Рис. 9.10 Настраиваем источник ODBC для подключения к файлу Excel

Осталось нажать два раза на кнопку OK, чтобы закрыть окно создания источника данных ODBC.

В принципе, в коде программы можно написать значение свойства ConnectionString вручную, воспользовавшись документацией по ADO. Выглядеть соответствующая строка, к примеру, может так:

cn.ConnectionString = «Provider=MSDASQL.1;DSN = FactExcel;DBQ=C:Fact.xls ;»

Но зачем что-то писать руками, когда можно сгенерировать нужное значение автоматически? А сгенерировать можно очень просто:

  • так, как описано в предыдущем разделе, создаем файл UDL (можно воспользоваться уже готовым);
  • щелкаем по нему два раза мышью, переходим на вкладку Provider и выбираем Microsoft OLE DB Provider for ODBC Drivers;
  • переходим на вкладку Connection и в списке Use Data Source Name выбираем созданный нами источник данных ExcelVolumes. Остальные поля можно не заполнять (см. рис. 9.11). Для проверки можно нажать на кнопку Test Connection, а затем — OK.

Рис. 9.11 Настраиваем параметры подключения к созданному источнику ODBC

  • последнее действие — открываем созданный нами файл UDL в блокноте, копируем из него строку подключения и используем в нашей программе.

Итоговый код процедуры для подключения к Excel может выглядеть так:

Public Sub ConnectToExcel()

Dim cn As New ADODB.Connection

cn.ConnectionString = «Provider=MSDASQL.1;Data Source=ExcelVolumes»

‘Про Recordset мы будем говорить в следующем разделе

‘Этот код помещен для наглядной проверки

Dim rs As New ADODB.Recordset

Чтобы подключиться к файлу Excel, нам потребовалось:

  • Создать именованный диапазон в книге Excel;
  • Создать источник данных ODBC с именем ExcelVolumes;
  • Написать три строки кода начиная с создания объекта Connection до вызова его метода Open.
Ссылка на основную публикацию
Adblock
detector