Выгрузка в excel из mysql
Обновлено: 07.07.2024
У многих при работе с PHP в связке с MySQL возникает такая потребность, как экспорт данных из базы в формат xls, для того чтобы люди, которым нужны эти данные, обрабатывали их в программе Excel или просто пользователям было удобно смотреть эти данные. Недавно у меня возникла такая потребность и сегодня я расскажу, как это дело можно реализовать.
Сразу скажу, что этот способ достаточно простой, но данные выгружаются нормально.
Для начала приведу пример конечного xls файла, в программе Excel выгрузка будет выглядеть примерно так:
Другими словами, никаких картинок, стилей выгружено не будет, только заголовки столбцов и сами данные.
До того как я пришел именно к такому варианту выгрузки я пробовал выгружать в формат csv, но получалось немного коряво, потом пробовал отрисовывать таблицу и сохранять ее с расширением xls, также получалось какая-то ерунда, способ, который я сейчас опишу, меня полностью устроил, и сейчас с Вами я им поделюсь.
Для начала приведу весь код, который я максимально прокомментировал, можете выделить его и сохранить с расширением php и пробовать, только не забудьте прописать настройки подключения к базе данных.
Экспорт данных из MySQL в Excel на PHP
А еще чтобы Вы понимали, какие данные я выгружаю, приведу пример простой таблицы в БД (у меня ее название test):
Тестовая таблица:
| id | firstname | name |
| 1 | Иванов | Иван |
| 2 | Петров | Петр |
| 2 | Петров2 | Петр2 |
Код:
Вся идея здесь заключается в функции pack(), которая упаковывает данные в бинарную строку, а мы в свою очередь последовательно заполняем эту строку данными, которые мы выгрузили из базы данных MySql с помощью обычной функции mysql_query().
Для того чтобы проверить работоспособность данного кода, с учетом того, что Вы настроили подключение к базе данных и создали аналогичную таблицу, можете послать такой запрос:
И у Вас должно выгрузиться две строки с id равным 2.
И теперь каждый, которому Вы разрешите выгружать данные может легко экспортировать их на свой локальный компьютер через web интерфейс. Данный способ удобен как для корпоративных пользователей, если Вы разрабатываете приложение для своей организации, так и для пользователей Вашего web сайта в Интернете. Надеюсь, данный способ Вам помог. Удачи!
Заметка! Если Вас интересует язык SQL, рекомендую пройти мой онлайн-курс по основам SQL, который ориентирован на изучение SQL как стандарта, таким образом, Вы сможете работать в любой системе управления базами данных. Курс включает много практики: онлайн-тестирование, задания и многое другое.
Вы можете установить компонент MySQL для Excel при установке MySQL Community Server 6. Или вы можете установить его как дополнение к существующей установке MySQL Server. Однако для установки этого компонента есть некоторые предпосылки.
MySQL для Excel Требования
Для установки MySQL для Excel Installer на вашем компьютере должно быть установлено следующее:
Microsoft Office Excel 2007 или выше, для Microsoft Windows.
Visual Studio Tools для Office 4.0 и MySQL Installer могут установить это для вас.
Доступное соединение с MySQL Server.
MySQL для Excel загружается и выполняется путем выбора вкладки меню «Данные» в Excel, а затем выбора значка «База данных MySQL для Excel». Это открывает новую боковую панель Excel с доступными опциями MySQL для Excel. Панель навигации со значком MySQL для Excel показана на следующем снимке экрана:
Редактировать данные MySQL в Excel
MySQL для Excel позволяет загружать и редактировать данные MySQL непосредственно из Microsoft Excel, или же вы можете сделать это вручную, нажав «Принять изменения».
В приведенном ниже примере используется таблица расположения примера базы данных сотрудников, но экран будет выглядеть одинаково для любой таблицы. В MySQL для Excel откройте соединение MySQL, щелкните схему сотрудника, затем выберите таблицу местоположений, нажмите «Изменить данные MySQL», затем выберите «Импорт», чтобы импортировать данные в новый лист Microsoft Excel для редактирования.
Вот пошаговое руководство по редактированию и фиксации данных:
Шаг 1:
Загрузить Microsoft Office Excel 7
Шаг 2:
Нажмите на вкладку данных , см. Рисунок выше, значок базы данных «MySQL для Excel» появится ниже.
Шаг 3:
Нажмите на значок базы данных «MySQL для Excel». Он открывает новую боковую панель Excel с доступными параметрами MySQL для Excel. Панель навигации со значком MySQL для Excel показана на следующем рисунке.
Здесь наша База данных является сотрудником, и мы работаем с таблицей местоположений , но экран будет выглядеть одинаково для любой таблицы.
Шаг 4:
В боковой панели MySQL для Excel откройте соединение MySQL двойным щелчком мыши. Здесь наше соединение - Локальный экземпляр MySQL5.6, и для принятия пароля появится следующий экран.
Шаг 5:
Введите пароль для соединения с сервером MySQL. Базы данных будут отображаться в боковой панели MySQL для Excel. Наша База данных является сотрудником. Смотрите следующую картинку.
Шаг 6:
Дважды щелкните по нужной базе данных, и отобразятся таблицы в базе данных. Наш стол - это местоположение. Смотрите следующую картинку.
Шаг 7:
Выберите таблицу, которую вы хотите редактировать, нажмите «Редактировать данные MySQL» на панели навигации, как показано выше, и посмотрите следующий снимок экрана.
Шаг 8:
Шаг 9:
Здесь, в нашей таблице, идентификатор первого столбца является первичным ключом. Если мы изменим значение ID в ячейке A19 вместо 17 и нажмем клавишу ввода, цвет этой ячейки будет зеленым. Теперь мы изменили значение ячейки F16 Лондона и нажмите клавишу ввода, цвет ячейки изменится на зеленый, а желтый цвет в последнюю очередь означает, что ячейки принимают новые данные. Введенные здесь данные вставляются в таблицу MySQL.
Шаг 10:
Теперь, если мы нажмем кнопку «Восстановить данные», посмотрите следующий снимок экрана.
Здесь на картинке выше, если вы нажмете кнопку «Обновить данные из БД», данные будут обновлены и отобразятся исходные данные из БД, а если вы нажмете «Восстановить измененные данные», вы потеряете только что сделанные изменения. На этом этапе, если вы нажмете кнопку «Применить изменения», посмотрите следующий снимок экрана.
На изображении выше показано, как цвета ячейки F16 преобразованы из зеленого в синий, что означает успешное выполнение изменений. Теперь вы можете закрыть сохранение окна Excel или нет, но таблица базы данных была обновлена. Вы можете видеть это, чтобы сделать шаг снова. Если вы установили флажок «Автоматическая фиксация», эффект сразу же отобразится на вашем листе, и данные будут обновлены в базе данных MySQL.
Импорт данных MySQL в Excel
Данные можно импортировать из MySQL в электронную таблицу Microsoft Excel с помощью параметра «Импорт данных MySQL» после выбора таблицы, представления или процедуры для импорта.
Прежде всего, вы делаете первые 6 шагов, описанных выше в «Редактировании данных MySQL в Excel», затем выбираете таблицу, которую хотите импортировать. Здесь наш стол - местоположение. Поэтому выберите таблицу местоположений, а затем нажмите «Импорт данных MySQL» и посмотрите на скриншот, показанный ниже.
Выбор столбцов для импорта
По умолчанию все столбцы выбраны и будут импортированы. Определенные столбцы могут быть выделены (или не выбраны) с использованием стандартного метода Microsoft Windows: либо Control + щелчок мышью для выбора отдельных столбцов, либо Shift + щелчок мышью для выбора диапазона столбцов.
Белый цвет фона указывает, что столбец или столбцы были выбраны, и они готовы к импорту, с другой стороны, серый цвет указывает, что столбцы не выбраны и столбец не будет импортирован.
Если щелкнуть правой кнопкой мыши в любом месте сетки предварительного просмотра, откроется контекстное меню с параметром «Нет» или «Выбрать все» в зависимости от текущего состояния.
Таблица импорта
Включить имена столбцов в качестве заголовков: по умолчанию этот параметр включен, и он обрабатывает имена столбцов в верхней части электронной таблицы Microsoft Excel как строку «заголовков» и будет вставлен в качестве заголовка.
Ограничить и строки и начать с строки : по умолчанию этот параметр отключен, если включен, это ограничивает диапазон импортируемых данных. Параметр Limit to по умолчанию равен 1, и этот предел может быть изменен путем определения количества строк для импорта. Параметр «Начать со строки» по умолчанию равен 1, т. Е. Начинается с первой строки, и его можно изменить, определяя номер, с которого начинается импорт. Каждый параметр имеет максимальное значение COUNT (строки) в таблице.
Теперь предполагается, что мы хотим импортировать столбцы LOATION_ID и CITY. Щелкните мышью по столбцу LOCATION_ID, затем нажмите и удерживайте клавишу CTRL, щелкните столбец CITY и посмотрите следующий снимок экрана.
Теперь, если мы нажмем кнопку «Импорт», все строки для этих двух столбцов будут импортированы в Microsoft Excel Worksheet.
Предполагается, что мы хотим импортировать только 6 строк, начиная с 3-го ряда. Теперь посмотрите следующий снимок экрана.
Здесь на рисунке выше показано, что все столбцы выбраны, и значение Limit to равно 6, что означает, что будет импортировано 6 строк, и начало импорта начнется с 3-й строки, потому что мы установили значение Start with Строка 3. Теперь нажмите кнопку «Импорт» и посмотрите следующий снимок экрана.
Добавить данные Excel в MySQL
Данные электронной таблицы Microsoft Excel можно добавлять в таблицу базы данных MySQL с помощью параметра «Добавить данные Excel в таблицу».
Отображения столбцов
Сопоставление столбцов Excel со столбцами MySQL можно выполнить автоматически (по умолчанию), вручную или с помощью хранимой процедуры сопоставления. Чтобы добавить данные из Excel в MySQL, необходимо выполнить следующий шаг:
Сначала выполните 6 шагов «Редактирования данных MySQL в Excel», затем введите записи в таблицу Excel, совместимую со структурой таблицы MySQL, в которую вы хотите добавить записи. Вот пример, в котором мы взяли только одну строку, вы можете взять более одной строки и затем выбрать записи. Смотрите следующий снимок экрана.
Теперь нажмите «Добавить данные Excel в таблицу», как упомянуто выше, и посмотрите на экран как.
Здесь на картинке выше, вы ищете два цвета для легенды. Один красный, а другой зеленый.
Зеленый цвет указывает, что исходный столбец сопоставлен (сопоставленный означает, что исходные столбцы в приведенной выше сетке, созданной в электронной таблице, были сопоставлены со структурой таблицы MySQL в качестве целевых столбцов, показанных в нижней сетке) с целевым столбцом и здесь на рисунке выше. показывает, что все исходные столбцы были сопоставлены с целевыми столбцами.
Если вы перетащите заголовок любого целевого столбца на зеленый цвет и оставите его вне сетки, цвет целевого столбца будет красным, а цвет исходного столбца - серым. Смотрите на картинке ниже.
Здесь на рисунке выше целевой столбец 4 стал красным, а исходный столбец 4 - серым.
Красный цвет означает, что целевой столбец не сопоставлен, а серый цвет означает, что исходный столбец не сопоставлен.
Следует отметить, что исходный столбец может быть сопоставлен с несколькими целевыми столбцами, хотя это действие генерирует диалоговое окно с предупреждением, и при щелчке правой кнопкой мыши по целевому столбцу отображается меню с параметрами либо «Удалить сопоставление столбцов» для одного столбца, либо «Очистить». Все сопоставления для всех столбцов.
Предположим, мы отображаем вручную путем перетаскивания исходного столбца 4 с целевыми столбцами 4 и столбцами 5 Целевой столбец 4 будет отображаться просто, но при выполнении процесса для целевого столбца 5 появится диалоговое окно с предупреждением, показанное ниже.
Методы картирования
Вот посмотрите три метода отображения:
Метод автоматического сопоставления пытается сопоставить имена исходных столбцов Excel с именами столбцов целевой таблицы MySQL.
В методе сопоставления вручную имена столбцов источника перетаскиваются вручную с именами столбцов назначения. После выбора Автоматического метода также можно выполнить метод ручного перетаскивания.
Вы можете сохранить свои собственные стили сопоставления с помощью кнопки « Сохранить сопоставление» и сохранить имя, например «name (dbname.tablename)», и оно будет доступно в поле со списком «Метод сопоставления».
Сохраненные сопоставления могут быть удалены или переименованы в диалоговом окне « Дополнительные параметры ».
Расширенные настройки
Если мы нажмем кнопку «Дополнительные параметры», диалоговое окно будет выглядеть примерно так:
Выполните автоматическое сопоставление при открытии диалогового окна : с помощью этих параметров автоматически сопоставляется цель и источник при открытии диалогового окна «Добавить данные».
Автоматически сохранять сопоставление столбцов для данной таблицы . Чтобы проверить этот параметр, сохраняется каждая подпрограмма сопоставления после выполнения операции добавления. Процедура отображения сохраняется в формате «tablenameMapping (dbname.tablename)». Это может быть выполнено вручную с помощью кнопки Store Mapping.
Перезагрузите сохраненное сопоставление столбцов для выбранной таблицы автоматически : если существует сохраненная подпрограмма сопоставления, которая сопоставляет все имена столбцов в исходной сетке с целевой сеткой, то она автоматически загружается.
Расширенные параметры поля данных:
Используйте первые 100 (по умолчанию) строк данных Excel для предварительного просмотра и расчета типов данных. Это определяет количество строк, которые отображает предварительный просмотр, и значения, которые влияют на функцию автоматического сопоставления.
При проверке Использовать отформатированные значения данные из Excel обрабатываются как Текст, Двойной или Дата. Это включено по умолчанию. Если мы отключим его, существующие данные никогда не будут обрабатываться как тип даты, поэтому, например, это означает, что дата может быть представлена в виде числа.
Сохраненные сопоставления столбцов - это список сохраненных сопоставлений столбцов, которые были сохранены с помощью функции «Автоматически сохранять сопоставление столбцов для данной таблицы» или вручную с параметром «Сопоставление хранилищ».
После завершения всех шагов, если мы нажмем кнопку «Добавить», появится следующий экран.
Экспорт данных Excel в MySQL
Данные электронной таблицы Microsoft Excel можно экспортировать в новую таблицу базы данных MySQL с помощью параметра « Экспорт данных Excel в новую таблицу» . Сначала вы делаете первые 6 шагов, описанных выше, в разделе « Редактирование данных MySQL в Excel» . Затем введите несколько записей с нужным заголовком в электронную таблицу Excel и выберите записи. Теперь посмотрите пример записи на следующем рисунке -
Теперь нажмите «Экспорт данных Excel в новую таблицу» с помощью стрелки на изображении выше и посмотрите на следующий экран.
1 - Укажите уникальное имя для таблицы MySQL. Предположим, что имя таблицы MySQL "счет-фактура"
2 - Если установлен переключатель радиокнопки Добавить первичный ключ, будет добавлен дополнительный столбец первичного ключа, вы можете изменить имя столбца, и по умолчанию тип данных будет целым числом, которое отображается в 7.
3 - Когда вы установите переключатель «Использовать существующие столбцы», имена столбцов будут доступны в поле со списком, и вы сможете выбрать столбец первичного ключа из списка, и столбец будет выбран. Предположим, вы выбрали столбец INVOICE_NO, посмотрите следующую картинку.
Здесь на рисунке выше был выбран столбец INVOICE_NO, и теперь вы можете изменить имя столбца (точка 6), тип данных (точка 7) и ограничение (точка 9), которые активны.
4 - Если вы установите флажок Первая строка содержит имена столбцов (пункт 4), первый столбец в ваших данных Excel будет заголовком, в противном случае это будет столбец1, столбец2 и т. Д.
5 - Предположим, вы изменили тип данных и имя столбца для других столбцов, которые вы можете нажать на заголовок столбца. Предполагая, что мы хотим изменить тип данных INVOICE_DT, нажмите на INVOICE_DT, и этот столбец будет выбран, а затем вы можете использовать пункты 6,7 и 9.
6 - Вы можете изменить имя столбца.
7 - Вы можете изменить тип данных
8 - Расширенный параметр показывает следующее диалоговое окно.
Расширенные настройки
Используйте первые 100 (по умолчанию) строк данных Excel для предварительного просмотра и вычисления типов данных : этот параметр определяет количество строк, отображаемых при предварительном просмотре, и указанные значения влияют на функцию автоматического сопоставления.
Анализировать и пытаться определить правильный тип данных на основе содержимого поля столбца. Этот параметр пытается анализировать данные и определять тип данных для столбца. Когда столбец содержит несколько типов данных, он определяется как VARCHAR.
Добавить дополнительный буфер к длине VARCHAR (округлить до 12, 25, 45, 125, 255) : если он включен, он автоматически определяет тип данных и устанавливает значение VARCHAR, а затем находит максимальную длину для всех строк в столбце, и округляет максимальную длину до одной из указанных выше длин и, если она отключена, то длина VARCHAR устанавливается равной длине самой длинной записи в электронной таблице Excel.
Автоматически установите флажок «Индекс» для столбцов с целочисленными значениями : по умолчанию этот параметр включен, и тогда для столбцов с целочисленным типом данных будет включен параметр «Создать индекс» по умолчанию.
Автоматически установите флажок «Разрешить пустой» для столбцов без индекса: по умолчанию этот параметр включен, а при включении для столбцов без флажка «Создать индекс» автоматически включается параметр конфигурации «Разрешить пустой».
Использовать отформатированные значения: по умолчанию этот параметр включен, и данные из Excel обрабатываются как текстовые, двойные или даты, но если они отключены, они независимы, то есть не имеют определенного типа данных.
Удалить столбцы, которые не содержат данных, в противном случае пометьте их как «Исключенные»: если этот параметр включен, столбцы без данных в Excel удаляются и не отображаются на панели предварительного просмотра. По умолчанию этот параметр отключен, и тогда эти столбцы будут существовать, но с включенным параметром «Исключить столбец».
После завершения всех настроек нажмите кнопку «Экспортировать» и увидите следующий снимок экрана:
Microsoft SQL Server позволяет встроенными средствами языка T-SQL в SQL запросе импортировать данные из файла Excel в базу данных. Сегодня я подробно расскажу, как это делается, какие условия необходимо выполнить, чтобы эта операция проходила успешно, расскажу про особенности импорта для самых распространённых случаев конфигураций SQL сервера и приведу конкретный порядок действий и практические примеры.
Начну я с того, что импортировать данные из Excel в Microsoft SQL Server можно с помощью «Распределенных запросов» и с помощью «Связанных серверов». Это, скорей всего, Вы уже знаете, так как я уже не раз писал об этом (ссылки на соответствующие материалы указаны чуть выше).
Обратиться к файлу Excel и импортировать данные в Microsoft SQL Server можно с помощью T-SQL инструкций OPENDATASOURCE, OPENROWSET или OPENQUERY.
Однако в вышеупомянутых статьях я упустил несколько важных моментов, одним из которых является то, что у всех конфигурация SQL сервера разная, за счет чего у многих возникают различные проблемы и появляются ошибки во время выполнения распределенных запросов и обращений к связанным серверам. Также я описывал способ загрузки данных из Excel, который на сегодняшний день уже устарел, поэтому сегодня я постараюсь дать Вам немного больше информации о том, как импортировать данные из файла Excel в Microsoft SQL Server на языке T-SQL.
Введение
Итак, как я уже сказал, очень важную роль здесь играет конфигурация SQL сервера, в частности, какая версия сервера установлена, x86 или x64.
Если говорить о последних версиях Microsoft SQL Server 2016-2019, то они только x64 и устанавливаются на 64-разрядные версии Windows.
Исходя из этого я разделю статью на несколько частей, в каждой из которых я расскажу про особенности импорта данных из Excel для самых распространённых случаев конфигураций и приведу конкретный порядок действий.
Для того чтобы быстро узнать, какая версия SQL Server установлена у Вас на компьютере, можете выполнить простой SQL запрос
Обращение к файлу Excel и, соответственно, импорт данных в Microsoft SQL Server происходит с помощью специальных провайдеров (поставщиков). Для работы с Excel в Microsoft SQL Server обычно используются:
Во всех примерах ниже я буду посылать простой запрос SELECT на выборку данных из Excel файла, для того чтобы проверить доступ к данным в файле Excel. Чтобы осуществить импорт данных (загрузить данные в БД), Вы можете использовать любой удобный для Вас способ, например, конструкцию SELECT INTO или INSERT INTO.
Дополнительно рекомендовано закрывать файл Excel во время обращения к нему в распределенных запросах, а также указывать путь к файлу без пробелов (хотя современный SQL сервер умеет работать с пробелами).
Импорт данных из Excel 2003 (файл xls) в Microsoft SQL Server x86
Шаг 1 – Проверяем наличие провайдера Microsoft.Jet.OLEDB.4.0 на SQL Server
В результирующем наборе данных должна присутствовать строка с Microsoft.Jet.OLEDB.4.0. Если такого провайдера нет, то скорей всего в системе нет установленного Excel 2003 и, соответственно, его нужно установить.
Шаг 2 – Предоставление прав пользователю на временный каталог
Особенностью распределённых запросов и работы со связанным серверами Excel в x86 версиях SQL Server является то, что независимо от имени какой учетной записи посылается SQL запрос к Excel, эта учетная запись должна иметь права на запись во временный каталог той учетной записи, под которой работает сама служба SQL Server.Так как поставщик OLE DB создает временный файл во время запроса во временном каталоге SQL Server, используя учетные данные пользователя, выполняющего запрос.
Таким образом, если служба SQL Server работает от имени или локальной, или сетевой службы, необходимо дать соответствующие права на временный каталог этих служб всем пользователям, которые будут посылать распределенные запросы и обращаться к связанному серверу Excel (если сервер работает от имени пользователя, который посылает SQL запросы, то такие права давать не требуется, они у него уже есть).
Это можно сделать с помощью встроенной утилиты командной строки icacls.
Например, для локальной службы команда будет выглядеть следующим образом.
Для сетевой службы
Вместо UserName укажите имя пользователя, который посылает запрос.
Шаг 3 – Включаем распределенные запросы на SQL Server
По умолчанию возможность использования распределённых запросов, в частности функций OPENDATASOURCE и OPENROWSET, в Microsoft SQL Server запрещена, поэтому данную возможность нужно сначала включить.
Она включается с помощью системной хранимой процедуры sp_configure, которая отвечает за системные параметры сервера. Нам необходимо параметру Ad Hoc Distributed Queries присвоить значение 1, для этого выполняем следующую SQL инструкцию.
Шаг 4 – Выполняем SQL запрос, обращение к файлу Excel
Ниже я приведу несколько вариантов обращения к файлу Excel (TestExcel.xls).
OPENROWSET
OPENDATASOURCE
Linked Server
Импорт данных из Excel 2007 и выше (файл xlsx) в Microsoft SQL Server x86
Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server
Точно так же, как и в предыдущем примере, сначала проверяем, установлен ли у нас необходимый нам провайдер, в данном случае нам нужен Microsoft.ACE.OLEDB.12.0.
Шаг 2 – Установка провайдера Microsoft.ACE.OLEDB.12.0 (32-bit)
Если провайдера нет, то его необходимо установить.
Вот ссылка на скачивание провайдера
Выберите и скачайте файл, соответствующий архитектуре x86 (т.е. в названии без x64).
Шаг 3 – Предоставление прав пользователю на временный каталог
В данном случае также даем права на временный каталог локальной или сетевой службы всем пользователям, которые будут посылать SQL запросы к файлу Excel.
Используем все ту же утилиту командной строки icacls.
Для локальной службы
Вместо UserName укажите имя пользователя, который посылает запрос.
Шаг 4 – Включаем распределенные запросы на SQL Server
Включаем возможность использования OPENDATASOURCE и OPENROWSET на Microsoft SQL Server, повторюсь, что по умолчанию данная возможность отключена.
Шаг 5 – Настройка провайдера Microsoft.ACE.OLEDB.12.0
В данном случае дополнительно потребуется настроить провайдер Microsoft.ACE.OLEDB.12.0. Для этого включим следующие параметры провайдера (для отключения укажите 0 вместо 1).
Если не включать данные параметры, то, скорей всего, появится ошибка примерно следующего содержания
Шаг 6 – Выполняем SQL запрос, обращение к файлу Excel
Примеры обращения к файлу Excel (TestExcel.xlsx).
OPENROWSET
OPENDATASOURCE
Linked Server
Импорт данных из Excel (любые файлы) в Microsoft SQL Server x64
Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server
Шаг 2 – Установка провайдера Microsoft.ACE.OLEDB.12.0 (64-bit)
В случае, если провайдер не установлен, его необходимо скачать и установить.
Скачиваем файл x64.
Шаг 3 – Включаем распределенные запросы на SQL Server
Необходимость включения возможности использования распределенных запросов (OPENDATASOURCE и OPENROWSET) на Microsoft SQL Server x64 также есть, поэтому сначала включаем ее, выполнив точно такую же инструкцию.
Шаг 4 – Настройка провайдера Microsoft.ACE.OLEDB.12.0
Шаг 5 – Выполняем SQL запрос, обращение к файлу Excel
Здесь используются точно такие же параметры в SQL запросах, что и в предыдущем примере. Для удобства продублирую их еще раз.
Примеры обращения к файлу Excel (TestExcel.xlsx).
OPENROWSET
OPENDATASOURCE
Linked Server
Подведение итогов
Ну и в заключение я сгруппирую действия, которые необходимо выполнять в зависимости от выпуска SQL Server (x68 или x64) и версии файла Excel (xls или xlsx), в одну таблицу, для Вашего удобства.
| Действие / Настройка | Импорт Excel 2003 (файл xls) в SQL Server x86 | Импорт Excel 2007 (файл xlsx) в SQL Server x86 | Импорт Excel (любые файлы) в SQL Server x64 |
| Установка Excel 2003 | Да | Нет | Нет |
| Установка провайдера Microsoft.ACE.OLEDB.12.0 | Нет | Да (x86) | Да (x64) |
| Предоставление прав на временный каталог служб (если SQL сервер работает от имени служб) | Да | Да | Нет |
| Настройка провайдера Microsoft.ACE.OLEDB.12.0 | Нет | Да | Нет (по необходимости) |
| Параметры подключения в SQL запросах | Microsoft.Jet. OLEDB.4.0 и Excel 8.0 | Microsoft.ACE. OLEDB.12.0 и Excel 12.0 | Microsoft.ACE. OLEDB.12.0 и Excel 12.0 |
| Включение распределённых запросов на SQL Server | Да | Да | Да |
Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.
Главное меню » Базы данных » MySQL » Экспорт данных MySQL в Excel в PHP
И ногда вам может потребоваться экспорт в формат Excel из MySQL для локального использования. База данных или таблицы базы данных MySQL могут быть экспортированы в различные форматы файлов, такие как CSV, XML, SQL, Excel и т. д., С помощью клиента PHP, phpMyAdmin. Также можно экспортировать данные MySQL с помощью сценария PHP вместо экспорта данных вручную. Когда веб-приложению требуется, чтобы данные из базы данных MySQL хранились в формате Excel, для выполнения этой задачи лучше всего использовать скрипт PHP. В этой статье показано, как экспортировать данные MySQL с помощью сценария PHP.Предпосылки
Перед экспортом любых данных MySQL выполните следующие задачи. Вы должны открыть клиент MySQL в терминале или клиент phpMyAdmin в браузере для выполнения следующих задач. Таблица с пятью записями будет создана в базе данных после выполнения следующих команд SQL.
A. Создайте базу данных MySQL
Выполните следующую команду SQL, чтобы создать базу данных с именем компании.
Б. Создайте таблицу
Выполните следующую команду SQL, чтобы создать таблицу с именем items.
C. Вставить данные в таблицу
Если вы откроете таблицу элементов из клиента phpMyAdmin.
Экспорт данных MySQL в файл Excel
Здесь вы должны создать файл PHP с именем read_and_export.php, чтобы создать следующий сценарий, который считывает записи в таблице элементов и создает файл Excel с содержимым таблицы элементов. В сценарии объявляется объект подключения к базе данных для извлечения данных из таблицы базы данных. Затем определяется запрос выбора для чтения всех записей из таблицы элементов, которые хранятся в переменной с именем $ items. Эта переменная используется для отображения содержимого таблицы в табличной форме и создания файла Excel с содержимым таблицы на основе действий пользователя. Когда пользователь нажимает кнопку, которая позже будет создана в документе HTML для экспорта данных таблицы в формате Excel, функция isset() этого скрипта вернет значение «true». Чтобы создать файл Excel, имя файла определяется с расширением xsl. Необходимая информация заголовка передается с помощью функции header(). Затем переменная $heading используется для установки заголовка столбцов в файле Excel. Ключевые значения переменной $ items используются в качестве значений заголовка. Затем функция implode() используется для записи записей таблицы элементов в файл Excel.
Читать Как сопоставить значения NULL с другими значимыми значениямиread_and_export.php
Создайте еще один файл PHP с именем index.php со следующим кодом для отображения записей в таблице элементов в браузере с помощью кнопки «Экспорт в Excel». Здесь файл read_and_export.php включен в начало сценария, чтобы установить соединение с базой данных, прочитать данные таблицы и создать файл Excel с данными таблицы, когда пользователь нажимает кнопку. Данные таблицы будут отображаться в табличном формате с использованием начальной загрузки и jQuery.
index.php
Выход
Здесь файлы read_and_export.php и index.php хранятся в папке var var/www/html/php/export. Запустите следующий URL-адрес в браузере, чтобы запустить index.php.
При нажатии кнопки «Экспорт в Excel» появится следующее диалоговое окно. Установите переключатель «Сохранить файл» и нажмите кнопку «ОК», чтобы создать и загрузить файл Excel с данными таблицы.
Когда вы щелкаете загруженный файл, чтобы открыть его, появляется следующее диалоговое окно, в котором вы можете выбрать необходимые параметры перед отображением файла. Данные в каждом столбце разделяются табуляцией (\t) во время создания файла Excel. Таким образом, здесь выбирается вкладка перед созданием файла.
Вывод
Файл Excel можно создать из таблицы MySQL, разделив значения столбцов определенным разделителем. Разделителем может быть запятая (,) табуляция (\t), точка с запятой (;), пробел ( ) или другие конкретные символы. В этой статье табуляция используется в качестве разделителя. Перед отображением содержимого файла Excel необходимо выбрать соответствующий символ в диалоговом окне «Импорт текста». Если в содержимом данных таблицы MySQL используются одинарные или двойные кавычки, то эти данные не будут сохранены в файле Excel. Я надеюсь, что это руководство помогло вам узнать, как экспортировать данные таблицы MySQL в файл Excel.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
Читайте также: