Excel connector не устанавливается

Обновлено: 06.07.2024

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.

В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.

Теперь немного о действиях, совершенных мной с целью подключения к базе:

В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».

Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.

Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).

После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.

Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».

Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:

Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».

В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.

Всем привет.
Наступил новый 2016 год, а значит пора обновить инструменты для упрощения скучной механической работы. Отделы аналитики, маркетинга, продаж часто сталкиваются со следующими трудностями при обновлении отчетности:
1. Данные приходится собирать воедино из нескольких источников.
2. Отчеты составляются в Excel, что накладывает значительные ограничения на объем обрабатываемых данных.
3. Внесение изменений в заранее настроенные разработчиками выгрузки дело как правило не самое быстрое.

Если отчеты нужно обновлять еженедельно или даже ежедневно, то эта процедура становится весьма напряжной даже для самых терпеливых. С помощью надстройки Excel Power Query и записи данных в MySQL можно свести обновление большинства отчетов до простого нажатия кнопки «Обновить»:
1. Данные из любого количества источников импортируются через SQL-запросы в обычные таблицы Excel.
2. Даже из большой базы можно записывать в Excel только небольшую часть данных (например, итоговые суммы за нужный диапазон дат с группировкой только по нужным столбцам).
3. Изменения в отчет можно вносить просто поменяв SQL-запрос. Далее формируем нужный отчет стандартными средствами Excel.

В этой статье я покажу как настраивать и автоматически заполнять простые базы данных MySQL (на примере выгрузки статистики всех ключевых слов из Яндекс Метрики), а потом одной кнопкой обновлять отчеты в Excel, используя надстройку Power Query. Power Query имеет весьма странные особенности работы при составлении SQL-запросов (особенно динамических), которые мы разберем во второй части статьи.

Выбор MySQL (или любой другой популярной базы данных) вполне очевиден — бесплатно, относительно просто, возможность работать с довольно большими базами данных без технических хитростей. В качестве примера будем использовать Amazon Web Services: дешево (в большинстве случаев используемый инстанс будет бесплатен для вас в течение 12 месяцев).

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

После этих действий на вашем инстансе должна открываться такая страница:

3. Заходим под пользователем root и паролем, который вводили при настройке. Для доступа к базе данных «извне» (т. е. из Excel) нам потребуется пользователь, отличный от root. Заводим его в интерфейсе phpMyAdmin в меню Пользователи --> Добавить пользователя. Добавим пользователя stats, зададим пароль и назначим ему привилегии SELECT и INSERT. Итого получим:

4. Теперь создадим базу данных data:

5. В данном примере будем наполнять базу статистикой посещений по ключевым словам из Яндекс Метрики. Для этого создадим таблицу seo (обратите внимание, что у столбца id надо отметить опцию A_I (auto increment)):

6. Для получения статистики по ключевым словам из Яндекс Метрики можно использовать следующий скрипт. В качестве параметров нужно указать начальную и конечную дату выгрузки (переменные $startDate и $endDate), авторизационный токен (в коде есть описание как его получить), номер счетчика, из которого нужно получить статистику, и параметры базы данных: ID инстанса, логин (у нас «stats»), пароль и название базы (у нас «data»). Скопируйте в корневую папку инстанса этот код и запустите командой «php seo.php».

Если возникнут ошибки при соединении с базой, то они отобразятся в консоли и выполнение будет прервано. В случае успешного выполнения получим статистику ключевых слов за выбранный период:

Отлично, данные получены. Посмотрим как получать их в Excel.

1. После установки выбираем MySQL:

3. В открывшемся окне дважды кликаем на таблицу seo и получаем:

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

1. Сначала составляем таблицу, в которой указываем нужные нам параметры. В нашем примере это дата выгрузки. Формат ячеек со значениями лучше выставить как тестовый, т. к. Excel любит изменять формат ячеек по своему усмотрению:

2. Создадим запрос Power Query «Из таблицы», который будет просто дублировать эту таблицу:

3. В опциях запроса обязательно укажите формат второго столбца как Текст, иначе последующий SQL-запрос будет некорректным. Далее жмем «Закрыть и загрузить».

Итого мы получили запрос Power Query к обычной таблице, из которого будет брать значение начала и конца выгрузки.

Чтобы сделать SQL-запрос потребуется отключить одну опцию: заходим в Параметры и настройки --> Параметры запроса --> Конфиденциальность и выбираем «Игнорировать уровни конфиденциальности для возможного улучшения производительности». Жмем Ок.

4. Теперь делаем запрос к нашей базе данных, указывая в качестве начала и конца периода значения таблицы из пункта 3. Снова подключаемся к базе в Power Query и нажимаем «Расширенный редактор» в меню.

Например, мы хотим получить сумму визитов, которые принесли ключевые слова, содержащие «2015». На языке M запрос выглядит так:
let
Source = MySQL.Database("ec2-. compute.amazonaws.com", "data", [Query &Text.From(Таблица1[Значение])&"' and endDate<='"&Text.From(Таблица1[Значение])&"' and query like '%2015%';"])
in
Source

В параметрах startDate и endDate указываются значения в таблице из пункта 3. При запросе «Для выполнения этого собственного запроса к базе данных необходимы разрешения» жмем «Редактировать разрешение», проверяем, что все параметры подтянулись корректно и выполняем запрос. Теперь полученный ответ от SQL-запроса можно обработать обычными формулами Excel в привычном вам виде.

5. Важно! Когда вы будете обновлять выгрузку в следующий раз, то это приходится делать следующим способом (другие почему-то дают ошибку):
— меняем даты в таблице из пункта 1
— заходим в меню Данные --> Подключения и нажимаем «Обновить все»:

В этом случае все запросы выполнятся корректно и ваши отчеты обновятся автоматически. Итого для обновления отчета вам потребуется только изменить параметры запроса и нажать «Обновить все».

Приложение Excel позволяет создавать подключение к внешним источникам, в том числе базам данных. Но при необходимости подключиться к базе данных под управлением СУБД MySQL, возникает проблема. Программа просто «не умеет» этого делать, но «научить» ее легко.

Установка драйвера

Создание источника данных

Далее необходимо создать источник данных ODBC. Для этого заходим в «Панель управления», выбираем пункт «Администрирование», в нем пункт «Источники данных (ODBC)». Откроется следующее окно:

Выбираем «Добавить» и попадаем в меню выбора драйвера:

В списке имеется два возможных драйвера для MySQL различных кодировок: Unicode и ANSI. Выбирайте необходимую Вам. Если не знаете, какая Вам нужна, или это не имеет значения, то лучше устанавливать Unicode.
В параметрах подключения указываете:

Произвольное название источника;
Описание источника;
Сервер и порт. Порт оставляйте по умолчанию, если того не требуют настройки сервера;
Пользователь и пароль, как при подключении в phpMyAdmin.

Протестируйте подключение к источнику, нажав кнопку «Test». Если подключение прошло успешно, то в списке баз данных «Database» должен появиться список доступных баз. Выдираем необходимую и жмем «OK». Источник создан.

Подключение к источнику из Excel

Проходим в книге Excel на вкладку «Данные» -> раздел «Подключения» -> «Из других источников» -> «Из мастера подключения данных».

Выбираем «ODBC DSN» -> созданное ранее подключение.

Дальнейшая работа аналогична стандартному подключению к SQL Server.

Ошибка совпадения архитектур

После установки соответствующего драйвера на 64-разрядную ОС, создании источника и подключения к нему, даже если все сделано правильно, может быть выдана ошибка «dns архитектура драйвера и архитектура приложения не соответствуют друг другу».
Проблема заключается в следующем. Вероятно, что во время инсталляции пакета программ Microsoft Office были выбраны параметры по умолчанию, при которых устанавливается 32-рязрядная версия. Таким образом, происходит несовпадение разрядностей драйвера и приложения.
Решение – скачать и установить дополнительно драйвер для Windows 32-bit.
Если установить только 32-битный драйвер, то подключение будет возможно, но создать источник через панель управления не получиться.

Вместо предисловия

Заранее хочу оговориться, что сам я не профессионал в области разработки под MS Office, и здесь возможно найдутся люди, которые раскритикуют мое решение в пух и прах. Что ж, я буду рад любой конструктивной критике. Так же эта статья не является исчерпывающим руководством по написанию Excel Add-ins, не ставит цели подробно описать архитектуру Excel Add-ins или содержимое пространства имен Microsoft.Office.Core. Это вводная статья, которая, как я надеюсь, поможет новичку сориентироваться в новой для него теме и проведет через процесс создания add-in’а, который делает что-то осмысленное. Итак, приступим!

Готовим солюшен

Откроем студию и создадим новый проект типа Excel 2010 Add-in. Назовем его SampleAddIn:

Если у вас все получилось правильно (а что собственно здесь могло получиться неправильно. ), вы увидите солюшен со следующей структурой:

Единственный файл, который сгененрировала для нас студия – это ThisAddIn.cs:

Здесь все просто – мы всего лишь регистрируем обработчики событий запуска и завершения нашего add-in’а. Сейчас, естественно, наш Add-in ничего не делает, но вы уже можете его запустить как обычный проект, по нажатию F5. Что особенно приятно, на этом этапе можно не заморачиваться развертыванием, т.к. студия сама запускает Excel с нужными настройками безопасности и подсовывает ему наш Add-in. Если все получилось, то при запуске Excel вы увидите такую надпись:

Рисуем интерфейс

Добавим в солюшен элемент типа Ribbon и назовем его SampleRibbon

В дизайнере добавим туда кнопку:

И зададим названия для вкладки, группы и кнопки:

А так же создадим обработчик нажатия кнопки:

Ковыряем чуть глубже

На данный момент мы столкнулись с двумя проблемами: во-первых, если в классе ThisAddIn есть поле Application, дающее доступ к запущенному экземпляру Excel, то в классе Ribbon ничего подобного нет, а следовательно непонятно, как именно взаимодействовать с листами Excel’я. Во-вторых, как мы все знаем, смешение логики отображения и бизнес-логики является отличной заготовкой для хорошей порции спагетти в вашем коде. Поэтому изменим код, так чтобы избавиться от этих двух проблем, а заодно разберемся в «магии», которая встроила наш Ribbon в Excel без каких-либо действий с нашей стороны.

За создание пользовательских лент в Add-in’е отвечает метод CreateRibbonExtensibilityObject из класса AddInBase, от которого на самом деле унаследован наш класс ThisAddIn. Слава богу, этот метод был предусмотрительно сделан виртуальным, так что перегрузить его не составит проблем. Для начала добавим в класс ThisAddIn код:

И запустим проект. Вуаля! Вкладка исчезла. Теперь изменим класс SampleRibbon:

И класс ThisAddIn:

Работаем с ячейками

Теперь пришло время для того, ради чего собственно и был затеян весь это сыр-бор – генерации таблицы. Для начала создадим пару вспомогательных методов для оформления ячеек:

Теперь напишем код для получения списка дней месяца:

И сведем это все воедино:

Если вы нигде не ошиблись, то запустив проект, и нажав на кнопку на нашей вкладке, вы увидите нечто подобное:

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