Как подключить sql к excel

Обновлено: 23.11.2024

Удаленный просмотр кубов из Excel через VPN-подключение без использования аутентификации Windows

Технически, когда вы подключаетесь к другой сети через VPN-подключение, вы видите все разрешенные машины в этой сети. Таким образом, легко подключиться к экземпляру SQL Server, используя проверку подлинности SQL Server. Тем не менее, я объясняю эту часть для некоторых из вас, ребята, которые могут быть новичками в подключении из Excel напрямую к базе данных на SQL Server и создании ярких отчетов в Excel.

Но как насчет прямого подключения из Excel к удаленному экземпляру служб Analysis Services без использования проверки подлинности Windows? Ты прав! Я говорю, что вы можете подключиться напрямую из своего собственного Excel к удаленному серверу SSAS без использования проверки подлинности Windows. Ну, технически режим проверки подлинности SQL Server недоступен для служб Analysis Services. Так что же на самом деле означает, когда я говорю «без использования аутентификации Windows»? Если вам интересно найти ответ, продолжайте читать эту статью.

Вы работаете консультантом по бизнес-аналитике, и вам сказали, что у клиента должны быть следующие простые отчеты в Excel:

· Вы должны подключиться к серверу клиента, используя предоставленное VPN-подключение

· VPN-подключение может быть установлено через Windows VPN, Cisco VPN и т. д., поэтому VPN-клиент или используемый порт и протокол на самом деле не имеют значения

· Microsoft Excel НЕ установлен на сервере клиента

· Вам НЕ разрешено устанавливать Excel на сервер

· Поскольку это дорогостоящий процесс, клиент не будет настраивать виртуальную машину в своей сети, чтобы вы могли удаленно подключиться к ней и установить Excel, а затем подключиться к своим экземплярам SQL Server/Analysis Services

· Между вашей сетью и сетью клиента нет доверительных отношений, поэтому ваше доменное имя пользователя и пароль не могут быть аутентифицированы в сети клиента

· Клиент должен иметь несколько отчетов в Excel поверх базы данных SQL Server и кубов OLAP в службах Analysis Services (SSAS)

· У вас есть право запускать приложение от имени администратора на удаленном сервере

· Вам необходимо подключиться к удаленному серверу напрямую из вашего собственного Microsoft Excel, установленного на вашем компьютере

· Клиент также предоставил удаленный доступ к рабочему столу сервера

· На удаленном рабочем столе установлена SQL Server Management Studio (SSMS)

· На удаленном сервере SQL ваша учетная запись является членом роли сервера «securityadmin», поэтому вы можете создать новый вход в систему SQL Server

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

Прежде чем начать, вам необходимо создать базу данных. Если у вас его нет, ознакомьтесь со статьями Создание базы данных в Базе данных SQL Azure и Создание IP-брандмауэра на уровне сервера, чтобы получить базу данных с образцами данных и запустить ее за несколько минут.

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

Вам также потребуется копия Excel. В этой статье используется Microsoft Excel 2016.

Подключить Excel и загрузить данные

Чтобы подключить Excel к базе данных в базе данных SQL, откройте Excel, а затем создайте новую книгу или откройте существующую книгу Excel.

В строке меню в верхней части страницы выберите вкладку "Данные", выберите "Получить данные", выберите "Из Azure", а затем выберите "Из базы данных SQL Azure".

В диалоговом окне базы данных SQL Server выберите База данных слева, а затем введите имя пользователя и пароль для сервера, к которому вы хотите подключиться. Выберите Подключиться, чтобы открыть навигатор.

В зависимости от сетевой среды вы не сможете подключиться или потеряете соединение, если сервер не пропускает трафик с IP-адреса вашего клиента. Перейдите на портал Azure, щелкните SQL-серверы, щелкните свой сервер, щелкните брандмауэр в разделе настроек и добавьте IP-адрес своего клиента. Дополнительные сведения см. в разделе Как настроить параметры брандмауэра.

В навигаторе выберите базу данных, с которой вы хотите работать, из списка, выберите таблицы или представления, с которыми вы хотите работать (мы выбрали vGetAllCategories), а затем нажмите «Загрузить», чтобы переместить данные из базы данных в электронную таблицу Excel. .

Импортируйте данные в Excel и создайте сводную диаграмму

Теперь, когда вы установили соединение, у вас есть несколько вариантов загрузки данных. Например, следующие шаги создают сводную диаграмму на основе данных, найденных в вашей базе данных в базе данных SQL.

Выполните действия, описанные в предыдущем разделе, но на этот раз вместо выбора «Загрузить» выберите «Загрузить в» в раскрывающемся списке «Загрузить».

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

Теперь на листе есть пустая сводная таблица и диаграмма.

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

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

Создайте постоянное соединение с помощью файла .odc

Чтобы сохранить сведения о подключении на постоянной основе, вы можете создать файл .odc и сделать это подключение доступным для выбора в диалоговом окне "Существующие подключения".

В строке меню в верхней части страницы выберите вкладку "Данные", а затем выберите "Существующие подключения", чтобы открыть диалоговое окно "Существующие подключения".

Нажмите "Обзор", чтобы открыть диалоговое окно "Выбор источника данных".

Выберите файл +NewSqlServerConnection.odc, а затем выберите Открыть, чтобы открыть мастер подключения данных.

В мастере подключения данных введите имя сервера и учетные данные базы данных SQL. Выберите Далее.

Выберите базу данных, содержащую ваши данные, из раскрывающегося списка.

Выберите интересующую вас таблицу или представление. Мы выбрали vGetAllCategories.

Выберите "Далее".

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

Выберите способ импорта данных. Мы решили сделать сводную таблицу. Вы также можете изменить свойства соединения, выбрав Свойства. Выберите OK, когда будете готовы. Если вы не выбрали сохранение пароля вместе с файлом, вам будет предложено ввести свои учетные данные.

Убедитесь, что новое подключение сохранено, развернув вкладку "Данные" и выбрав "Существующие подключения".

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

Но сначала мы собираемся повторить преимущества подключения Excel к данным в SQL Server.

Преимущества подключения Excel к SQL Server

Чтобы продемонстрировать преимущества подключения Excel к SQL Server, рассмотрим пример.

Эта таблица используется торговыми представителями производственной компании для подготовки ценовых предложений для клиентов.У каждого торгового представителя есть копия электронной таблицы, которая включает список товарно-материальных ценностей и их цены. Список товарно-материальных ценностей и цен извлекается из финансовой системы администратором. В листе предложений используются некоторые формулы проверки данных для получения цен, когда торговый представитель выбирает элемент из раскрывающегося списка.

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

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

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

Подключение Excel к базе данных SQL Server: 3 простых шага

Самый простой способ подключить Excel к базе данных SQL Server — использовать SQL Spreads. SQL Spreads — это простая надстройка Excel.

Загрузку SQL Spreads можно найти здесь, а процесс установки описан здесь.

Шаг 1. Соберите сведения о подключении к базе данных

Для подключения Excel к SQL Server вам потребуется следующая информация, поэтому получите ее в качестве первого шага:

Имя SQL-сервера
Имя базы данных
Таблица или представление, содержащее необходимую информацию
Данные для входа (если не используется аутентификация Windows)

Форма имени SQL Server будет зависеть от того, установлен ли SQL Server локально, в сети или удаленно (обратите внимание, что это то же имя, что и при подключении к SQL Server в SQL Server Management Studio).

Типичное имя сервера	Case
Localhost	Когда SQL Server установлен локально
localhost\sqlexpress< /td>	При локальной установке SQL Server Express
Домен\Имя сервера	При установке SQL Server в корпоративной сети
IP-адрес	При установке SQL Server в удаленном месте

Шаг 2. Подключитесь к SQL Server

После установки SQL Spreads вы увидите новую вкладку на ленте. перейдите сюда и нажмите кнопку «Режим дизайна».

На панели конструктора SQL Spreads справа нажмите кнопку "Изменить", чтобы открыть диалоговое окно подключения к SQL Server.

Введите имя вашего SQL Server в поле имени SQL Server:

Выберите, следует ли подключаться с помощью входа в систему Windows (проверка подлинности Windows) или ввести имя пользователя и пароль (проверка подлинности SQL Server). Проверка подлинности Windows является более безопасной из двух вариантов (дополнительную информацию см. здесь).

Нажмите "ОК". SQL Spreads попытается подключиться к базе данных. Если соединение установлено успешно, ваши базы данных появятся на панели SQL Spreads Designer.

Шаг 3. Выберите нужную таблицу в SQL Server

Теперь, когда мы создали подключение из Excel к SQL Server, мы можем выбрать, какую таблицу данных мы хотим использовать в Excel.

В конструкторе SQL Spreads Designer щелкните базу данных, а затем выберите свою таблицу.

Как только вы выбираете таблицу, данные в таблице заполняются на листе Excel.

Теперь вы можете просматривать все данные в таблице SQL Server и использовать их в книге Excel. Настоящая сила SQL Spreads заключается в возможности обновлять данные в таблице SQL Server прямо из Excel. Например, я могу обновить цены в таблице продуктов прямо в Excel и сохранить изменения обратно в SQL Server!

Пример: простая форма коммерческого предложения в Excel, подключенная к SQL Server

Вернемся к нашему предыдущему примеру с формой предложения и списком запасов. Мы собираемся изменить это, чтобы форма котировки получала данные из таблицы на сервере SQL, а не из статического списка в электронной таблице.

Если вы хотите следовать этому примеру, вам потребуется доступ к образцу базы данных AdventureWorks, который вы можете получить здесь .

Сначала мы создадим новый лист в рабочей книге quote_sample и оставим ему имя по умолчанию Sheet2 — сюда будут помещены данные из SQL Server.

Далее мы собираемся подключиться к базе данных AdventureWorks, используя шаги, описанные выше.

Шаг (1): Детали подключения

Вот подробности подключения, которые я буду использовать для подключения к базе данных SQL Server:

Имя сервера SQL: Andy\SQLExpress
Имя базы данных: AdventureWorks
Таблица или представление: Production.Product
Данные для входа: аутентификация Windows

Шаг (2): подключение к SQL Server

Мы хотим подключиться к таблице «Продукт» в SQL Server и заполнить лист «Продукт» в таблице предложений. Для этого мы нажимаем SQL Spreads > Design Mode и вводим данные о соединении и расширяем правильную базу данных (в нашем случае AdventureWorks), чтобы мы могли выбрать соответствующую таблицу (в нашем случае Production.Product).

Шаг (3): выберите таблицу Production.Product в SQL Server

Нажмите на таблицу "Производство.Продукт" в списке, и данные будут автоматически введены в лист "Продукт".

Теперь мы можем использовать эти данные в качестве источника для нашего поиска в шаблоне Quote, но прежде чем мы это сделаем, мы рассмотрим, как фильтровать данные, возвращенные из SQL, чтобы отображались только товары, имеющиеся на складе.

Перейдите на вкладку "Фильтры" в конструкторе SQL-распространений. В разделе «Тип фильтра» мы можем выбрать «Столбец» или «Пользовательский SQL». Мы собираемся выбрать «Пользовательский SQL», потому что нам нужно выполнить SQL-запрос, который будет возвращать только товары в наличии, а это означает, что нам нужно соединить таблицу Product с таблицей ProductInventory.

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

в поле Пользовательский SQL:

Когда вы нажимаете «Сохранить», данные в таблице «Продукт» сокращаются до отфильтрованных записей. Наконец, мы собираемся обновить именованный диапазон, который использовался при поиске проверки данных в листе котировок:

Кроме того, нам также необходимо обновить функцию XLOOKUP, которую лист коммерческих предложений использует для получения цены, когда пользователь выбирает элемент, — она должна ссылаться на новую таблицу Table2, которая представляет собой данные из SQL Server.

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

Бонусная функция! Обновить цены в Excel и сохранить на SQL Server

Мы подключили Excel к SQL Server, чтобы наши торговые представители могли создавать предложения на основе актуальных цен и доступности.

Мы можем сделать еще один шаг и предоставить назначенному пользователю (например, администратору) возможность обновлять продажные цены товаров непосредственно в Excel и сохранять обновления обратно в таблицу «Производство.Продукт» в SQL. Сервер.

Например, если я хочу изменить цену первого товара в списке на 39,99 долларов США, я могу ввести в ячейку ListPrice и нажать "Сохранить в базе данных" — это так просто! Конечно, мы бы хотели, чтобы эта возможность была доступна только определенным пользователям, и это легко сделать с помощью SQL Spreads.

Обзор

В этой статье мы показали, насколько просто подключить Excel к SQL Server с помощью надстройки SQL Spreads. Мы также рассмотрели пример, чтобы показать, как простая электронная таблица котировок может использовать актуальные данные о ценах продаж из базы данных SQL, а также как цены в базе данных могут обновляться непосредственно из Excel.

Загрузите пробную версию SQL Spreads и подключите Excel к SQL Server сегодня.

Энди Макдональд

Энди более 20 лет проработал в инженерном, финансовом и ИТ-секторах, занимаясь анализом и представлением данных с помощью таких инструментов, как SQL Server, Excel, Power Query и Power BI.

Пишут для SQL-распространений о Excel и SQL Server и о том, как связать их вместе.

Надстройка Devart Excel для SQL Server позволяет подключать Excel к базам данных SQL Server, извлекать и загружать оперативные данные SQL Server в Excel, а затем изменять эти данные и сохранять изменения обратно в SQL Server. Вот как вы можете подключить Excel к SQL Server и загрузить данные SQL Server в Excel за несколько простых шагов.

Чтобы начать связывание Excel с SQL Server, на ленте щелкните вкладку DEVART, а затем нажмите кнопку Получить данные. Отобразится мастер импорта данных, в котором необходимо создать подключение к Excel SQL Server и настроить запрос для получения данных из SQL Server в Excel:

1. Укажите параметры подключения

В диалоговом окне "Редактор подключения" необходимо ввести необходимые параметры подключения:

Данные для входа — выберите, следует ли использовать аутентификацию Windows или аутентификацию SQL Server. В последнем случае необходимо ввести имя пользователя и пароль SQL Server.
Имя пользователя — ваше имя пользователя SQL Server. Требуется только для проверки подлинности SQL Server.
Пароль — ваш пароль SQL Server. Требуется только для проверки подлинности SQL Server.
База данных — имя базы данных SQL для подключения к Excel.

Если вам нужно настроить подключение к Excel SQL Server более подробно, вы можете дополнительно нажать кнопку «Дополнительно» и настроить дополнительные параметры подключения. Там вы можете настроить шифрование соединения, параметры отказоустойчивости и т.д.

Чтобы проверить правильность подключения Excel к SQL Server, нажмите кнопку "Проверить подключение".

2. Выберите, следует ли сохранять соединение в книге Excel

При желании вы можете изменить параметры хранения данных подключения и запроса в книге Excel и в настройках Excel:

Разрешить сохранение данных, относящихся к надстройкам, на листе Excel. Снимите этот флажок, если вы не хотите сохранять какие-либо данные, относящиеся к надстройкам Excel, на листе Excel (подключения, запросы и т. д.). В этом случае, если вы хотите перезагрузить данные из SQL Server в Excel или сохранить измененные данные обратно в SQL Server, вам потребуется повторно ввести как параметры подключения, так и запрос.
Разрешить сохранение строки подключения на листе Excel. Снимите этот флажок, если вы не хотите, чтобы параметры подключения к SQL Server сохранялись в файле Excel. В этом случае вам нужно будет повторно вводить параметры подключения каждый раз, когда вы хотите перезагрузить данные SQL Server или изменить и сохранить их на SQL Server. Однако вы можете поделиться книгой Excel, и никто не сможет получить от нее какие-либо сведения о подключении.
Разрешить сохранение пароля — рекомендуется снять этот флажок. Если вы не снимите этот флажок, все параметры подключения, включая ваш пароль SQL Server, будут сохранены в книге Excel. Любой, у кого есть надстройка Excel для SQL Server и рабочая книга, сможет связать Excel с SQL Server, получить от него данные и изменить их.Но в этом случае вам не нужно будет ничего повторно вводить при перезагрузке данных из SQL Server в Excel или сохранении их в SQL Server.
Разрешить повторное использование подключения в Excel. Установите этот флажок, если вы хотите сохранить это подключение на своем компьютере и повторно использовать его в других книгах Excel. Это не влияет на сохранение параметров подключения в самой книге. Вам нужно указать имя подключения, и после этого вы сможете просто выбрать это подключение из списка

3. Настроить запрос для получения данных

Чтобы экспортировать данные из Salesforce в Excel, вы можете либо использовать Visual Query Builder для визуальной настройки запроса, либо переключиться на вкладку SQL-запрос и ввести SQL-запрос. Чтобы настроить запрос визуально, выполните следующие действия:

В списке объектов выберите таблицу SQL Server, чтобы загрузить ее данные в Excel.

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

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

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

После указания запроса можно дополнительно нажать кнопку "Далее" и просмотреть некоторые из первых возвращаемых строк. Или нажмите «Готово» и начните загрузку данных.

После того, как данные загружены из SQL Server в Excel, вы можете работать с этими данными, как с обычным рабочим листом Excel. Вы можете мгновенно обновить данные из SQL Server, щелкнув Обновить на вкладке Devart на ленте, и, таким образом, всегда иметь свежие оперативные данные из SQL Server в своей книге.

Если вы хотите отредактировать данные SQL Server в Excel и сохранить их обратно в SQL Server, вам нужно сначала нажать «Режим редактирования» на вкладке Devart на ленте. В противном случае внесенные вами изменения не могут быть сохранены на SQL Server.

После того, как вы запустите режим редактирования, вы сможете редактировать данные, как обычно делаете это в Excel — удалять строки, изменять значения их ячеек. Столбцы, которые нельзя редактировать в SQL Server, будут выделены курсивом, и вы не сможете редактировать значения в этих столбцах. Чтобы добавить новую строку, введите необходимые значения в последнюю строку таблицы, выделенную зеленым цветом.

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

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