Как сделать подключение в excel

Обновлено: 05.07.2024

Конечно, Excel используется для электронных таблиц, но знаете ли вы, что вы можете подключить Excel к внешним источникам данных? В этой статье мы собираемся обсудить, как подключить электронную таблицу Excel к таблице базы данных MySQL и использовать данные в таблице базы данных для заполнения нашей электронной таблицы. Есть несколько вещей, которые вам нужно сделать, чтобы подготовиться к этой связи.

подготовка

Во-первых, вы должны загрузить самую последнюю версию драйвера Open Database Connectivity (ODBC) для MySQL. Текущий драйвер ODBC для MySQL может быть расположен в

После загрузки файла убедитесь, что вы проверили его хэш-код md5, указанный на странице загрузки.

Далее вам нужно будет установить только что загруженный драйвер. Дважды щелкните файл, чтобы начать процесс установки. После завершения процесса установки вам потребуется создать имя источника базы данных (DSN) для использования с Excel.

Создание DSN

DSN будет содержать всю информацию о соединении, необходимую для использования таблицы базы данных MySQL. В системе Windows вам нужно будет нажать Пуск, затем Панель управления, затем Администрирование, затем Источники данных (ODBC). Вы должны увидеть следующую информацию:

Обратите внимание на вкладки на изображении выше. Пользовательский DSN доступен только пользователю, который его создал. Системный DSN доступен любому, кто может войти в систему. Файл DSN — это файл .DSN, который можно транспортировать и использовать в других системах, в которых установлены те же ОС и драйверы.

Возможно, вам придется прокрутить вниз, чтобы увидеть драйвер MySQL ODBC 5.x. Если его нет, значит, что-то пошло не так с установкой драйвера в разделе Подготовка этого поста. Чтобы продолжить создание DSN, убедитесь, что драйвер MySQL ODBC 5.x выделен, и нажмите кнопку Готово. Теперь вы должны увидеть окно, подобное приведенному ниже:

Создание подключения к электронной таблице

В более старых версиях Excel это немного более сложный процесс. Во-первых, вы должны увидеть что-то вроде этого:

Следующим шагом является нажатие на кнопку Добавить. Это откроет вам окно существующих соединений:

Учитывая перечисленные варианты выбора источника данных, вы хотите выделить ODBC DSN и нажать Далее. На следующем шаге мастера подключения к данным отобразятся все источники данных ODBC, доступные в используемой вами системе.

Следующим шагом мастера подключения к данным является сохранение и завершение. Поле имени файла должно быть заполнено автоматически. Вы можете предоставить описание. Описание, использованное в этом примере, самоочевидно для любого, кто может его использовать. Затем нажмите кнопку Готово в правом нижнем углу окна.

Теперь вы должны вернуться к окну Workbook Connection. Соединение для передачи данных, которое вы только что создали, должно быть перечислено:

Импорт данных таблицы

Для этого поста таблица, с которой мы работали, имела два поля. Первое поле представляет собой поле INT с автоматическим приращением под названием ID. Второе поле — VARCHAR (50) и называется fname. Наша последняя таблица выглядит примерно так:

Как вы, наверное, заметили, первая строка содержит имена столбцов таблицы. Вы также можете использовать стрелки раскрывающегося списка рядом с именами столбцов для сортировки столбцов.

Заворачивать

В этом посте мы рассмотрели, где найти последние драйверы ODBC для MySQL, как создать DSN, как создать соединение данных электронной таблицы с помощью DSN и как использовать соединение данных электронной таблицы для импорта данных в электронную таблицу Excel. Наслаждайтесь!

Очень часто в практической работе возникает необходимость подключиться к таблице на листе 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)

Рис. 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

Microsoft Excel — очень важный инструмент для многих при создании рабочих листов. Вы можете добавлять данные для просмотра, вычисления и дальнейшего анализа. Помимо добавления вручную, Microsoft Excel позволяет импортировать данные прямо из Интернета.

Импорт данных из Интернета в Microsoft Excel избавляет вас от необходимости вручную вводить данные или копировать и вставлять их. После подключения Excel к источнику веб-данных он импортирует необходимую информацию и обновляется при наличии изменений. Вот все, что вам нужно знать об импорте данных непосредственно из Интернета в Excel.

Важные моменты, на которые следует обратить внимание при импорте данных из Интернета в Microsoft Excel

Для импорта данных из Интернета в Microsoft Excel необходимо выполнить два требования:

Версия Microsoft Excel: Power Query поддерживает импорт данных. С помощью Power Query пользователи Excel могут подключать, преобразовывать, комбинировать и загружать данные. Однако Power Query доступен только в Excel для Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013 и Excel 2010.

Формат данных: при импорте данных из Интернета в Microsoft Excel данные должны быть в табличной форме или встроены в таблицу HTML. Если данных нет в таблице, Power Query не сможет их идентифицировать.

Как импортировать веб-данные в Microsoft Excel

Используя функцию Power Query в Microsoft Excel, вы можете импортировать данные, представленные в табличной форме на веб-сайте, в рабочий лист. Вот как это работает.

Шаг 1: Запустите Microsoft Excel на вашем компьютере.

Шаг 2: В интерфейсе ленты вверху нажмите Данные.

Шаг 5: Вставив ссылку, нажмите ОК, чтобы загрузить веб-страницу в браузере.

Шаг 7: Когда Microsoft Excel загрузит данные, вы должны увидеть окно навигатора. Выберите любую из таблиц, перечисленных под навигатором, чтобы проверить, импортированы ли ваши данные.

Это все, что нужно для импорта веб-данных в Microsoft Excel с помощью Интернета. Если вы хотите, чтобы данные постоянно обновлялись в соответствии с изменениями на веб-сайте, прочтите ниже об обновлении веб-данных.

Как обновить веб-данные в Microsoft Excel

Как мы упоминали выше, когда вы импортируете данные с веб-страницы в Microsoft Excel, они обновляются автоматически. Вы также можете установить свои предпочтения относительно того, как вы хотите, чтобы обновление было выполнено. Вот что вам нужно сделать, чтобы обновить веб-данные в Excel.

Шаг 1: Запустите рабочий лист, подключенный к веб-странице.

Шаг 2: Щелкните по таблице.

Включить фоновое обновление: Этот параметр отмечен по умолчанию и обеспечивает постоянное обновление вашего контента.
Обновлять каждые: Этот параметр гарантирует, что Excel обновляет данные только в определенные периоды. При выборе этого параметра вам нужно будет ввести минутные интервалы, в которых вы хотите, чтобы это происходило.
Обновить данные при открытии файла: Данные в файле Excel обновляются только при его запуске.

Шаг 7: Выберите ОК, чтобы сохранить настройки обновления.

Читайте также: