Экспорт в excel из mysql в

Экспорт в excel из mysql в

Доброе время суток!

Как правильно выполнить экспорт из Excel в MySQL?

Стоит dbForge Studio for MySQL. Все пробовал через него.

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

Через кучу попыток удалось через dbForge Studio for MySQL импортировать XLS-файл, но японский все равно кракозябрами. При импорте для таблицы и для строк указывал UTF.

При этом, чтобы удалось выполнить импорт, пришлось убрать почти все строки. Закономерности не понял, но сначала мог вставить только если в excel`e было 5 строчек, затем если 9, а потом — до 50 дошел. Вроде все выполнял одинаково — открыл исходный файл, скопировал часть строк (5, 10, 50), вставил в другой (вставлял с самого начала, замещая содержимое), сохранял и отправлял на импорт. Каждый раз загружалось разное количество строк («загружалось» — это если во время импорта не возникало ошибок)

В данном случае может быть и баг dbforge, но вопрос не в этом, т.к., насколько понял, мне все равно придется экспортировать в CSV, а с этим тоже проблемы.

Пробовал сделать экспорт из Excel в CSV, но MS Excel не признает при экспорте UTF, а он нужен — есть записи не только английском или русском, но и остальных языках.

Поставил Libre Office. Сделал экспорт в CSV в нем, указал UTF — экспортировалось. Попробовал загрузить — все языки нормально загрузились, а японский — кракозяброй.

Если экспорт данных из XLS в MySQL можно сделать не используя CSV, то как?

Если делать через CSV, то данный вопрос можно несколько переформулировать:

Как (или точнее чем) экспортировать XLS в CSV так, чтобы при импорте в базу нормально отображались различные языки? У меня, повторюсь, возникла проблема только с японским. Русский, немецкий, французский, хинди и еще некоторые экспортировались в CSV и записались в БД нормально.

На SO находил несколько похожих вопросов (про экспорт из XLS в CSV в UTF), но одного этого мне оказалось мало — у меня сразу после экспорта не отображался тот самый японский.

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

У многих при работе с PHP в связке с MySQL возникает такая потребность, как экспорт данных из базы в формат xls, для того чтобы люди, которым нужны эти данные, обрабатывали их в программе Excel или просто пользователям было удобно смотреть эти данные. Недавно у меня возникла такая потребность и сегодня я расскажу, как это дело можно реализовать.

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

Для начала приведу пример конечного xls файла, в программе Excel выгрузка будет выглядеть примерно так:

Другими словами, никаких картинок, стилей выгружено не будет, только заголовки столбцов и сами данные.

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

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

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

А еще чтобы Вы понимали, какие данные я выгружаю, приведу пример простой таблицы в БД (у меня ее название test):

Создаем полноценные таблицы в Excel из базы MySQL

* Язык разработки: PHP 5.3+
* Охваченные темы: MySQL, PHP, PHPExcel, PDO
* Уровень сложности: Средний
* Время на завершение: 1 час
* Прикрепленные файлы

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

Данное руководство расскажет вам о том, как воспользоваться PHP-компонентами (которые распространяются с открытым исходным кодом) для создания настоящих таблиц Excel из указателей MySQL SELECT. Интересно? Тогда давайте приступим!

PHPExcel – это набор классов PHP, которые позволяют нам считывать и записывать различные форматы файлов таблиц, а также управлять ими. Перед тем как начать, вам понадобится дистрибутив PHPExcel. Последнюю версию компонента PHPExcel можно скачать здесь http://www.phpexcel.net .

Основное взаимодействие между таблицами данных и электронными таблицами

Значение SQL SELECT возвращает нам набор результатов, а точнее, подборку колонок с именами, в ячейках которых содержатся данные. В PHP/MySQL каждая ячейка результата может быть (и зачастую так и бывает) представлена в виде ассоциативного массива, в котором ключ к массиву – это название колонки.

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

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

Читать еще:  Формате excel

Этап 1: учимся правильно выставлять наименования колонок по алфавиту

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

Схема наименования колонок в Excel

Когда названия колонок доходят до буквы Z, к имени начинает добавляться еще одна буква, т.е.: A… Z,AA, AB … AZ. После AZ пойдет BA (как можно видеть ниже).

Алгоритм наименования колонок

Следующий код PHP показывает нам, как правильно называть колонки от А до ZZ.

Данный алгоритм не пойдет далее ZZ. Алгоритм приводится в исполнение (немного в другом виде) с помощью дополнительного файла в пользовательской функции MySqlExcelBuilder::mapColumns.

Этап 2: Форматирование и тестирование SQL-значений

Представленная ниже схема отображает нам упрощенное взаимодействие клиент/заказ. Значения SQL CREATE для нижеприведенной схемы включены в заархивированный в zip-формат файл xls_sample.sql, который приложен к руководству.

«быстрое и грязное» значение SELECT

Обычно здесь можно просто быстро извлечь данные из значения SQL в таблицах:

Главное преимущество подобного метода заключается в экономии времени. Хотя результаты редко выглядят приемлемо.

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

Следующий скриншот отображает нам результат вышеприведенного запроса:

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

Имена колонок написаны с маленькой буквы, и вместо пробелов мы видим нижнее подчеркивание.

Получаем более привлекательный результат из значения SELECT

Нам нужно отформатировать наше значение SELECT, чтобы оно выглядело серьезно и привлекательно. Итак, используя инструмент PHPMyAdmin, отредактируйте значение SQL так, чтобы названиями колонок были реальные слова, и чтобы отображались только нужные пользователям колонки. Переформатированное значение SQL выглядит примерно так:

Что в результате дает:

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

Этап 3: Отображение результатов MySQL на странице электронной таблицы

Класс MySqlExcelBuilder включает в себя функционал, требующийся для того, чтобы добавить значения SQL в страницу электронной таблицы Excel при помощи PDO и PHPExcel. Полный класс находится в сопутствующем zip-файле.

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

* $pdo – это объект данных в PHP, использующийся для создания запроса к базе данных.
* $phpExcel – это объект PHPExcel, использующийся для создания и управления электронной таблицей.
* $sql_pages — удерживает SQL-значение и информацию об имени и форматировании страницы.
* конструктор (не отображен) инициализирует данные из PDO и PHPExcel.

Подготовка каждой страницы

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

Функция add_page используется для добавления значения SQL в страницы с именем:

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

Пример использования пользовательской функции

Данный код является примером того, как следует использовать пользовательскую функцию add_page:

Нижеприведенное изображение отображает нам, как пользовательская функция add_page должна быть определена на электронной таблице.

Этап 4: Разработка электронной таблицы

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

Пользовательская функция getExcel()

Пользовательская функция getExcel() использует PHPExcel для создания отдельных рабочих таблиц из SQL-значений, которые вы определяли в 3 этапе. После того, как будут созданы рабочие таблицы, объект PHPExcel возвращается к запрашиваемому. Ниже можно видеть описание 4 основных отделов пользовательской функции getExcel.

А. Повторение на страницах

Основной цикл данной пользовательской функции повторяется на страницах, которые ранее были добавлены посредством add_page. В каждом повторении, в объекте phpExcel создается последующая страница, а затем добавляются данные. Пользовательская функция createSheet в PHPExcel используется для создания новых рабочих таблиц для каждой ранее добавленной страницы.

Изображение, приведенное ниже, отображает нам процесс взаимодействия кода с таблицей. column_map обсуждается далее в руководстве.

Б. Логика «Начало страницы»

В начале каждой страницы есть специальное форматирование. Сначала для каждой конкретной страницы из базы данных извлекается строка, которая выполняет задачи, нужные для произведения форматирования в начале страницы. Она вовлекает пользовательскую функцию mapColumns, которую мы обсуждали в этапе №1. PHPExcel, как и Excel, использует пару БукваЧисло для определения конкретной ячейки. В MySqlExcelBuilder это рассматривается в виде ключа ячейки (cellKey). Ключ ячейки создается за счет соединения заголовка столбика с числом строчки.

Некоторые дополнительные аспекты, которые следует учитывать в вышеприведенном отрывке кода:

* setCellValue – вносит актуальное значение в поле. Учтите, что ячейка является частью рабочей таблицы. Определенная ячейка определяется переменной cellKey.
* getStyle – данный параметр возвращает данные для атрибута стиля конкретной ячейки, поэтому данным значением можно управлять.
* getColumnDimension – это метод объекта рабочей таблицы. Ширина колонки привязана к переменной col.

Читать еще:  Расчет в экселе

В. Заполнение данными

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

Г. Добавляем формулы

Последняя часть getExcel() показывает нам, как в рабочую таблицу PHPExcel добвалять формулы. В данном случае, здесь все относится к колонке. PHPExcel вносит формулы в ячейки точно также, как вы бы вносили их в рабочую таблицу в Excel. Значение ячеек начинается со знака равенства (=), а затем следует формула. В данном случае, у нас идет сумма нескольких ячеек. Смотрим дальше:

А вот так выглядит код:

Этап 5: Вносим последние штрихи

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

Этап 6: Сохранение файла

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

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

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

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

Спасибо за то, что были с нами!

Вам понравился материал? Поблагодарить легко!
Будем весьма признательны, если поделитесь этой статьей в социальных сетях:

Экспорт из Excel в MySQL

Дата публикации: 2015-10-27

От автора: в этом уроке мы рассмотрим экспорт из Excel в MySQL на PHP. В одном из уроков нашего сайта, мы с Вами изучали библиотеку PHPExcel, которая используется для работы с таблицами Microsoft Excel, используя язык PHP. При этом на примере создания прайс-листа для сайта, мы научились переносить данные из СУБД MySql в данные таблицы. Но очень часто при разработке сайтов, необходимо выполнить обратную задачу – выгрузить данные из таблиц Microsoft Excel в базу данных СУБД MySql. Поэтому в данном уроке мы займемся решением данной задачи.

Установка библиотеки PHPExcel

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

При этом если Вы не знакомы с библиотекой PHPExcel и с основами работы с ней, то настоятельно рекомендую просмотреть вышеуказанный урок, так как сегодня мы не будем тратить время на изучение основ. Для данного урока мы будем использовать тестовый сайт, который написан с использованием объектно-ориентированного подхода и шаблона проектирования MVC. Сейчас я не буду приводить его код, так как он будет доступен Вам в дополнительных материалах к уроку. В функционале данного сайта описан механизм загрузки на сервер файлов формата Excel и соответственно, данный механизм нам необходимо доработать, а именно, после успешной загрузки файла на сервер, необходимо прочитать содержимое данного файла и перенести данные из необходимых столбцов таблицы Excel, в базу данных MySql.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

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

Загрузка в excel из php. PHP – выгрузка данных в Excel из базы MySQL. Экспорт данных из MySQL в Excel на PHP

.
Способ, представленный в той заметке действительно очень прост, но не всегда может быть удобен.
Есть множество других способов передать табличные данные из PHP в Excel, я опишу тот, который показался мне наиболее простым и функциональным. Нужно особенно отметить, что что я не говорю о генерации xls файла, а лишь предлагаю пользователю открыть полученные данные при помощи Excel так, что пользователи не искушённые в программировании не заметят подлога.

Итак, первое что необходимо сделать — разместить на нашей страничке ссылку на скрипт генерирующий следующие заголовки:
header(«Pragma: public»);
header(«Expires: 0»);
header(«Cache-Control: must-revalidate, post-check=0, pre-check=0»);
header(«Cache-Control: private», false);
header(«Content-Type: application/x-msexcel»);
header(«Content-Disposition: attachment; filename=»» . iconv(«UTF-8», «CP1251», $object->getFileName()) . «»;»);
header(«Content-Transfer-Encoding:­ binary»);
header(«Content-Length: » . $object->getFileSize());

Читать еще:  Excel удалить все пробелы

$object — сферический объект в вакууме, который каждый читатель реализует так, как ему больше нравится. Назначение геттеров getFileName() и getFileSize() понятно из названий. Здесь стоит выделить один неочевидный нюанс (спасибо за то что напомнил об этом) — getFileName() конечно может возвращать любое имя файла, но если вы хотите что бы браузер предложил открыть полученный контент в Excel, то расширение файла должно быть xls.
Ничего нового я пока не рассказал, все это придумано до меня, впрочем как и то что будет описано ниже.
Как верно было отмечено в комментариях к заметке , Excel гораздо быстрее работает с XML. Но самое главное преимущество, пожалуй, все же не в скорости, а в гораздо более широких возможностях. Углубляться в дебри я не стану, а лишь приведу простой пример, и ссылку на подробное описание всех тегов.

Итак, после того как заголовки сгенерированны, нам нужно отдать пользователю собственно данные. Я обычно заворачиваю генерацию таблицы в отдельный метод:
echo $object->getContent();

А таблицу генерирую при помощи Smarty:

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

Подробнее о структуре XML используемой в MS Excel можно почитать в

.
Способ, представленный в той заметке действительно очень прост, но не всегда может быть удобен.
Есть множество других способов передать табличные данные из PHP в Excel, я опишу тот, который показался мне наиболее простым и функциональным. Нужно особенно отметить, что что я не говорю о генерации xls файла, а лишь предлагаю пользователю открыть полученные данные при помощи Excel так, что пользователи не искушённые в программировании не заметят подлога.

Итак, первое что необходимо сделать — разместить на нашей страничке ссылку на скрипт генерирующий следующие заголовки:
header(«Pragma: public»);
header(«Expires: 0»);
header(«Cache-Control: must-revalidate, post-check=0, pre-check=0»);
header(«Cache-Control: private», false);
header(«Content-Type: application/x-msexcel»);
header(«Content-Disposition: attachment; filename=»» . iconv(«UTF-8», «CP1251», $object->getFileName()) . «»;»);
header(«Content-Transfer-Encoding:­ binary»);
header(«Content-Length: » . $object->getFileSize());

$object — сферический объект в вакууме, который каждый читатель реализует так, как ему больше нравится. Назначение геттеров getFileName() и getFileSize() понятно из названий. Здесь стоит выделить один неочевидный нюанс (спасибо savostin за то что напомнил об этом) — getFileName() конечно может возвращать любое имя файла, но если вы хотите что бы браузер предложил открыть полученный контент в Excel, то расширение файла должно быть xls.
Ничего нового я пока не рассказал, все это придумано до меня, впрочем как и то что будет описано ниже.
Как верно было отмечено в комментариях к заметке о генерации xls в PHP , Excel гораздо быстрее работает с XML. Но самое главное преимущество, пожалуй, все же не в скорости, а в гораздо более широких возможностях. Углубляться в дебри я не стану, а лишь приведу простой пример, и ссылку на подробное описание всех тегов.

Итак, после того как заголовки сгенерированны, нам нужно отдать пользователю собственно данные. Я обычно заворачиваю генерацию таблицы в отдельный метод:
echo $object->getContent();

А таблицу генерирую при помощи Smarty:

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

Подробнее о структуре XML используемой в MS Excel можно почитать в

У многих при работе с PHP в связке с MySQL возникает такая потребность, как экспорт данных из базы в формат xls, для того чтобы люди, которым нужны эти данные, обрабатывали их в программе Excel или просто пользователям было удобно смотреть эти данные. Недавно у меня возникла такая потребность и сегодня я расскажу, как это дело можно реализовать.

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

Для начала приведу пример конечного xls файла, в программе Excel выгрузка будет выглядеть примерно так:

Другими словами, никаких картинок, стилей выгружено не будет, только заголовки столбцов и сами данные.

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

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

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

А еще чтобы Вы понимали, какие данные я выгружаю, приведу пример простой таблицы в БД (у меня ее название test ):

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector