Экспорт в excel из mysql в - Учим Эксель

Export MySQL data to Excel in PHP

I’m trying to get my MySQL data to Excel file, but I’m having problems with Excel cells. All my text goes to one cell, I would like to have each row value in separate Excel cell. Here is my code:

12 Answers 12

Just Try With The Following :

PHP Part :

I think this may help you to resolve your problem.

Try this code. It’s definitly working.

If you just want your query data dumped into excel I have to do this frequently and using an html table is a very simple method. I use mysqli for db queries and the following code for exports to excel:

PHPExcel is your friend. Very easy to use and works like a charm.

This is new version of php code

I think you should try with this API

code for excel file

if mysqli version

Posts by John Peter and Dileep kurahe helped me to develop what I consider as being a simpler and cleaner solution, just in case anyone else is still looking. (I am not showing any database code because I actually used a $_SESSION variable.)

The above solutions invariably caused an error upon loading in Excel, about the extension not matching the formatting type. And some of these solutions create a spreadsheet with the data across the page in columns where it would be more traditional to have column headings and list the data down the rows. So here is my simple solution:

  1. Change to .csv (which Excel instantly updates to .xls and there is no error upon loading.)
  2. Use the comma as delimiter.
  3. Double quote the Key and Value to escape any commas in the data.
  4. I also prepended column headers to $results so the spreadsheet looked even nicer.

Try the Following Code Please. just only update two values.
1.your_database_name 2.table_name

You can export the data from MySQL to Excel by using this simple code.

This is baes on John Peter’s answer above. The code is working perfectly but I needed it for WordPress. So, I did something like this:

Not the answer you're looking for? Browse other questions tagged php mysql excel export or ask your own question.

Linked

Related

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

site design / logo © 2021 Stack Exchange Inc; user contributions licensed under cc by-sa. rev 2021.10.14.40466

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Автоматизация обработки таблиц в Excel и перенос данных в MySQL
[ Разбор кейса ]

Карло, ИТ эксперт, работал над сложной, но достаточно обычной задачей. Ему необходимо было создать систему, которая бы создавала отчеты для управления на базе Excel файлов, приобретенных от порядка 10-20 исследовательских лабораторий.

Поначалу эти отчеты делали в Excel – в основном вручную – и направляли управлению и лабораториям в печатном виде либо PDF.

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

В схожих задачках принципиально, во-1-х, минимизировать работу вручную и уменьшить время на подготовку данных. Во-2-х, аккуратненько экспортировать приготовленные данные в MySQL.

Один из подходов к автоматизации – сделать особый макрос VBA. Но при таком большенном объеме нужных операций написание макроса с нуля займет необоснованно много времени. Не считая того, если вы хоть раз пробовали настроить интеграцию макроса с наружной базой данных, вы представляете, как мучительной быть может синхронизация вручную. Одно маленькое изменение в процессе (например, другое заглавие начальной таблицы) ведет к нескончаемым правкам в скрипте.

Надстройка XLTools, с иной стороны, отдала Карло гибкие инструменты для автоматизации всего процесса без VBA: извлечь, приготовить и экспортировать данные в базу данных MySQL.

XLTools Автоматизация – это мощнейший и многоцелевой инструмент. Последовательность всех операций записана на одном листе Excel, и все операции вручную сводятся к нажатию одной единственной клавиши.

Разглядим некие операции, автоматические в этом процессе.

Шаг 1. Автоматом собрать данные из файлов Excel

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

Конкретно это и делает команда XLTools.ExtractTable :

К примеру, нам необходимо извлечь эту таблицу с числовыми кодами, присвоенными лабораториям:

extract table before

Для этого, пропишем команду автоматизации XLTools.ExtractTable :

XLTools.ExtractTable
WorkbookFile: source.xls
Range: LABCODE!E:F
SkipTopRowsCount: 4
ApplyTableName: LabCodes
Headers: lab,stat
PreserveFormat: TRUE
OutputTo: NewHiddenSheet[LabCodes]

Укажите спектр данных, которые необходимо извлечь:

Укажите, куда поместить извлеченные данные:

Поместить итог на новейший сокрытый лист “LabCodes”.

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

Выполнив эту команду, получим такую таблицу “LabCodes”, начиная с ячейки A1 на новеньком сокрытом листе “LabCodes”:

Extract table after

Буквально так же мы извлекли остальные нужные таблицы из файлов лабораторий. Опосля этого мы готовы к работе с полным объемом данных.

Шаг 2. Соединить две таблицы Excel c помощью автоматического SQL запроса

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

Для обработки данных мы советуем команду XLTools.SQLSelect . Мы весьма любим и сами нередко используем эту команду:

К примеру, нам необходимо было соединить эти две таблица и переименовать столбцы. Не считая того, нам необходимо было обработать положительные/отрицательные значения: столбец “sign” показывает, что значения в столбце “d” положительные либо отрицательные.

merge tables before

Для этого, мы используем команду XLTools.SQLSelect :

XLTools.SQLSelect
SQLQuery: SELECT
tbl2.[lab] as [ic_code],
tbl1.[Nb] as [rank],
tbl1.[%] as [percent],
tbl1.[N°] as [lab_code],
CASE WHEN tbl1.[sign] = '-' THEN -1 * tbl1.[d] ELSE tbl1.[d] END as [dev],
tbl1.[Sd] as [s_dev],
tbl1.[D2] as [dist],
tbl1.[Method] as [method],
FROM [source.xls].[Table1]tbl1
LEFT JOIN [LabCodes]tbl2 ON tbl1.[Nb] = tbl2.[stat]
PreserveFormat: TRUE
ApplyTableName: data
OutputTo: NewSheet[data]

Укажите, какие поля необходимо избрать (SELECT):

Укажите, как выгрузить итог запроса:

Как вы сделайте эту команду, через секунду будет сгенерирована таблица результата:

SQL Query merge result

Направьте внимание, что все столбцы имеют новейшие наименования. Значения в столбце “dev” или положительные, или отрицательные. XLTools.SQLSelect – хороший инструмент для автоматизации обработки таблиц Excel. Фактически все сложные манипуляции в проекте Карло мы выполнили при помощи SQL запросов.

Шаг 3. Автоматом экспортировать данные из Excel в базу данных MySQL

В проекте Карло было много сложных и вдохновляющих задач (СПАСИБО)! Если извлечение данных и SQL запросы уже издавна включены в Автоматизацию XLTools, то экспорт из Excel в MySQL – новенькая разработка.

Опосля того, как мы подготовили и необходимым образом отформатировали данные, создаем команду XLTools.ExportToMySQL :

XLTools.SQLSelect
Tables: zscore-fix,zscore-pt,outliers,repeatability,data
Server: Имя сервера либо IP адресок сервера
Database: Имя вашей БД
Uid: Имя юзера для доступа в вашу БД
Pwd: Пароль для доступа в вашу БД
ApplyTableName: ExportResults
OutputTo: NewSheet[ExportResults]

Укажите, какие таблицы необходимо экспортировать в базу данных:

Введите данные для подключения к MySQL:

Укажите, где поместить отчет о экспорте данных:

По окончании выполнения, команда выгрузит данные в MySQL и создаст сводный отчет о экспорте:

export to my sql report

Отчет указывает, сколько записей (строк) из каждой таблицы Excel было экспортировано в БД. В случае ошибок во время экспорта, они будут указаны с крайнем столбце. К слову, описание ошибок будет приведено на языке вашей базы данных. Наш клиент Карло работает в Риме, и ошибки будут выводиться на итальянском.

В этом случае, экспорт 2-ух записей в таблице “zscore-fix” не был успешен. Похоже, некие ячейки в данной нам таблице были пустыми – а такие записи не могут быть экспортированы в MySQL. Сейчас просто возвратиться и поправить ошибки.

Привязать выполнение всего процесса к одной кнопочке

XLTools Автоматизация также дозволяет назначать пользовательские клавиши для этих установок. В примерах, рассмотренных выше, мы делали каждую команду раздельно. Но мы можем выделить всю последовательность установок и привязать их к своим кнопочкам на вкладке XLTools.

Напр., команды Карло привязаны к трем кнопочкам. “Prepare & Export” запускает весь процесс (извлечение, подготовка и экспорт данных) по нажатию всего одной клавиши:

У вас есть процессы в Excel, которые можно заавтоматизировать?

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

Что если в XLTools нет (пока нет) функционала, подходящего для вашей определенной задачки? Мы попытаемся создать его вам. В базе почти всех новейших надстроек – предложения наших юзеров. Благодаря проекту Карло, Автоматизация сейчас поддерживает Экспорт из Excel в MySQL.

Благодарность: Карло Мелис (Carlo Melis), ИТ эксперт, работает по заданию интернационального негосударственного органа по сертификации пищевой продукции, Рим, Италия.

Надстройки в фокусе: Автоматизация без VBA (извлечение таблиц, SQL запросы, экспорт из Excel в MySQL). Поддерживается в версии XLTools 5.0.0.762 и выше.

Импорт и экспорт баз данных MySQL через консоль и phpMyAdmin

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

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

Существует два метода импорта/экспорта:

  • через веб-интерфейс PhpMyAdmin;
  • подключившись к консоли MySQL локально либо через SSH.

Подробнее о подключении через протокол SSH можно выяснить тут.

Все описанные деяния с MySQL идентичны по выполнению на всех операционных системах Linux.

Экспорт базы данных при помощи PhpMyAdmin

Сначала требуется выполнить вход в интерфейс PhpMyAdmin. Перечень имеющихся баз находится в верхнем левом углу.

Базы данных phpmyadmin

Выбрав нужную базу данных, необходимо перейти в раздел «Экспорт».

Экспорт базы данных PhpMyAdmin

Для резвого экспорта подойдёт вариант «Резвый – показывать минимум опций», при котором употребляются рациональные характеристики переноса. Чтоб открыть перечень доп опций, необходимо избрать способ «Обыденный – показывать все вероятные опции».

Во вкладке «Формат» рекомендуется выставить расширение SQL (.sql) для экспортируемого файла. Но также можно употреблять и остальные форматы, такие как CSV (.csv) и XML (.xml).

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

Экспорт базы данных MySQL через консоль

Экспорт базы данных производится при помощи утилиты MySQLdump:

Переменные в приведенном примере:

  • «Login» – имя юзера MySQL.
  • «Password» – пароль юзера MySQL.
  • «db_name» – наименование базы данных.
  • «db_name.sql» – конечный файл.

MySQLdump дозволяет экспортировать базу данных с удаленного сервера (если хосту разрешено подключение):

*Заместо «XXX.XXX.XXX.XXX» указывается животрепещущий IP-адрес сервера.

Импорт базы данных при помощи PhpMyAdmin

Для начала нужно авторизоваться в интерфейсе PhpMyAdmin и избрать базу данных в левом верхнем углу. Опосля этого стоит перейти в раздел «Импорт».

Импорт базы данных PhpMyAdmin

Сейчас необходимо надавить «Избрать файл» и в открывшемся проводнике избрать SQL-файл (принимаются также архивы Gzip, Bzip2, Zip). В разделе «Формат» отмечается расширение применяемого файла.

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

Импорт базы данных MySQL через консоль (подмена имеющейся)

Для начала нужно авторизоваться в командной строке MySQL:

*Заместо «Login» и «Password» вводятся действительные данные юзера MySQL.

Сейчас выбирается существующая база данных, с которой планируется предстоящая работа:

*Заместо «db_name» вводится наименование имеющейся БД.

Остаётся выполнить импорт таблиц последующей командой:

*Заместо «db_name.sql» вводится путь до файла SQL (за ранее загружается на сервер).

Импорт и создание базы данных MySQL через консоль

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

Для начала нужно авторизоваться в консоли MySQL:

*Заместо «Login» и «Password» вводятся действительные данные юзера MySQL.

Дальше необходимо сделать новейшую базу данных:

*Заместо «new_database» вводится собственное наименование базы данных.

На дисплее покажется последующий вывод:

Сейчас необходимо избрать сделанную базу, чтоб все следующие команды относились к ней:

Как импортировать и экспортировать базы данных в MySQL и MariaDB

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

Для того, чтоб выполнить все последующие деяния, у вас должны быть:

а) доступ к серверу на базе Linux, на котором работает MySQL/MariaDB;
б) заглавие базы данных и данные доступа к ней.

Используем консоль

Экспорт

Для того, чтоб произвести экспорт, мы будем употреблять утилиту mysqldump. С помощью нее осуществляется работа с текстовыми файлами базы данных. Итак, вы должны знать заглавие базы данных, также иметь доступ (логин и пароль) к аккаунту, который имеет, по последней мере, доступ read only (лишь для чтения).

Для экспорта базы данных введите вот такую команду:

в которой необходимо ввести имя юзера с нужным доступом, заглавие подходящей для вас базы данных, также data-dump.sql – файл в текущей директории, куда будут сохранены данные.

Опосля ввода данной нам команды вы не увидите никакого вывода на дисплее, но вы сможете проверить содержимое файла data-dump.sql для того, чтоб убедиться, что сейчас он является запасной копией вашей базы данных.

Содержимое файла обязано смотреться приблизительно так, как показано ниже. В документе будет обозначено заглавие базы данных (в данном случае MySQL), ее заглавие и остальные данные.

Если во время процесса экспорта будут какие-нибудь ошибки, утилита mysqldump выведет на экран сообщение о их.

Импорт

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

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

Опосля того, как вы подключились к консоли MySQL, сделайте новейшую базу данных (в данном случае new_database):

Опосля этого на дисплее возник последующий вывод:

Сейчас для выхода из консоли MySQL нажмите CTRL+D. Дальше перебегайте к самому {импорту}. Создать это можно, введя вот такую команду:

Команда весьма похожа на команду экспорта, для вас необходимо ввести имя юзера, заглавие новейшей базы данных, куда вы будете импортировать данные (в качестве примера new_database), и заглавие самого файла, который вы собираетесь импортировать (data-dump.sql).

Если команда выполнена корректно, то никакого вывода на дисплее вы не увидите; на дисплее могут отобразиться лишь сообщения о каких-либо ошибках. Как и в случае с экспортом, проверить, буквально ли все прошло удачно, вы сможете методом подключения к MySQL и просмотра данных. Создать это можно, например, используя команды USE и SHOW. Команда use описывает, какая база данных будет употребляться в последующих запросах. Введите:

Тогда и при всех следующих запросах в данном сеансе автоматом будет употребляться эта база данных. Данную установку можно поменять, использовав команду use с заглавием иной базы данных.

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

Допустим, нам необходимо поглядеть, перечень таблиц в базе. Для этого вводим:

Желаете узреть перечень столбцов в некий определенной таблице? Используйте команду SHOW COLUMNS FROM и заглавие необходимо для вас таблицы:

Статистику по работе сервера можно получить в ответ на команду:

Используем phpMyAdmin

Экспорт и импорт баз данных можно также созодать через phpMyAdmin. В общем и целом, пожалуй, это даже наиболее обычный путь, чем внедрение консоли.

Экспорт

Зайдите в phpMyAdmin и изберите базу данных, с которых вы желаете работать.

Дальше изберите вкладку «Экспорт» и, в зависимости от собственных предпочтений, резвый либо обыденный способ экспорта. 2-ой подойдет для тех, кто желает без помощи других выставить все опции.

Как импортировать и экспортировать базы данных в MySQL и MariaDB

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

Импорт

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

Импорт баз данных

Изберите файл для импорта на вашем компе и проверьте опции. Быстрее всего, они подходят для импортирования вашего файла, но при желании их можно поменять. Нажмите клавишу «Вперед» — и будет выполнен импорт файла. Вы увидите надпись вроде таковой:

Импорт удачно завершён, выполнено 32 запроса.

Ниже в красноватой рамке могут идти сообщения о появившихся ошибках (к примеру, о дублировании).

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

Заключение

Выбор пригодного способа экспорта и импорта баз данных зависит лишь от вас и ваших предпочтений – кому-то проще работать в консоли, а для кого-либо понятнее phpMyAdmin. Основное, необходимо часто созодать бэкапы, в том числе и ваших баз данных.

К слову, полезную информацию о базах данных я также отыскал в Справочном центре Timeweb.

Интересно почитать:  В excel как сделать примечание в
Ссылка на основную публикацию
Adblock
detector