Экспорт mysql в excel php

Экспорт данных MySQL в Excel в PHP

Главное меню » Базы данных » MySQL » Экспорт данных MySQL в Excel в PHP

Предпосылки

Перед экспортом любых данных MySQL выполните следующие задачи. Вы должны открыть клиент MySQL в терминале или клиент phpMyAdmin в браузере для выполнения следующих задач. Таблица с пятью записями будет создана в базе данных после выполнения следующих команд SQL.

A. Создайте базу данных MySQL

Выполните следующую команду SQL, чтобы создать базу данных с именем компании.

Б. Создайте таблицу

Выполните следующую команду SQL, чтобы создать таблицу с именем items.

C. Вставить данные в таблицу

Если вы откроете таблицу элементов из клиента phpMyAdmin.

Экспорт данных MySQL в файл Excel

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.

Источник

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:

oi5jr

95Zm2

12 Answers 12

Just Try With The Following :

PHP Part :

I think this may help you to resolve your problem.

lNOMP

Try this code. It’s definitly working.

cb7H2

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.

O0Xld

This is new version of php code

6aLN9

I think you should try with this API

oi5jr

code for excel file

photo

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:

Источник

PHP – выгрузка данных в Excel из базы MySQL

У многих при работе с 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 как стандарта, таким образом, Вы сможете работать в любой системе управления базами данных. Курс включает много практики: онлайн-тестирование, задания и многое другое.

Источник

Как сделать экспорт большой таблицы в Excel средствами PHP?

Добрый день, уважаемые пользователи.

Имеется таблица MySQL 70 столбцов, более 150 тыс. записей

А выборка может содержать 25 тыс., 60 тыс. записей.

Формат CSV не подходит, так как он не поддерживает редактирование формате ячеек.

Как быть подскажите?

3zmme5qhycs

$sheet->getDefaultStyle()->getBorders()->getTop()->applyFromArray(
array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’ => ‘000000’
)
)
);
$sheet->getDefaultStyle()->getBorders()->getBottom()->applyFromArray(
array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’ => ‘000000’
)
)
);
$sheet->getDefaultStyle()->getBorders()->getLeft()->applyFromArray(
array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’ => ‘000000’
)
)
);
$sheet->getDefaultStyle()->getBorders()->getRight()->applyFromArray(
array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’ => ‘000000’
)
)
);

//Ширина столбцов
$sheet->getColumnDimension(‘B’)->setWidth(16);
.
$sheet->getColumnDimension(‘AS’)->setWidth(16);

//$sheet->getColumnDimension(‘B’)->setWidth(20);
$sheet->getStyle(1)->getAlignment()->setWrapText(true);
$sheet->getStyle(‘B1’)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$sheet->getStyle(1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

$sheet->setCellValueByColumnAndRow(0,1,iconv(«windows-1251», «UTF-8″,»Столбец 1»));
.
$sheet->setCellValueByColumnAndRow(70,1,iconv(«windows-1251», «UTF-8″,»Столбец 70»));

///////////////////////////////////////////////////////////////////////////
//Запись данных
$i=2;
$q=mysql_query(«SELECT * FROM t_base «.$dop_query.» LIMIT 10000″);
while ($r=mysql_fetch_array($q))
<
$sheet->setCellValueByColumnAndRow(0,$i,rep($r[‘cell_1’]));
.
$sheet->setCellValueByColumnAndRow(70,$i,rep($r[‘cell_70’]));
$i++;
>
///////////////////////////////////////////////////////////////////////////
//Сохранение данных в файл
$objWriter = new PHPExcel_Writer_Excel5($xls);
//$objWriter->setTempDir($_SERVER[‘DOCUMENT_ROOT’].’/ex/’);
$objWriter->save($_SERVER[‘DOCUMENT_ROOT’].$path.$file_name);

Источник

Экспорт и импорт данных между MySQL и Microsoft Excel

Установка и настройка

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

Вы можете установить компонент MySQL для Excel при установке MySQL Community Server 6. Или вы можете установить его как дополнение к существующей установке MySQL Server. Однако для установки этого компонента есть некоторые предпосылки.

MySQL для Excel Требования

Для установки MySQL для Excel Installer на вашем компьютере должно быть установлено следующее:

.NET Framework 4.0 (клиент или полный профиль).

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:

Шаг 3:

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

Шаг 4:

Шаг 5:

Введите пароль для соединения с сервером MySQL. Базы данных будут отображаться в боковой панели MySQL для Excel. Наша База данных является сотрудником. Смотрите следующую картинку.

Шаг 6:

Шаг 7:

Выберите таблицу, которую вы хотите редактировать, нажмите «Редактировать данные MySQL» на панели навигации, как показано выше, и посмотрите следующий снимок экрана.

Шаг 8:

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

Шаг 9:

Здесь, в нашей таблице, идентификатор первого столбца является первичным ключом. Если мы изменим значение ID в ячейке A19 вместо 17 и нажмем клавишу ввода, цвет этой ячейки будет зеленым. Теперь мы изменили значение ячейки F16 Лондона и нажмите клавишу ввода, цвет ячейки изменится на зеленый, а желтый цвет в последнюю очередь означает, что ячейки принимают новые данные. Введенные здесь данные вставляются в таблицу MySQL.

Шаг 10:

Теперь, если мы нажмем кнопку «Восстановить данные», посмотрите следующий снимок экрана.

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

Здесь на рисунке выше вы видите сообщение об ошибке, и цвет ячейки A19 изменился с зеленого на красный, потому что столбец идентификатора является первичным ключом, а уникальность здесь нарушена. Теперь мы вернули значение ячейки A19 в исходное значение, то есть 18, и нажмите кнопку Commit Changes, и теперь посмотрим на снимок экрана ниже.

На изображении выше показано, как цвета ячейки F16 преобразованы из зеленого в синий, что означает успешное выполнение изменений. Теперь вы можете закрыть сохранение окна Excel или нет, но таблица базы данных была обновлена. Вы можете видеть это, чтобы сделать шаг снова. Если вы установили флажок «Автоматическая фиксация», эффект сразу же отобразится на вашем листе, и данные будут обновлены в базе данных MySQL.

Импорт данных MySQL в Excel

Данные можно импортировать из MySQL в электронную таблицу Microsoft 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 и столбцами 5 Целевой столбец 4 будет отображаться просто, но при выполнении процесса для целевого столбца 5 появится диалоговое окно с предупреждением, показанное ниже.

Методы картирования

Вот посмотрите три метода отображения:

Метод автоматического сопоставления пытается сопоставить имена исходных столбцов Excel с именами столбцов целевой таблицы MySQL.

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

Вы можете сохранить свои собственные стили сопоставления с помощью кнопки « Сохранить сопоставление» и сохранить имя, например «name (dbname.tablename)», и оно будет доступно в поле со списком «Метод сопоставления».

Сохраненные сопоставления могут быть удалены или переименованы в диалоговом окне « Дополнительные параметры ».

Расширенные настройки

Если мы нажмем кнопку «Дополнительные параметры», диалоговое окно будет выглядеть примерно так:

Выполните автоматическое сопоставление при открытии диалогового окна : с помощью этих параметров автоматически сопоставляется цель и источник при открытии диалогового окна «Добавить данные».

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

Расширенные параметры поля данных:

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

При проверке Использовать отформатированные значения данные из Excel обрабатываются как Текст, Двойной или Дата. Это включено по умолчанию. Если мы отключим его, существующие данные никогда не будут обрабатываться как тип даты, поэтому, например, это означает, что дата может быть представлена в виде числа.

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

Экспорт данных Excel в MySQL

Теперь нажмите «Экспорт данных Excel в новую таблицу» с помощью стрелки на изображении выше и посмотрите на следующий экран.

Здесь на рисунке выше был выбран столбец INVOICE_NO, и теперь вы можете изменить имя столбца (точка 6), тип данных (точка 7) и ограничение (точка 9), которые активны.

Расширенные настройки

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

Анализировать и пытаться определить правильный тип данных на основе содержимого поля столбца. Этот параметр пытается анализировать данные и определять тип данных для столбца. Когда столбец содержит несколько типов данных, он определяется как VARCHAR.

Добавить дополнительный буфер к длине VARCHAR (округлить до 12, 25, 45, 125, 255) : если он включен, он автоматически определяет тип данных и устанавливает значение VARCHAR, а затем находит максимальную длину для всех строк в столбце, и округляет максимальную длину до одной из указанных выше длин и, если она отключена, то длина VARCHAR устанавливается равной длине самой длинной записи в электронной таблице Excel.

Автоматически установите флажок «Индекс» для столбцов с целочисленными значениями : по умолчанию этот параметр включен, и тогда для столбцов с целочисленным типом данных будет включен параметр «Создать индекс» по умолчанию.

Автоматически установите флажок «Разрешить пустой» для столбцов без индекса: по умолчанию этот параметр включен, а при включении для столбцов без флажка «Создать индекс» автоматически включается параметр конфигурации «Разрешить пустой».

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

Удалить столбцы, которые не содержат данных, в противном случае пометьте их как «Исключенные»: если этот параметр включен, столбцы без данных в Excel удаляются и не отображаются на панели предварительного просмотра. По умолчанию этот параметр отключен, и тогда эти столбцы будут существовать, но с включенным параметром «Исключить столбец».

После завершения всех настроек нажмите кнопку «Экспортировать» и увидите следующий снимок экрана:

Источник

Моя дача
Adblock
detector